Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
1  
I suspect your database server doesn't have sufficient resources to do it's job efficiently. How much RAM is allocated to the mysql VM? One tell-tale sign is the high amount of page-execution time. Database servers are known for requiring the most amount of RAM... and not having enough to keep the indexes & such in cache... is a HUGE performance killer. Minimum RAM requirements for any database server... is <<MORE>>. –  TheCompWiz May 9 '12 at 14:03
1  
Are these 336 queries for one single page? –  Oliver May 9 '12 at 14:15
    
Thank you for the clarification question; both VMs have 2GB RAM, Drupal database currently is the first and only one, and as I said is udner development = on average about 2-3 concurrent accesses at any given time. The VM physical server should have no trouble handling that: Dell R710 with 24GB RAM. –  Gnudiff May 9 '12 at 14:26
    
And the MySQL status shows: Uptime: 3191 Threads: 1 Questions: 814 Slow queries: 0 Opens: 1417 Flush tables: 1 Open tables: 64 Queries per second avg: 0.255 –  Gnudiff May 9 '12 at 14:28
    
Yes those queries are from loading a random page. I just checked and loading front page actually does around >1000 queries! :-O –  Gnudiff May 9 '12 at 14:29

3 Answers 3

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?

share|improve this answer
    
Thank you. Mysql tuner just recommended to increase size of some buffers and pools, which I did. That didn't help. Memcache sounds like a plan, will try to implement it, thanks. –  Gnudiff May 10 '12 at 9:58

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).

share|improve this answer
    
Thank you for the answer. Since I needed a solution quickly (and you can see the question is rather an old one), I simply accepted that PHP is extremely bad with remote databases and moved the SQL server to Drupal VM. Maybe some other people who experience the same problem will try out your solution and comment/vote here. –  Gnudiff Jan 3 '13 at 10:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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