mysql监控调优.docx

mysql监控调优.docx

《mysql监控调优.docx》由会员分享，可在线阅读，更多相关《mysql监控调优.docx（14页珍藏版）》请在冰豆网上搜索。

mysql监控调优

运行中的mysql状态查看,对正在运行的mysql进行监控，其中一个方式就是查看mysql运行状态。

（1）QPS（每秒Query量） 

QPS=Questions（orQueries）/seconds 

mysql>show global statuslike'Question%'; 

（2）TPS（每秒事务量） 

TPS=（Com_commit+Com_rollback）/seconds 

mysql>showglobalstatuslike'Com_commit'; 

mysql>showglobalstatuslike'Com_rollback'; 

（3）keyBuffer命中率 

mysql>show global status like 'key%'; 

key_buffer_read_hits=（1-key_reads/key_read_requests）*100% 

key_buffer_write_hits=（1-key_writes/key_write_requests）*100% 

（4）InnoDBBuffer命中率 

mysql>showstatuslike'innodb_buffer_pool_read%'; 

innodb_buffer_read_hits=（1-innodb_buffer_pool_reads/innodb_buffer_pool_read_requests）*100% 

（5）QueryCache命中率 

mysql>showstatuslike'Qcache%'; 

Query_cache_hits=（Qcahce_hits/（Qcache_hits+Qcache_inserts））*100%; 

（6）TableCache状态量 

mysql>showglobal statuslike'open%'; 

比较open_tables 与opend_tables值 

（7）ThreadCache命中率 

mysql>showglobalstatuslike'Thread%'; 

mysql>showglobalstatuslike'Connections'; 

Thread_cache_hits=（1-Threads_created/connections）*100% 

（8）锁定状态 

mysql>showglobal statuslike'%lock%'; 

Table_locks_waited/Table_locks_immediate=0.3% 如果这个比值比较大的话，说明表锁造成的阻塞比较严重 

Innodb_row_lock_waitsinnodb行锁，太大可能是间隙锁造成的 

（9）复制延时量 

mysql>showslavestatus 

查看延时时间 

（10）TmpTable状况（临时表状况） 

mysql>showstatuslike'Create_tmp%'; 

Created_tmp_disk_tables/Created_tmp_tables比值最好不要超过10%，如果Created_tmp_tables值比较大， 

可能是排序句子过多或者是连接句子不够优化 

（11）BinlogCache使用状况 

mysql>showstatuslike'Binlog_cache%'; 

如果Binlog_cache_disk_use值不为0，可能需要调大binlog_cache_size大小 

（12）Innodb_log_waits量 

mysql>showstatuslike'innodb_log_waits'; 

Innodb_log_waits值不等于0的话，表明innodblog buffer因为空间不足而等待

性能指标

1QPS计算（每秒查询数）

针对MyISAM引擎为主的DB

mysql>showGLOBALstatuslike'questions';

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

|Variable_name|Value|

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

|Questions|2009191409|

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

1rowinset（0.00sec）

mysql>showglobalstatuslike'uptime';

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

|Variable_name|Value|

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

|Uptime|388402|

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

1rowinset（0.00sec）

QPS=questions/uptime=5172，mysql自启动以来的平均QPS，如果要计算某一时间段的QPS，可在高峰期间获取间隔时间t2-t1，然后分别计算出t2和t1时刻的q值，QPS=（q2-q1）/（t2-t1）

针对InnnoDB引擎为主的DB

mysql>showglobalstatuslike'_update';

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

|Variable_name|Value|

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

|Com_update|87094306|

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

1rowinset（0.00sec）

mysql>showglobalstatuslike'_select';

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

|Variable_name|Value|

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

|Com_select|1108143397|

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

1rowinset（0.00sec）

mysql>showglobalstatuslike'_delete';

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

|Variable_name|Value|

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

|Com_delete|379058|

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

1rowinset（0.00sec）

mysql>showglobalstatuslike'uptime';

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

|Variable_name|Value|

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

|Uptime|388816|

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

1rowinset（0.00sec）

QPS=（_update+_insert+_delete+_select）/uptime=3076,某一时间段的QPS查询方法同上。

2TPS计算（每秒事务数）

mysql>showglobalstatuslike'_commit';

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

|Variable_name|Value|

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

|Com_commit|7424815|

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

1rowinset（0.00sec）

mysql>showglobalstatuslike'_rollback';

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

|Variable_name|Value|

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

|Com_rollback|1073179|

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

1rowinset（0.00sec）

mysql>showglobalstatuslike'uptime';

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

|Variable_name|Value|

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

|Uptime|389467|

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

1rowinset（0.00sec）

TPS=（_commit+_rollback）/uptime=22

3线程连接数和命中率

mysql>showglobalstatuslike'threads_%';

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

|Variable_name|Value|

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

|Threads_cached|480|//代表当前此时此刻线程缓存中有多少空闲线程

|Threads_connected|153|//代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数

|Threads_created|20344|//代表从最近一次服务启动，已创建线程的数量

|Threads_running|2|//代表当前激活的（非睡眠状态）线程数

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

4rowsinset（0.00sec）

mysql>showglobalstatuslike'Connections';

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

|Variable_name|Value|

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

|Connections|381487397|

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

1rowinset（0.00sec）

线程缓存命中率=1-Threads_created/Connections=99.994%

我们设置的线程缓存个数

mysql>showvariableslike'%thread_cache_size%';

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

|Variable_name|Value|

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

|thread_cache_size|500|

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

1rowinset（0.00sec）

根据Threads_connected可预估thread_cache_size值应该设置多大，一般来说250是一个不错的上限值，如果存足够大，也可以设置成thread_cache_size值和threaads_connected值相同；

或者通过观察threads_created值，如果该值很大或一直在增长，可以适当增加thread_cache_size的值；在休眠状态下每个线程大概占用256KB左右的存，所以当存足够时，设置太小也不会节约太多存，除非该值已经超过几千。

4表缓存

mysql>showglobalstatuslike'open_tables%';

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

|Variable_name|Value|

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

|Open_tables|2228|

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

1rowinset（0.00sec）

我们设置的打开表的缓存和表定义缓存

mysql>showvariableslike'table_open_cache';

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

|Variable_name|Value|

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

|table_open_cache|16384|

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

1rowinset（0.00sec）

mysql>showvariableslike'table_defi%';

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

|Variable_name|Value|

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

|table_definition_cache|2000|

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

1rowinset（0.00sec）

针对MyISAM：

mysql每打开一个表，都会读入一些数据到table_open_cache缓存中，当mysql在这个缓存中找不到相应的信息时，才会去磁盘上直接读取，所以该值要设置得足够大以避免需要重新打开和重新解析表的定义，一般设置为max_connections的10倍，但最好保持在10000以。

还有种依据就是根据状态open_tables的值进行设置，如果发现open_tables的值每秒变化很大，那么可能需要增大table_open_cache的值。

table_definition_cache通常简单设置为服务器中存在的表的数量，除非有上万表。

针对InnoDB：

与MyISAM不同，InnoDB的opentable和openfile并无直接联系，即打开frm表时其相应的ibd文件可能处于关闭状态；

故InnoDB只会用到table_definiton_cache，不会使用table_open_cache；

其frm文件保存于table_definition_cache中，而idb则由innodb_open_files决定（前提是开启了innodb_file_per_table），最好将innodb_open_files设置得足够大，使得服务器可以保持所有的.ibd文件同时打开。

5最接数

mysql>showglobalstatuslike'Max_used_connections';

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

|Variable_name|Value|

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

|Max_used_connections|1785|

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

1rowinset（0.00sec）

我们设置的max_connections大小

mysql>showvariableslike'max_connections%';

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

|Variable_name|Value|

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

|max_connections|4000|

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

1rowinset（0.00sec）

通常max_connections的大小应该设置为比Max_used_connections状态值大，Max_used_connections状态值反映服务器连接在某个时间段是否有尖峰，如果该值大于max_connections值，代表客户端至少被拒绝了一次，可以简单地设置为符合以下条件：

Max_used_connections/max_connections=0.8

6Innodb缓存命中率

mysql>showglobalstatuslike'innodb_buffer_pool_read%';

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

|Variable_name|Value|

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

|Innodb_buffer_pool_read_ahead_rnd|0|

|Innodb_buffer_pool_read_ahead|268720|//预读的页数

|Innodb_buffer_pool_read_ahead_evicted|0|

|Innodb_buffer_pool_read_requests|0|//从缓冲池中读取的次数

|Innodb_buffer_pool_reads|29912739|//表示从物理磁盘读取的页数

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

5rowsinset（0.00sec）

缓冲池命中率=（Innodb_buffer_pool_read_requests）/（Innodb_buffer_pool_read_requests+Innodb_buffer_pool_read_ahead+Innodb_buffer_pool_reads）=99.994%

如果该值小于99.9%，建议就应该增大innodb_buffer_pool_size的值了，该值一般设置为存总大小的75%-85%，或者计算出操作系统所需缓存+mysql每个连接所需的存（例如排序缓冲和临时表）+MyISAM键缓存，剩下的存都给innodb_buffer_pool_size，不过也不宜设置太大，会造成存的频繁交换，预热和关闭时间长等问题。

7MyISAMKeyBuffer命中率和缓冲区使用率

mysql>showglobalstatuslike'key_%';

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

|Variable_name|Value|

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

|Key_blocks_not_flushed|0|

|Key_blocks_unused|106662|

|Key_blocks_used|107171|

|Key_read_requests|883825678|

|Key_reads|133294|

|Key_write_requests|217310758|

|Key_writes|2061054|

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

7rowsinset（0.00sec）

mysql>showvariableslike'%key_cache_block_size%';

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

|Variable_name|Value|

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

|key_cache_block_size|1024|

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

1rowinset（0.00sec）

mysql>showvariableslike'%key_buffer_size%';

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

|Variable_name|Value|

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

|key_buffer_size|134217728|

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

1rowinset（0.00sec）

缓冲区的使用率=1-（Key_blocks_unused*key_cache_block_size/key_buffer_size）=18.6%

读命中率=1-Key_reads/Key_read_requests=99.98%

写命中率=1-Key_writes/Key_write_requests=99.05%

可看到缓冲区的使用率并不高，如果很长一段时间后还没有使用完所有的键缓冲，可以考虑把缓冲区调小一点。

键缓存命中率可能意义不大，因为它和应用相关，有些应用在95%的命中率下就工作良好，有些则需要99.99%，所以从经验上看，每秒的缓存未命中次数更重要，假设一个独立磁盘每秒能做100个随机读，那么每秒有5个缓冲未命中可能不会导致I/O繁忙，但每秒80个就可能出现问题。

每秒缓存未命中=Key_reads/uptime=0.33

8临时表使用情况

mysql>showglobalstatuslike'Created_tmp%';

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

|Variable_name|Value|

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

|Created_tmp_disk_tables|19226325|

|Created_tmp_files|117|

|Created_tmp_tables|56265812|

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

3rowsinset（0.00sec）

mysql>showvariableslike'%tmp_table_size%';

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

|Variable_name|Value|

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

|tmp_table_size|67108864|

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

1rowinset（0.00sec）

可看到总共创建了56265812临时表，其中有19226325涉及到了磁盘IO，大概比例占到了0.34，证明数据库应用中排序，join语句涉及的数据量太大，需要优化SQL或者增大tmp_table_size的值，我设的是64M。

该比值应该控制在0.2以。

9binlogcache使用情况

mysql>