Mysql的查询缓存区的优化
[
2009/02/17 11:27 | by askwan ]
2009/02/17 11:27 | by askwan ]
本文接着我的上篇文章来
Mysql的查询缓存区query cache http://www.askwan.com/post/141/
查看查询缓存区的相关变量：
have_query_cache
是否支持查询缓存区 “YES”表是支持查询缓存区
query_cache_limit 可缓存的Select查询结果的最大值 1048576 byte /1024 = 1024kB 即最大可缓存的select查询结果必须小于1024KB
query_cache_min_res_unit 每次给query cache结果分配内存的大小 默认是 4096 byte 也即 4kB
在这里，我延伸三点重要的东西
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.内存块的概念，先看下这个：
Qcache_total_blocks 表示所有的块
Qcache_free_blocks 表示未使用的块 这个值比较大，那意味着，内存碎片比较多，用flush query cache清理后，
为被使用的块其值应该为1或0 ，因为这时候所有的内存都做为一个连续的快在一起了：
其他几个状态变量的意义：
Qcache_free_memory 表示查询缓存区现在还有多少的可用内存
Qcache_hits 表示查询缓存区的命中个数，也就是直接从查询缓存区作出响应处理的查询个数
Qcache_inserts 表示查询缓存区此前总过缓存过多少条查询命令的结果
Qcache_lowmem_prunes 表示查询缓存区已满而从其中溢出和删除的查询结果的个数
Qcache_not_cached 表示没有进入查询缓存区的查询命令个数
Qcache_queries_in_cache 查询缓存区当前缓存着多少条查询命令的结果
优化提示：
如果Qcache_lowmem_prunes 值比较大，表示查询缓存区大小设置太小，需要增大。
如果Qcache_free_blocks 较多，表示内存碎片较多，需要清理，flush query cache
根据我看的 《High Performance MySQL》中所述，关于query_cache_min_res_unit大小的调优
，书中给出了一个计算公式，可以供调优设置参考：
query_cache_min_res_unit = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
还要注意一点的是，FLUSH QUERY CACHE 命令可以用来整理查询缓存区的碎片，改善内存使用状况，
但不会清理查询缓存区的内容，这个要和RESET QUERY CACHE相区别，不要混淆，后者才是清除查询缓存区中
的所有的内容。
-------------------END--------------------
Mysql的查询缓存区query cache http://www.askwan.com/post/141/
查看查询缓存区的相关变量：
mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 33554432 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
6 rows in set (0.00 sec)
have_query_cache
是否支持查询缓存区 “YES”表是支持查询缓存区
query_cache_limit 可缓存的Select查询结果的最大值 1048576 byte /1024 = 1024kB 即最大可缓存的select查询结果必须小于1024KB
query_cache_min_res_unit 每次给query cache结果分配内存的大小 默认是 4096 byte 也即 4kB
在这里，我延伸三点重要的东西
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.内存块的概念，先看下这个：
mysql> show status like 'qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 5096 |
| Qcache_free_memory | 18964096 |
| Qcache_hits | 12192192 |
| Qcache_inserts | 3560370 |
| Qcache_lowmem_prunes | 17326 |
| Qcache_not_cached | 303599 |
| Qcache_queries_in_cache | 10201 |
| Qcache_total_blocks | 25937 |
+-------------------------+----------+
8 rows in set (0.00 sec)
Qcache_total_blocks 表示所有的块
Qcache_free_blocks 表示未使用的块 这个值比较大，那意味着，内存碎片比较多，用flush query cache清理后，
为被使用的块其值应该为1或0 ，因为这时候所有的内存都做为一个连续的快在一起了：
mysql> show status like 'qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 18539240 |
| Qcache_hits | 12192502 |
| Qcache_inserts | 3560515 |
| Qcache_lowmem_prunes | 17326 |
| Qcache_not_cached | 303607 |
| Qcache_queries_in_cache | 10318 |
| Qcache_total_blocks | 21081 |
+-------------------------+----------+
8 rows in set (0.00 sec)
其他几个状态变量的意义：
Qcache_free_memory 表示查询缓存区现在还有多少的可用内存
Qcache_hits 表示查询缓存区的命中个数，也就是直接从查询缓存区作出响应处理的查询个数
Qcache_inserts 表示查询缓存区此前总过缓存过多少条查询命令的结果
Qcache_lowmem_prunes 表示查询缓存区已满而从其中溢出和删除的查询结果的个数
Qcache_not_cached 表示没有进入查询缓存区的查询命令个数
Qcache_queries_in_cache 查询缓存区当前缓存着多少条查询命令的结果
优化提示：
如果Qcache_lowmem_prunes 值比较大，表示查询缓存区大小设置太小，需要增大。
如果Qcache_free_blocks 较多，表示内存碎片较多，需要清理，flush query cache
根据我看的 《High Performance MySQL》中所述，关于query_cache_min_res_unit大小的调优
，书中给出了一个计算公式，可以供调优设置参考：
query_cache_min_res_unit = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
还要注意一点的是，FLUSH QUERY CACHE 命令可以用来整理查询缓存区的碎片，改善内存使用状况，
但不会清理查询缓存区的内容，这个要和RESET QUERY CACHE相区别，不要混淆，后者才是清除查询缓存区中
的所有的内容。
-------------------END--------------------
Mysql的查询缓存区query cache
[ 2009/02/16 17:36 | by askwan ]
2009/02/16 17:36 | by askwan ]
查询缓存区（query cache）是Mysql 4.0版本后引入的一项功能，本意是提高SQL查询性能，即把某些SQL查询命令的结果存放在内存里，当其他用户再次执行一条完全相同的查询命令时，Mysql将会把缓存在内存里的查询结果直接发送给他，而不用再对数据库表进行查询。
但是并不是说查询缓存区就只有优点，而没有缺点，我举个例子，一个项目里如果是update， delete ，insert等数据库操作比select操作更多，那我想觉得这时候就不适合开启查询缓存区的功能，为什么呢，原因很简单，查询缓存区缓存的是查询的结果，如果数据库表内容经常的改变，那很多查询结果多必须从查询缓存区里删除掉，特别是内存较小的系统，这样的话，只会加剧数据库的负担。
基于这一点，用不用开启查询缓存区的功能，查阅不少文档后，总结几条如下：
1. 就是查询缓存区适合select 操作较多，而insert update delete操作较少的情况，并且重复的SQL查询越多，效果越好。
2. 就是SQL查询语句命令必须严格的相同，包括字母大小写，空格，举个例子
有这么两行查询语句，
咋看怎么长的都一样，而且查询的结果也是一样的，是的，没错，除了“select”和“Select”一个字母大小写的不同外，其他的都一样，但是在Mysql内部，SQL分析器却会把这两条长的一样的语句当作两条不同的语句，mysql的确很傻很天真，嘿嘿，
这里还要注意的是mysql对数据库和数据表是区分大小写的，而对数据列是不区分大小写的。
3.就是select 命令里不能含有一些特殊的函数，比如CUDATE(),NOW(),有这些函数在里面，查询缓存区几乎不起作用，只会给系统带来额外开销。
查询缓存区的启用
如果 query_cache_size=0，那么当前没有开启查询缓存区
在my.cnf的[mysqld]配置段
开启查询缓存区有几个相关项，如
query_cache_size =128M
query_cache_type = 1 (有三个值，分别是0，1，2)
qurey_cache_limit=128K;
意思是给启用查询缓存区并给查询缓存区分配128M的内存空间， 而且允许查询的结果数据必须不大于128KB大小
query_cache_type为1，表示在SQL语句里加上了SQL_NO_CACHE关键字的，强制不缓存查询结果。
query_cache_type为0，表示关闭SQL语句里的SQL_CACHE和SQL_NO_CACHE功能，也就是禁用查询缓存功能。
query_cache_type为2，表示在SQL语句里，只有包含着SQL_CACHE的查询才会进入查询缓存区。
下篇我将介绍下查询缓存区的优化方法。
------------------END------------------
但是并不是说查询缓存区就只有优点，而没有缺点，我举个例子，一个项目里如果是update， delete ，insert等数据库操作比select操作更多，那我想觉得这时候就不适合开启查询缓存区的功能，为什么呢，原因很简单，查询缓存区缓存的是查询的结果，如果数据库表内容经常的改变，那很多查询结果多必须从查询缓存区里删除掉，特别是内存较小的系统，这样的话，只会加剧数据库的负担。
基于这一点，用不用开启查询缓存区的功能，查阅不少文档后，总结几条如下：
1. 就是查询缓存区适合select 操作较多，而insert update delete操作较少的情况，并且重复的SQL查询越多，效果越好。
2. 就是SQL查询语句命令必须严格的相同，包括字母大小写，空格，举个例子
有这么两行查询语句，
select username,email from members order by username limit 10;
Select username,email from members order by username limit 10;
咋看怎么长的都一样，而且查询的结果也是一样的，是的，没错，除了“select”和“Select”一个字母大小写的不同外，其他的都一样，但是在Mysql内部，SQL分析器却会把这两条长的一样的语句当作两条不同的语句，mysql的确很傻很天真，嘿嘿，
这里还要注意的是mysql对数据库和数据表是区分大小写的，而对数据列是不区分大小写的。
3.就是select 命令里不能含有一些特殊的函数，比如CUDATE(),NOW(),有这些函数在里面，查询缓存区几乎不起作用，只会给系统带来额外开销。
查询缓存区的启用
如果 query_cache_size=0，那么当前没有开启查询缓存区
在my.cnf的[mysqld]配置段
开启查询缓存区有几个相关项，如
query_cache_size =128M
query_cache_type = 1 (有三个值，分别是0，1，2)
qurey_cache_limit=128K;
意思是给启用查询缓存区并给查询缓存区分配128M的内存空间， 而且允许查询的结果数据必须不大于128KB大小
query_cache_type为1，表示在SQL语句里加上了SQL_NO_CACHE关键字的，强制不缓存查询结果。
query_cache_type为0，表示关闭SQL语句里的SQL_CACHE和SQL_NO_CACHE功能，也就是禁用查询缓存功能。
query_cache_type为2，表示在SQL语句里，只有包含着SQL_CACHE的查询才会进入查询缓存区。
下篇我将介绍下查询缓存区的优化方法。
------------------END------------------
Mysql监控快速指南
[ 2009/02/15 09:20 | by askwan ]
2009/02/15 09:20 | by askwan ]
Mysql作为广泛应用的数据库系统，平时运维工作中对她的监控必不可少，现在把我对Mysql数据库的监控体会写成下文，欢迎拍砖。
无论是DBA或是SA，监控的目标都很明确，无外乎
1.快速的得到Mysql过去一段时间或者当前运行的状态
2.因硬件升级或者系统配置的改变而诊断对Mysql数据库性能影响
3.在Mysql数据库系统出现故障要能够及时收到告警
4.为日后编写运维报告提供各项数据指标供分析
5.……
想到了再做补充。而所有以上这些目的，通过各种方法和手段都可以做到。
自从Mysql数据库系统启动并且提供服务，Mysql内部自身的一套“计数器”就开始工作，可以通过
Show Global Status ， Show global variables ，Show full processlist
得到Mysql数据库系统当前各种系统变量和状态 ，并且后文介绍个各种监控工具几乎都是对这三个指令的输出
进行数据的计算和统计分析。
无论是DBA或是SA，监控的目标都很明确，无外乎
1.快速的得到Mysql过去一段时间或者当前运行的状态
2.因硬件升级或者系统配置的改变而诊断对Mysql数据库性能影响
3.在Mysql数据库系统出现故障要能够及时收到告警
4.为日后编写运维报告提供各项数据指标供分析
5.……
想到了再做补充。而所有以上这些目的，通过各种方法和手段都可以做到。
自从Mysql数据库系统启动并且提供服务，Mysql内部自身的一套“计数器”就开始工作，可以通过
Show Global Status ， Show global variables ，Show full processlist
得到Mysql数据库系统当前各种系统变量和状态 ，并且后文介绍个各种监控工具几乎都是对这三个指令的输出
进行数据的计算和统计分析。
分析Mysql慢查询日志的几把瑞士军刀
[ 2009/02/13 11:37 | by askwan ]
2009/02/13 11:37 | by askwan ]
第一把 mysqldumpslow
属于Mysql嫡系血统，Mysql自带，可以统计各个SQL慢查询 出现次数 执行的最长时间 累计总消耗时间 等待锁的时间 返回行数和返回总行数等
第二把 mysqlsla
官方网站：http://hackmysql.com
这网站除了这个, 还share了不少其他mysql 相关的武器 ,个个强悍!
统计出来的信息非常全面 ,就不依依表述了 ,能快速定位主要问题, 强烈推荐.
第三把 mysql-explain-slow-log
一个perl写的脚本 ;
下载：http://www.willamowius.de/mysql-tools.html
用后感觉功能就一般般.
第四把 mysql-log-filter
与mysqldumpslow类似 ,功能相当.
第五把 myprofi
一个php脚本
下载地址http://myprofi.sourceforge.net/
分析结果比较简洁试用.
ps:记录mysql满查询,在配置文件my.cnf中修改
long_query_time = 1
log-slow-queries = /usr/local/mysql/data/slow.log
log-queries-not-using-indexes
功能最强大还算mysqlsla, 如果再搭配其他几把刀, SQL 写的有问题的或者需要优化的语句 ,几乎可以一网打尽了 ！
