MySql 通过show status 优化数据库性能
mysql数据库的性能状态监控点非常多,其中很多量都是不能忽视的必须监控的量,且90%以上的内容 可以在连接上mysql后执行show status 或是 show veriables的输出值 获得,需要注意的是以上的命令获得的状态值实际上是累计值,所以如果 要计算时段内的变化 量还需要稍加处理,下面看下几项需要重点关注的性能状态:
1. key buffer 命中率
key buffer 命中率代表了myisam类型表的索引cache命中率,命中率的大小直接影响myisam类型表的读写性能。key buffer 命中率实际上包括读命中率和写命中率两种,mysql中并没有直接给出这两个命中率的值,但是可以通过如下方式计算:
key_buffer_read_hits=(1-key_reads/key_read_requests) * 100%
key_buffer_write_hits=(1-key_writes/key-write-requests)*100%
获取所需要状态的变量值:
show status like 'key%'
mysql> show status like 'key%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 13396 |
| Key_blocks_used | 19 |
| Key_read_requests | 71 |
| Key_reads | 19 |
| Key_write_requests | 1 |
| Key_writes | 1 |
+------------------------+-------+
7 rows in set (0.00 sec)
命中率过低,说明myisam类型表的读写存在问题。
2. innodb buffer 命中率
这里innodb buffer 所指的是innodb_buffer_pool,也就是用来缓存innodb类型表和索引的内在空间。类似key buffer,同样可以根据mysql提供的相应的状态信息计算其命中率:
innodb_buffer_read_hits=(1-innodb_buffer_pool_reads/innodb_buffer_pool_read_requests) * 100%;
获取所需状态变量值:
mysql> show status like 'innodb_buffer_pool_read%';
+---------------------------------------+----------+
| Variable_name | Value |
+---------------------------------------+----------+
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 27269024 |
| Innodb_buffer_pool_reads | 827 |
+---------------------------------------+----------+
命中率过低,说明innodb类型表的读写存在问题。
3. query cache命中率
query cache 是mysql的查询cache,在my.cnf配置文件若打开,则可以对查询过的语句结果进行cache。对于一些用户数不高或一次性统计平台建议关闭查询缓存。若开启query cache,则对query cache 命中率进行监控也是需要的,它可以告诉我们是数据库是否在正确使用query cache。query cache命中率计算如下:
query_cache_hits =(Qcache_hits/(Qcache_hits+Qcache_inserts))* 100%;
获取变量值:
mysql> show status like 'Qcache%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
+-------------------------+-------+
8 rows in set (0.00 sec)
4. table_cache 命中率
table_cache指定表调整缓存的大小,当mysql访问某个表时,若表缓存空间还有空间,则将该表就被打开并将数据放入其中,下次访问此表时可以更快的访问表的内容。通过查峰值时间的状态值open_tables 和 opened_tables可以决定是否需要增加table_cache值。需要注意的是table_cache设置很太高,可能会造成文件描述符不足,从而造成性能不稳定或是连接失败。
table_cache的当前状态量可以帮助我们判断系统参数table_open_cache的设置是否合理。如果状态量open_tables与opened_tables之间的比率过低,则代表table cache设置过小,网上有人认为这值的比率设置在80%最好。
获取变量:
mysql> show status like 'open%';
+--------------------------+---------+
| Variable_name | Value |
+--------------------------+---------+
| Open_files | 6 |
| Open_streams | 0 |
| Open_table_definitions | 87 |
| Open_tables | 26 |
| Opened_files | 1554504 |
| Opened_table_definitions | 0 |
| Opened_tables | 0 |
+--------------------------+---------+
5. thread cache命中率
在mysql中,为了尽可能提高客户端连接的过程,实现 了一个thread cache池,将空闲的连接线程存放在其中,而不是请求完成后销毁,当有新的连接请求的时候,mysql首先检查thread cache是否存储空闲的连接线程,如果存在则取出来直接使用,如果没有空闲连接线程,才创建新的线程。
thread cache命中率能直接反应出系统参数thread_cache_size设置是否合理。一个合理的read_cache_size参数能够节约大量创建新连接时所需要消夏的资源。
thread cache命中率计算方式如下:
thread_cache_hits = (1- threads_created/connections) * 100 %;
获取所需要状态变量值:
mysql> show status like 'thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 23581 |
| Threads_running | 1 |
+-------------------+-------+
正常来说,thread cache命中率在90%以上才算合理。
6. tmp table相关状况分析
tmp table 主要用于监控mysql使用临时表的量是否过多,是否有临时表过大而不得不从内存中换出到磁盘文件中,临时表使用状态 信息获取如下:
mysql> show status like 'created_tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 65 |
| Created_tmp_tables | 0 |
+-------------------------+-------+
Created_tmp_disk_tables为临时表过大无法在内存中完成,而不得不使用磁盘的次数。若create_tmp_tables非常大,则可甬系统排序操作过多,或者可能是连接方式不是很优化。而如果是create_tmp_dis_table/create_tmp_tables比率过高,如超过10%,则需要考虑tmp_table_size参数是否需要调整大些。建议tmp_table_size与max_heap_table_size需要设置成一样大。
7. binlog cache
若打开binlog日志功能,则需要考虑binlog cache问题。binlog不是一有数据就写到binlog中,而是先写入到binlog cache中,再写入到binlog中。
Binlog_cache_disk_use为binlog使用硬盘使用量, Binlog_cache_use 为binlog已使用的量。若 Binlog_cache_disk_use大于0,则说明binlog_cache不够用。
mysql> show status like 'binlog_cache%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 58 |
| Binlog_cache_use | 39785 |
+-----------------------+-------+
8. innodb_log_waits
innodb_log_waits状态变量直接反应innodb log buffer 空间不足造成等待的次数。innodb_log_waits直接反应系统的写入性能,当值 达到每秒1次时,就需要增加innodb_log_buffer_size的值,适当的增加不会造成内存不足的问题。
9. 复制延时量
复制延时量:复制延时量将直接影响了Slave数据库处于不一致状态的时间长短。如果我们是通过Slave来提供读服务,就不得不重视这个延时量。我们可以通过在Slave节点上执行“SHOWSLAVESTATUS”命令,取Seconds_Behind_Master项的值来了解Slave当前的延时量(单位:秒)。当然,该值的准确性依赖于复制是否处于正常状态。每个环境下的Slave所允许的延时长短与具体环境有关,所以复制延时多长时间是合理的,只能由读者朋友根据各自实际的应用环境来判断。
mysql> show slave status\G
Seconds_Behind_Master: NULL
10. 锁状态
mysql的锁有表锁和行锁,myisam最小锁为表锁,innodb最小锁为行锁,可以通过以下命令获取锁定次数、锁定造成其他线程等待次数,以及锁定等待时间信息。
mysql> show status like '%lock%';
+------------------------------------------+---------+
| Variable_name | Value |
+------------------------------------------+---------+
| Com_lock_tables | 0 |
| Com_unlock_tables | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 13396 |
| Key_blocks_used | 19 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Qcache_free_blocks | 0 |
| Qcache_total_blocks | 0 |
| Table_locks_immediate | 1570736 |
| Table_locks_waited | 7294 |
+------------------------------------------+---------+
如当Table_locks_waited与Table_locks_immediate的比值较大,则说明我们的表锁造成的阻塞比较严重,可能需要调整Query语句,或者更改存储引擎,亦或者需要调整业务逻辑。当然,具体改善方式必须根据实际场景来判断。而Innodb_row_lock_waits较大,则说明Innodb的行锁也比较严重,且影响了其他线程的正常处理。同样需要查找出原因并解决。造成Innodb行锁严重的原因可能是Query语句所利用的索引不够合理(Innodb行锁是基于索引来锁定的),造成间隙锁过大。也可能是系统本身处理能力有限,则需要从其他方面来考虑解决。