返回
前端
分类

可以忽略 query,那么和这个数据表相关的全部Cache全部都会无效

日期: 2020-01-02 07:59 浏览次数 : 115

MySQL Troubleshoting:Waiting on query cache mutex

当你的数据库打开了Query Cache(简称QC)功能后,数据库在执行SELECT语句时,会将其结果放到QC中,当下一次处理同样的SELECT请求时,数据库就会从QC取得结果,而不需要去数据表中查询。

必赢备用网址 1
备注:插图来自淘宝苏普的博客并保留水印,如果觉得不当还请及时告知 :)

 

必赢备用网址 2

写在前面:MySQL的query cache大部分情况下其实只是鸡肋而已,建议全面禁用。当然了,或许在你的场景下还是挺好的,还能发挥作用,那就继续使用吧,把本文当做参考就好。

今天被MySQL Query Cache 炕了、线上大量 Waiting on query cache mutex

在这个”Cache为王”的时代,我们总是通过不同的方式去缓存我们的结果从而提高响应效率,但一个缓存机制是否有效,效果如何,却是一个需要好好思考的问题。在MySQL中的Query Cache就是一个适用较少情况的缓存机制。在上图中,如果缓存命中率非常高的话,有测试表明在极端情况下可以提高效率238%。但实际情况如何?

不过,可能有的人人为只需要把 query_cache_size 大小调整为 0 就可以了,可以忽略 query_cache_type 参数的值,反正它也是可以在线调整的。

     

Query Cache有如下规则,如果数据表被更改,那么和这个数据表相关的全部Cache全部都会无效,并删除之。这里“数据表更改”包括: INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE等。举个例子,如果数据表posts访问频繁,那么意味着它的很多数据会被QC缓存起来,但是每一次posts数据表的更新,无论更新是不是影响到了cache的数据,都会将全部和posts表相关的cache清除。如果你的数据表更新频繁的话,那么Query Cache将会成为系统的负担。有实验表明,糟糕时,QC会降低系统13%的处理能力。

事实果真如此吗?让我们来实际模拟测试下就知道了。

     那么什么是 Query Cache?

如果你的应用对数据库的更新很少,那么QC将会作用显著。比较典型的如博客系统,一般博客更新相对较慢,数据表相对稳定不变,这时候QC的作用会比较明显。如果数据库一共往QC中写入了约800W次缓存,但是实际命中的只有约500W次。也就是说,每一个缓存的使用率约为0.66次。很难说,该缓存的作用是否大于QC系统所带来的开销。但是有一点是很肯定的,QC缓存的作用是很微小的,如果应用层能够实现缓存,将可以忽略QC的效果。

我们模拟了以下几种场景:

     

————-下面是关于Query Cache相关参数—————–

1、初始化时,同时设置 query_cache_size 和 query_cache_type 的值为 0;

2、初始化时,设置 query_cache_size = 0,但设置 query_cache_type = 1;

3、初始化时,设置 query_cache_size = 0,query_cache_type = 1,但是启动后立刻 修改 query_cache_type = 0

4、初始化时,设置 query_cache_size = 0,query_cache_type = 0,但是启动后立刻 修改 query_cache_type = 1

5、初始化时,设置 query_cache_size = xMB,query_cache_type = 1,但是启动后立刻 修改 query_cache_type = 0

必赢备用网址 3

mysql> show variables like '%query_cache%';

 

     

+------------------------------+---------+

经过测试,可以得到下面几个重要结论(详细测试过程请见最后):

     QC 缓存的是整个SELECT的结果集、而非执行计划、QC的为人原则是:执行查询最快的方式就是不去执行

| Variable_name                | Value   |

1、想要彻底关闭query cache,务必在一开始就设置 query_cache_type = 0,即便是启动后将 query_cache_type 从 1 改成 0,也不行;

2、即便query_cache_size = 0,但 query_cache_type 非 0 的话,在实际环境中,可能会频繁发生 Waiting for query cache lock;

3、一开始就设置 query_cache_type = 0 的话,没有办法在运行 过程中再次动态启用,反过来则可以。也就是说,一开始是启用 query cache 的, 在运行过程中将其关闭,但事实上仍然会发生  Waiting for query cache lock,并没有真正的关闭;

     但是、QC 简单粗暴的失效策略、令人蛋疼、任何不同(空格、TAB缩进、DML等)都会导致该表的Cache不可用

+------------------------------+---------+

 

     失效通过single mutex 控制、有比较严重的锁竞争

| have_query_cache             | YES     |

关于query cache的延伸阅读,请见:

     

| query_cache_limit            | 1048576 |

1、我的前同事waterbin帅哥的悲惨经历:MySQL Troubleshoting:Waiting on query cache mutex
2、淘宝苏普的旧文:Query Cache,看上去很美

     如何关闭QC?

| query_cache_min_res_unit     | 4096    |

 

     控制 2个参数:

| query_cache_size             | 1048576 |

详细测试过程:

     ① query_cache_type = off

| query_cache_type             | OFF     |

一、测试方法

     ② query_cache_size = 0

| query_cache_wlock_invalidate | OFF     |

采用sysbench模拟并发oltp请求:

     

+------------------------------+---------+

sysbench --test=tests/db/oltp.lua --oltp_tables_count=10 --oltp-table-size=100000 --rand-init=on --num-threads=64 --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=1800 --max-requests=0 run

     总体而言、QC不建议使用、鸡肋功能、"夫鸡肋,弃之如可惜,食之无所得"、导致几十上百倍的性能差异

6 rows in set (0.00 sec)

二、具体几种测试模式

     如果、确实有这个缓存需求、应用允许的情况下、可用效率高的Redis或者MC等替代

query_cache_size:设置Query Cache所使用的内存大小,默认值为0,大小必须是1024的整数倍,如果不是整数倍,MySQL会自动调整降低最小量以达到1024的倍数。

1、一直关闭QC(query cache的简写,下同),即  query_cache_size = 0, query_cache_type = 0

 

query_cache_type:控制Query Cache功能的开关,可以设置为0(OFF),1(ON)和2(DEMAND)三种:0表示关闭Query Cache功能,任何情况下都不会使用Query Cache;1表示开启Query Cache功能,但是当SELECT语句中使用的SQL_NO_CACHE提示后,将不使用Query Cache;2(DEMAND)表示开启Query Cache功能,但是只有当SELECT语句中使用了SQL_CACHE提示后,才使用Query Cache。

测试过程中,一直都没有和query cache lock相关的状态出现,结果tps:2295.34

Troubleshoting:Waiting on query cache mutex 今天被MySQL Query Cache 炕了、线上大量 Waiting on query cache mutex 那么什么是 Query Cache? QC 缓存的是整个...

query_cache_limit:允许Cache的单条Query结果集的最大容量,默认是1MB,超过此参数设置的Query结果集将不会被Cache。

 

query_cache_min_res_unit:设置Query Cache中每次分配内存的最小空间大小,也就是每个Query的Cache最小占用的内存空间大小。

2、启用QC,但QC size 设置为 0,即:query_cache_必赢备用网址 ,size = 0,query_cache_type = 1

query_alloc_block_size:缓存的块大小,默认为8192字节。

测试过程中,一直有 Waiting for query cache lock 状态出现,结果tps:2272.52

query_cache_wlock_invalidate:控制当有写锁加在表上的时候,是否先让该表相关的Query Cache失效,1(TRUE),在写锁定的同时将使该表相关的所有Query Cache 失效。0(FALSE),在锁定时刻仍然允许读取该表相关的Query Cache。

 

Qcache_lowmem_prunes:这是一个状态变量(show status),当缓存空间不够需要释放旧的缓存时,该值会自增。

3、启用QC,但QC size为0,但启动时立刻关闭QC,即初始化时 query_cache_size = 0,query_cache_type = 1,启动后立刻修改 query_cache_type = 0

如何确认一个系统的 Query Cache 的运行是否健康,命中率如何,设置量是否足够?

测试过程中,也一直有 Waiting for query cache lock 状态出现,结果tps:2311.54

mysql> show global status like '%Qcache%';

 

+-------------------------+---------+

4、关闭QC,但启动后立刻启用QC,即初始化时 query_cache_size = 0,query_cache_type = 0,启动后立刻修改 query_cache_type = 1

| Variable_name           | Value   |

这时,会提示报错信息:

+-------------------------+---------+

可以忽略 query,那么和这个数据表相关的全部Cache全部都会无效。失败:ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it

| Qcache_free_blocks      | 1       |

也就是说,如果一开始就关闭 QC 的话,是没办法在运行过程中动态再启用QC的。

| Qcache_free_memory      | 1031832 |

 

| Qcache_hits             | 0       |

5、启用QC,并设置QC size为256M,即 query_cache_size = 256M,query_cache_type = 1

| Qcache_inserts          | 0       |

这种情况下,在测试过程中一直有 Waiting for query cache lock 状态出现,并且结果tps也很差,只有 1395.39(几个案例中最差的一种)

| Qcache_lowmem_prunes    | 0       |

 

| Qcache_not_cached       | 128998  |

6、启用QC,设置QC size为256M,但启动后立刻关闭QC,即 query_cache_size = 256M,query_cache_type = 1,启动后立刻修改 query_cache_type = 0

| Qcache_queries_in_cache | 0       |

这种情况下,在测试过程中也一直有  Waiting for query cache lock 状态出现,结果tps:2295.79(在这个模式下,如果设置 query_cache_type = 2,效果也不佳)

| Qcache_total_blocks     | 1       |

 

+-------------------------+---------+

第三种模式下,虽然看起来tps还不错,但毕竟上面只是简单模拟测试,实际情况下如果有频繁的query cache lock的话,tps肯定不会太好看。

8 rows in set (0.00 sec)

因此,总的来说,想要获得较高tps的话,最好还是一开始就关闭QC,不要心存侥幸或者固守陈规。

mysql> show global status like '%Com_select%';

+---------------+--------+

| Variable_name | Value |

+---------------+--------+

| Com_select    | 129157 |

+---------------+--------+

Qcache_free_blocks:目前还处于空闲状态的Query Cache中内存Block数目。

Qcache_free_memory:目前还处于空闲状态的Query Cache内存总量。

Qcache_hits:Query Cache命中次数。

Qcache_inserts:向Query Cache中插入新的Query Cache的次数,也就是没有命中的次数。

Qcache_lowmem_prunes:当Query Cache内存容量不够,需要从中删除老的Query Cache以给新的Cache对象使用的次数。

Qcache_not_cached:没有被Cache的SQL数,包括无法被Cache的SQL以及由于query_cache_type设置的不会被Cache 的 SQL。

Qcache_queries_in_cache:目前在Query Cache中的SQL数量。

Qcache_total_blocks:Query Cache中总的Block数量。

可以根据这几个状态计算出Cache命中率,计算出Query Cache大小设置是否足够。

写在前面:MySQL的Query Cache大部分情况下其实只是鸡肋而已,建议全面禁用,默认关闭。当然了,或许在你的场景下还是挺好的,还能发挥作用,那就继续使用吧,把本文当做参考就好。不过,可能有的人人为只需要把query_cache_size大小调整为0就可以了,可以忽略query_cache_type参数的值,反正它也是可以在线调整的。事实果真如此吗?让我们来实际模拟测试下就知道了。

我们模拟了以下几种场景:

1、初始化时,同时设置query_cache_size和query_cache_type的值为0;

2、初始化时,设置query_cache_size = 0,但设置query_cache_type = 1;

3、初始化时,设置query_cache_size = 0,query_cache_type = 1,但是启动后立刻修改query_cache_type = 0 ;

4、初始化时,设置query_cache_size = 0,query_cache_type = 0,但是启动后立刻修改query_cache_type = 1 ;

5、初始化时,设置query_cache_size = xMB,query_cache_type = 1,但是启动后立刻修改query_cache_type = 0 ;

经过测试,可以得到下面几个重要结论(详细测试过程请见最后):

1、想要彻底关闭query cache,务必在一开始就设置query_cache_type = 0,即便是启动后将query_cache_type从1改成0,也不行;

2、即便query_cache_size = 0,但query_cache_type非0的话,在实际环境中,可能会频繁发生Waiting for query cache lock;

3、一开始就设置query_cache_type = 0的话,没有办法在运行过程中再次动态启用,反过来则可以。也就是说,一开始是启用query cache的, 在运行过程中将其关闭,但事实上仍然会发生Waiting for query cache lock,并没有真正的关闭;

详细测试过程:

一、测试方法

采用sysbench模拟并发OLTP请求:

$ sysbench --test=tests/db/oltp.lua --oltp_tables_count=10 --oltp-table-size=100000 --rand-init=on --num-threads=64 --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=1800 --max-requests=0 run

二、具体几种测试模式

1、一直关闭QC(query cache的简写,下同),即 query_cache_size = 0, query_cache_type = 0

测试过程中,一直都没有和query cache lock相关的状态出现,结果tps:2295.34。

2、启用QC,但QC size 设置为 0,即:query_cache_size = 0,query_cache_type = 1

测试过程中,一直有 Waiting for query cache lock 状态出现,结果tps:2272.52。

3、启用QC,但QC size为0,但启动时立刻关闭QC,即初始化时 query_cache_size = 0,query_cache_type = 1,启动后立刻修改query_cache_type = 0

测试过程中,也一直有 Waiting for query cache lock 状态出现,结果tps:2311.54。

4、关闭QC,但启动后立刻启用QC,即初始化时 query_cache_size = 0,query_cache_type = 0,启动后立刻修改query_cache_type = 1

这时,会提示报错信息:失败:ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it。也就是说,如果一开始就关闭 QC 的话,是没办法在运行过程中动态再启用QC的。

5、启用QC,并设置QC size为256M,即query_cache_size = 256M,query_cache_type = 1

这种情况下,在测试过程中一直有Waiting for query cache lock状态出现,并且结果tps也很差,只有1395.39(几个案例中最差的一种)。

6、启用QC,设置QC size为256M,但启动后立刻关闭QC,即query_cache_size = 256M,query_cache_type = 1,启动后立刻修改query_cache_type = 0

这种情况下,在测试过程中也一直有Waiting for query cache lock状态出现,结果tps:2295.79(在这个模式下,如果设置query_cache_type = 2,效果也不佳)。

第三种模式下,虽然看起来tps还不错,但毕竟上面只是简单模拟测试,实际情况下如果有频繁的query cache lock的话,tps肯定不会太好看。因此,总的来说,想要获得较高tps的话,最好还是一开始就关闭QC,不要心存侥幸或者固守陈规。

转自: