Mysql性能优化(二)--MySQL监控
1、检查监控服务是否正常提供；
命令：
mysqladmin -uroot -p密码 -h服务器主机地址 ping
正常应该显示:mysqld is alive
2、获取mysql当前的状态值
mysqladmin -uroot -p密码 -h服务器主机地址 status
uptime:服务器启动后的运行时间
Questions: SQL查询数
Threads: 当前连接数
Slow queries:慢查询数
Opens:已经打开的表的数量
Open_table 表示打开过的表数量
Queries per second avg: 平均查询执行时间
3、获取当前数据库当前的连接信息
mysqladmin -uroot -p密码 -h服务器主机地址 ping processlist
4、检查，修复，分析，优化Mysql_Server中相关的表
Mysqlcheck -uroot -p密码 -h服务器主机地址 --all-databases
注意：该命令应该在数据库使用不频繁的时候进行使用；
5、查看mysql是否使用了临时表，临时表过多会使内存转化到磁盘上
命令：
Show status like ‘Created_ tmp%’;
created_tmp_tables=0 表示没有使用临时表
查看服务器对临时表的配置：
show variables where Variable_name in (‘tmp_table_size‘,‘max_heap_table_size‘);
系统会在什么时候使用临时表：服务器在处理语句时会创建内部临时表，用户没有办法直接控制临时表的使用
1、使用union 关键词时
2、评估某些视图，例如使用TEMPTABLE算法 UNION或聚合的视图
3、派生表的时候
4、为子查询或半联接实现而创建的表
5、包含一个ORDER BY子句和另一个GROUP BY子句的语句
6、DISTINCT结合 ORDER BY可能需要一个临时表
7、INSERT ... SELECT从同一表中选择并插入到该表中的语句，MySQL创建了一个内部临时表来保存中的行 SELECT，然后将这些行插入目标表中；
8、多表 UPDATE语句
9、 或 表达式的
如何判断是否使用了临时表？
要确定一条语句是否需要一个临时表，可以使用 EXPLAIN并检查该 Extra列是否显示 Using temporary对于派生或具体化的临时表，EXPLAIN 不一定会说Using temporary。
Select_type值说明
7、连接数监控
命令：
show VARIABLES like "max_connections"; //最大连接数
show global status like ‘Threads_connected‘; //当前连接数
show global status like ‘Threads_running‘; //正在处理的连接数
show global status like ‘Thread%‘; //查看进行情况
Threads的状态有：
： 线程高速缓存中的线程数。
：当前打开的连接数。
：创建用于处理连接的线程数。如果 较大，则可能要增加 值。高速缓存未命中率可以计算为 / 。
：未休眠的线程数。
命令：
show variables like ‘Thread%‘;
参数说明：
thread_cache_size 服务器应缓存多少线程以供重用；如果服务器每秒看到数百个连接，则通常应设置 足够高，以便大多数新连接使用缓存的线程
thread_handling 服务器用于连接线程的线程处理模型
no-threads（服务器使用一个线程来处理一个连接）
one-thread-per-connection（服务器使用一个线程来处理每个客户端连接
thread_stack 每个线程的堆栈大小
命令：show status like ‘Connections‘;
变量说明：
Connections：与MySQL服务器的连接尝试次数（成功或失败）
监控说明：
连接缓存命中率(Threads_Cache_Hit)=(Connections-Threads_connected)/Connections*100
建议该值在90%左右，甚至更高
8、缓存监控
命令：
show global status like ‘Qcache_%‘;
变量说明：
Qcache_free_blocks 查询缓存中的可用内存块数
Qcache_free_memory 查询缓存的可用内存量
Qcache_hits 查询缓存命中数
Qcache_inserts 添加到查询缓存中的查询数
Qcache_lowmem_prunes 由于内存不足而从查询缓存中删除的查询数
Qcache_not_cached 非缓存查询的数量
Qcache_queries_in_cache 在查询缓存中注册的查询数
Qcache_total_blocks 查询缓存中的块总数
命令：show variables like ‘%Query_cache%‘;
变量说明：
have_query_cache YES表示mysqld支持查询缓存，NO则不支持。
query_cache_limit 缓存的最大值，默认值是1M
query_cache_min_res_unit 查询缓存分配的块的最小大小
query_cache_size 分配用于缓存查询结果的内存量
query_cache_type 设置查询缓存类型
query_cache_wlock_invalidate 通常，当一个客户端获取表WRITE 上的锁时MyISAM，如果查询高速缓存中存在查询结果，则不会阻止其他客户端发出从该表读取的语句。将此变量设置为1会导致获取WRITE表的 锁，以使查询缓存中引用该表的所有查询无效。这会强制尝试访问该表的其他客户端在锁定生效时等待。
监控说明：
1、Qcache_free_blocks 如果大约等Qcache_total_blocks/2,则说明碎片非常严重；
2、如果Qcache_lowmem_prunes 的值在增加，并具有大量的自由块，意味着碎片导致查询正在被从缓存中永久删除；
3、缓存碎片率 = Qcache_free_blocks/Qcache_total_blocks*100% 如果超过20% 则可以使用FLUSH QUERY CACHE整理缓存碎片；
4、缓存利用率 = (query_cache_size - Qcache_free_memory)/query_cache_size*100%
如果缓存利用率在25%以下的话则说明query_cache_size设置过大，如果利用率在80%以上而且Qcache_lowmem_prunes>50的话，则说明query_cache_size可能有点小，或者是碎片太多；
9、索引缓存
命令：
show variables like ‘key_buffer_size%‘;
参数说明：
key_buffer_size 是用于索引块的缓冲区的大小
命令：
show status like ‘key%‘;
参数说明：
Key_blocks_not_flushed MyISAM索引缓存中已更改但尚未刷新到磁盘 的索引块数
Key_blocks_unused MyISAM 索引高速缓存中未使用的块数
Key_blocks_used MyISAM 索引高速缓存中使用的块数
Key_read_requests 从MyISAM索引高速缓存中读取索引块的请求数
Key_reads 从磁盘到MyISAM索引高速缓存中的索引块的物理读取数
Key_write_requests 将索引块写入MyISAM索引高速缓存的请求数 。
Key_writes 从MyISAM索引高速缓存到磁盘的索引块的物理写入次数
监控说明：
1、索引缓存未命中率(key_cache_miss_rate)= /
1%表示每100个索引中有1个在缓存中找不到，要直接从硬盘中读取
2、key_reads 建议<0.1%
3、索引缓存命中率
Key_buffer_read_hits=(1-Key_reads/Key_read_requests)*100%
Key_buffer_write_hits = (1-Key_writes/Key_write_requests)*100%
这两个值应该越大越好；
10、表扫描情况
命令:
show global status like ‘handler_read%‘;
变量说明:
Handler_read_first 索引中第一个条目的读取次数。如果此值很高，则表明服务器正在执行很多全索引扫描
Handler_read_key 基于键读取行的请求数。如果此值很高，则表明表已为查询正确索引。
Handler_read_last 读取索引中最后一个键的请求数
Handler_read_next 按键顺序读取下一行的请求数。如果要查询具有范围约束的索引列或进行索引扫描，则此值将增加
Handler_read_prev 按键顺序读取上一行的请求数。这种读取方法主要用于优化ORDER BY ... DESC
Handler_read_rnd 基于固定位置读取行的请求数。如果要执行很多需要对结果进行排序的查询，则此值很高。可能有很多查询需要MySQL扫描整个表，或者联接未正确使用键。
Handler_read_rnd_next 读取数据文件下一行的请求数。如果要进行大量表扫描，则此值较高。通常，这表明您的表未正确建立索引，或者未编写查询以利用拥有的索引。
命令：
show global status like ‘com_select‘;
变量说明：
Com_select 每次查询语句已被执行的次数；
监控说明：
表扫描率 = Handler_read_rnd_next/com_select
如果表扫描率超过4000，说明进行了太多表的扫描，很有可能索引没有建好，增加read_buffer_size 值会好一些，但最好不要超过8M;
好了，以上就是MySQL性能优化的第二步操作了，针对MySQL的监控，更多技术交流请加QQ群：1085210541