Improving Mysql Performance With Query Cache

Improving MySQL Performance with Query Cache

In my continued efforts to make the site run faster, I’ve set my sights on query caching on MySQL. I think query caching is turned on by default when you install MySQL. At least for me, I didn’t have to do anything. I notice it’s been working because whenever I run a query a second time, it’s always significantly faster.

SETTINGS
You can control settings for query caching in the MySQL config file, usually located at:

/etc/mysql/my.cnf

Just go to the “Query Cache Configuration” section. You should see three settings:

- query_cache_type
Turn it on/off. 1 is on; 0 is off.

- query_cache_limit
The amount of memory to allocate per query. Any query that takes up more than that amount will not be cached.

- query_cache_size
Total amount of memory to allocate for query caching. The more you use, the more queries that fit fall within the query_cache_limit will be cached.

If they’re not there, you can just add them yourself.

MY MODIFICATIONS
Here were my original settings:
query_cache_type = 1
query_cache_limit = 1M
query_cache_size = 16M

Here are my modified settings:
query_cache_type = 1
query_cache_limit = 4M
query_cache_size = 128M

Because Linode celebrated their anniversary recently by giving their users 40-50% more RAM on their VPS’s, I decided it’s time to put some of that extra memory to use. I upped the query_cache_limit to 4M and query_cache_size to 128M. I noticed a performance increase almost immediately. Now, larger queries are being cached (within 4MB), and I can cache a lot more queries with more memory (128MB).

PERFORMANCE IMPROVEMENTS
My server load dropped from an average of 3-5 to 1-3. You can check this by using the “uptime” command in linux.

You can see how your new settings are affecting mysql by logging into it and typing in the “show status” command:

mysql> SHOW STATUS LIKE ‘%qcache%’

You’ll get a bunch of values for these variables:
Qcache_free_blocks
Qcache_free_memory
Qcache_hits
Qcache_inserts
Qcache_lowmem_prunes
Qcache_not_cached
Qcache_queries_in_cache
Qcache_total_blocks

The two that you’re most interested in are Qcache_hits and Qcache_inserts. You want the ratio of Qcache_hits to Qcache_inserts to be as high as you can get. Before upping the numbers, my ratio was about 1 to 1. Afterwards, it was about 2 to 1, so for every query that’s cached, it’s getting hit twice. I’m not sure how great that is, but it’s better than before. And I notice the performance boost.

REFERENCE:
Turn on MySQL query cache to speed up query performance?
http://www.techiecorner.com/45/turn-on-mysql-query-cache-to-speed-up-mys...
(A good general overview)

MySQL's Query Cache
http://www.databasejournal.com/features/mysql/article.php/3110171/MySQLs...
(Discusses Qcache varirables in more detail)

MySQL Performance Blog: MySQL Query Cache
http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/
(None of the details, but a good descriptive article with lots of readable text)

Sections:  business   note from the editor   
Topics:  coding   database   internet   lamp   linode   linux   mysql   mysql performance   mysql query cache   query cache   site updates   vps   
SENH'S LATEST BLOG ENTRIES
  • "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 MoviesWithButter.com, 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 NBA.com and the injury report from SactownRoyalty.com 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
SENH'S RELATED BLOG ENTRIES
  • Finally Upgraded Sites to Use New Hardware from Linode Cloud: SSDs, Double RAM, Faster CPUs
    Around April, my VPS provider Linode announced that they’ve just finished a major $45M upgrade to their hardware and network, which means free upgrades for their existing users. The new hardware uses SSD RAID drives and faster CPUs. I hadn’t bothered because Wopular and Movies With Butter have been relatively stable. 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
  • 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. 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