同乐学堂2.8 高效地使用MySQL问题排查工具
为了结束本章的内容，本节打算再介绍一下我们使用过的工具，并补充介绍一下我们之前忽略的其他有用的特性。
2.8.1 SHOW PROCESSLIST和INFORMATION_SCHEMA. PROCESSLIST表
SHOW PROCESSLIST命令是当你怀疑遇到并发问题时的首选工具。它虽然不会给出多语句事务中语句间的关系，但是它会暴露出问题的特征以确认需要对并发进行更多调查。一个最主要的特征就是线程长时间处于“Sleep”状态。本章的示例中使用的是SHOW PROCESSLIST的精简版本，该版本会删减长查询。SHOW FULL PROCESSLIST命令展现的是完整的查询，如果你有长查询且很难从查询的开头推测出其完整版本，该命令会比较方便。从5.1版本开始，MySQL提供了INFORMATION_SCHEMA.PROCESSLIST表，该表中的数据与SHOW FULL PROCESSLIST的输出相同。在繁忙的服务器中，该表极大地方便了排错，因为你可以使用SQL来缩小希望查看的输出范围：
那么为什么我在大多数例子中使用SHOW PROCESSLIST命令而不是PROCESSLIST表呢？首先，SHOW PROCESSLIST命令在所有MySQL版本中都支持。当我进行支持工作时，我可以从客户直接申请这些信息而不用事先确认其使用的MySQL的版本。另一个原因也是由于我的支持工作：当为客户工作的时候，我们不清楚其环境的所有详细信息，因此查看未经过过滤的进程列表可以给我们提供一些信息以进行深入了解。当你调试自己的应用程序时就有不同的考虑了。
因为你已经知道哪个进程是重要的，所以你可以在查询中通过WHERE条件限制输出，例如：
这样做可以节约分析结果的时间。
2.8.2 SHOW ENGINE INNODB STATUS和InnoDB监控器
这些工具展示当你使用InnnoDB表时最重要的信息。可以通过SHOW ENGINE INNODB STATUS命令和创建InnoDB监控表获取相同的信息。这些表不是为用户准备的，而是一种告诉InnoDB引擎每隔若干秒向错误日志中写入InnoDB的状态信息的方式。这里将讨论标准监控器和锁监控器。InnoDB还提供了表空间监控器和表监控器，这两个监控器会分别输出共享表空间u和InnoDB内部字典中的信息。表空间监控器和表的监控器与并发问题没有直接关系，因此这里略过它们。标准的InnoDB监控器就是执行SHOW ENGINEINNODB STATUS命令时获得的信息。与并发相关的，我们关心的是SEMAPHORES、LATEST DETECTED DEADLOCK 和 TRANSACTIONS。SEMAPHORES部分包含了线程等待互斥锁或读写锁的信息。这里需要注意的是等待线程的数量或者长时间等待的线程。不过，长时间等待并不是问题的一个必要特征。例如，在一个超大的表上执行CHECK TABLE命令可能会长时间持有信号量。但是如果你发现普通操作持续很长时间，你应该检查一下你安装的版本是否可以处理你拥有的InnoDB线程数。降低innodb_thread_concurrency的值可以起到作用。LATEST DETECTED DEADLOCK部分包含最近检测到的死锁的信息。如果从服务器启动没有发现死锁，那么该部分就是空的。可以通过监控该部分内容确认应用程序中是否有死锁。当发现有死锁的时候，要么从你的应用程序中移除查询，这样就没有冲突导致死锁，要么添加代码小心地处理死锁。TRANSACTIONS部分包含地所有当前正在执行的事务的信息。对于本章的讨论来说，特别需要注意的是，该部分会列出所有活动事务正在等待的所有锁的信息。如果打开InnoDB锁监控器，那么该部分还会包含事务持有哪个锁的信息。这对调试锁等待来说是非常有用的信息。为了说明InnoDB锁监控器怎样帮助你调试锁问题，回顾一下2.3.1节中的示例。如果打开InnoDB锁监控器并且执行同样的查询，我们将会在SHOW ENGINE INNODB STATUS的输出中看到一些额外的信息：
将该输出与你之前看到的没有锁信息的输出进行比较。最重要的区别在于，现在你拥有持有锁的事务的信息：
我在整个事务信息中识别出关于锁“Record lock, heap no 6”的信息。我们关注以下信息：
这是关于锁记录的物理内容。当你检查正在等待的事务的时候，你可以看到它会等待同一个锁（注意PHYSICAL RECORD）：
这是非常有用的信息，因为你可以清楚地将正在等待锁的事务与持有锁的事务联系起来。之前讨论了InnoDB INFORMATION_SCHEMA表。为什么使用InnoDB监控器而不是直接查看这些表呢？原因就是INFORMATION_SCHEMA表仅包含当前信息，而InnoDB监控器可以将信息转储到错误日志文件中，这样你可以在稍后分析它。在你想要找到在应用运行程序的时候都发生了什么的时候，这些信息尤为重要。关于InnoDB监控器的输出还有其他有用的部分。当你怀疑一个不确定的死锁或仅是一个长时间的锁的时候，查看FILE I/O、INSERT BUFFER AND ADAPTIVE HASH INDEX、BUFFER POOL AND MEMORY以及ROW OPERATIONS等信息是很有意义的。不论何时，当读或写操作停止并且线程互相等待的时候，这很有可能是一个锁定问题，哪怕锁住的线程处于Updating状态。
2.8.3 INFORMATION_SCHEMA中的表
从5.1版本的InnoDB插件开始，MySQL开始支持新的InnoDB INFORMATION_SCHEMA表。它们与并发问题相关的部分是：INNODB_TRX包含当前运行的所有事务的列表。
INNODB_LOCKS包含事务持有的当前锁的相关信息以及每个事务等待的锁的信息。INNODB_LOCK_WAITS包含事务正在等待的锁的信息。这些表易于使用并且可以很快提供有关事务状态和锁的信息。你在本章前面看到的示例解释了我关于这个主题需要说明的一切。调试并发问题时有用的典型的INFORMATION_SCHEMA表查询
关于事务正在等待的所有锁的信息：
阻塞的事务列表：
或
特定表上的锁的列表：
等待锁的事务列表：
2.8.4 PERFORMANCE_SCHEMA中的表
性能架构允许你从底层监控MySQL服务器的运行状态。该架构实现为包含基于PERFORMANCE_SCHEMA存储引擎的表的数据库。存储引擎通过服务器源代码中定义的“检测点”收集事件数据。PERFORMANCE_SCHEMA中的表不使用持久性磁盘存储。为了调试并发问题，可以使用COND_INSTANCES、FILE_INSTANCES、MUTEX_ INSTANCES和RWLOCK_INSTANCES表以及各种EVENT_WAITS_*表。THREADS表有助于建立内部线程和MySQL的用户线程之间的关系。所有的*INSTANCES表都包含NAME和OBJECT_INSTANCE_BEGIN字段，这两个字段分别代表实例的名称和对象被检测时的内存地址。COND_INSTANCES表包含等待条件列表，这些条件是在服务器启动后生成的。条件（对于学习过并发的程序员来说这会是一个熟悉的术语）是指使一个线程等待其他线程的方式方法。
FILE_INSTANCES表包含性能架构可见的文件列表。当服务器首次打开文件的时候就将文件名插入该表，并且在文件从磁盘中删除之前都会保存在表中。目前打开文件会有一个正的OPEN_COUNT计数。Number字段保存当前使用该文件的文件句柄的数量。MUTEX_INSTANCES表包含性能架构可见的互斥列表。互斥记录中LOCKED_ BY_THREAD_IS的值为NOT NULL部分是当前锁定的互斥。RWLOCK_INSTANCE表包含所有读/写锁实例的列表。WRITE_LOCKED_BY_ THREAD_IS字段代表持有锁的线程ID。READ_LOCKED_BY_COUNT字段代表当前在实例上获取了多少读锁。EVENTS_WAITS_* 系列表包含每个线程等待的事件的信息。例如，要找出事务正在等待哪种类型的锁，可以使用下面的查询：
上述信息表明24号线程在等待InnoDB的kernel_mutex，而通过SHOW PROCESSLIST发现，同一个查询正处于Updating状态：
THREADS表包含当前所有正在运行的线程列表。ID是内部分配的，与连接线程的ID完全不同。进一步来说，服务器运行了很多与连接线程无关的内部线程。（例如，从服务器运行一个SQL线程和I/O线程。）该表包含的PROCESSLIST_ID字段记录与每个特定线程相关联的连接线程ID，前提是如果存在的话。*_SUMMARY_*表包含被终止事件的聚合信息。举例来说，要找出哪个表使用得最多，可以尝试下面的查询。该查询对于分别使用独立的文件保存表数据的存储引擎有效，如MyISAM引擎或者开启了innodb_file_per_table选项的InnoDB存储引擎。
2.8.5 日志文件
有两种MySQL服务器日志文件有助于处理并发问题：错误日志文件和通用查询日志文件。错误日志文件包含错误的相关信息。它会包含不安全的复制语句、由于长信号量等待而产生的故意崩溃等相关信息。我在第1章已经建议过，错误日志文件是你遇到问题时第一个需要检查的地方。对于并发问题来说，建议相同。当你不确定问题的来源的时候，请先查看错误日志文件。通用查询日志有助于找到通过其他途径找不到的查询。一个典型例子就是阻塞其他事务的多语句事务。如果这是由一个应用程序调用的，那么有时候很难判断哪个查询导致了该问题。在这种情况下，打开通用查询日志，等待问题重现，然后查询通用日志以找到由特定线程执行的查询。一种典型的排错方式就是查看InnoDB监控器的输出，找到可能阻塞其他事务的事务的MySQL线程ID，然后执行以下查询。
该查询将会返回锁定线程执行的查询列表。你会发现BEGIN或者START TRANSACTION语句启动了整个多语句事务。一旦你发现了不和谐的事务，你可以研究对应用程序做何改变以避免以后出现类似的锁定。为了举例说明上面的观点，回顾一下2.3.1节中的示例。在SHOW PROCESSLIST的输出中，我们看到：
阻塞的是MySQL 217号线程中的26244353号事务。当前唯一持有锁的事务是MySQL 184号线程中的26244352号事务。不过在查看通用日志文件之前，我们仍不是很清楚184号线程在做什么：
从该输出中，可以很容易地看到，与阻塞的事务一样，事务正在更新同一个表中的同一行记录。有了这些信息，我们可以重构应用程序。 [1] 以后会做修改：5.6.2版本包含多线程从服务器功能的预览。[2] 如2.7.1节讨论的那样，这种情况将会改变。[3] 该规则的例外是称为“半安全”的异常，如一个非事务表是只读或是只写（例如，日志表在这种上下文中是只写的）的时候。当你与事务表一起使用该类表的时候，需要小心。
微信扫一扫，打赏作者吧～
