• 登录
  • Subscribe RSS Feed
 

【MySQL】Query Cache的使用问题

02月 2, 2012

在mysql version : 5.1.48-log的测试的时候，原来打算使用query cache，结果一直使用不了，后面测试发现是参数的配置不合理导致的。

原参数配置如下：

root@test 01:47:31>show variables like '%query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 1024 |
| query_cache_min_res_unit | 1024 |
| query_cache_size | 31457280 |
| query_cache_type | DEMAND |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
6 rows in set (0.00 sec)

由于参数query_cache_min_res_unit值设置和query_cache_limit值设置一样大小，导致query_cache_type=DEMAND时候，就是SQL中添加SQL_CACHE选项，还是不缓存。

下面测试看到Qcache_hits值一直没有增加，也就是不能命中query cache ：

root@test 02:00:20>flush status;
Query OK, 0 rows affected (0.00 sec)

root@test 02:00:27>show global status like '%qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 31437704 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 23 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.00 sec)

root@test 02:00:30>select sql_cache * from test where id = 2 ;
+----+------+
| id | col |
+----+------+
| 2 | b |
+----+------+
1 row in set (0.00 sec)

root@test 02:00:52>show global status like '%qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 31437704 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 256 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.00 sec)

root@test 02:00:56>select sql_cache * from test where id = 2 ;
+----+------+
| id | col |
+----+------+
| 2 | b |
+----+------+
1 row in set (0.01 sec)

root@test 02:00:59>show global status like '%qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 31437704 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 308 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.00 sec)

设置参数query_cache_min_res_unit值小于和query_cache_limit值的时候，查询就可以命中了，可以看到第一次查询之后Qcache_inserts+1，第二次查询之后命中Qcache_hits+1 ：

root@test 02:03:43>set global query_cache_min_res_unit = 1000;
Query OK, 0 rows affected (0.00 sec)

root@test 02:03:51>show variables like '%query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 1024 |
| query_cache_min_res_unit | 1000 |
| query_cache_size | 31457280 |
| query_cache_type | DEMAND |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
6 rows in set (0.00 sec)

root@test 02:04:14>show global status like '%qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 31437704 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1965 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.00 sec)

root@test 02:04:14>select sql_cache * from test where id = 2 ;
+----+------+
| id | col |
+----+------+
| 2 | b |
+----+------+
1 row in set (0.00 sec)

root@test 02:04:20>show global status like '%qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 31436192 |
| Qcache_hits | 0 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2145 |
| Qcache_queries_in_cache | 2 |
| Qcache_total_blocks | 6 |
+-------------------------+----------+
8 rows in set (0.00 sec)

root@test 02:04:32>select sql_cache * from test where id = 2 ;
+----+------+
| id | col |
+----+------+
| 2 | b |
+----+------+
1 row in set (0.00 sec)

root@test 02:04:36>show global status like '%qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 31436192 |
| Qcache_hits | 1 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2175 |
| Qcache_queries_in_cache | 2 |
| Qcache_total_blocks | 6 |
+-------------------------+----------+
8 rows in set (0.00 sec)

另外，对于query_cache_type=DEMAND的时候，即使SQL不加SQL_CACHE选项，也是需要到query cache中去查询一下，具体如下：

root@test 02:04:37>show global variables like '%query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 1024 |
| query_cache_min_res_unit | 1000 |
| query_cache_size | 31457280 |
| query_cache_type | DEMAND |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
6 rows in set (0.00 sec)

root@test 02:10:58>set profiling = 1;
Query OK, 0 rows affected (0.00 sec)

root@test 02:11:15>select * from test where id = 2 ;
+----+------+
| id | col |
+----+------+
| 2 | b |
+----+------+
1 row in set (0.00 sec)

root@test 02:11:29>show profile source for query 1;
+--------------------------------+----------+------------------+---------------+-------------+
| Status | Duration | Source_function | Source_file | Source_line |
+--------------------------------+----------+------------------+---------------+-------------+
| starting | 0.000086 | NULL | NULL | NULL |
| checking query cache for query | 0.000058 | unknown function | sql_cache.cc | 1446 |
| Opening tables | 0.000021 | unknown function | sql_base.cc | 4517 |
| System lock | 0.000010 | unknown function | lock.cc | 258 |
| Table lock | 0.000016 | unknown function | lock.cc | 269 |
| init | 0.000032 | unknown function | sql_select.cc | 2484 |
| optimizing | 0.000016 | unknown function | sql_select.cc | 820 |
| statistics | 0.000070 | unknown function | sql_select.cc | 1011 |
| preparing | 0.000021 | unknown function | sql_select.cc | 1033 |
| executing | 0.000010 | unknown function | sql_select.cc | 1755 |
| Sending data | 0.000020 | unknown function | sql_select.cc | 2309 |
| end | 0.000011 | unknown function | sql_select.cc | 2530 |
| query end | 0.000009 | unknown function | sql_parse.cc | 4986 |
| freeing items | 0.000019 | unknown function | sql_parse.cc | 6012 |
| logging slow query | 0.000009 | unknown function | sql_parse.cc | 1687 |
| cleaning up | 0.000009 | unknown function | sql_parse.cc | 1655 |
+--------------------------------+----------+------------------+---------------+-------------+
16 rows in set (0.00 sec)

其中上面status : checking query cache for query 中对应的source_function就是send_result_to_client，在MYSQL源码中有如下信息：

int Query_cache::send_result_to_client(THD *thd, char *sql, uint query_length)
{
...
if (thd->locked_tables || thd->variables.query_cache_type == 0 ||
query_cache_size == 0)
goto err;
...
}

所以,只有query_cache_type=OFF，关掉query cache时候，才不会再去query cache中查询一次，具体如下：

root@test 02:14:20>show variables like '%query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 1024 |
| query_cache_min_res_unit | 1000 |
| query_cache_size | 31457280 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
6 rows in set (0.00 sec)

root@test 02:14:31>set profiling=1;
Query OK, 0 rows affected (0.00 sec)

root@test 02:14:33>select * from test where id = 2 ;
+----+------+
| id | col |
+----+------+
| 2 | b |
+----+------+
1 row in set (0.00 sec)

root@test 02:14:52>show profile source for query 1;
+--------------------+----------+------------------+---------------+-------------+
| Status | Duration | Source_function | Source_file | Source_line |
+--------------------+----------+------------------+---------------+-------------+
| starting | 0.000125 | NULL | NULL | NULL |
| Opening tables | 0.000035 | unknown function | sql_base.cc | 4517 |
| System lock | 0.000012 | unknown function | lock.cc | 258 |
| Table lock | 0.000016 | unknown function | lock.cc | 269 |
| init | 0.000034 | unknown function | sql_select.cc | 2484 |
| optimizing | 0.000017 | unknown function | sql_select.cc | 820 |
| statistics | 0.000066 | unknown function | sql_select.cc | 1011 |
| preparing | 0.000023 | unknown function | sql_select.cc | 1033 |
| executing | 0.000010 | unknown function | sql_select.cc | 1755 |
| Sending data | 0.000022 | unknown function | sql_select.cc | 2309 |
| end | 0.000011 | unknown function | sql_select.cc | 2530 |
| query end | 0.000009 | unknown function | sql_parse.cc | 4986 |
| freeing items | 0.000035 | unknown function | sql_parse.cc | 6012 |
| logging slow query | 0.000010 | unknown function | sql_parse.cc | 1687 |
| cleaning up | 0.000010 | unknown function | sql_parse.cc | 1655 |
+--------------------+----------+------------------+---------------+-------------+
15 rows in set (0.00 sec)

root@test 02:15:01>show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------+
| 1 | 0.00043500 | select * from test where id = 2 |
+----------+------------+---------------------------------+
1 row in set (0.00 sec)

【附】
http://www.alidba.net/index.php/archives/423
http://www.mysqlops.com/2011/08/10/mysql-query-cache.html
--EOF--

tags: ,
posted in MySQL by Orz DBA

Follow comments via the RSS Feed | 发表评论 | Trackback URL

Leave Your Comment

 
Powered by BlogCN.com - WordPress and MySQL. Theme by Shlomi Noach, openark.org