mysqldump extremely slow when using a private ip

Question

I am facing a very strange issue with Mysql.

I have two cloud server and they are in the same region and VPC network so the internal connection speed is quite fast. Both servers are running CentOS 7.6 I performed a simple test using scp to copy one large file between them and the speed is about 160MB/s.

Then I installed a MySQL 5.7 on one of the server. I imported a database(about 40GB) into it.

The next time when I tried to do a mysqldump (locally on the db server) I found the speed is very slow. It is about 4~5 MB/s. It took over 30 minutes to complete.

Here is the command I used:

mysqldump -q --master-data=2 --single-transaction -utest -p'xxxxx' -h192.168.1.47 testdb > testdb.sql

192.168.1.47 is the server private IP.

Then I removed "-h192.168.1.47" part and use "localhost" instead, the speed is very fast and it was doing about 20~30MB/s. It only took six minutes to dump the database.

I tried to dump the db over network on my other server and it is also very slow.

My question: Is there any settings or limitation on the Linux side or MySQL setting that may cause this behavior? Below is mysql configure file.

symbolic-links=0

skip-name-resolve
bind-address=192.168.1.47
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
table_open_cache=4096
open_files_limit=65535
max_connections=1500
interactive_timeout=3600
wait_timeout=3600
interactive_timeout=3600
innodb_lock_wait_timeout=300
max_allowed_packet=64M
innodb_log_file_size=512M
innodb_log_buffer_size=1M
tmp_table_size=1024M
max_heap_table_size=1024M
innodb_buffer_pool_size=80G
query_cache_type=0
query_cache_size=0
log-bin=/data/mysql/dbmaster
server-id=1
binlog_format=mixed
expire_logs_days=5


log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Answer 1

mysqldump will use named pipes for localhost, rather than TCP. You can double-check that this is the issue by comparing np:localhost with tcp:\\localhost. (I think that's the right syntax for np: but you might need to specify a filename, say in /tmp. I read something about tcp:(local) and np:(local) somewhere and am not sure if that was literal, worth a try if my suggestions don't work.) If tcp:\\localhost matches using your IP address, then the difference is the overhead of using the TCP stack rather than named pipes.

Note that a 40GB database is 40GB on disk, which is a relatively compact representation. In SQL that takes WAY more bytes! As it says in the documentation for mysqldump, it's not the efficient way to backup a database. The efficient way is to back up the file structure that the SQL database uses (which is far more efficient in many ways, including allowing incremental backups.)

mysqldump is useful because it's easy, and is fine for proof-of-concept work, but shouldn't be used as a routine backup method for anything other than small databases.

If the performance of tcp://localhost is fast (similar to just 'localhost') then verify that communication to your IP is using the loopback interface using this:

tcpdump -i lo -n

Note that this will generate LOTS of output if traffic is using the loopback interface (which is what you want for best performance.) So do it in a different window, or pipe or redirect it, and be prepared to abort the mysqldump right away. If this does NOT generate lots of output. You'll also have to weed out the normal background traffic. If there's lots of background traffic, try something like this:

tcpdump -i lo -n > junk

Run that for two seconds and ^C when mysqldump isn't running and check the size of junk. Try it again for two seconds when it is, and junk should be huge in comparison, if it's using the loopback device (which you want.)

If it's not using the loopback device for your external IP address, something is wrong with your local system networking configuration and you'd want to open a new question on that.