mysql监控调优.docx
《mysql监控调优.docx》由会员分享,可在线阅读,更多相关《mysql监控调优.docx(14页珍藏版)》请在冰豆网上搜索。
![mysql监控调优.docx](https://file1.bdocx.com/fileroot1/2022-12/18/611e9b6c-88e1-4d85-b897-3e060d7b0005/611e9b6c-88e1-4d85-b897-3e060d7b00051.gif)
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>