MySQL remote connection slow

Question

As the traffic keeps growing on my e-commerce(PrestaShop) and the navigation starts being slow I decided to after reading many posts in here separate my web server from my DB server. I did set up a MySQL remote connection. Both of the servers are on the same datacenter and have less than 1ms ping between each other.

When I switch DB to the new remote server, everything is extremely slow,

I did skip-name-resolve in my.cnf config but nothing noticeable changed at all. any action on the website takes over 10s to load

What am I missing? Is it because of PrestaShop? How can I improve my server availability then? Should I just load balancer between servers with web and DB in the same box?

[client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking #skip_name_resolve skip-name-resolve #bind-address = 127.0.0.1 # # * Fine Tuning # key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 myisam-recover = BACKUP #max_connections = 100 #table_cache = 64 #thread_concurrency = 10 # * Query Cache Configuration # query_cache_limit = 1M query_cache_size = 16M #general_log_file = /var/log/mysql/mysql.log #general_log = 1 log_error = /var/log/mysql/error.log #slow_query_log_file = /var/log/mysql/mysql-slow.log #slow_query_log = 1 #long_query_time = 2 #log_queries_not_using_indexes #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name # chroot = /var/lib/mysql/ # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer = 16M [mysql] #no-auto-rehash # faster start of mysql but no tab completition 

I am not getting any packet loss between the servers.

How could I properly investigate and find the causes of this slowness?

My servers both on Debian 8:
CPU: Intel 2x Xeon E5-2630v3 - 16c/32t - 2,4GHz /3,2GHz
RAM: 128Go DDR4 ECC 1866 MHz
Disques: SoftRaid 2x2To SSD

Answer 1

Check out the MySQL compression protocol - handy when there are tons of records to return to a webpage from an external server (especially a WAN)

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-connections.html

When set to true (or 1), this option enables compression of all information sent between the client and the server if both support compression. The default is no compression (false or 0). If you are connecting using command parameters, the equivalent parameter is --compress (-C). Compression is available for MySQL Shell connections using classic MySQL protocol only. You can set the defaultCompress MySQL Shell configuration option to enable compression for every global session. The MySQL Shell \status command shows whether or not compression is enabled for the session.

Answer 2

Try run a MySQL query manually from your Application server to Database server with mysql command like this and determine how it will takes to run:

• time mysql -u'username' -p'password' -e'show status'
  

Also, Make sure there is no limitation such as Firewall rules for network rate limit.

Answer 3

First of all check settings, if you sure that there no network bottlenecks.

If second server it's fresh installation so possibly nobody adjust settings on this server.

Secondary you can get good results if you have separated traffic.

As example:

• interfaceA of web server connected directly to the DB server interface

• interfaceB of web server used for processing web requests

Answer 4

There's no clear answer to your question since there's just not enough information. However, instead of focusing on the network as the root cause of your problem, I'd rather investigate MySQL configuration on the new server. Usually, it's in /etc/mysql/my.cnf (or similar). Compare both versions of the old and the new installation. Maybe you need to increase the connection pool or something?