mysql优化参数query_cache_size

查询缓存使用情况
mysql> show status like ‘%qcache%’;
+————————-+———-+
| Variable_name | Value |
+————————-+———-+
| Qcache_free_blocks | 1041 |
| Qcache_free_memory | 83003648 |
| Qcache_hits | 76534835 |
| Qcache_inserts | 7677916 |
| Qcache_lowmem_prunes | 1516232 |
| Qcache_not_cached | 330027 |
| Qcache_queries_in_cache | 173376 |
| Qcache_total_blocks | 347796 |
+————————-+———-+
8 rows in set (0.00 sec)
Qcache_free_blocks
目前还处于空闲状态的Query cache内存block的数目 这个值比较大,意味着内存碎片比较多,可以使用flush query cache清理内存碎片
Qcache_free_memory
目前还处于空闲状态的Query Cache内存总量
Qcache_hits
查询缓存区的命中个数,也就是直接从查询缓存区作出响应处理的查询个数
Qcache_inserts
向Query Cache中插入新的Query Cache的次数,也就是没有命中的次数
Qcache_lowmem_prunes
其代表的意义为:查询缓存去因内存不足而不得不从查询缓存区删除的查询缓存信息,删除算法为LRU; 当query内存容量不足,需要从中删除老的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的总数量

以下有几个概念:
碎片率
查询缓存内存碎片率:Qcache_free_blocks / Qcache_total_blocks * 100%
如果查询缓存碎片率超过20%,可以用FLUSH QUERY CACHE整理缓存碎片,或者试试减小query_cache_min_res_unit,如果你的查询都是小数据量的话

命中率
查询缓存命中率:Qcache_hits/(Qcache_hits + Qcache_inserts) * 100%

缓存利用率
查询缓存内存使用率:(query_cache_size – Qcache_free_memory) / query_cache_size * 100%
查询缓存利用率在25%以下的话说明query_cache_size设置的过大,可适当减小;查询缓存利用率在80%以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小,要不就是碎片太多。

查询服务器关于query_cache的配置:
mysql> show variables like ‘query_cache%’;
+——————————+———–+
| Variable_name | Value |
+——————————+———–+
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 268435456 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+——————————+———–+
5 rows in set (0.01 sec)

have_query_cache
是否支持查询缓存区 “YES”表是支持查询缓存区
query_cache_limit
允许Cache单条Query结果集的最大容量,默认是1MB,超过此参数设置Query结果集将不会被Cache
query_cache_min_res_unit
设置Query Cache中每次分配内存的最小空间大小,也就是每个Query的缓存块的最小占用的内存空间大小
内存块分配的最小单元非常重要,设置过大可能增加内存碎片的概率发生,太小又可能增加内存分配的消耗,为此在系统平稳运行一个阶段性后,可参考公式的计算值
查询缓存最小内存块: (query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache
query_cache_size
设置Query Cache所使用的内存大小,默认是0,大小必须是1024的整数倍,如果不是整数倍,MySQL 会自动调整降低最小量以达到1024的倍数
我们如何判断query_cache_size是否设置过小,依然也只有先预设置一个值,推荐为:32M~128M之间的区域,待系统平稳运行一个时间段(至少1周),并且观察这周内的相关状态值:
(1).Qcache_lowmem_prunes;
(2).命中率;
(3).内存使用率;
若整个平稳运行期监控获得的信息,为命中率高于80%,内存使用率超过80%,并且Qcache_lowmem_prunes的值不停地增加,而且增加的数值还较大,则说明我们为查询缓冲区分配的内存过小,可以适当地增加查询缓存区的内存大小;
query_cache_type
控制Query Cache功能的开关,可以设置为以下三种:
0(OFF):关闭Query Cache功能,任何情况下都不会使用Query Cache
1(on):开启Query Cache功能,但是当select 语句中使用SQL_NO_CACHE提示后,将不使用Query Cache
3(DEMAND):开启Query Cache功能,但是只有当 SELECT 语句中使用了SQL_CACHE 提示后,才使用 Query Cache
PS:在写操作比较频繁的mysql上,建议禁用该选项,因为表发生变化之后所有的Query Cache都会失效
query_cache_wlock_invalidate
控制当写锁定发生在表上的时候,是否先失效该表相关的Query Cache,如果设置为1(true),则在写锁定的同时将失效该表相关的Query Cache,如果设置为0(False)则在锁定时仍然允许读取该表相关的Query Cache。

mysql使用查询缓存时的相关优化:
1、尽量用小表的简单替代大表的复杂查询
2、尽量用批量写入取代单条写入
2、控制query_cache_size的大小,甚至是禁用查询缓存
3、通过DEMAND+SQL_CACHE/SQL_NO_CACHE来灵活控制某个select是否需要进行缓存
4、对于写密集型的应用,直接禁用查询缓存

查询缓存的优化流程:
初始状态,有查询进来,判断缓存是否命中,如果命中,查询完成,没有命中,到下一步
判断是否多数查询都无法缓存,是(判断Query_cache_limit设置是否够大,如果不够大,增大query_cache_limit,如果够大,则查询完成,查询无法被缓存)否,到下一步
是否有太多失效,是(判断是否碎片过多,如果是,缩小query_cache_res_unit的值或者使用命令Flush Query Cache来整理碎片;否,则判断是否有太多内存不足导致失效,是增大query_cache_size,否在判断是否有太多更新操作,是完成,该场景不适合查询缓存,否有其他的错误配置),否,转下一步
判断查询是否完成预热,是,完成,查询从来不曾被处理过;否,等待缓存预热完成,再次返回到初始状态

查询缓存的工作原理,基本上可以用二句话概括:
1、缓存SELECT操作或预处理查询(注释:5.1.17开始支持)的结果集和SQL语句;
2、新的SELECT语句或预处理查询语句,先去查询缓存,判断是否存在可用的记录集,判断标准;与缓存的SQL语句,是否完全一样,区分大小写;

查询缓存对什么样的查询语句,无法缓存其记录集,大致有以下几类:
1、查询语句中加了SQL_NO_CACHE参数;
2、查询语句中含有获得值的函数,包涵自定义函数,如:CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ等;
3、对系统数据库的查询:mysql、information_schema
4、查询语句中使用SESSION级别变量或存储过程中的局部变量;
5、查询语句中使用了LOCK IN SHARE MODE、FOR UPDATE的语句
6、查询语句中类似SELECT …INTO 导出数据的语句;
7、事务隔离级别为:Serializable情况下,所有查询语句都不能缓存;
8、对临时表的查询操作;
9、存在警告信息的查询语句;
9、不涉及任何表或视图的查询语句;
10、某用户只有列级别权限的查询语句;

查询缓存的优缺点:
不需要对SQL语句做任何解析和执行,当然语法解析必须通过在先,直接从Query Cache中获得查询结果;
查询缓存的判断规则,不够智能,也即提高了查询缓存的使用门槛,降低其效率;
Query Cache的起用,会增加检查和清理Query Cache中记录集的开销,而且存在SQL语句缓存的表,每一张表都只有一个对应的全局锁;

业务场景
通过上述的知识梳理和分析,我们至少知道查询缓存的以下几点:
1、查询缓存能够加速已经存在缓存的查询语句的速度,可以不用重新解析和执行而获得正确得记录集;
2、查询缓存中涉及的表,每一个表对象都有一个属于自己的全局性质的锁;
3、表若是做DDL、FLUSH TABLES 等类似操作,触发相关表的查询缓存信息清空;
4、表对象的DML操作,必须优先判断是否需要清理相关查询缓存的记录信息,将不可避免地出现锁等待事件;
5、查询缓存的内存分配问题,不可避免地产生一些内存碎片;
6、查询缓存对是否是一样的查询语句,要求非常苛刻,而且还不智能;
查询缓存适合什么样的业务场景呢?只要是清楚了查询缓存的上述优缺点,就不难罗列出来,业务场景要求:
1、整个系统以读为主的业务,比如门户型、新闻类、报表型、论坛等网站;
2、查询语句操作的表对象,非频繁地进行DML操作,可以使用query_cache_type=2模式,然后SQL语句加SQL_CACHE参数指定;

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注