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、GROUP_CONCAT() 或COUNT(DISTINCT) 表达式的

如何判断是否使用了临时表？

  要确定一条语句是否需要一个临时表，可以使用 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的状态有：

Threads_cached：  线程高速缓存中的线程数。

Threads_connected：当前打开的连接数。

Threads_created：创建用于处理连接的线程数。如果 Threads_created较大，则可能要增加 thread_cache_size值。高速缓存未命中率可以计算为 Threads_created/ Connections。

Threads_running：未休眠的线程数。

命令：

show variables like 'Thread%';

参数说明：

thread_cache_size    服务器应缓存多少线程以供重用；如果服务器每秒看到数百个连接，则通常应设置 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)= Key_reads/ Key_read_requests

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