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

Question

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.

Answer 1

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?

Answer 2

I experienced same problem, and used Google Chrome PageSpeed and YSlow to find out my problems.

Basically, I was doing these steps below:

Try installing PHP APC, set APC shm size to 256Mb (There are a lot of guide for it). Then make sure KeepAlive is On for Apache, and also activate Apache compression too (via Apache mod_deflate).

Answer 3

There are numbers of things you can do:

• Tweak performance of mysqld with /etc/my.cnf:
  • major/MySQLTuner-perl · GitHub
  • MySQL Tuning Primer Script in Launchpad
• Enable caching:
  • PECL :: Package :: APC
  • memcached - a distributed memory object caching system
  • Varnish Community | Varnish makes websites fly!

Try to explore following as well:

• Drupal Groups | Meetup, discuss, plan, and work on Drupal and Drupalcamps
• Drupal Answers
  • High performance | Drupal Groups

Answer 4

My suspicion is that there is not enough memory for PHP to run. Check the memory_limit in PHP.INI file. CHange it to -1 (unlimited memory) to see if the performance improves. If it doesn't then you are in for a long haul. Here are links to my published articles on improving Drupal Performance

1. Tuning LAMP stack to boost performance of Drupal
2. Improving the Performance of Drupal Sites

Answer 5

As this question is still attracting answers, I will answer it myself, since I sort of solved it back then.

Basically as soon as I moved Mysql to the same vm as Drupal, everything became lightning fast. So the issue was the huge number of sql queries Drupal was issuing for every page load = bad architecture.