2

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

  • From YOUR new SERVER, Additional information request, please. Post on pastebin.com or here. A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, for server tuning analysis. – Wilson Hauck Oct 15 '18 at 23:44
  • 2nd request. From YOUR new SERVER, Additional information request, please. Post on pastebin.com or here. Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, for server tuning analysis. – Wilson Hauck Nov 10 '18 at 20:28
  • Please consider posting data requested Nov 10 to allow Suggestions specific to YOUR new SERVER to be provided after analysis of the data only YOU can post for us to analyze. You are probably asking yourself why should I post anything. There are more than 300 GLOBAL STATUS and more than 300 GLOBAL VARIABLES that determine in large part how your MySQL will perform. Consider the following link to a Review posted by one of my customers, instagram.com/p/BqicW3Hn_5Y then work with us, please. – Wilson Hauck Dec 23 '18 at 19:18
0

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.

  • This is the results I am getting : real 0m0.015s user 0m0.004s sys 0m0.000s – Chris Oct 10 '18 at 12:54
  • So there is no latency or connection issue between your two servers. Try to optimize my.cnf / MySQL Configuration with Wizards or Best Practices such as Percona Configuration Tool Wizard. – Daniel Gordi Oct 10 '18 at 13:27
0

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

0

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?

  • I understand, I edited my question and added my my.cnf. – Chris Oct 10 '18 at 12:52
  • @chris On Oct 15 and Nov 10, we have asked for additional information to try to help you improve your response time. Until you post the data, we do not know. Tuning is a very intense subject that can not be supported with ONE QUESTION and ONE ANSWER. – Wilson Hauck Nov 20 '18 at 22:21
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.