1、mysql监控调优运行中的mysql状态查看,对正在运行的mysql进行监控,其中一个方式就是查看mysql运行状态。(1)QPS(每秒Query量)QPS = Questions(or Queries) / secondsmysql show global status like Question%;(2)TPS(每秒事务量)TPS = (Com_commit + Com_rollback) / secondsmysql show global status like Com_commit;mysql show global status like Com_rollback;(3)key B
2、uffer 命中率mysqlshow 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)InnoDB Buffer命中率mysql show status like innodb_buffer_pool_read%;innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / inn
3、odb_buffer_pool_read_requests) * 100%(5)Query Cache命中率mysql show status like Qcache%;Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts ) * 100%;(6)Table Cache状态量mysql show global status like open%;比较 open_tables 与 opend_tables 值(7)Thread Cache 命中率mysql show global status like Thread%;m
4、ysql show global status like Connections;Thread_cache_hits = (1 - Threads_created / connections ) * 100%(8)锁定状态mysql show global status like %lock%;Table_locks_waited/Table_locks_immediate=0.3% 如果这个比值比较大的话,说明表锁造成的阻塞比较严重Innodb_row_lock_waits innodb行锁,太大可能是间隙锁造成的(9)复制延时量mysql show slave status查看延时时间(1
5、0) Tmp Table 状况(临时表状况)mysql show status like Create_tmp%;Created_tmp_disk_tables/Created_tmp_tables比值最好不要超过10%,如果Created_tmp_tables值比较大,可能是排序句子过多或者是连接句子不够优化(11) Binlog Cache 使用状况mysql show status like Binlog_cache%;如果Binlog_cache_disk_use值不为0 ,可能需要调大 binlog_cache_size大小(12) Innodb_log_waits 量mysql s
6、how status like innodb_log_waits;Innodb_log_waits值不等于0的话,表明 innodb log buffer 因为空间不足而等待性能指标1 QPS计算(每秒查询数)针对MyISAM引擎为主的DBmysql show GLOBAL status like questions;+-+-+| Variable_name | Value |+-+-+| Questions | 2009191409 |+-+-+1 row in set (0.00 sec)mysql show global status like uptime;+-+-+| Variabl
7、e_name | Value |+-+-+| Uptime | 388402 |+-+-+1 row in set (0.00 sec)QPS=questions/uptime=5172,mysql自启动以来的平均QPS,如果要计算某一时间段的QPS,可在高峰期间获取间隔时间t2-t1,然后分别计算出t2和t1时刻的q值,QPS=(q2-q1)/(t2-t1)针对InnnoDB引擎为主的DBmysql show global status like _update;+-+-+| Variable_name | Value |+-+-+| Com_update | 87094306 |+-+-+
8、1 row in set (0.00 sec)mysql show global status like _select;+-+-+| Variable_name | Value |+-+-+| Com_select | 1108143397 |+-+-+1 row in set (0.00 sec)mysql show global status like _delete;+-+-+| Variable_name | Value |+-+-+| Com_delete | 379058 |+-+-+1 row in set (0.00 sec)mysql show global status
9、like uptime;+-+-+| Variable_name | Value |+-+-+| Uptime | 388816 |+-+-+1 row in set (0.00 sec)QPS=(_update+_insert+_delete+_select)/uptime=3076,某一时间段的QPS查询方法同上。2 TPS计算(每秒事务数)mysql show global status like _commit;+-+-+| Variable_name | Value |+-+-+| Com_commit | 7424815 |+-+-+1 row in set (0.00 sec)m
10、ysql show global status like _rollback;+-+-+| Variable_name | Value |+-+-+| Com_rollback | 1073179 |+-+-+1 row in set (0.00 sec)mysql show global status like uptime;+-+-+| Variable_name | Value |+-+-+| Uptime | 389467 |+-+-+1 row in set (0.00 sec)TPS=(_commit+_rollback)/uptime=223 线程连接数和命中率mysql sho
11、w global status like threads_%;+-+-+| Variable_name | Value |+-+-+| Threads_cached | 480 | /代表当前此时此刻线程缓存中有多少空闲线程| Threads_connected | 153 | /代表当前已建立连接的数量,因为一个连接就需要一个线程,所以也可以看成当前被使用的线程数| Threads_created | 20344 | /代表从最近一次服务启动,已创建线程的数量| Threads_running | 2 | /代表当前激活的(非睡眠状态)线程数+-+-+4 rows in set (0.00
12、sec)mysql show global status like Connections;+-+-+| Variable_name | Value |+-+-+| Connections | 381487397 |+-+-+1 row in set (0.00 sec)线程缓存命中率=1-Threads_created/Connections = 99.994%我们设置的线程缓存个数mysql show variables like %thread_cache_size%;+-+-+| Variable_name | Value |+-+-+| thread_cache_size | 500
13、 |+-+-+1 row in set (0.00 sec)根据Threads_connected可预估thread_cache_size值应该设置多大,一般来说250是一个不错的上限值,如果存足够大,也可以设置成thread_cache_size值和threaads_connected值相同;或者通过观察threads_created值,如果该值很大或一直在增长,可以适当增加thread_cache_size的值;在休眠状态下每个线程大概占用256KB左右的存,所以当存足够时,设置太小也不会节约太多存,除非该值已经超过几千。4 表缓存mysql show global status like
14、 open_tables%;+-+-+| Variable_name | Value |+-+-+| Open_tables | 2228 |+-+-+1 row in set (0.00 sec)我们设置的打开表的缓存和表定义缓存mysql show variables like table_open_cache;+-+-+| Variable_name | Value |+-+-+| table_open_cache | 16384 |+-+-+1 row in set (0.00 sec)mysql show variables like table_defi%;+-+-+| Varia
15、ble_name | Value |+-+-+| table_definition_cache | 2000 |+-+-+1 row in set (0.00 sec)针对MyISAM:mysql每打开一个表,都会读入一些数据到table_open_cache 缓存 中,当mysql在这个缓存中找不到相应的信息时,才会去磁盘上直接读取,所以该值要设置得足够大以避免需要重新打开和重新解析表的定义,一般设置为max_connections的10倍,但最好保持在10000以。还有种依据就是根据状态open_tables的值进行设置,如果发现open_tables的值每秒变化很大,那么可能需要增大ta
16、ble_open_cache的值。table_definition_cache 通常简单设置为服务器中存在的表的数量,除非有上万表。针对InnoDB:与MyISAM不同,InnoDB的open table和open file并无直接联系,即打开frm表时其相应的ibd文件可能处于关闭状态;故InnoDB只会用到table_definiton_cache,不会使用table_open_cache;其frm文件保存于table_definition_cache中,而idb则由innodb_open_files决定(前提是开启了innodb_file_per_table),最好将innodb_ope
17、n_files设置得足够大,使得服务器可以保持所有的.ibd文件同时打开。5 最接数mysql show global status like Max_used_connections;+-+-+| Variable_name | Value |+-+-+| Max_used_connections | 1785 |+-+-+1 row in set (0.00 sec)我们设置的max_connections大小mysql show variables like max_connections%;+-+-+| Variable_name | Value |+-+-+| max_connect
18、ions | 4000 |+-+-+1 row in set (0.00 sec)通常max_connections的大小应该设置为比Max_used_connections状态值大,Max_used_connections状态值反映服务器连接在某个时间段是否有尖峰,如果该值大于max_connections值,代表客户端至少被拒绝了一次,可以简单地设置为符合以下条件:Max_used_connections/max_connections=0.86 Innodb 缓存命中率mysql show global status like innodb_buffer_pool_read%;+-+-+
19、| 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 | /表示从物理磁盘读取的页数+-+-+5 rows in set (0.00 sec)缓
20、冲池命中率 = (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,不过也不宜设置太大,
21、会造成存的频繁交换,预热和关闭时间长等问题。7 MyISAM Key Buffer命中率和缓冲区使用率mysql show global status like 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_w
22、rites | 2061054 |+-+-+7 rows in set (0.00 sec)mysql show variables like %key_cache_block_size%;+-+-+| Variable_name | Value |+-+-+| key_cache_block_size | 1024 |+-+-+1 row in set (0.00 sec)mysql show variables like %key_buffer_size%;+-+-+| Variable_name | Value |+-+-+| key_buffer_size | 134217728 |+
23、-+-+1 row in set (0.00 sec)缓冲区的使用率=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%,所以从经验上看,每秒
24、的缓存未命中次数更重要,假设一个独立磁盘每秒能做100个随机读,那么每秒有5个缓冲未命中可能不会导致I/O繁忙,但每秒80个就可能出现问题。每秒缓存未命中=Key_reads/uptime=0.338 临时表使用情况mysql show global status like Created_tmp%;+-+-+| Variable_name | Value |+-+-+| Created_tmp_disk_tables | 19226325 | Created_tmp_files | 117 | Created_tmp_tables | 56265812 |+-+-+3 rows in set (0.00 sec)mysql show variables like %tmp_table_size%;+-+-+| Variable_name | Value |+-+-+| tmp_table_size | 67108864 |+-+-+1 row in set (0.00 sec)可看到总共创建了56265812 临时表,其中有19226325 涉及到了磁盘IO,大概比例占到了0.34,证明数据库应用中排序,join语句涉及的数据量太大,需要优化SQL或者增大tmp_table_size的值,我设的是64M。该比值应该控制在0.2以。9 binlog cache使用情况mysql
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1