Ryfujk

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

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.

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

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?

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.