It seems that every 2 or 3 months or so, I find myself screaming “why is Drupal so slow.. AGAIN!?” Of course since I never do anything wrong it must be Drupal’s fault..

Last week, after Drupal had been running along fine for the last few months and CPU usage hovering around 0.80% or less most of the time, I started seeing the site come to a crawl again. This time everything was fine until someone posted a new forum topic (node) or posted a forum (node) comment. Posting new nodes suddenly went from taking about one-second to 15-45 seconds, and each time new node was created, CPU usage shot up to 7%-15%. This can be a problem with the site’s primary function is a forum – and members weren’t shy about voicing their displeasure with the “lag”.

Using my ninja-like troubleshooting skillz I was able to deduce that this sudden slowness was most likely due to one the following changes I had made immediately before the big-slowdown:

• Enabled Organic Groups
• Enabled Views
• Installed Panels and created a new front/home page
• Changed the “Who’s Online” block from showing on only a few dozen nodes to showing on every node (but I was sure this had nothing to do with it – I make this sort of change all the time)
• Added a new view-block to all pages, showing only new forum comments (filtered from “all other” types of comments such as blog comments, OG comments, etc)

Because I change the location of blocks all the time I was as certain as I could be that changing/adding the Who’s Online block and filtered forum-comments block had nothing to do with the problem, so I focused on OG, Panels and Views.  These modules are large and complicated so surely they were causing the trouble.

After almost two days of uninstalling and re-installing the new modules, and testing and futzing with the Devel module, I had determined that Panels, Views and Organic Groups had nothing to do with the sudden skyrocketing CPU usage.  I also focused on the database, cleaning, repairing, optimizing, etc, with no results – CPU was still shooting up near 10% any time a new node was created.

This left me with only two possible culprits:

1. The site is just broken and this is how it will be from now on
2. One of the other changes I made which I assumed had nothing to do with the problem was causing the problem

I took a closer look at the “Who’s Online” block, which is custom  (from Drupal Snippets):

$number = db_result(db_query('SELECT COUNT(uid) AS number FROM {users} WHERE status=1'));
if (user_access('access content')) {
// Count users with activity in the past defined period.
$time_period = 120;
// Perform database queries to gather online user lists.
$guests = db_fetch_object(db_query('SELECT COUNT(sid) AS count FROM {sessions} WHERE timestamp >= %d AND uid = 0', time() - $time_period));
$users = db_query('SELECT uid, name, access FROM {users} WHERE access >= %d AND uid != 0 ORDER BY access DESC', time() - $time_period);
$total_users = db_num_rows($users);
// Format the output with proper grammar.
echo "There are $number registered members. ";
if ($total_users == 1 && $guests->count == 1) {
$output = t('%members and %visitors online.', array('%members' => format_plural($total_users, 'and ', ' @count users'), '%visitors' => format_plural($guests->count, '1 guest', '@count guests')));
}
else {
$output = t('

%visitors are viewing the site right now.’, array(‘%members’ => format_plural($total_users, ’1 user’, ‘@count users’), ‘%visitors’ => format_plural($guests->count, ’1 guest’, ‘@count guests’)));
}
}
return $output;

I did notice with the Devel module that this query was running a bit slow, but after some Goog’ling it also dawned on me that since blocks are not normally cached, adding this query to EVERY page like I had done could attribute to my problem. Slamming the CPU with this query several times per second could add-up.  I removed this block from all pages and my CPU issue immediately was 25% better – still slower than before, but measurably better.

This left me with my last change: The custom Views-block to that I created to display only new forum comments (filtering Poll, Blog, Page and OG comments) on all pages. I hadn’t focused on this with Devel, so again using my ninja-like troublshotting skillz I removed the block. – Bingo! CPU instantly dropped below 1%, and started hovering around 0.2%, peaking at 1% or less when new nodes were created.

So my problems were the two items I assumed were not a problem – Making me think back to my first business lesson that I learned from my mentor Charlie McManus when I was 17 years old:

Never assume: ass/u/me (it makes an ASS of U and ME.. Usually ME)

I dropped the custom filter/view block, and added back my Who’s Online block to only a few selected pages. CPU is now averaging below 0.5%, and the site is faster than ever before – particularly when creating new nodes.

Thanks Charlie McManus, I guess you really did know what you were talking about..