0

I've inherited a Drupal site that is experiencing slow load times and I'm trying to troubleshoot the problem. The site is run on a dedicated box with what should be enough resources.

I ran the Devel module to see the MySQL queries and it's telling me that the following query is being run four times taking a total of 2-3 seconds.

execute SELECT DISTINCT node.nid AS nid, node.title AS node_title,
node_revisions.body AS node_revisions_body,
node_revisions.format AS node_revisions_format, node.sticky AS node_sticky,
node.type AS node_type FROM node node LEFT JOIN node_revisions node_revisions
ON node.vid = node_revisions.vid WHERE node.status = 1 ORDER BY node_sticky DESC

There are almost 400 other queries that are taking over 10 ms, but this one above is by far the longest and that it is being run multiple times, I suspect is causing some of the load problems.

Any suggestions on what is going on here or where to look for more information? All modules and core files are up to date.

  • Do you get the same speed problems when running the query manually? – Clive Mar 13 '13 at 16:24
  • Much faster if I run through phpMyAdmin. Just tried again and it found 3500+ rows in 0.0005 sec. Also right after I posted this question, looked at other logs and it appears my index.php file is hitting the CPU really hard. Not certain what is going on here.. – user2166301 Mar 13 '13 at 16:37
  • Pretty much everything goes through index.php so that does makes sense. What version of Drupal are you using? – Clive Mar 13 '13 at 16:47
  • It's running 6.28. Few other items: just moved this site and 10 others (mostly small) to dedicated box as we were getting poor performance/customer service on an old shared plan. All other sites have had a nice performance boost. This problem site when it was transferred need some massive updates (I think it was running 6.12). It was always slow on the old shared server, but doesn't seem to have benefited at all from the new dedicated. – user2166301 Mar 13 '13 at 17:25
  • Wanted to update everyone that was kind enough to respond. Looks like it was a broken module (jquery ui using date popup to be specific). Re-installed jquery_ui after receiving errors relating to that and the load issues went away. Now onto the other Drupal problems :) – user2166301 Mar 20 '13 at 18:39
1

You have to work very hard at caching. Particularly if the page is static, then make sure you caching on in Home → Administer → Site configuration.

If this is not enough investigate 'boost cache' or alternative caching systems. This can takes some work to setup/prime and tune.

Caching most effective way of getting drupal-6 to be performant. What queries drupal makes can't really be beaten as you can't change the logic of Drupal. If the queries aren't being made at all because of caching they won't be slow.

Second thing. You could also make sure that MySQL is using INNODB tables and that you have set innodb_buffer_pool_size to as high a level as you can given memory limitations. Here is a MySQL tuning script that might provides some hints http://www.day32.com/MySQL/.

Third thing is to make sure that only the modules that are in use are installed on the system. Every module has an overhead.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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