Mysql – Separating theSQL server from LAMP and performance degradation

lampMySQL

I'm currently using LAMP stack (over Centos 6.5) for our sites, and I want to try separating the mySQL from the "web server".

Until now I was using DigitalOcean's VPS with quad core CPU and 8GB of RAM.
Yesterday I've created a new DigitalOcean VPS for a single staging environment (at the moment) with the following specs and utils:

  • 2 CPU cores
  • 2GB RAM
  • Centos 6.7
  • Private network with the "web server" (Ping time: min/avg/max/mdev = 0.352/0.481/0.662/0.073 ms
  • mySQL 5.6 (same as it was on the current server)
  • Same my.cnf configuration with 2 changes:
    1. Added: bind-address=X.X.X.X (the private network's internal IP address)
    2. Changed innodb_buffer_pool_size from 2GB to 1GB (because we have less RAM now..)

The web server is running a Drupal site, and has APCu, opCACHE and memcached installed.

When checking the load time with Chrome it seems that the site is now being loaded slower in about 0.5 – 1.5 sec.
Unfortunately NewRelic proves me right (see the attached screenshot)

enter image description here

Since this is the first time I'm configuration a remote mySQL server, I'm wondering if this is something I've missed? any configuration or adjustment that may improve the performance?

Best Answer

I'm not sure why you'd be surprised since your MySQL server has less RAM assigned to it, and placing it on a different machine adds load on the network interface.

You should monitor your network activity to see if it causes any kind of bottleneck.

Depending on your SQL queries and PHP scripts, there could possibly be ways to better optimize those tasks in terms of query performance as well as improving the results sent back to PHP to reduce the network load.

Related Topic