精通 DevOps 工具与最佳实践 - DevOps 知识中心 精通 DevOps 工具与最佳实践 - DevOps 知识中心

监控MySQL性能：使用SHOW STATUS和SHOW PROCESSLIST

掌握使用两个关键命令进行实时MySQL性能监控：SHOW STATUS和SHOW PROCESSLIST。学习如何解读全局性能计数器、识别活跃连接、发现长时间运行或阻塞的查询，并立即诊断资源瓶颈。本指南提供了分析线程活动、InnoDB指标以及执行KILL等针对性操作的实用示例。

监控MySQL性能：使用SHOW STATUS和SHOW PROCESSLIST

当基于MySQL的应用变慢时，SHOW STATUS和SHOW PROCESSLIST是在打开仪表盘之前可以运行的最快内置检查。它们不能解释所有问题，但能回答两个实际的问题：服务器一直在做什么，以及现在正在运行什么？

使用SHOW STATUS了解实时系统健康状况

SHOW STATUS命令（通常与SHOW GLOBAL STATUS或SHOW SESSION STATUS同义使用）提供了自上次重启或当前会话开始以来服务器活动的丰富信息。这些状态变量充当计数器，跟踪从连接尝试到缓存效率和锁等待的一切。

全局状态与会话状态

执行此命令时，理解范围至关重要：

  • SHOW GLOBAL STATUS：显示自MySQL服务器实例启动以来累积的计数器。这提供了服务器整体健康状况和长期趋势的鸟瞰图。
  • SHOW SESSION STATUS：仅显示当前使用的连接（会话）特有的计数器。这对于隔离特定事务的性能影响非常有用。

SHOW GLOBAL STATUS的关键性能指标（KPI）

虽然SHOW GLOBAL STATUS返回数百个变量，但有几个对于初始性能分类至关重要。通常，您需要将输出通过管道传递给grep或使用WHERE子句进行过滤以获取相关信息。

1. 连接和线程监控

这些变量帮助您了解连接负载：

变量名 描述
Threads_connected 当前打开的连接数（客户端）。
Threads_running 当前正在执行查询的活跃线程数（通常应较低）。
Max_used_connections 自服务器启动以来同时连接的最大数量。用于调整max_connections的大小。

示例： 检查活跃连接：

SHOW GLOBAL STATUS LIKE 'Threads_%';

2. 查询缓存和效率

如果您使用的是传统查询缓存（在较旧的MySQL版本中可用，在较新版本中已弃用/移除），这些指标至关重要：

  • Qcache_hits：从缓存中提供查询的次数。
  • Qcache_lowmem_prunes：由于内存不足导致缓存驱逐旧条目的查询次数。

3. InnoDB引擎指标（对现代MySQL最关键）

对于使用InnoDB存储引擎的现代部署，监控缓冲池活动：

  • Innodb_buffer_pool_read_requests：总读取请求数。
  • Innodb_buffer_pool_reads：从磁盘进行的物理读取次数（物理读取与请求的高比例表明需要更大的缓冲池）。

实用提示： 要快速评估缓冲池效率，计算命中率：(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests。

4. 临时表和排序

这些指示MySQL正在进行的内部处理量：

  • Created_tmp_tables：创建的内存临时表数量。
  • Created_tmp_disk_tables：必须写入磁盘的临时表数量（慢）。

如果Created_tmp_disk_tables很高，您可能需要增加tmp_table_size或max_heap_table_size。

使用SHOW PROCESSLIST诊断活跃工作负载

SHOW STATUS告诉您已经发生了什么，而SHOW PROCESSLIST告诉您现在正在发生什么。它显示有关服务器内当前正在执行的线程的信息，使您能够识别长时间运行或阻塞的查询。

进程列表的结构

该命令输出多个列，每个列提供有关活跃连接的上下文：

列 描述
Id 唯一的连接ID（用于终止进程）。
User 连接的用户账户。
Host 连接来源的主机。
db 线程当前使用的数据库。
Command 正在执行的命令类型（例如，Query、Sleep、Connect）。
Time 线程处于当前状态的秒数。
State 线程正在执行的具体操作（例如，Sending data、Copying to tmp table）。
Info 正在执行的实际SQL语句（如果较长则可能被截断）。

过滤和解读输出

对于大型生产系统，完整的进程列表可能令人不知所措。标准做法是使用FULL关键字确保看到完整的查询文本，然后按Time或State列进行过滤。

1. 查看完整命令文本

如果您怀疑有慢查询，请始终使用FULL，因为标准输出通常会截断Info字段：

SHOW FULL PROCESSLIST;

2. 识别阻塞或慢查询

监控Time和Command列：

  • 高Time值：任何运行时间过长的查询（例如，超过10秒，具体取决于您的SLA）都需要立即调查。检查相应的Info列以查看SQL。
  • Command = 'Sleep'：这些连接处于空闲状态，但仍消耗资源。如果它们积累过多，请考虑调整wait_timeout变量。
  • Command = 'Query'：这些是正在运行的语句。密切关注它们的State。

3. 识别锁定问题

当查询因等待资源而卡住时，State列通常会指示这一点：

  • Waiting for table metadata lock
  • Waiting for table lock
  • Waiting for lock

如果您看到许多线程处于等待状态，这表明存在争用，通常是由持有其他线程所需锁的长时间运行事务引起的。

操作：终止进程

如果您识别出严重降低性能的失控查询，可以使用KILL命令后跟进程Id来终止它：

KILL 12345; -- 将12345替换为进程列表中的实际Id

警告： 谨慎使用KILL。如果事务正在进行复杂的写操作，终止活跃事务可能会使数据库处于不一致状态。如果可能，始终先尝试识别并优化查询。

结合状态和进程信息进行故障排除

有效的MySQL监控通常涉及这两个命令之间的关联：

  1. 初始检查： 运行SHOW FULL PROCESSLIST。注意任何高时间查询或过多连接。
  2. 上下文检查： 使用SHOW GLOBAL STATUS LIKE 'Threads_connected'检查连接数。您面临的是连接洪流还是只有一个坏查询？
  3. 深入分析： 如果特定查询很慢，请在查询运行时通过检查Innodb_buffer_pool_reads或临时表创建率来分析其对资源计数器的影响（需要基线比较）。

通过定期检查这些动态输出，您可以超越猜测，应用有针对性的解决方案来提高MySQL的稳定性和速度。

一个实际的分诊例程

一个好的第一遍检查不到一分钟。从进程列表开始：

SHOW FULL PROCESSLIST;

扫描大量活跃查询、长Time值、锁等待和许多空闲的Sleep连接。单个慢报告查询的处理方式与数百个等待同一表锁的Web连接不同。

然后检查线程计数器：

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Threads_connected',
  'Threads_running',
  'Max_used_connections'
);

Threads_connected告诉您有多少客户端已连接。Threads_running在减速期间通常更重要，因为它显示有多少线程正在积极工作。许多已连接的空闲客户端可能浪费资源，但许多正在运行的线程可能意味着服务器承受着真正的压力。

接下来，检查工作负载是否正在创建基于磁盘的临时表：

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Created_tmp_tables',
  'Created_tmp_disk_tables',
  'Sort_merge_passes'
);

这些计数器自启动以来是累积的，因此单个快照可能会误导您。在事件期间每隔几分钟拍摄两个快照。如果磁盘临时表快速增加，请检查包含GROUP BY、ORDER BY、大型连接、文本列或缺少索引的查询。在某些情况下提高tmp_table_size可能会有所帮助，但更好的查询或索引通常是更干净的修复方法。

查看InnoDB压力

大多数现代MySQL部署使用InnoDB，因此InnoDB计数器值得关注：

SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_buffer_pool_read%';

Innodb_buffer_pool_read_requests计算逻辑读取请求。Innodb_buffer_pool_reads计算必须转到磁盘的读取次数。如果在正常流量期间物理读取快速增加，则缓冲池可能对于工作集来说太小，查询可能扫描了太多数据，或者批处理作业可能将有用的页面推出了缓存。

锁等待是另一个常见的痛点：

SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_row_lock%';

行锁等待的增加并不自动意味着InnoDB已损坏。它们通常意味着事务持有锁的时间比应用程序预期的要长。查找打开的事务、慢更新或启动事务、调用外部服务并稍后提交的代码路径。

对于更深入的锁和事务详细信息，SHOW ENGINE INNODB STATUS\G可能会有所帮助，但其输出很密集。当进程列表显示锁等待并且您需要识别其背后的事务模式时使用它。

更安全地使用KILL

KILL很有用，但它不是清理按钮。如果您终止运行大型事务的连接，MySQL可能需要回滚工作，而回滚可能需要时间。在某些事件中，这仍然是正确的做法，但要谨慎做出决定。

当您的MySQL版本和权限支持时，首选先终止查询：

KILL QUERY 12345;

这会尝试停止当前语句，同时保持连接存活。如果客户端行为不当或连接必须消失，请使用：

KILL CONNECTION 12345;

在终止任何内容之前，捕获进程列表行、用户、主机、数据库和SQL文本。事件发生后，这些详细信息有助于您修复源头，而不是等待相同的查询再次出现。

常见的进程列表状态及其含义

Sending data并不总是意味着MySQL正在通过网络发送行。它通常意味着服务器正在读取、过滤、排序或准备行。如果查询在此状态下花费很长时间，请对语句运行EXPLAIN并查找表扫描、糟糕的连接顺序或缺少索引。

Copying to tmp table或Creating sort index通常指向昂贵的排序或分组。检查索引是否支持WHERE和ORDER BY模式。有时查询正在执行产品要求的确切操作，但它应该属于异步报告而不是请求路径。

Waiting for table metadata lock通常出现在DDL和普通查询冲突时。一个看似简单的ALTER TABLE可能会在打开的事务后面等待，而后来的查询会在待处理的DDL后面堆积。在这种情况下，终止最旧的阻塞器可能比终止每个等待的查询更安全。

将计数器转化为有用的证据

由于SHOW STATUS值主要是计数器，因此速率比原始数字更有用。捕获相同的变量两次：

SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

等待六十秒，然后再次运行它们。差值告诉您那一分钟内的速率。这与仪表盘使用的想法相同，但手动执行在您只有终端访问权限时很有帮助。

在事件期间做笔记。"Threads_running从8跳到90，进程列表显示70个查询在等待orders上的元数据锁，Max_used_connections没有变化"是一个有用的诊断。"MySQL很慢"则不是。

当这些命令不够用时

SHOW STATUS和SHOW PROCESSLIST是第一响应工具。它们不能替代慢查询日志、性能模式、查询计划或主机级指标。如果相同的问题再次出现，请启用或查看慢查询日志，并使用EXPLAIN检查最差的语句。

对于重复的连接峰值，请查看应用程序池设置和部署行为。提高max_connections可能会争取时间，但它也可能让服务器接受比实际能运行的更多工作。对于重复的锁等待，请检查应用程序中的事务边界。在代码调用外部API时保持打开的事务可能会阻塞不相关的请求，并使MySQL看起来比实际更慢。

还要检查主机。如果磁盘延迟高、CPU饱和、内存交换或嘈杂的邻居正在窃取资源，MySQL计数器将显示症状，但不是全部原因。良好的诊断结合了数据库命令和系统指标。

SHOW STATUS为您提供计数器和上下文。SHOW FULL PROCESSLIST为您提供实时工作负载。一起使用时，它们帮助您区分连接压力、单个坏查询、锁争用、磁盘密集型临时工作和InnoDB缓存压力。