Finally, Got Rid Of That Pesky Count Query

Finally, Got Rid of That Pesky Count Query

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

Sections:  business   note from the editor   
Topics:  coding   drupal   mkill   mysql   site updates   slow query log   ubuntu   wopular   
  • "Storm The Gates" Mobile Game
    My friends just launched a mobile game called "Storm the Gates"! It's available on both Android and iOS. I'm not much of a gamer, so I didn't know what to do when I started it. But my 9-year-old son turned it on and knew exactly what to do. More
  • "Ip Man 3" Has New Release Date in China and Posters to Prove It
    Over at, I’ve written two articles regarding “Ip Man 3,” the film that’s holding its own in Asian territories against “Star Wars: The Force Awakens.” If you’re not familiar with Asian cinema,”Ip Man” one of the most popular franchises in that area of the world. More
  • Live-Blogged The Golden Globes at MWB
    Yesterday, I live-blogged The Golden Globes -- for various reasons. First, it gets traffic. Second, I might as well do something useful while watching TV. Lastly, I was looking forward to seeing Ricky Gervais host the event again. Nothing against Tina Fey and Amy Poehler, but he’s my favorite. A google search revealed that the last time I did this for this award show was More
  • Kings vs. Mavs: Ominous Stats, News for Sacramento Before Game
    The preview on and the injury report from don’t look good for the Sacramento Kings going into tonight’s game against the playoff-bound Dallas Mavericks. More
  • What?! Kings Beat Thunder, 116-104
    (Image from the Sacramento Bee. Click here to view more images from the game.) What a win. It was a wild one, especially in the first half. We [Sacramento Kings] were down by 17 in the first quarter. In frustration, I was about to turn off the TV, but the teaml called a timeout. More
  • Pressflow, Locked Tables & Hang Time
    I just installed Pressflow and simultaneously updated Drupal late last week. Pressflow is a modified version of Drupal that allows it to scale more easily. For sites with huge traffic and databases, it's essential. It supports database replication, Squid and Varnish reverse-proxy caching, and is optimized for MySQL and PHP 5. More
  • Moved SimplePie File Caching to Database
    A while back, My ISP told me my backups took half a day to complete, which is too long. If anything goes wrong during that period, the process would have to start all over again, which happened quite often. The customer rep told me that my server had too many files, and even though they didn’t take up much disk space when added up, the process of copying each one of them takes a lot of time. More
  • Downtime & How to Make a Repair Job on MySQL Go Faster
    The site crashed yesterday morning at around 9am, although it’s hardly noticable until a couple hours later. I didn’t notice it until I started picking stories to feature on the homepage. Whenever i tried to feature an article, it would stop the site from loading up. From that, I knew that the MySQL database must be checking for corrupt tables. More
  • Another Network Outage ... Argh ...
    I spent the whole day dealing with servers. The facility where Wopular and MoviesWithButter are hosted were having some network issues. I was able to get MWB up and running within an hour or so. Wopular was a whole different story. More
  • Pages Not Indexed by Google Because of Autofresh
    About a year ago, I decided to add an autorefresh to pages that contain feeds so that content would always stay fresh. I set it to autofresh every 3 minutes. I could have been done this via ajax, but my ajax kung fu wasn’t good enough then (or now). More


Comment On This Story

Welcome to Wopular!

Welcome to Wopular

Wopular is an online newspaper rack, giving you a summary view of the top headlines from the top news sites.

Senh Duong (Founder)
Wopular, MWB, RottenTomatoes

Subscribe to Wopular's RSS Fan Wopular on Facebook Follow Wopular on Twitter Follow Wopular on Google Plus

MoviesWithButter : Our Sister Site

More News