While updating Wopular, I noticed that from time to time, the site would locked up for a couple minutes. Most of the time, I would just wait it out. I finally looked into the slow query log for mysql and found the troublesome query.
It’s a count query that joins the two largest tables in the database, which is why it takes a couple minutes to run. The result of the count is always zero. It’s a useless query that just locks up the site whenever it runs. I’m not sure how it gets accessed or from what page.
All I know is it’s generated by the Views module. I find that most of the site’s performance issues originated from Views. It’s an awesome module for Drupal, but if you have a huge database with tables containing millions of rows, the module will break your site - use it very selectively or not at all. For a new site I’m working on, I didn’t bother installing it.
Anyway, because I couldn’t figure out which page it came from, I had to resort to modifying Drupal core to ignore the query. All count queries come from pager.inc, so all I had to do was match all variations of the count query and just bypass it.
If that didn’t work, I would have to resort to using the mkill utility on ubuntu to automatically kill the query whenever it runs.
Fortunately, modifying pager.inc worked. Even better, most of the other slow queries in the slow query log disappeared, so I’m guessing they were just queries locked up by this expensive count query.
The site has also been operating pretty smoothly since I changed the wait_timeout field from 300 to 30 in the mysql config file a couple months ago. That just kills idle queries that takes longer than 30 seconds to run. Some sites recommend lowering that number to 15 seconds. I think that might be too drastic, but I might give it try sometime.
