MySQL重点关注性能及相关分析命令详解

215次阅读

共计 5461 个字符，预计需要花费 14 分钟才能阅读完成。

一、MySQL 性能监控关注点

QPS（每秒 Query 量）：这里的 QPS 实际上是指 MySQL Server 每秒执行的 Query 总量：
QPS = Queries / Seconds

TPS（每秒事务量）：在 MySQL Server 中并没有直接事务计数器，我们只能通过回滚和提交计数器来计算出系统的事务量。所以，我们需要通过以下方式来得到客户端应用程序所请求的 TPS 值：
TPS = (Com_commit + Com_rollback) / Seconds

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%

Innodb Buffer 命中率：这里 Innodb Buffer 所指的是 innodb_buffer_pool，也就是用来缓存 Innodb 类型表的数据和索引的内存空间。类似 Key buffer，我们
同样可以根据 MySQL Server 提供的相应状态值计算出其命中率：
innodb_buffer_read_hits=(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%

Query Cache 命中率：如果我们使用了 Query Cache，那么对 Query Cache 命中率进行监控也是有必要的，因为他可能告诉我们是否在正确的使用 Query Cache。
Query Cache 命中率的计算方式如下：
Query_cache_hits= (Qcache_hits / (Qcache_hits + Qcache_inserts)) * 100%

Table Cache 状态量：Table Cache 的当前状态量可以帮助我们判断系统参数 table_open_cache 的设置是否合理。如果状态变量 Open_tables 与 Opened_tables 之间的比率过低，则代表 Table Cache 设置过小：
SHOW STATUS LIKE ‘Open%’;

Thread Cache 命中率：Thread Cache 命中率能够直接反应出我们的系统参数 thread_cache_size 设置的是否合理。一个合理的 thread_cache_size 参数能够
节约大量创建新连接时所需要消耗的资源。
Thread Cache 命中率计算方式如下：
Thread_cache_hits = (1 – Threads_created / Connections) * 100%

锁定状态：锁定状态包括表锁和行锁两种，我们可以通过系统状态变量获得锁定总次数，锁定造成其他线程等待的次数，以及锁定等待时间信息。
SHOW STATUS LIKE ‘%lock%’;
通过锁相关的系统变量，我们可以得出表锁总次数，其中造成其他现线程等待的次数。同时还可以得到非常详细的行锁信息，如行锁总次数，行锁总时间，每次行锁等待时间，行锁造成最大等待时间以及当前等待行锁的线程数。通过对这些量的监控，我们可以清晰的了解到系统整体的锁定是否严重。如当 Table_locks_waited 与 Table_locks_immediate 的比值较大，则说明我们的表锁造成的阻塞比较严重，可能需要调整 Query 语句，或者更改存储引擎，亦或者需要调整业务逻辑。当然，具体改善方式必须根据实际场景来判断。而 Innodb_row_lock_waits 较大，则说明 Innodb 的行锁也比较严重，且影响了其他线程的正常处理。同样需要查找出原因并解决。造成 Innodb 行锁严重的原因可能是 Query 语句所利用的索引不够合理（Innodb 行锁是基于索引来锁定的），造成间隙锁过大。也可能是系统本身处理能力有限，则需要从其他方面（如硬件设备）来考虑解决。

复制延时量：复制延时量将直接影响了 Slave 数据库处于不一致状态的时间长短。
在 Slave 节点上执行“SHOW SLAVE STATUS”命令，取 Seconds_Behind_Master 项的值来了解 Slave 当前的延时量（单位：秒）。

Tmp table 状况：Tmp Table 的状况主要是用于监控 MySQL 使用临时表的量是否过多，是否有临时表过大而不得不从内存中换出到磁盘文件上。临时表使用状态信息可以通过如下方式获得：
SHOW STATUS LIKE ‘Created_tmp%’;
+————————-+——-+
| Variable_name | Value |
+————————-+——-+
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 0 |
+————————-+——-+
如果 Created_tmp_tables 非常大，则可能是系统中排序操作过多，或者是表连接方式不是很优化。而如果是 Created_tmp_disk_tables Created_tmp_tables 的比率过高，如超过 10%，则我们需要考虑是否 tmp_table_size 这个系统参数所设置的足够大。

Binlog Cache 使用状况：Binlog Cache 用于存放还未写入磁盘的 Binlog 信息。
相关状态变量如下：
SHOW STATUS LIKE ‘Binlog_cache%’；
Binlog_cache_disk_use 值不为 0，则说明 Binlog Cache 大小可能不够，可以增加 binlog_cache_size 系统参数大小。

Innodb_log_waits 量：Innodb_log_waits 状态变量直接反应出 Innodb Log Buffer 空间不足造成等待的次数。
SHOW STATUS LIKE ‘Innodb_log_waits’;
该变量值发生的频率将直接影响系统的写入性能，所以当该值达到每秒 1 次时就该增加系统参数 innodb_log_buffer_size 的值，毕竟这是一个系统共用的缓存，适当增加并不会造成内存不足的问题。

二、性能分析命令详解

SHOW STATUS;
FLUSH STATUS;

查看当前连接数 SHOW STATUS LIKE ‘Thread_%’;
Thread_cached: 被缓存的线程的个数
Thread_running：处于激活状态的线程的个数
Thread_connected：当前连接的线程的个数
Thread_created：总共被创建的线程的个数

Thread cache hits
Thread_connected = SHOW GLOBAL STATUS LIKE Thread_created;
Connections = SHOW GLOBAL STATUS LIKE ‘Connections’;
TCH=(1 – (Threads_created / Connections)) * 100

查看活动连接内容
SHOW PROCESSLIST;

如果 TCH 数小于 90%, 创建连接耗费了时间, 增大 Thread_cached 数量

QPS（每秒查询处理量）MyISAM 引擎

Questions = SHOW GLOBAL STATUS LIKE ‘Questions’;
Uptime = SHOW GLOBAL STATUS LIKE ‘Uptime’;
QPS=Questions/Uptime

TPS（每秒传输的事物处理个数），即服务器每秒处理的事务数，如果是 InnoDB 会显示，没有 InnoDB 就不会显示。

Com_commit = SHOW GLOBAL STATUS LIKE ‘Com_commit’;
Com_rollback = SHOW GLOBAL STATUS LIKE ‘Com_rollback’;
Uptime = SHOW GLOBAL STATUS LIKE ‘Uptime’;
TPS=(Com_commit + Com_rollback)/Uptime

QPS 和 TPS 值一定要实时监控, 如果接近架构搭建时的测试峰值, 愿上帝与你同在

Read/Writes Ratio
Qcache_hits = SHOW GLOBAL STATUS LIKE ‘Qcache_hits’;
Com_select = SHOW GLOBAL STATUS LIKE ‘Com_select’;
Com_insert = SHOW GLOBAL STATUS LIKE ‘Com_insert’;
Com_update = SHOW GLOBAL STATUS LIKE ‘Com_update’;
Com_delete = SHOW GLOBAL STATUS LIKE ‘Com_delete’;
Com_replace = SHOW GLOBAL STATUS LIKE ‘Com_replace’;
R/W=(Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) * 100

读写比, 优化数据库的重要依据, 读的多就去优化读, 写的多就去优化写

Slow queries per minute
Slow_queries = SHOW GLOBAL STATUS LIKE ‘Slow_queries’;
Uptime = SHOW GLOBAL STATUS LIKE ‘Uptime’;
SQPM=Slow_queries / (Uptime/60)

Slow queries /Questions Ratio
Slow_queries = SHOW GLOBAL STATUS LIKE ‘Slow_queries’;
Questions = SHOW GLOBAL STATUS LIKE ‘Questions’;
S/Q=Slow_queries/Questions

新版本上线时要着重关注慢查询

Full_join per minute
Select_full_join = SHOW GLOBAL STATUS LIKE ‘Select_full_join’;
Uptime = SHOW GLOBAL STATUS LIKE ‘Uptime’;
FJPM=Select_full_join / (Uptime/60)

没有使用索引而造成的 full_join, 优化索引去吧

Innodb buffer read hits
Innodb_buffer_pool_reads = SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_reads’;
Innodb_buffer_pool_read_requests = SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_read_requests’;
IFRH=(1 – Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100

InnoDB Buffer 命中率目标 95%-99%;

Table Cache
Open_tables= SHOW GLOBAL STATUS LIKE ‘Open_tables’;
Opened_tables= SHOW GLOBAL STATUS LIKE ‘Opened_tables’;
table_cache= SHOW GLOBAL STATUS LIKE ‘table_cache’;

table_cache 应该大于 Open_tables 小于 Opened_tables

Temp tables to Disk ratio

Created_tmp_tables = show global status like ‘Created_tmp_tables’;
Created_tmp_disk_tables = show global status like ‘Created_tmp_disk_tables’;

TDR=(Created_tmp_disk_tables/Created_tmp_tables)*100

SHOW GLOBAL STATUS LIKE ‘Innodb_row_lock_%’;

Innodb_row_lock_current_waits

The number of row locks currently being waited for. Added in MySQL 5.0.3.

Innodb_row_lock_time

The total time spent in acquiring row locks, in milliseconds. Added in MySQL 5.0.3.

Innodb_row_lock_time_avg

The average time to acquire a row lock, in milliseconds. Added in MySQL 5.0.3.

Innodb_row_lock_time_max