Turn on MySQL query cache to speed up query performance?


Many times developers looking for ways to speed up query, in mysql we can enable query cache to speed up query performance. Whenever query cache is enable, it will cache the query in memory and boost query performance.

As we know, speed is always the most important element in developing a website especially for those high traffic database driven website. You can try to turn on query cache to speed up query.

To speed up query, enable the MySQL query cache, before that you need to set few variables in mysql configuration file (usually is my.cnf or my.ini)

Advertisements

– 1st, set query_cache_type to 1. (There are 3 possible settings: 0 (disable / off), 1 (enable / on) and 2 (on demand).

query-cache-type = 1

– 2nd, set query_cache_size to your expected size. I’d prefer to set it at 20MB.

query-cache-size = 20M

If you set your query-cache-type = 2 ( on demand ), you would wan to modify your sql query to support cache.

SELECT SQL_CACHE field1, field2 FROM table1 WHERE field3 = ‘yes’

To check if your mysql server already enable query cache, simply run this query:-

SHOW VARIABLES LIKE ‘%query_cache%’;

You will see this result:-

+——————-+———+
| Variable_name | Value |
+——————-+———+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_size | 20971520 |
| query_cache_type | ON |
+——————-+———+
4 rows in set (0.06 sec)

To check if your MySQL query cache is working, simply perform a sql query for 2 times and check the query cache variable like below:-

SHOW STATUS LIKE ‘%qcache%’;

+————————-+———-+
| Variable_name | Value |
+————————-+———-+
| Qcache_queries_in_cache | 1 |
| Qcache_inserts | 3 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_free_memory | 20947592 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 4 |
+————————-+———-+

For the first time you execute your SQL query, the time it should take take be longer compare to the second time query. This is due to the MySQL query cache is working!

To know more about MySQL query cache click here

[tags]mysql tuning, mysql server tuning, mysql performance tuning, mysql, mysql server, mysql db, mysql db server, mysql query cache, query cache, boost performance[/tags]




Share this with your friends:-

16 Responses to “Turn on MySQL query cache to speed up query performance?”

  1. chua says:

    hi jenitha, the cache will store all the most recent used query.
    while the least recently used query will be discarded based on capacity.
    so the query will be faster for those query tat always being used.

  2. jenitha says:

    hi all,

    will this query cache will result gud in all version of Mysql? and if query cache is only for least recently used query, then how it il work for entire db?

  3. chua says:

    Hi Pradeep, mysql will only clear those query which is least recent used.
    try to run few more query to c the changes.

  4. pradeep jangir says:

    hi all,

    i set query_cache_type=1 and size=25mb

    then execute query then query make cache and second time query execute fastly
    but then another query execute and re execute first query then take more time like first time execution why tell me.

    if they destroye cache immediately, how to manage cache for previous query

  5. streaky says:

    “the query cache itself becomes pretty much useless over time, because it fragments the more it gets used”

    Depends on your data and queries really, but if you run out of blocks the sensible thing to do is lower your query_cache_min_res_unit – http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_query_cache_min_res_unit – Much more handy than just dumping all that data.

    Munin has some useful plugins for helping decide if this is an issue and if cache size is too big or too small over time.

  6. WordPress Performance: Time Consuming MySQL Queries and Caching…

    I have been looking at a client’s WordPress install the last few days. They have a large number of posts and page load times in general have been increasing. I installed some query logging based on the code in this article … Rails-Like SQL …

  7. Daniel says:

    the query cache itself becomes pretty much useless over time, because it fragments the more it gets used. you have to run a “FLUSH QUERY CACHE;” every hour or so (depending on your load) in a cron job to get arround this issue.

    you can check the current fragmentation with “SHOW STATUS LIKE ‘Qcache_free_blocks’;” (Values closer to 1 are better)

    see http://dev.mysql.com/doc/refman/5.0/en/flush.html and http://dev.mysql.com/doc/refman/5.0/en/query-cache-status-and-maintenance.html for more info…

  8. sogua says:

    normally share hosting environment by default enable the mysql query cache.
    or else check with your hosting provider if there is anyway for you to enable the query cache.

  9. Damian says:

    > what can i do if i can change my.cnf or my.ini? because i’m using share hosting.. please advise thanks

    Use a .my.cnf in your home directory.

  10. callrid says:

    what can i do if i can change my.cnf or my.ini? because i’m using share hosting.. please advise thanks

  11. suganthan says:

    that ’s a better way .

  12. lily says:

    that ‘s a better way .

  13. […] root access which won’t be available on shared hosts. More info about query cache is available on techiecorner.com and the MySQL Performance […]

  14. […] root access which won’t be available on shared hosts. More info about query cache is available on techiecorner.com and the MySQL Performance […]

  15. […] root access which won’t be available on shared hosts. More info about query cache is available on techiecorner.com and the MySQL Performance […]

Leave a Reply