Hi,
A MySQL database dump is located on an AWS EC2 instance in the us-east-1 (N.Virginia) region (ec2-use1). The RDS instance is located in the ap-southeast-2 (Sydney) region (rds-apse2).
I use the following command to import the data and it performs at a speed of about ~2.5MB/sec, with peaks up to 3:
[user@ec2-use1]$ pv user_service.dump | mysql --defaults-file=mysqlroot.cnf -h rds-apse2 -u sp user_service
However, if I launch an EC2 instance in the RDS region (ec2-apse2) and import the data through a pipe:
[user@ec2-use1]$ pv user_service.dump | ssh ec2-user@ec2-apse2 mysql --defaults-file=mysqlroot.cnf -h rds-apse2 -u sp user_service
the speed doubles (averaging 5.5MB/sec with peaks up to 10):
If I import the data through an SSH tunnel:
[user@ec2-use1]$ ssh -L 3306:rds-apse2:3306 ec2-user@ec2-apse2
[user@ec2-use1]$ pv user_service.dump | mysql --defaults-file=mysqlroot.cnf -h localhost -u sp user_service
the speed is the same as without SSH.
Any ideas why the speed increases when working through the SSH pipe (or why does the mysql tool alone perform slowly)?
I also tried the following:
1. Disabled Nagle algorithm (compiled a library with redefined
socket() function) and loaded it with
LD_PRELOAD - it didn't help:
# pv user_service.dump | LD_PRELOAD=/home/ec2-user/tcp_nodelay.so mysql --defaults-file=mysqlroot.cnf -h rds-apse2 -u sp user_service
^C.5MiB 0:00:23 [2.99MiB/s] [==============>
2. Disabled SSH compression - it didn't change the speed at all:
# pv user_service.dump | ssh -o Compression=no ec2-user@ec2-apse2 mysql --defaults-file=mysqlroot.cnf -h rds-apse2 -u sp user_service
390MiB 0:01:14 [5.20MiB/s]
3. Played with low and high values of
--net-buffer-length mysql's cli parameter - nothing's changed
4.. Increased
--max-allowed-packet value of mysql's cli parameter to its maximum value - didn't help
Regards,
Alex