Mysql查询缓存碎片、缓存命中率及Nagios监控

2024-01-17 06:58

本文主要是介绍Mysql查询缓存碎片、缓存命中率及Nagios监控,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

Mysql 的优化方案,在互联网上可以查找到非常多资料,今天对Mysql缓存碎片和命中率作了详细了解,个人作了简单整理。


一、Mysql查询缓存碎片和缓存命中率。



  1. mysql> SHOW STATUS LIKE 'qcache%';
  2. +-------------------------+-----------+
  3. | Variable_name | Value |
  4. +-------------------------+-----------+
  5. | Qcache_free_blocks | 5 |
  6. | Qcache_free_memory | 134176648 |
  7. | Qcache_hits | 110 |
  8. | Qcache_inserts | 245 |
  9. | Qcache_lowmem_prunes | 0 |
  10. | Qcache_not_cached | 7119 |
  11. | Qcache_queries_in_cache | 9 |
  12. | Qcache_total_blocks | 31 |
  13. +-------------------------+-----------+
  14. 8 rows in set (0.01 sec)

MySQL 查询缓存变量

变量名

说明

Qcache_free_blocks

缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE 会对缓存中的碎片进行整理,从而得到一个空闲块。

Qcache_free_memory

缓存中的空闲内存。

Qcache_hits

每次查询在缓存中命中时就增大。

Qcache_inserts

每次插入一个查询时就增大。命中次数除以插入次数就是不中比率;用 1 减去这个值就是命中率。在上面这个例子中,大约有 87% 的查询都在缓存中命中。

Qcache_lowmem_prunes

缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看;如果这个数字在不断增长,就表示可能碎片非常严重,或者内存很少。(上面的 free_blocks free_memory 可以告诉您属于哪种情况)。

Qcache_not_cached

不适合进行缓存的查询的数量,通常是由于这些查询不是 SELECT 语句。

Qcache_queries_in_cache

当前缓存的查询(和响应)的数量。

Qcache_total_blocks

缓存中块的数量。




  1. mysql>SHOW VARIABLES LIKE '%query_cache%';

  2. +------------------------------+-----------+

  3. | Variable_name | Value |

  4. +------------------------------+-----------+

  5. | have_query_cache | YES |

  6. | query_cache_limit | 1048576 |

  7. | query_cache_min_res_unit | 4096 |

  8. | query_cache_size | 134217728 |

  9. | query_cache_type | ON |

  10. | query_cache_wlock_invalidate | OFF |

  11. +------------------------------+-----------+

  12. 6 rows in set (0.00 sec)

query_cache_min_res_unit 查询缓存分配的最小块的大小(字节)

query_alloc_block_size 为查询分析和执行过程中创建的对象分配的内存块大小
Qcache_free_blocks 代表内存自由块的多少,反映了内存碎片的情况

==========================
1)当查询进行的时候,Mysql把查询结果保存在qurey cache中,但如果要保存的结果比较大,超过query_cache_min_res_unit的值 ,这时候mysql将一边检索结果,一边进行保存结果,所以,有时候并不是把所有结果全部得到后再进行一次性保存,而是每次分配一块 query_cache_min_res_unit 大小的内存空间保存结果集,使用完后,接着再分配一个这样的块,如果还不不够,接着再分配一个块,依此类推,也就是说,有可能在一次查询中,mysql要 进行多次内存分配的操作。
2)内存碎片的产生。当一块分配的内存没有完全使用时,MySQL会把这块内存Trim掉,把没有使用的那部分归还以重 复利用。比如,第一次分配4KB,只用了3KB,剩1KB,第二次连续操作,分配4KB,用了2KB,剩2KB,这两次连续操作共剩下的 1KB+2KB=3KB,不足以做个一个内存单元分配, 这时候,内存碎片便产生了。
3)使用flush query cache,可以消除碎片

4)如果Qcache_free_blocks值过大,可能是query_cache_min_res_unit值过大,应该调小些
5query_cache_min_res_unit的估计值:(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

检查查询缓存使用情况

检查是否从查询缓存中受益的最简单的办法就是检查缓存命中率

当服务器收到SELECT 语句的时候,Qcache_hits Com_select 这两个变量会根据查询缓存

的情况进行递增

查询缓存命中率的计算公式是:Qcache_hits/(Qcache_hits + Com_select)

mysql> show status like '%Com_select%';

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

| Variable_name | Value |

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

| Com_select | 1 |

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

1 row in set (0.00 sec)

此时的查询缓存命中率:3/3+1=75%;由于个人的测试数据库,查询较少,更行更少,命中率颇高。


二、监控缓存命中率

通过Nagios+pnp4nagios来监控缓存命中率,并通过图表来展示。

1、监控脚本: check_mysql_qch.sh.sh


  1. #!/bin/bash

  2. #function:查询缓存命中率

  3. #time:20121130

  4. #author:system group

  5. while getopts ":w:c:h" optname

  6. do

  7. case "$optname" in

  8. "w")

  9. WARN=$OPTARG

  10. ;;

  11. "c")

  12. CIRT=$OPTARG

  13. ;;

  14. "h")

  15. echo "Useage: check_mysql_qch.sh -w warn -c cirt"

  16. exit

  17. ;;

  18. "?")

  19. echo "Unknown option $OPTARG"

  20. exit

  21. ;;

  22. ":")

  23. echo "No argument value for option $OPTARG"

  24. exit

  25. ;;

  26. *)

  27. # Should not occur

  28. echo "Unknown error while processing options"

  29. exit

  30. ;;

  31. esac

  32. done

  33. [ $? -ne 0 ] && echo "error: Unknown option " && exit

  34. [ -z $WARN ] && WARN=60

  35. [ -z $CIRT ] && CIRT=50

  36. export selete=`/usr/local/mysql/bin/mysql -h 127.0.0.1 -uroot -Bse "SHOW GLOBAL STATUS LIKE 'Com_select';" |awk '{print $2}'`

  37. export hits=`/usr/local/mysql/bin/mysql -h 127.0.0.1 -uroot -Bse "SHOW GLOBAL STATUS LIKE 'Qcache_hits';" |awk '{print $2}'`

  38. a=$(($selete+$hits))

  39. #rw_ratio=$(($a/$b))

  40. #echo "rw_ratio=$rw_ratio"

  41. #ratio=$(($rw_ratio*100))

  42. #echo "ratio=$ratio"

  43. if [ $a -ne "0" ];then

  44. percent=`awk 'BEGIN{printf "%.2f%\n",('$hits'/'$a')*100}'`

  45. Qch=`awk 'BEGIN{printf ('$hits'/'$a')*100}'`

  46. fi

  47. C=`echo "$Qch < $CIRT" | bc`

  48. W=`echo "$Qch < $WARN" | bc`

  49. O=`echo "$Qch > $WARN" | bc`

  50. if [ $C == 1 ];then

  51. echo -e "CIRT - Mysql Qcache Hits is $percent,Com_select is $selete,Qcache_hits is $hits|Qcache_hits=${Qch}%;${selete};${hits};0"

  52. exit 2

  53. fi

  54. if [ $W == 1 ];then

  55. echo -e "WARN - Mysql Qcache Hits is $percent,Com_select is $selete,Qcache_hits is $hits|Qcache_hits=${Qch}%;${selete};${hits};0"

  56. exit 1

  57. fi

  58. if [ $O == 1 ];then

  59. echo -e "OK - Mysql Qcache Hits is $percent,Com_select is $selete,Qcache_hits is $hits|Qcache_hits=${Qch}%;${selete};${hits};0"

  60. exit 0

  61. fi

2、生成报表

Pnp4nagios templatescheck_mysql_qch.php


  1. <?php

  2. #

  3. # Copyright (c) 2006-2010 system (http://www.cnfol.com)

  4. # Plugin: check_mysql_qch

  5. #

  6. $opt[1] = "--vertical-label hits/s -l0 --title \"Mysql Qcache Hits for $hostname / $servicedesc\" ";

  7. #

  8. #

  9. #

  10. $def[1]= rrd::def("var1", $RRDFILE[1], $DS[1], "AVERAGE");

  11. if ($WARN[1] != "") {

  12. $def[1] .= "HRULE:$WARN[1]#FFFF00 ";

  13. }

  14. if ($CRIT[1] != "") {

  15. $def[1] .= "HRULE:$CRIT[1]#FF0000 ";

  16. }

  17. $def[1] .= rrd::area("var1", "#0000FF", "Mysql Qcache Hits percent") ;

  18. $def[1] .= rrd::gprint("var1", array("LAST", "AVERAGE", "MAX"), "%6.2lf");

  19. ?>

结果:

(监控状态)


这篇关于Mysql查询缓存碎片、缓存命中率及Nagios监控的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/615196

相关文章

MySQL 中的 JSON 查询案例详解

《MySQL中的JSON查询案例详解》:本文主要介绍MySQL的JSON查询的相关知识,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql 的 jsON 路径格式基本结构路径组件详解特殊语法元素实际示例简单路径复杂路径简写操作符注意MySQL 的 J

Go语言开发实现查询IP信息的MCP服务器

《Go语言开发实现查询IP信息的MCP服务器》随着MCP的快速普及和广泛应用,MCP服务器也层出不穷,本文将详细介绍如何在Go语言中使用go-mcp库来开发一个查询IP信息的MCP... 目录前言mcp-ip-geo 服务器目录结构说明查询 IP 信息功能实现工具实现工具管理查询单个 IP 信息工具的实现服

Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

《Windows上如果忘记了MySQL密码重置密码的两种方法》:本文主要介绍Windows上如果忘记了MySQL密码重置密码的两种方法,本文通过两种方法结合实例代码给大家介绍的非常详细,感... 目录方法 1:以跳过权限验证模式启动 mysql 并重置密码方法 2:使用 my.ini 文件的临时配置在 Wi

MySQL重复数据处理的七种高效方法

《MySQL重复数据处理的七种高效方法》你是不是也曾遇到过这样的烦恼:明明系统测试时一切正常,上线后却频频出现重复数据,大批量导数据时,总有那么几条不听话的记录导致整个事务莫名回滚,今天,我就跟大家分... 目录1. 重复数据插入问题分析1.1 问题本质1.2 常见场景图2. 基础解决方案:使用异常捕获3.

SQL中redo log 刷⼊磁盘的常见方法

《SQL中redolog刷⼊磁盘的常见方法》本文主要介绍了SQL中redolog刷⼊磁盘的常见方法,将redolog刷入磁盘的方法确保了数据的持久性和一致性,下面就来具体介绍一下,感兴趣的可以了解... 目录Redo Log 刷入磁盘的方法Redo Log 刷入磁盘的过程代码示例(伪代码)在数据库系统中,r

mysql中的group by高级用法

《mysql中的groupby高级用法》MySQL中的GROUPBY是数据聚合分析的核心功能,主要用于将结果集按指定列分组,并结合聚合函数进行统计计算,下面给大家介绍mysql中的groupby用法... 目录一、基本语法与核心功能二、基础用法示例1. 单列分组统计2. 多列组合分组3. 与WHERE结合使

Mysql用户授权(GRANT)语法及示例解读

《Mysql用户授权(GRANT)语法及示例解读》:本文主要介绍Mysql用户授权(GRANT)语法及示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql用户授权(GRANT)语法授予用户权限语法GRANT语句中的<权限类型>的使用WITH GRANT

Mysql如何解决死锁问题

《Mysql如何解决死锁问题》:本文主要介绍Mysql如何解决死锁问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录【一】mysql中锁分类和加锁情况【1】按锁的粒度分类全局锁表级锁行级锁【2】按锁的模式分类【二】加锁方式的影响因素【三】Mysql的死锁情况【1

SQL BETWEEN 的常见用法小结

《SQLBETWEEN的常见用法小结》BETWEEN操作符是SQL中非常有用的工具,它允许你快速选取某个范围内的值,本文给大家介绍SQLBETWEEN的常见用法,感兴趣的朋友一起看看吧... 在SQL中,BETWEEN是一个操作符,用于选取介于两个值之间的数据。它包含这两个边界值。BETWEEN操作符常用

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》:本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分