§ UPDATE执行慢排查分析

本文介绍在GreatSQL数据库中，如果UPDATE请求响应较慢如何进行排查分析。

§ 写在前面

在开始分析排查先简单了解 UPDATE 请求在 MySQL/GreatSQL 中的生命周期是什么，以及如何执行一个事务。

理解UPDATE的工作过程后，更有利于排查分析慢的原因。

1. 连接器

客户端发起一个 TCP 请求后，MySQL/GrreatSQL Server 端会负责通信协议处理、线程处理、账号认证、安全检查。

2. 分析器

MySQL/GrreatSQL Server 端对一个 SQL 请求进行词法分析（识别 select、from），然后会对语法进行分析判断语法是否正确。

3. 优化器

优化器会分析 SQL 语句，选择合适的索引，根据预结果集判断是否使用全表扫描。

请求进入 InnoDB 引擎后，首先判断该事务涉及到的数据页是否在buffer pool中（以下简称ibp），不存在则会从磁盘中加载此事务涉及的数据页到ibp中，并对相应的索引数据页加锁。
将修改前的数据写入到 Undo 中，修改后将回滚针执行 Undo log 中修改前的行。
写 redo log buffer 在 BP 中对数据进行修改操作，并将修改后的值写入到 redo log buffer 中等待异步 sync到磁盘。
写 binlog cache，同时将修改的信息记录到 binlog cache 中，等待落盘。如果 binlog cache 不够用时，会写入到 binlog 临时文件。
写 change buffer。如果这个事务需要在二级索引上做修改，写入到 change buffer page 中，等待之后，事务需要读取该二级索引时进行 merge。

执行事务提交会进入二阶段提交模式（prepare 阶段和 commit 阶段。

两阶段涉及两个参数（sync_binlog 和 innodb_flush_log_at_trx_commit）。

事务提交分为 prepare 阶段与 commit 阶段（两阶段提交）。
Redo log prepare。
Binlog write&fync: 执行器把 binlog cache 里的完整事务和 redo log prepare 中的 xid 写入到 binlog 中。
Redo log commit commit。
事务提交，释放行记录持有的排它锁。
Binlog 和 redo log 落盘后触发刷新脏页操作。
如果事务发生ROLLBACK，因为系统异常或显示回滚，所有数据变更会变成原来的，通过回滚日志中数据进行恢复。

1. 在事务执行阶段

因为锁等待，包括行锁以及MDL锁，这是通过观察 sys.innodb_lock_waits 和 sys.schema_table_lock_waits 就能看到锁等待情况。
因为物理I/O较慢或物理I/O代价较高导致，这时通常IOPS或iowait指标较高。
因为buffer pool不够用，需要等待释放部分page，这时通常能看到 Innodb_buffer_pool_wait_free 这个状态指标值大于0。
因为binlog & redo log两阶段协同提交，导致落盘慢，这时通常能看到类似 waiting for handler commit 的线程状态。

查看当时实例系统负载情况（IO、CPU、memory），排除系统负载干扰

如果 CPU 高、IO 高、wa 大：

先排查慢 SQL，再查当前并发数，一般是大量并发慢 SQL 导致。

如果 CPU 高、IO 中、wa 小：

排查慢 SQL，在查看当前并发数，一般是单个计算 SQL 导致。

如果 CPU 低、IO 高、wa 低：

排查当前占用 io 高的线程，有可能是 page clean 导致或日志刷新频繁导致。

执行 SHOW PROCESSLIST，查看当前线程是否有下列几种情况：

converting HEAP to ondisk、copy to tmp table、Copying to group table、Copying to tmp table等状态。
Copying to tmp table on disk、Creating sort index、Creating tmp table等状态。
Killed、Rolling back等状态。
Sorting result、Sending data、Searching rows for update等状态。
Waiting for XX状态。

如果有，抓紧分析并优化这些正在执行的SQL。

通过 EXPLAIN 分析SQL的执行情况，是否走索引，是否有额外分组、排序、临时表，以及多表关联查询时驱动表选错等情况。

使用 PROFILING 分析SQL语句哪个执行阶段最慢。

观察是单个 SQL 执行慢，还是所有语句都慢，如果是所有SQL都慢，有可能是那个时段受到其他外部影响，导致数据库整体性能都很差，需要通过系统层的监控工具辅助排查分析。

使用 tcpdump 进行抓包，分析是 MySQL/GreatSQL 返回慢，还是网络慢。

使用 strace 分析 MySQL/GreatSQL 内部哪里慢，哪个函数导致的。

UPDATE慢的问题还不止于上面列举的这些情况，本文主要是提供一个排查分析的思路，更多原因还需要进一步具体分析。

另外，本文以UPDATE请求为例，实际上INSERT和DELETE请求的情形也可以采用同样的排查分析思路进行。

参考资料：

greatsql-wx