Mysql – How to speed up Drupal 7 with remote DB (not DNS resolving)

drupal7MySQLnetworkingremoteslow-connection

we have Drupal 7 on Ubuntu LTS virtual machine (VMWare) with MySQL 5.1 server on the same physical server, but on a different virtual machine.

The Drupal website is currently under development, therefore there is practically no traffic, however, despite ping and shell access being lightning fast, the Drupal site is running extremely slowly (>5 sec to load any page). After research here and elsewhere I understand that the problem is with the fact that SQL is accessed via network interface, which Drupal apparently does really badly.

I have checked DNS resolution and there should be none either from Drupal side (db host is given by ip, there is a dns entry in /etc/hosts file) nor from MySQL side (skip-name-resolve is on, drupal client is granted access by ip address).

The Drupal devel querylog says:

Executed 336 queries in 5993.89 ms. Queries exceeding 5 ms are
highlighted. Page execution time was 7015.35 ms. Memory used at:
devel_boot()=5.53 MB, devel_shutdown()=93.35 MB, PHP peak=94 MB.

The wierd thing is that for a single page load about 2/3 of queries are fast, others are exceedingly slow:

1.765 DrupalDatabaseCache::getMultipleP A E
SELECT cid, data, created, expire, serialized FROM cache_bootstrap WHERE cid IN (:cids_0)
default
575.825 DrupalDatabaseCache::getMultipleP A E
SELECT cid, data, created, expire, serialized FROM cache_bootstrap WHERE cid IN (:cids_0)
default
13.585 DrupalDatabaseCache::getMultipleP A E
SELECT cid, data, created, expire, serialized FROM cache_bootstrap WHERE cid IN (:cids_0)
default
466.9914 DrupalDatabaseCache::getMultipleP A E
SELECT cid, data, created, expire, serialized FROM cache WHERE cid IN (:cids_0)
default
1.841 drupal_lookup_pathP A E
SELECT source FROM url_alias WHERE alias = :alias AND language IN (:language, :language_none) ORDER BY language ASC, pid DESC
default

The situation is remedied for anon access by enabling Drupal cache. The whole of the admin interface, however, is still as slow.

It is the current company policy to have DB server separately. Could there be any other options to speed up Drupal?

The default database configuration says driver 'mysql', maybe I should change it to 'mysqli' or 'pdo_mysql'? If I change it to mysqli, however, the database.inc complains that the relevant driver doesn't exist — anything to install there specifically for drupal, as mysqli & pdo are both enabled in PHP itself.

Best Answer

I think there are two areas where you can optimize your site:

  1. Reduce the individual queries by tuning your MySQL database. I would start with mysqltuner.pl, which will show you obvious problems with your installation and provide tuning hints.
  2. Reduce the number of queries. I know of one big Drupal installation where this was done using memcache on the web server host.

Did you check Managing site performance at drupal.org?