Wednesday, May 9, 2018

rsync, scp, sftp speed test




Related links:

SUMMARY

Use simple encryption protocol. It contributes the most to speed of data transfer.   




Choice
Command
Pros
Cons
#1
scp
  • can be speed up choosing simple encryption
  • can recursively copy directories

#2
rsync
flexible and convenient for directory synchronization
possible, but not easy to configure to NOT to use encryption
#3
sftp
can be speed up choosing simple encryption
can't recursively copy directories


Notice: when running any tool it consumes about 5-10% of CPU at both sender and receiver machines, apparently, doing encryption/decryption.

TEST DETAILS

These are the actual commands and generated output by these tools.

rsync 

default "-a" --archive mode
"-z" compress
rsync -a --progress DIR2REPLICATE root@10.10.10.2:/tmp
   411533312   0%   45.38MB/s    0:26:04
  1407877120   1%   44.41MB/s    0:26:16
  1716748288   2%   42.09MB/s    0:27:36
  2002550784   2%   46.47MB/s    0:24:541
  2382397440   3%   45.31MB/s    0:25:24
  2762407936   3%   45.34MB/s    0:25:15
 rsync -az --progress DIR2REPLICATE root@10.10.10.2:/tmp  
991383915 100%   13.67MB/s    0:01:09
   990955265 100%   14.02MB/s    0:01:07
   202624740 100%   15.42MB/s    0:00:12
   202771784 100%   15.87MB/s    0:00:12
    91676674 100%   12.86MB/s    0:00:06
    91628045 100%   11.76MB/s    0:00:07
  1082301721 100%   16.86MB/s    0:01:01
  1081744094 100%   17.14MB/s    0:01:00
   444531263 100%   13.06MB/s    0:00:32
   444311917 100%   12.97MB/s    0:00:32
    25956199 100%   11.99MB/s    0:00:02
    25387962 100%   16.94MB/s    0:00:01
    94059363 100%   15.51MB/s    0:00:05
    94189273 100%   14.61MB/s    0:00:06
   369550738 100%   16.31MB/s    0:00:21
   370924791 100%   15.96MB/s    0:00:22
   143659839 100%   14.75MB/s    0:00:09
   141681760 100%   14.58MB/s    0:00:09
    74662680 100%   14.45MB/s    0:00:04
    73882769 100%   12.73MB/s    0:00:05
     1809543 100%   13.59MB/s    0:00:00

###
### "-c arcfour" cipher is defined in RFC 4253; it is plain RC4 with a 128-bit key
###
rsync -a -P -e "ssh -T -c arcfour -o Compression=no -x" DIR2REPLICATE root@10.10.10.2:/tmp  
  1081744094 100%   65.35MB/s    0:00:15
   444531263 100%   56.34MB/s    0:00:07
   444311917 100%   61.61MB/s    0:00:06
   369550738 100%   53.94MB/s    0:00:06
   370924791 100%   60.03MB/s    0:00:05
 23319017231 100%   65.89MB/s    0:05:37
 23308793162 100%   64.88MB/s    0:05:42
 11951287020 100%   65.68MB/s    0:02:53
  3453648896  28%   68.11MB/s    0:02:0

 

scp


default "-r" recursive
"-C" compress "-r" recursive
scp -r DIR2REPLICATE root@10.10.10.2:/tmp
100%  193MB  64.4MB/s   00:03
100%  424MB  60.6MB/s   00:07
100%  945MB  63.0MB/s   00:15
100%  945MB  59.1MB/s   00:16
100% 1032MB  64.5MB/s   00:16
100% 1032MB  60.7MB/s   00:17
100%  749MB  53.5MB/s   00:14
100% 1253MB  62.6MB/s   00:20
18% 4615MB  62.6MB/s   05:18
scp -Cr DIR2REPLICATE root@10.10.10.2:/tmp
100%  193MB  16.1MB/s   00:12
100%  424MB  14.6MB/s   00:29
100%  945MB  15.0MB/s   01:03
100%  945MB  14.8MB/s   01:04
100%  424MB  14.1MB/s   00:30
100%  352MB  17.6MB/s   00:20
100%  193MB  17.6MB/s   00:11
100%  135MB  16.9MB/s   00:08
100% 1032MB  17.8MB/s   00:58
100% 1032MB  17.8MB/s   00:58
100%  354MB  17.7MB/s   00:20
100%  749MB  18.3MB/s   00:41
100% 1253MB  18.4MB/s   01:08
  6% 1518MB  17.7MB/s   21:43
"-c arcfour" cipher is defined in RFC 4253; it is plain RC4 with a 128-bit key.

scp -c arcfour -r DIR2REPLICATE root@10.10.10.2:/tmp
100%  424MB 141.3MB/s   00:03
100%  945MB 135.0MB/s   00:07
100%  945MB 189.1MB/s   00:05
100%  424MB 141.2MB/s   00:03
100%  352MB 117.5MB/s   00:03
100% 1032MB 147.4MB/s   00:07
100% 1032MB 147.5MB/s   00:07
100%  749MB 149.8MB/s   00:05
100% 1253MB 156.6MB/s   00:08
100%   24GB 142.0MB/s   02:53
100%  595MB 119.1MB/s   00:05
100%   82GB 138.3MB/s   10:09
51% 9099MB 141.3MB/s   01:01


sftp


default behavior
"-R" to increase request queue length (default is 64)
"-B" to increase read/write request size (default is 32 KB)
sftp  root@10.10.10.2:/tmp
10% 2363MB  57.8MB/s   05:43 ETA
15% 3349MB  58.1MB/s   05:25 ETA
32% 7311MB  59.3MB/s   04:11 ETA
35% 7803MB  60.6MB/s   03:58 ETA
43% 9594MB  62.1MB/s   03:23 ETA
69%   15GB  58.6MB/s   01:55 ETA
77%   17GB  62.1MB/s   01:20 ETA
sftp  -R 128 -B 65536 root@10.10.10.2:/tmp
  2%  551MB  58.9MB/s   06:08 ETA
  8% 1806MB  62.3MB/s   05:28 ETA
41% 9170MB  60.6MB/s   03:35 ETA
56%   12GB  62.6MB/s   02:32 ETA
100%   22GB  62.5MB/s   05:56
"-c arcfour" cipher is defined in RFC 4253; it is plain RC4 with a 128-bit key.

sftp -oCiphers=arcfour root@10.10.10.2:/tmp
3%  711MB 142.5MB/s   02:31 ETA
18% 4115MB 146.0MB/s   02:04 ETA
23% 5156MB 148.1MB/s   01:55 ETA
28% 6379MB 144.6MB/s   01:49 ETA
34% 7672MB 144.0MB/s   01:41 ETA
37% 8389MB 143.7MB/s   01:36 ETA
62%   14GB 143.8MB/s   00:58 ETA
85%   19GB 142.4MB/s   00:22 ETA
92%   20GB 142.3MB/s   00:12 ETA
100%   22GB 144.4MB/s   02:34



TEST ENVIRONMENT

The test was performed between two servers interconnected by private 10 Gbit link with 9000 MTU "jumbo frame". The files copies were large (100's GB) binary files.

iperf network bandwidth test between 10.10.10.2 and 10.10.10.1
Network interface configuration (10 Gbit, MTU 9000)
Server listening on TCP port 5001
TCP window size: 85.3 KByte (default)
------------------------------------------------------------
[  4] local 10.10.10.2 port 5001 connected with 10.10.10.1 port 57279
[ ID] Interval       Transfer     Bandwidth
[  4]  0.0-10.0 sec  11.5 GBytes  9.89 Gbits/sec
[  5]  0.0-30.0 sec  34.6 GBytes  9.89 Gbits/sec
[  4]  0.0- 0.9 sec  1000 MBytes  9.80 Gbits/sec
[  5]  0.0- 8.8 sec  9.77 GBytes  9.53 Gbits/sec
[  4]  0.0- 8.7 sec  10.0 GBytes  9.89 Gbits/sec
[  5]  0.0-86.8 sec   100 GBytes  9.89 Gbits/sec

[root@10.10.10.2]# cat /etc/sysconfig/network-scripts/ifcfg-bond0
DEVICE="bond0"
BOOTPROTO=none
IPADDR=10.10.10.2
NETMASK=255.255.255.192
ONBOOT="yes"
USERCTL=no
TXQUEUELEN=100000
MTU=8912
BONDING_OPTS="mode=1 miimon=200 primary=eth0"


ALTERNATIVE METHODS TO MOVE DATA FAST

Copy directories via netcat: tar | nc. Renders speed  ~251 Mb/s ( = ~1 TB/hr).

### On receiver ###
  nc -v -l 5555  | tar -xvf -
  
### On Sender: test2del - large directory to move ###
  time tar -cvf - test2del | nc -v 10.100.100.2 5555
 
### Output calculated ###
11GB in  27.465 s = 293 MB/s
42GB in 2m51.513s = 249 Mb/s (~1 TB/hr)
42GB in 2m50.630s = 251 Mb/s (~1 TB/hr)


at 1 comment:
Location: Greensboro, NC, USA

Tuesday, March 27, 2018

Hive: converting Netezza datatype to Hive datatypes




PROBLEM: 
Dynamically create target Hive tables and load them with data exported from Netezza. 

SOLUTION: 
To create Hive tables dynamically you need to convert Netezza to Hive datatypes, then proceed with the load.  Here is a datatype conversion table: 

SELECT
DISTINCT
FORMAT_TYPE as NZ_ORIG_DATATYPE,
SQLX..REPLACE(
SQLX..REPLACE(
SQLX..REPLACE(
SQLX..REPLACE(
SQLX..REPLACE(
SQLX..REPLACE(
SQLX..REPLACE(
SQLX..REPLACE(
SQLX..REPLACE(FORMAT_TYPE
,'CHARACTER VARYING' ,'VARCHAR'      )
,'NATIONAL VARCHAR'  ,'VARCHAR'      )
,'REAL'              ,'FLOAT'        )
,'NUMERIC(38,0)'     ,'BIGINT'       )
,'NATIONAL CHARACTER','CHAR'         )
,'INTEGER'           ,'INT'          )
,'NUMERIC'           ,'DECIMAL'      )
,'BYTEINT'           ,'TINYINT'      )
,'DOUBLE PRECISION' ,'DECIMAL(14,10)')
FROM _v_relation_column
WHERE OBJCLASS=4905;
at No comments:
Location: Hicksville, NY, USA

How to identify max precision scale of factional numeric datatypes

PROBLEM:
This is to prevent data loss of fractional datatypes precision during the data transfer into target table, which otherwise will implicit happen. E.g. precision of a decimal datatype will be reduced to fit source value into the target field.
For data migration from source system to another target database, we need to identify precision (p) and scale (s) in of DOUBLE PRECISION, REAL, or FLOAT datatype in order to create target table with explicitly stated datatype DECIMAL (p,s).  

SOLUTION: 
In this case we migrating data from Netezza NPS 7.2 to Hortonworks Hive 1.2 Main idea is to convent fractional part of a number to string and count its length:

select
  max(
    length(
        trim(
          trim(
             to_char(
                  DECIMAL_COL01-int8(DECIMAL_COL01)  /* <-         chop off integers part:    1234.56789 - 1234 = .56789                   */
             ,'9.999999999999999999999999999999')    /* <-         append char with zeros:    .56789 -> 0.567890000000000000000000000000   */
            )                                        /* <-              trim blank spaces:                                                 */
      ,'0')                                          /* <-  trim leading and trailing '0':    .56789                                       */
   )-1)                                              /* <- lenght of a string without '.':                                                 */
as MAX01 from TESTDB..MYNUMERICS;


PUTTING THIS ALL TOGETHER:
/* Source table definition */
CREATE TABLE TESTDB..MYNUMERICS
(
       DECIMAL_COL01 DOUBLE PRECISION,
       DECIMAL_COL02 DOUBLE PRECISION,
       DECIMAL_COL03 DOUBLE PRECISION,
       DECIMAL_COL04 DOUBLE PRECISION,
       DECIMAL_COL05 DOUBLE PRECISION,
       DECIMAL_COL06 DOUBLE PRECISION,
       DECIMAL_COL07 DOUBLE PRECISION,
       DECIMAL_COL08 DOUBLE PRECISION,
       DECIMAL_COL09 DOUBLE PRECISION,
       DECIMAL_COL10 DOUBLE PRECISION,
       DECIMAL_COL11 DOUBLE PRECISION,
       DECIMAL_COL12 DOUBLE PRECISION
) DISTRIBUTE ON RANDOM;

SELECT * FROM TESTDB..MYNUMERICS ORDER BY RANDOM() LIMIT 1000;



Get maximum scale (s) for all fractional columns:
/* Get lenght of FRACTIONAL PART part, e.g. how many digits precision after decimal point */
select
  max(length(trim(trim(to_char(DECIMAL_COL01-int8(DECIMAL_COL01),'9.999999999999999999999999999999')),'0'))-1) as MAX01 --- DOUBLE PRECISION,
,max(length(trim(trim(to_char(DECIMAL_COL02-int8(DECIMAL_COL02),'9.999999999999999999999999999999')),'0'))-1) as MAX02 --- DOUBLE PRECISION,
,max(length(trim(trim(to_char(DECIMAL_COL03-int8(DECIMAL_COL03),'9.999999999999999999999999999999')),'0'))-1) as MAX03 --- DOUBLE PRECISION,
,max(length(trim(trim(to_char(DECIMAL_COL04-int8(DECIMAL_COL04),'9.999999999999999999999999999999')),'0'))-1) as MAX04 --- DOUBLE PRECISION,
,max(length(trim(trim(to_char(DECIMAL_COL05-int8(DECIMAL_COL05),'9.999999999999999999999999999999')),'0'))-1) as MAX05 --- DOUBLE PRECISION,
,max(length(trim(trim(to_char(DECIMAL_COL06-int8(DECIMAL_COL06),'9.999999999999999999999999999999')),'0'))-1) as MAX06 --- DOUBLE PRECISION,
,max(length(trim(trim(to_char(DECIMAL_COL07-int8(DECIMAL_COL07),'9.999999999999999999999999999999')),'0'))-1) as MAX07 --- DOUBLE PRECISION,
,max(length(trim(trim(to_char(DECIMAL_COL08-int8(DECIMAL_COL08),'9.999999999999999999999999999999')),'0'))-1) as MAX08 --- DOUBLE PRECISION,
,max(length(trim(trim(to_char(DECIMAL_COL09-int8(DECIMAL_COL09),'9.999999999999999999999999999999')),'0'))-1) as MAX09 --- DOUBLE PRECISION,
,max(length(trim(trim(to_char(DECIMAL_COL10-int8(DECIMAL_COL10),'9.999999999999999999999999999999')),'0'))-1) as MAX10 --- DOUBLE PRECISION,
,max(length(trim(trim(to_char(DECIMAL_COL11-int8(DECIMAL_COL11),'9.999999999999999999999999999999')),'0'))-1) as MAX11 --- DOUBLE PRECISION,
,max(length(trim(trim(to_char(DECIMAL_COL12-int8(DECIMAL_COL12),'9.999999999999999999999999999999')),'0'))-1) as MAX12 --- DOUBLE PRECISION,
from TESTDB..MYNUMERICS;
Get maximum precision (p) for each column in my this data set. 
/* Get lenght of WHOLE NUMBER part*/
select                                           
 max(length(trim(to_char(int8(DECIMAL_COL01),'999999999999999999999999999999')))) as MAX01 --- DOUBLE PRECISION,
,max(length(trim(to_char(int8(DECIMAL_COL02),'999999999999999999999999999999')))) as MAX02 --- DOUBLE PRECISION,
,max(length(trim(to_char(int8(DECIMAL_COL03),'999999999999999999999999999999')))) as MAX03 --- DOUBLE PRECISION,
,max(length(trim(to_char(int8(DECIMAL_COL04),'999999999999999999999999999999')))) as MAX04 --- DOUBLE PRECISION,
,max(length(trim(to_char(int8(DECIMAL_COL05),'999999999999999999999999999999')))) as MAX05 --- DOUBLE PRECISION,
,max(length(trim(to_char(int8(DECIMAL_COL06),'999999999999999999999999999999')))) as MAX06 --- DOUBLE PRECISION,
,max(length(trim(to_char(int8(DECIMAL_COL07),'999999999999999999999999999999')))) as MAX07 --- DOUBLE PRECISION,
,max(length(trim(to_char(int8(DECIMAL_COL08),'999999999999999999999999999999')))) as MAX08 --- DOUBLE PRECISION,
,max(length(trim(to_char(int8(DECIMAL_COL09),'999999999999999999999999999999')))) as MAX09 --- DOUBLE PRECISION,
,max(length(trim(to_char(int8(DECIMAL_COL10),'999999999999999999999999999999')))) as MAX10 --- DOUBLE PRECISION,
,max(length(trim(to_char(int8(DECIMAL_COL11),'999999999999999999999999999999')))) as MAX11 --- DOUBLE PRECISION,
,max(length(trim(to_char(int8(DECIMAL_COL12),'999999999999999999999999999999')))) as MAX12 --- DOUBLE PRECISION,
from TESTDB..MYNUMERICS;

Outcome: 
The DOUBLE PRECISION columns in this data set is safe to set as DECIMAL(12,10) in target Hive table to guarantee no data loss, which could implicitly happen during the data load transfer. 

RELATED LINKS:
at No comments:

Wednesday, March 14, 2018

Practical network speed test: 1 Gbit v.s. 10 Gbit with 1500 MTU v.s. 9000 MTU

QUESTION:
How much practical network throughput will gain converting from standard 1500 MTU size to  MTU 9000 (aka jumbo frame) at 10 Gbit network?

ANSWER: 
The empirical test shows ~ 3x improvement transferring data over 10 Gbit versus 1 Gbit network having standard MTU 1500.  Changing the 10 Gbit network interface to 9000 MTU gains additional 3x improvement over standard 1500 MTU. 



The speed test was conducted at different network conditions over several days of running iperf3 command. 

SOUND AND CLEAR:

The data heavy environment where large amount of data is moved across should expect ~3x improvement cranking up to 9000 MTU at 10 Gbit interface. Target applications are Big Data systems, database backups, data warehouses, large data analytic. Technically, the MTU has to be set to 8912 to account for 88 (42+46).

Useful commands:


/sbin/ifconfig bond3 mtu 1500 up   
/opt/network-tools/iperf3 -s
/sbin/ifconfig bond3 mtu 8912 up   ### Setup MTU here


Set up config permanently in the interface startup script:
 
[root@serverX~]# cat /etc/sysconfig/network-scripts/ifcfg-bond0
DEVICE="bond0"
BOOTPROTO=none
IPADDR=10.20.30.99
NETMASK=255.255.255.192
ONBOOT="yes"
USERCTL=no
TXQUEUELEN=100000
MTU=8912
BONDING_OPTS="mode=1 miimon=200 primary=eth0"




Related links:


at No comments:
Subscribe to: Posts (Atom)