2

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.

Gnudiff
  • 533
  • 5
  • 20
  • 2
    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 <>. – TheCompWiz May 09 '12 at 14:03
  • 2
    Are these `336 queries` for one single page? – Oliver May 09 '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 09 '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 09 '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 09 '12 at 14:29
  • Can you please paste some performance details using ab? ab -n10 http://127.0.0.1/ Do you have XCache, APC or eAccelerator installed? If not, please install it (preferably XCache) and compare the ab performance statistics. Note: Please don't down-vote, I can't post the comments (yet). I'll convert it to the answer. – kenorb Jul 31 '12 at 23:58

5 Answers5

0

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?

Oliver
  • 5,883
  • 23
  • 32
  • 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 09:58
0

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

Bonn
  • 43
  • 1
  • 8
  • 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 03 '13 at 10:31
0

There are numbers of things you can do:

Try to explore following as well:

Community
  • 1
alexus
  • 12,342
  • 27
  • 115
  • 173
0

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
sridhar pandurangiah
  • 743
  • 2
  • 11
  • 28
  • Thanks. I don't work there anymore so I have no idea. But note that putting Mysql on the same vm as drupal helped at that time. – Gnudiff Nov 17 '17 at 12:18
0

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.

Gnudiff
  • 533
  • 5
  • 20