I'm following the guide at http://devdocs.magento.com/guides/v2.1/install-gde/prereq/mysql_remote.html and I completed all the steps which worked but now my website is extremely extremely slow. It went from loading pages in less than 4 seconds to now loading pages in over a minute. I changed the database information back to the local database on my web server and it went back immediately to loading the website in a few seconds.

How can moving the database server to a separate dedicated server make things so slow? Am I missing something?

The Old Database Server

  • Centos7
  • 24 core processors
  • Plesk 12.5
  • 48GB Ram for mysql

The New Database Server

  • Centos7
  • 24 core processors
  • No Plesk
  • 100GB Ram for mysql

Things I have checked so far

  • No slow queries or mysql errors when switched over to the new database
  • Mysql configuration is the same on both servers
  • Tested on a different system remotely and it performed well using the same configuration for magento with the difference of the mysql server it was pointed to.

UPDATE: Mariadb log is at https://pastebin.com/rvNDh4vW Mariadb slow query log is at https://pastebin.com/mVibiHMe

  • need to see more logs or profiler – MagenX Apr 9 '17 at 20:16
  • @MagenX I updated my post to include my mariadb logs – user3610374 Apr 9 '17 at 20:42
  • 1
    This might be more suitable for server fault since the issue doesn't seem to be related to Magento but to your cluster config. – mbalparda Apr 9 '17 at 20:46

Is the new dedicated server local to the existing server (running the PHP code)? If not, and it's making connections over the web, that means every DB query is adding a significant amount of network latency (anywhere from 10 to 70 ms, depending on the distance). That could possibly explain the sudden slowness.

If you're running a multi-server environment, the servers in active discussion need to be more or less physically adjacent, and connected with a high-bandwidth link, to minimize latency.

One thing you can try is to SSH into your web server, and ping the IP (as you've configured it in connection info) of the DB server. That will tell you the network latency.

| improve this answer | |
  • it is a remote link, over a p2p vpn tunnel, there is about 90 to 100ms delay in that link using tcpdump on the link. it shows alot of mysql activity. but SHOW PROCESSLIST; does not show that many active queries. when I run the same test on the test system that has about the same latency it performs. SHOW PROCESSLIST; shows database queries going on. – user3610374 Apr 9 '17 at 15:52
  • If your DB link has 100 ms delay, that means every query that would normally take 1 ms suddenly takes 101 ms. Magento runs a lot of queries. Like I said, if you're running a multi-server environment, the servers should be basically physically adjacent. – Ryan Hoerr Apr 10 '17 at 12:58

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