KILL MYSQL SQL出现线程无法终止，出现freeing items现象问题

KILL查询SQL出现线程无法终止，出现freeing items现象问题；

 实例：今天公司DB 有大量的线程是freeing items 状态。DB服务器上跑应用，还有一些大的不固定的数据抓取查询(不固定，经常变动)，结果使用了Query cache ，导致Query Cache 池的抖动，频繁有cache失效，产生了freeing items 状态,下面是官方对freeing items的解释：

The thread has executed acommand. Some freeing of items done during this state involves the query cache.This state is usually followed by cleaning up.

线程已经执行了命令。在这个状态中涉及的查询缓存可以得到一些释放。这个状态通常后面跟随cleaning up状态。

最后把query_cache_limit为1M，很少有freeing items 状态。uery_cache_limit和query_cache_size的设置要紧结合具体的业务现状来调整。

 

原SQL语句；
  SELECT `资源类型`, SUM(`资源总数`) AS LONG_COL_0, SUM(`资源总数_统一单位后`) AS LONG_COL_1 FROM (select DISTINCT
  CAST(order_id as CHAR) as '订单id'
, CAST(customer_id as CHAR) as '客户id'
, contract_money as '合同实际总金额'
, CAST(creator_id as CHAR) as '合同维护人id'
, creator_name as '维护人名称'
, creator_org_id as '维护人所属组织id'
, creator_org_name as '维护人所属组织名称'
, sales_level as '销售级别（s、ss、ka）'
, CAST(rps_user_id as CHAR) as '招服id'
, rps_user_name as '招服名称'
, CAST(rps_org_id as CHAR) as '招服团队id'
, rps_org_name as '招服团队名称'
, CAST(rps_branch_id as CHAR) as '招服所属分公司id'
, rps_branch_name as '招服所属分公司名称'
, customer_name as '客户名称'
, customer_dq as '客户地区'
, customer_branch as '客户所属分公司'
, customer_area as '客户所属公司大区'
, industry as '行业'
, company_scale as '公司规模'
, company_kind as '公司性质'
, date(resource_start_date) as '资源服务开始日期'
, date(resource_timeout_date) as '资源服务结束日期'
, (case when substr(regexp_replace(resource_timeout_date,'-',''),1,8)-d_date>0 then '正在执行中的合同'
  else '已到期的合同' end) as '是否为在执行的合同'
, (case when resource_kind=2 then 'LPT精英简历'
  when resource_kind=3 then 'LPT意向沟通'
  when resource_kind=6 then 'LPT邀请应聘'
  when resource_kind=8 then 'LPT白领简历'
  when resource_kind=9 then 'LPT急聘点数'
  when resource_kind=10 then 'LPT猎币'
  else null end)as '资源类型'
, resource_total_cnt as '资源总数'
, (case when resource_kind=2 then resource_total_cnt*1
  when resource_kind=3 then resource_total_cnt*2
  when resource_kind=6 then resource_total_cnt*5
  when resource_kind=8 and business_kind=!6 then resource_total_cnt/5
    when resource_kind=9 then resource_total_cnt*5
    when resource_kind=10 then resource_total_cnt/50
  else 0 end) as '资源总数_统一单位后'
from   detail_bi
where consume_date>='2017-01-01'
and resource_start_date>='2017-01-01'
and lpt_service_effect_date>='2017-01-01'
and contract_effect_date>='2017-01-01'
and contract_status=3 -- 已生效
and order_status=2 -- 已开通
and business_kind in (0,1,6)  -- 合同签约、在线购买
and resource_kind in (2,3,6,8,9,10)  -- LPT精英简历、LPT意向沟通、LPT邀请应聘、LPT白领简历、LPT急聘点数、LPT猎币
and consume_kind in (2,6)  -- 客户消费、协商扣除
) SUB_QRY WHERE ( `资源服务开始日期` BETWEEN  '2017-06-01'  AND  '2018-08-05' ) AND ( `是否为在执行的合同` =  '正在执行中的合同' ) GROUP BY `资源类型` ORDER BY `资源类型` DESC LIMIT 5000000
 


 
 
 

一、工作原理

查询缓存的工作原理，基本上可以用二句话概括：

1.缓存SELECT操作或预处理查询（注释：5.1.17开始支持）的结果集和SQL语句；

2.新的SELECT语句或预处理查询语句，先去查询缓存，判断是否存在可用的记录集，判断标准：与缓存的SQL语句，是否完全一样，区分大小写；

二、查询缓存对什么样的查询语句，无法缓存其记录集，大致有以下几类：

 a.查询语句中加了SQL_NO_CACHE参数；

 b.查询语句中含有获得值的函数，包含自定义函数，如：CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ等；

 c.对系统数据库的查询：mysql、information_schema

 d.查询语句中使用SESSION级别变量或存储过程中的局部变量；

 e.查询语句中使用了LOCK  IN SHARE MODE、FOR UPDATE的语句

 f.查询语句中类似SELECT…INTO 导出数据的语句；

 g.事务隔离级别为：Serializable情况下，所有查询语句都不能缓存；

 h.对临时表的查询操作；

 i.存在警告信息的查询语句；

 j.不涉及任何表或视图的查询语句；

 k.某用户只有列级别权限的查询语句；

三、查询缓存的优缺点：

不需要对SQL语句做任何解析和执行，当然语法解析必须通过在先，直接从Query Cache中获得查询结果。

查询缓存的判断规则，不够智能，也即提高了查询缓存的使用门槛，降低其效率；

 Query Cache的起用，会增加检查和清理QueryCache中记录集的开销，而且存在SQL语句缓存的表，每一张表都只有一个对应的全局锁。

四、配置

是否启用mysql查询缓存，可以通过2个参数：query_cache_type和query_cache_size，其中任何一个参数设置为0都意味着关闭查询缓存功能，但是正确的设置推荐query_cache_type=0。

a. query_cache_type

值域为：0 -–不启用查询缓存；

值域为：1 -–启用查询缓存，只要符合查询缓存的要求，客户端的查询语句和记录集斗可以缓存起来，共其他客户端使用；

值域为：2 -–启用查询缓存，只要查询语句中添加了参数：SQL_CACHE，且符合查询缓存的要求，客户端的查询语句和记录集，则可以缓存起来，共其他客户端使用；

b. query_cache_size

允许设置query_cache_size的值最小为40K，对于最大值则可以几乎认为无限制，实际生产环境的应用经验告诉我们，该值并不是越大，查询缓存的命中率就越高，也不是对服务器负载下降贡献大，反而可能抵消其带来的好处，甚至增加服务器的负载，至于该如何设置，下面的章节讲述，推荐设置为：64M；

c. query_cache_limit

限制查询缓存区最大能缓存的查询记录集，可以避免一个大的查询记录集占去大量的内存区域，而且往往小查询记录集是最有效的缓存记录集，默认设置为1M，建议修改为16k~1024k之间的值域，不过最重要的是根据自己应用的实际情况进行分析、预估来设置；

d. query_cache_min_res_unit

设置查询缓存分配内存的最小单位，要适当地设置此参数，可以做到为减少内存块的申请和分配次数，但是设置过大可能导致内存碎片数值上升。默认值为4K，建议设置为1k~16K。

内存块分配的最小单元非常重要，设置过大可能增加内存碎片的概率发生，太小又可能增加内存分配的消耗，为此在系统平稳运行一个阶段性后，可参考公式的计算值：

查询缓存最小内存块 =(query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache（Qcache_free_memory:The amount of free memory for the query cache; Qcache_queries_in_cache:使用了query  cache查询的数量）

e. query_cache_wlock_invalidate

该参数主要涉及MyISAM引擎，若一个客户端对某表加了写锁，其他客户端发起的查询请求，且查询语句有对应的查询缓存记录，是否允许直接读取查询缓存的记录集信息，还是等待写锁的释放。默认设置为0，也即允许；

五、维护

1.查询缓存区的碎片整理

查询缓存使用一段时间之后，一般都会出现内存碎片，为此需要监控相关状态值，并且定期进行内存碎片的整理，碎片整理的操作语句：FLUSH QUERY CACHE；

2.清空查询缓存的数据

那些操作操作可能触发查询缓存，把所有缓存的信息清空，以避免触发或需要的时候，知道如何做，二类可触发查询缓存数据全部清空的命令：

(1).     RESET QUERY CACHE；

(2).     FLUSH TABLES；

六、性能监控

1.碎片率

查询缓存内存碎片率=Qcache_free_blocks/ Qcache_total_blocks * 100%

2.命中率

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

2.内存使用率

查询缓存内存使用率=(query_cache_size– Qcache_free_memory) / query_cache_size * 100%

3.Qcache_lowmem_prunes

该参数值对于检测查询缓存区的内存大小设置是否，有非常关键性的作用，其代表的意义为：查询缓存去因内存不足而不得不从查询缓存区删除的查询缓存信息，删除算法为LRU。

4.query_cache_size

我们如何判断query_cache_size是否设置过小，依然也只有先预设置一个值，推荐为：32M~128M之间的区域，待系统平稳运行一个时间段（至少1周），并且观察这周内的相关状态值：

(1).     Qcache_lowmem_prunes；

(2).     命中率；

(3).     内存使用率；

若整个平稳运行期监控获得的信息，为命中率高于80%，内存使用率超过80%，并且Qcache_lowmem_prunes的值不停地增加，而且增加的数值还较大，则说明我们为查询缓冲区分配的内存过小，可以适当地增加查询缓存区的内存大小；

若是整个平稳运行期监控获得的信息，为命中率低于40%，Qcache_lowmem_prunes的值也保持一个平稳状态，则说明我们的查询缓冲区的内存设置过大，或者说业务场景重复执行一样查询语句的概率低，同时若还监测到一定量的freeing items，那么必须考虑把查询缓存的内存条小，甚至关闭查询缓存功能；

七、业务场景

通过上述的知识梳理和分析，我们至少知道查询缓存的以下几点：

1.查询缓存能够加速已经存在缓存的查询语句的速度，可以不用重新解析和执行而获得正确得记录集；

2.查询缓存中涉及的表，每一个表对象都有一个属于自己的全局性质的锁；

3.表若是做DDL、FLUSH TABLES 等类似操作，触发相关表的查询缓存信息清空；

4.表对象的DML操作，必须优先判断是否需要清理相关查询缓存的记录信息，将不可避免地出现锁等待事件；

5.查询缓存的内存分配问题，不可避免地产生一些内存碎片；

6.查询缓存对是否是一样的查询语句，要求非常苛刻，而且还不智能；

我们再重新回到本节的重点上，查询缓存适合什么样的业务场景呢？只要是清楚了查询缓存的上述优缺点，就不难罗列出来，业务场景要求：

7.整个系统以读为主的业务，比如门户型、新闻类、报表型、论坛等网站；

8.查询语句操作的表对象，非频繁地进行DML操作，可以使用query_cache_type=2模式，然后SQL语句加SQL_CACHE参数指定。