MySQL Bugs: #115646: Discrepancy with Data import speed across regions

Bug #115646	Discrepancy with Data import speed across regions
Submitted:	19 Jul 10:16	Modified:	22 Aug 10:40
Reporter:	Lakshmi Bhavya K	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	any	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Inter-region mysqldump file import via mysql client is slower than importing over ssh.

How to repeat:
To describe further, Mysqldump file is located in Virginia region (Vir) and mysql RDS instance is in Sydney region (Syd):

== SCENARIO 1: Restoring the dump directly to RDS in SYD from EC2 instance in VIR
== SCENARIO 2: Restoring the dump to RDS via SSH connection from EC2 instance in VIR through EC2 instance in SYD

== SCENARIO 3: Copying the content of the dump from EC2 instance in VIR to EC2 instance in SYD and piping the content copied to restore the content to RDS from EC2 instance in SYD.

SCENARIO 1 (Direct import across regions):

[root@ip-172-31-47-41 ec2-user]# date;pv myemp2.sql | mysql -h xxxxx.c7wrfnlyu5kc.ap-southeast-2.rds.amazonaws.com -u root -p******* employee --max-allowed-packet=1073741824 --net-buffer-length=1048576; date
Wed Jun 26 14:47:46 UTC 2024
mysql: [Warning] Using a password on the command line interface can be insecure.
 160MiB 0:01:20 [1.99MiB/s] [===========================================================================================================================================>] 100%            
Wed Jun 26 14:49:14 UTC 2024
[root@ip-172-31-47-41 ec2-user]# 

Time taken :1:28
speed: 1.99MiB/s

SCENARIO 2 (import over ssh)

[root@ip-172-31-47-41 ec2-user]# date;pv myemp2.sql | ssh -i "ec2-sydney.pem" ec2-user@ec2-54-252-226-80.ap-southeast-2.compute.amazonaws.com mysql -h xxxxxxx.c7wrfnlyu5kc.ap-southeast-2.rds.amazonaws.com -u root -p******* employee;date
Wed Jun 26 14:51:09 UTC 2024
mysql: [Warning] Using a password on the command line interface can be insecure.                                                                                          ]  0% ETA 0:57:03
 160MiB 0:00:34 [4.65MiB/s] [===========================================================================================================================================>] 100%            
Wed Jun 26 14:51:44 UTC 2024
[root@ip-172-31-47-41 ec2-user]# 

Time taken: 35 seconds
Speed: 4.65MiB/s

SCENARIO 3 (import over sh with streaming)

[root@ip-172-31-47-41 ec2-user]# date;pv myemp2.sql |  ssh -i "ec2-sydney.pem" 
ec2-user@ec2-54-252-226-80.ap-southeast-2.compute.amazonaws.com "cat | mysql -h xxxxxx.c7wrfnlyu5kc.ap-southeast-2.rds.amazonaws.com -u root -p******* employee";date
Wed Jun 26 14:54:03 UTC 2024
mysql: [Warning] Using a password on the command line interface can be insecure.                                                                                          ]  0% ETA 0:57:03
 160MiB 0:00:32 [4.89MiB/s] [===========================================================================================================================================>] 100%            
Wed Jun 26 14:54:36 UTC 2024
[root@ip-172-31-47-41 ec2-user]#

Time taken: 33 seconds
speed: 4.89MiB/s

Scenario 1: 
No SSH agent is involved, and the content is sent directly by MySQL Client and it looks like it is not able to copy content as quickly as it is being copied by SSH.
Scenario 2 and 3 almost took the same time and it looks like both are following the same approach.

The contents of the dump file is copied from the first EC2 (IAD) to Second EC2 instance via SSH. No actual intermediate file is created. The content is streamed/sent to the RDS instance by the Second EC2 (SYD) via mysql client as it is (along side) being copied.

Though it is expected to see a less data import speed with cross region imports, why mysql client is not able to match with ssh speed with highest max allowed packet size and net buffer length.

Suggested fix:
Improve the speed of inter-region mysql dump import process to come closer to the speed of performing the same over ssh.

This issue was initially reported couple of months ago here: https://forums.mysql.com/read.php?24,724532

No replies since then.

> Suggested fix:
> Improve the speed of inter-region mysql dump import process to come closer to > the speed of performing the same over ssh.

In my understanding, importing data via an intermediate SSH host should be noticeably slower than a direct import, so I'd re-phrase the suggestion simply to: "Improve the speed of inter-region mysql dump import process"

Hi Mr. K,

We truly do not see what changes could we make in mysqldump to improve that speed.

mysqldump simply reads and writes from the files and devices as supplied by the administrator.

If you use a pipe, it will read / write from / to that pipe ......

mysqldump can not override network problems.

Not a bug.

Hi,

One last notice.

Do note that mysqldump has lot's of options ...........

There are those related to compression, related to the output options and also some variables that can improve significantly the buffering of the entire process.

You can find the most optimal combination of those for the slow networking .......

It is all properly described in our Reference Manual.

mysqldump is not used in the mentioned pipeline. we take the already prepared dump. 
if you meant mysql client tool, we tried a lot of different options (buffering, compression, etc) and they had absolutely no effect.

the network infrastructure is hidden from me (probably Lakshmi has more info on that), but i can assure that bandwidth between regions is high enough (around 1Gbit/sec, measured with iperf3) and data import thru the ssh pipe definitely doesn't show an  speed that might cause a bottleneck in the network.

Hi,

Please re-read our comments on how mysql client and mysqldump read / write to the files and streams.

Simply, they do not differentiate one source (or destination) from another. They both just simple read and write from / to the source and destination. Our client programs do not differentiate whether it is one region or another.

And there are client options in both files that can accelerate the process.

We are still waiting on the feedback from Development on this issue, but due to the vacations, we doubt that the answer will come soon.

Not a bug.

I completely understand your point. If you only could make a similar test within your infrastructure where you know the network is set up correctly..

import via ssh also uses same network and the speed is better with it.
Network  bandwidth is upto 10 gbps. As Alex mentioned we measured with iperf as well.
More over these machines are an idle one's and we tested multiple times and got the same result.

Are the statements in the dump file single inserts or multirow inserts?  aka was dumpfile created by mysqldump --extended-insert=0 or 1 ?

I could imagine if --extended-insert=0 the *number of queries* in dumpfile is higher because each row is inserted in one command and smaller,  hence back and forth from client <> server can be more latency in total?

the dump was made with extended inserts switched on

Hi,

This is still not a bug in our code.

Our Development is in the full agreement with this statement.

Could you please perform a test in your environment and share the results?

Hi,

We performed tests with some other Unix commands and got similar slowdown over the regions .......

The numbers are irrelevant ..... slowdown is still there with non-MySQL products .......

Can you share what commands/products did you test exactly?

Hi,

We have received a final response and a final opinion from our Development team.

It says:

"
This looks like AWS support case if anything !
"

Not a bug.

I understand this might be out of the scope of this bug, but do you have an explanation of why using SSH proxy host speeds up the transfer between regions?

Hello Team,

This is Sunny (Sr. Cloud Network and Security engineer) from AWS. I have been working on this issue quite a while.

I have performed several networking tests to verify the transfer speed. Let's take out the scenario of inter region, within same region itself the speed is stagnant. 

We tried increasing the RDS as well as source EC2/VM's RAM, ROM, Bandwidth, IOPS, CPU etc. but speed is capping under ~5-6 MB/s. We also tried different OS(mariadb and postgres) but no luck. What else you would like to suggest us to increase the transfer speed. 

I would request you to please look into this as it is affecting many customers. If you need any proof, data or information kindly let us know. We will be more than happy to help.

Looking forward to your response.

Hi Mr. Bhardwaj,

Thank you for writing to us.

We do not doubt your credibility nor know-how, nor should you doubt that our intricate knowledge of how mysqldump and other client programs function.

If you read our comments above, you would note that mysqldump does not know anything about single or multi-region I/O reading and writing. It is simply using the file(s)  that user and operating system  provide for the operation.

You can find more about it in our comments above.

If you have read the previous comments, you would have noted that our Development has taken good look at the problem and do not see that we could do anything in this regard.

Also, if you have read all of the above comments, you would have noted that all our client programs, including mysqldump, have many options that can be configured. There are all there for you to experiment with them and most of them do influence the performance.

You could also experiment with *nix general command line programs and see whether they have the same performance over the same regions.

Do note that both input and output to and from mysqldump can be quite large. Therefore, it is less immune to the inter-region speed than programs that simply copy a file ...... 

At this moment we do not have a single unsolved verified bug on the performance of our command-line client programs.

Our team that makes those programs have already taken a deep look at this report and provided their opinion.

For us, this is still not a bug.

Hello Team,

Thank you for your response. 

We have already tried tweaking parameters like nt buffer lenght, max packet size , etc. but no luck.

Example command: mysqldump -h xxxx.c02eyjujxzvp.ap-southeast-2.rds.amazonaws.com -uxxxx -pxxxx employees --extended-insert --net-buffer-length=1MB max_allowed_packet=1G > dump3008.sql

Could you please guide us on "*nix general command line programs" so that we can try this as well.

Hell Team,

We have been seeking for your help in this matter. Could you please guide us on "*nix general command line programs" so that we can proceed further with our testing.

Hi,

We already informed you that we checked our findings with two other backup programs that we have .....

We have tested (no longer supported) mysqlpump and make it run multi-threaded , each schema in it's own thread. We encountered no problems.

But, the best thing to try is MySQL Enterprise Backup. It was very , very fast .......

Also, one more very important information.

This is not a forum for free support.

This is only a forum of the bugs that we can repeat. 

We did not manage to repeat what you reported.

Hence, please consider our free support site or paid support site.

We are providing you , hereby, with both.

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

For details on getting support for MySQL products see http://www.mysql.com/support/
You can also check our forums (free) at http://forums.mysql.com/

Thank you for your interest in MySQL.

Dear MySQL team,

We still cannot understand what you have tested - that's why we continue to ask you for a bit of collaboration.

Your suggestion on multi-threaded mysqlpump or Enterprise backup does not also seem to be relevant b/c you mention schema per thread backup, while we're testing a single schema dump/restore.

Can you pls perform the inter-region restore (the far regions from each other the better) with:
1. a direct connections:
linux server with a single schema dump (region1) -> mysql server (region2)
2. a connection with an intermediate instance:
linux server with a single schema dump (region1) -> linux server (region2) -> mysql server (region2)

and tell us the measured restoration speeds for both cases.

It's not a support request. It's an attempt to make sure we're on the same page to resolve the bug or prove that there's no bug here.

Thanks for underestanding

HI,

First of all, both mysqlpump and MySQL Enterprise Backup work just find on the single schema.

We tested them .....

Next, our Development stated several times that a problem is not in our programs.

Hence, it is a final conclusion that this is not a bug, but a stricly AWS support problem.

And what was the results of your tests? Was the speed in #1 slower than in #2, equal or faster?

Very slightly slower .......

We can't share the results, since those would reveal our internal and protected servers.

Please, do not ask any more questions .......

This is not a forum for free support , as we underlined so many times ......

This is also  definitely NOT a forum for asking questions.

It is just for the repeatable test cases. Since we can not repeat, this is a closed report.

Dear MySQL Verification Team,

I was able to reproduce this issue within the Oracle Cloud (Ash means Ashborn region, Syd means Sydney region):

Syd-Syd:
[root@syd20240820 ~]# date; pv dump.sql | mysql -h 10.108.8.142 -u admin employees; date
Wed Aug 21 11:28:54 GMT 2024
160MiB 0:00:48 [3.29MiB/s] [===================================================================================================================>] 100%
Wed Aug 21 11:29:43 GMT 2024

Ash-Syd:
[root@iad20240820 ~]# date; pv dump.sql | mysql -h 10.108.8.142 -u admin employees; date
Wed Aug 21 11:25:23 GMT 2024
160MiB 0:02:21 [1.13MiB/s] [===================================================================================================================>] 100%
Wed Aug 21 11:27:52 GMT 2024

Ash-Syd-Syd:
[root@iad20240820 ~]# date; pv dump.sql | ssh 10.108.8.39 mysql -h 10.108.8.142 -u admin employees; date
Wed Aug 21 11:24:27 GMT 2024
160MiB 0:00:51 [3.09MiB/s] [===================================================================================================================>] 100%
Wed Aug 21 11:25:19 GMT 2024

As you can see, the direct connection is three times slower than a connection thru the intermediate host.

I've also made and analysed the pcap dumps of each transfer and found out that the proxy connection differs from the direct connection by constantly adjusting the tcp window size (check the screenshot attached; just a note - in that dumps I used netcat instead of ssh).

10.108.8.142 is an Oracle Cloud managed mysql server.

Then I've launched a local mysql server on the linux instance in the Sydney region and found out the same - no tcp window size adjustments were made during the data transfer:
Wed Aug 21 16:18:55 GMT 2024
 160MiB 0:01:51 [1.44MiB/s] [===================================================================================================================>] 100%
Wed Aug 21 16:20:52 GMT 2024

Then I've added netcat to the pipe and the speed has dramatically increased:
[root@iad20240820 ~]# date; pv dump.sql | nc 10.108.8.39 8888; date
Wed Aug 21 16:43:46 GMT 2024
 160MiB 0:00:37 [4.23MiB/s] [===================================================================================================================>] 100%
Wed Aug 21 16:44:26 GMT 2024

One thing you were right with - it's not a bug with mysql client tool, it's rather a bug with mysql server. Please, make your tests again and confirm.

tcp window size adjustments difference

Attachment: tcp-window-size.png (image/png, text), 12.35 KiB.

Hi Mr. Kolesnik,

We have been recently informed that  one of the projects for OCI, that is in progress, is the improvement in the performance between the OCI regions.

Hence, that project is already in the progress.

However, this is not a forum for the problems with OCI.

If you are OCI customer, you can raise the issue in MOS and ask to be informed on the progress of that project.

Thank you for your interest in MySQL, MDS and OCI.

In this particular case the OCI inter-region performance is not the issue. Please, stop pissing me off and start working on the bug we reported. If you still consider it's not a bug, explain why mysql server is not adjusting the tcp window size during the data transfer.

I will definitely raise a case within OCI and hopefully they will be more respectful.

Hi,

We have raised your problem once again with our Development team.

Hence, we shall have to wait for their answer.

Hi,

We also urge you to raise the case with OCI administrators.

If you have support for your MDS, you would do even better by creating a ticket for this problem.

SR 3-37865701981 has been created within MOS

Summarizing the explanation from the Oracle support team:

The inter-region speed decrease is expected b/c of the sequential nature of the mysql client/server communication (client waits for the server confirmation after each command sent) and much higher RTT (hundreds of milliseconds) between distant regions. Piping the data via an intermediate host moves the server confirmation messages to the same region where the 
server is located with lower RTT (singles of milliseconds). This allows the mysql client to send data much faster. So, the intermediate host works like a huge buffer in this case.