MySQL--无效更新导致的CPU飙升问题

问题描述

某业务使用的MySQL服务器CPU存在周期性波动，根据监控排查那种操作引发CPU异常！

首先排查QPS，发现增删改查的QPS均无明显波动。

然后排查操作记录数，发现增删改查的记录数均无明显波动，虽然每秒查询记录数和每秒更新记录数较高。

幸好监控指标较全，我们分别对采集到的指标和CPU指标进行逐一匹配，发现监控项Handler_write的波动与CPU使用率波动基本一致。

相关监控

CPU使用率较高且波动较大：

查询QPS:

更新QPS:

删除QPS:

插入QPS：

每秒插入记录数(MySQL存储引擎层):

每秒删除记录数(MySQL存储引擎层):

每秒更新记录数(MySQL存储引擎层):

每秒读取记录数(MySQL存储引擎层):

每秒更新记录数(MySQL Server层):

每秒写入记录数(MySQL Server层):

根据对比发现，每秒写入记录数(MySQL Server层)与CPU使用率波动一致：

相关资料

以上数值均来自MySQL状态统计数据，相关指标描述为：

• Handler_update
The number of requests to update a row in a table.

• Handler_write
The number of requests to insert a row in a table.

• Innodb_rows_inserted
The number of rows inserted into InnoDB tables.

• Innodb_rows_deleted
The number of rows deleted from InnoDB tables.

• Innodb_rows_updated
The number of rows updated in InnoDB tables.

• Innodb_rows_read
The number of rows read from InnoDB tables.

• Com_xxx
The Com_xxx statement counter variables indicate the number of times each xxx statement has
been executed. There is one status variable for each type of statement. For example, Com_delete
and Com_update count DELETE and UPDATE statements, respectively. Com_delete_multi and
Com_update_multi are similar but apply to DELETE and UPDATE statements that use multipletable syntax.
If a query result is returned from query cache, the server increments the Qcache_hits status
variable, not Com_select. See Section 8.10.3.4, “Query Cache Status and Maintenance”.
All Com_stmt_xxx variables are increased even if a prepared statement argument is unknown
or an error occurred during execution. In other words, their values correspond to the number of
requests issued, not to the number of requests successfully completed. For example, because
status variables are initialized for each server startup and do not persist across restarts, the
Com_shutdown variable that tracks SHUTDOWN statements normally has a value of zero, but can be
nonzero if SHUTDOWN statements were executed but failed.

问题分析

由于之前对监控项Handler_write的关注度较低，平时更多关注QPS和每秒操作记录数，因此想研究下Handler_write和Innodb_rows_inserted的主要区别。

PS: 由于该集群为PXC集群，所有业务表都是InnoDB存储引擎表。

在上面的监控中：

• MySQL Server层每秒插入记录数(Handler_write)峰值超过6万，InnoDB存储引擎层的插入记录数(Innodb_rows_inserted)峰值约400，两者差距巨大。
• MySQL Server层每秒更新记录数(Handler_update)峰值超过1.1万，InnoDB存储引擎层的更新记录数(Innodb_rows_updated)峰值约1.1万，两者波动基本相同。
• 插入QPS在1000到2500之间波动，更新QPS在10到30之间波动，但更新记录数较高。

由于QPS和更操作记录数壁纸较大，可以断定业务使用批量插入，在一条SQL中同时操作多条记录，但通常不允许在1条SQL中操作插入和更新太多记录(IN子查询包含太多记录)。

通过将慢查询阈值调整为0来抓取所有请求分析，发现耗时最多的SQL为INSERT INTO ON DUPLICATE KEY UPDATE，1次插入操作携带几十上百条记录，大量插入记录操作最终被转换为更新记录操作，导致"InnoDB存储引擎层的更新记录数"较高。

InnoDB存储引擎层总操作记录数=InnoDB存储引擎层的更新记录数+InnoDB存储引擎层的插入记录数+InnoDB存储引擎层的删除记录数=1.2万，与MySQL Server层每秒插入记录数(Handler_write)的6万峰值相比，主要差异在哪呢？

在做普通更新操作时，MySQL会返回三个记录数：

• Affected rows：影响记录数
• Matched rows：匹配记录数
• Changed rows：修改记录数

如下面测试：

mysql> show create table tb1002 \G
*************************** 1. row ***************************
       Table: tb1002
Create Table: CREATE TABLE `tb1002` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> select * from tb1002;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    2 |
|  3 |    3 |    3 |
|  4 |    4 |    4 |
|  5 |    5 |    5 |
|  6 |    6 |    7 |
+----+------+------+
6 rows in set (0.00 sec)

mysql> update tb1002 set c2=6 where id=6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update tb1002 set c2=6 where id=6;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> insert into tb1002(c1,c2)values(6,7),(7,7) on duplicate key update c2=VALUES(c2);
Query OK, 3 rows affected (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 0

不同的操作对Affected rows/Matched rows/Changed rows的影响不一样。

对于第二次的INSERT INTO ON DUPLICATE KEY UPDATE执行：

mysql> insert into tb1002(c1,c2)values(6,7),(7,7) on duplicate key update c2=VALUES(c2);
Query OK, 0 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

在MySQL Server层，认为该语句属于INSERT语句，执行该语句会对Com_insert状态值进行加1操作。

在MySQL Server层，认为该语句会插入2条记录，执行该语句会对Handler_write状态值进行加2操作。

在InnoDB存储引擎层，该语句实际未修改任何记录，执行该语句不会修改Innodb_rows_updated状态值和Innodb_rows_inserted状态值。

因此可以断定业务上执行大量插入，但并未造成实际的更新，因此MySQL Server层每秒插入记录数(Handler_write)和InnoDB存储引擎层的插入记录数(Innodb_rows_inserted)差距巨大。

问题总结

虽然MySQL提供REPLACE INTO和INSERT INTO ON DUPLICATE KEY UPDATE两者可以兼容插入和更新的命令，能有效降低业务操作复杂度，但滥用这两种命令很容易导致数据库服务出现各种问题。
伴随各种容器化技术，应用服务器相对于数据库服务器有更好更领过的弹性部署空间，因此应尽可能把一些复杂操作放到应用服务器处理，避免数据库服务负载过高引发系统故障。