Friday, July 20, 2007

mysql query cache

Mysql 4+ has a feature known as query cache. Here mysql caches the result set. So suppose a query is run and it takes 5 seconds to run and query cache is enabled, so results are cached in the cache. Next time if the same query is run again (remember - exactly same query that is strcmp(old_query, new_query) == 0) then the results are fetched from the cache and shown. And this takes very less time - say only 0.1 seconds.

I think, all of you who would be working with mysql for some time now, would be aware of this feature. The above para was just to refresh your memories.

Now lets check out the variables in mysql configuration file (my.cnf) which control the query cache.

mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
6 rows in set (0.00 sec)


have_query_cache says whether mysql supports query cache.

query_cache_limit Dont cache results which are larger than this size. By default it is 1 MB. If your result set is larger, you can increase it as you like.

query_cache_min_res_unit The minimum size for blocks allocated by query cache. Default is 4096 Bytes (4KB). Will talk about this later.

query_cache_size Amount of memory allocated for caching results. Default is 0 - which disables query cache. You can set it to 128 MB or 1 GB. Depending on the memory available with your machine

query_cache_type 0 or OFF would turn query caching off. 1 or ON would turn the query cache on and the result set of every mysql query would then be cached. 2 or DEMAND would enable query cache but all result sets wont be cached. To cache results in this case you will have to specify "SQL_CACHE" in the query.

query_cache_wlock_invalidate Setting this variable to 1 causes acquisition of a WRITE lock for a table to invalidate any queries in the query cache that refer to that table. This forces other clients that attempt to access the table to wait while the lock is in effect.

Now lets see how query cache works and how to tune it.

mysql> show status like '%qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 7 |
| Qcache_free_memory | 133638224 |
| Qcache_hits | 284 |
| Qcache_inserts | 626 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 550 |
| Qcache_total_blocks | 1116 |
+-------------------------+-----------+
8 rows in set (0.00 sec)


Qcache_free_blocks Number of free memory blocks in query cache

Qcache_free_memory Amount of free memory in query cache

Qcache_hits Number of hits to the query cache. Or, the number of times a query was found in the query cache.

Qcache_inserts Number of queries inserted in the query cache.

Qcache_lowmem_prunes Number of queries that where deleted from the query cache due to low cache memory.

Qcache_not_cached Number of not-cached queries

Qcache_queries_in_cache Number of queries registered in the query cache

Qcache_total_blocks Total number of blocks in the query cache

So as and when queries are inserted in the cache, the Qcache_inserts and Qcache_queries_in_cache would increase. Qcache_free_memory would ofcourse decrease. Whenever any DML query is run on a table, the queries in the cache related to that table are removed.

Some variables which let us know the efficiency of the query cache :

If the number of Qcache_hits is less than the number of queries_in_cache then the queries cached are not being used efficiently. And if Qcache_not_cached increases very quickly then queries are not being cached. This could be due to the fact that the result set of the queries are bigger than the variable query_cache_limit. So you should then increase this variable from its default value of 1M to 2M or maybe more.

If the variable Qcache_low_mem_prunes is increasing very fast, it would mean that the memory allocated to query cache is low. Cause mysql is freeing up memory to allocate new queries. Mysql is indirectly asking you to increase the query_cache_size

Mysql allocated memory for query result set in blocks. The default block size is 4K. So Qcache_free_blocks can be an indication of fragmentation. A high number as related to the Qcache_total_blocks means that the cache memory is seriously fragmentation. If the result set size is much less than 4K then fragmentation is high. There is another variable query_cache_min_res_unit which could then be used to decrease the block size from 4K to maybe 2K and help reduce fragmentation.

I had come across a similar issue on one of my servers. Where Qcache_free_memory was high, Qcache_total_blocks was also good. But Qcache_free_blocks was high and Qcache_lowmem_prunes was increasing very fast. And the server was performing very badly. What actually happened was that the cache memory was seriously fragmented. So almost every query was being executed and inserted in the query cache which would replace another query. The insertion and removal of queries from the query cache was taking up extra resources in addition to the resources needed for executing the query. This situation called for defragmentation of the query cache memory. So i ran FLUSH QUERY CACHE. It brought down Qcache_free_blocks and brought the increment of Qcache_lowmem_prunes to a crawl. And automatically the server performance improved. Now the query cache is flushed every 24 hours.

MySQL query cache is a very efficient tool if used properly.

9 comments:

jaypipes said...

Hi!

The Qcache_free_blocks is not actually the number of free memory blocks, but rather the number of blocks marked as "free" by the allocator to insert into. So, Qcache_free_blocks actually represents the cache fragmentation.

Cheers,

jay

sadnbj said...

This is what is happening on the server we just migrated to.
I was wondering what "high" meant in both Qcache_free_blocks Qcache_low_mem_prunes
Here is my QCACHE variables:

Qcache_free_blocks | 5874 |
| Qcache_free_memory | 21566464 |
| Qcache_hits | 9610455 |
| Qcache_inserts | 17840743 |
| Qcache_lowmem_prunes | 339461 |
| Qcache_not_cached | 4114163 |
| Qcache_queries_in_cache | 9249 |
| Qcache_total_blocks | 24434

When I do a FLush query cache it does bring down the Qcache free blocks and brings the low_mem_prunes to a crawl.. Should I increase my query cache (I am getting alerts that the percentage used is too low already)

Ponnusamy said...

Could you please let me know the following
query.

I have updated one table with one mysql account. If i fetch the rows from that table, i can able to get the modified one ( last updated).

But if i try ot fetch the same row from different mysql account, it is not giving me the last updated row, instead it is giving the row which was query cached when i feteched the same row from this same account.

Outline : Updating table is not clearing the query cache for different account.
Is this right

Jayant Kumar said...

This is quite strange. I tried it on my machine created 2 users u1 & u2 and fired a query on u2, inserted using u1 and again refired the same query using u2, and the new record was visible...

Using u2
==========

jayant@jayantbox:~$ /usr/local/mysql5.1.24/bin/mysql -u u2 -pu2passwd -S /tmp/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.24-rc-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like '%cache%';
+------------------------------+----------------------+
| Variable_name | Value |
+------------------------------+----------------------+
| binlog_cache_size | 32768 |
| have_query_cache | YES |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| max_binlog_cache_size | 18446744073709551615 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 10485760 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| table_definition_cache | 128 |
| table_open_cache | 64 |
| thread_cache_size | 25 |
+------------------------------+----------------------+
14 rows in set (0.00 sec)

mysql> \u test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> select * from post where title like '%title%';
+----+---------------------+---------------+----------------+
| id | time | title | body |
+----+---------------------+---------------+----------------+
| 1 | 2007-12-04 12:24:53 | updatetitle | this is test 1 |
| 2 | 2008-04-30 14:34:59 | updatetitle | testbody |
| 6 | 2008-08-25 13:44:29 | title from u1 | body from u2 |
| 7 | 2008-08-25 13:46:53 | title from u2 | body from u2 |
+----+---------------------+---------------+----------------+
4 rows in set (0.02 sec)

==============================

Insert using u1:

jayant@jayantbox:~$ /usr/local/mysql5.1.24/bin/mysql -u u1 -pu1passwd -S /tmp/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.24-rc-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> \u test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> insert into post(time, title, body) values (NOW(), 'after caching title', 'after caching body from u1');
Query OK, 1 row affected (0.00 sec)


==============================
Select again using u2:-

mysql> select * from post where title like '%title%';
+----+---------------------+---------------------+----------------------------+
| id | time | title | body |
+----+---------------------+---------------------+----------------------------+
| 1 | 2007-12-04 12:24:53 | updatetitle | this is test 1 |
| 2 | 2008-04-30 14:34:59 | updatetitle | testbody |
| 6 | 2008-08-25 13:44:29 | title from u1 | body from u2 |
| 7 | 2008-08-25 13:46:53 | title from u2 | body from u2 |
| 8 | 2008-08-25 13:48:17 | after caching title | after caching body from u1 |
+----+---------------------+---------------------+----------------------------+
5 rows in set (0.00 sec)


It seems to work fine... If you are using any language or server in between to fire queries and retrieve records, it might be the middle ware caching the results and not refreshing them on update.

Maik said...

Do you know if there are max or min values for query_cache_min_res_unit and query_cache_limit? Will very low or very high values decrease server performance?

Jayant Kumar said...

query_cache_min_res_unit : as discussed decides the block size for caching results. Larger block size would lead to more fragmentation and hence should reduce server performance

query_cache_limit : as discussed decides the max size of result set that should be cached. You would increase it only if you want to cache very large result sets.

I have not read or encountered any max/min values for these. The min values should be 0 which would disable the cache. The max value can be anything but it should be less than 2GB on a 32 bit machine. A very large value exceeding the memory limit would make the server performance terrible.

Anonymous said...

Where do you put put the query_cache_min_res_unit in my.cnf? Or you do something else. I'm trying to reduce to 2 instead of default 4 but I don't see the otpion on my.cnf

Jayant Kumar said...

just put in the my.cnf as
"query_cache_mis_res_unit = 2M"
And it should work...
Else refer to my.cnf server variables - you can find it on the mysql.com website.

Jayant Kumar said...

Facebook says : The query cache has had an interesting history with MySQL. I don't have much experience with it. I might want to use it in the future so I enabled it during a run of sysbench readonly on an 8-core server. The results are fascinating. Performance with the query cache enabled is bad for MySQL 5.0.44, good for MySQL 5.0.84 and then bad again for MySQL 5.1.38. By bad I mean that throughput does not increase with concurrency as it does when the query cache is disabled.

This lists the throughput in the number of transactions per second reported by sysbench readonly using an 8-core server. It was run for 1, 2, 4, 8, 16 and 32 concurrent sessions.



Throughput with query cache enabled:

1 2 4 8 16 32

5.0.44 663 746 872 834 730 694

5.0.84 639 1033 1819 3216 2636 2466

5.1.38 662 725 818 782 731 699



Throughput with query cache disabled:

1 2 4 8 16 32

5.0.44 856 1429 2807 4431 4327 4199

5.0.84 790 1466 2781 4393 4255 4172

5.1.38 747 1387 2698 4385 4293 4152



Configuration for the test with the query cache enabled:



innodb_buffer_pool_size=2000M
innodb_log_file_size=100M
innodb_doublewrite=0
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=8
max_connections=500
innodb_max_dirty_pages_pct=80
innodb_flush_log_at_trx_commit=2
query_cache_type=1
query_cache_size=100000000



Configuration for the test with the query cache disabled:



innodb_buffer_pool_size=2000M
innodb_log_file_size=100M
innodb_doublewrite=0
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=8
max_connections=500
innodb_max_dirty_pages_pct=80
innodb_flush_log_at_trx_commit=2