一文带你定位生产MySQL数据库大事务

落叶说Mysql数据库运维 2020-10-23

5270

在MySQL数据库中，慢sql，锁等待，大事务在导致业务响应慢，绝对能排上前三名。今天就说一说大事务对业务有哪些危害，以及怎么定位和排查大事务。

大事务的危害1.锁定太多的数据，造成大量的阻塞和锁超时。2.回滚所需要的时间比较长。3.执行时间长，容易造成主从延迟。

既然知道了大事务的危害，那么发现大事务，避免大事务就是我们要做的事情。

在应用层面1.尽量避免使用事务2.尽量避免在事务里使用select语句，将select语句移除事务3.在一个事务里，避免一次处理太多数据，如有批量处理大数据，应分批次处理。

数据库层面1.监控大事务，例如通过监控平台，及时获取事务超过10秒未提交的sql语句。2.设置安全参数safe-updates，禁止不带条件，不走索引的DML操作。3.如果业务允许，可以部署自动监测和查杀超过时间阀值未提交的大事务。

在这里详细说一下，大事务的监控，在这里先模拟一下事务

复制

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t_test set paymont=100 where id=99;
Query OK, 1 row affected (0.32 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_test where id=98;
+----+---------+
| id | paymont |
+----+---------+
| 98 |    1.98 |
+----+---------+
1 row in set (0.00 sec)

mysql> update t_test set paymont=101 where id=97;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
复制

这样就模拟出一个事务，下面就查询这个事务运行了多长时间，以及正在运行的sql语句

mysql> select now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXT from information_schema.innodb_trx a inner join
    ->    information_schema.PROCESSLIST b
    ->    on a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'
    ->    inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID
    ->    inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;
+---------------------+----------+-----+------+-----------+--------+-------------------------------------------+
| now()               | diff_sec | id  | user | host      | db     | SQL_TEXT                                  |
+---------------------+----------+-----+------+-----------+--------+-------------------------------------------+
| 2020-09-01 02:54:20 |      139 | 530 | root | localhost | testdb | update t_test set paymont=101 where id=97 |
+---------------------+----------+-----+------+-----------+--------+-------------------------------------------+
1 row in set (0.32 sec)
复制


复制

可是有时候，只有当前运行的sql语句，开发人员还是不太好找出对应的事务，如果有事务运行的完整的所有sql语句就好了，这里就不得不提一下PERFORMANCE_SCHEMA.events_statements_history这个视图。这个视图里记录了最近运行的sql语句，于是就有了下面的排查sql语句

mysql>  SELECT
    ->     ps.id 'PROCESS ID',
    ->     ps.USER,
    ->     ps.HOST,
    ->     esh.EVENT_ID,
    ->     trx.trx_started,
    ->     esh.event_name 'EVENT NAME',
    ->     esh.sql_text 'SQL',
    ->     ps.time
    ->     FROM
    ->     PERFORMANCE_SCHEMA.events_statements_history esh
    ->     JOIN PERFORMANCE_SCHEMA.threads th ON esh.thread_id = th.thread_id
    ->     JOIN information_schema.PROCESSLIST ps ON ps.id = th.processlist_id
    ->     LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.id
    ->     WHERE
    ->     trx.trx_id IS NOT NULL
    ->     AND ps.USER != 'SYSTEM_USER'
    ->     ORDER BY
    ->     esh.EVENT_ID;
+------------+------+-----------+----------+---------------------+------------------------------+-------------------------------------------+------+
| PROCESS ID | USER | HOST      | EVENT_ID | trx_started         | EVENT NAME                   | SQL                                       | time |
+------------+------+-----------+----------+---------------------+------------------------------+-------------------------------------------+------+
|        530 | root | localhost |        7 | 2020-09-01 02:52:01 | statement/com/Init DB        | NULL                                      |  196 |
|        530 | root | localhost |        8 | 2020-09-01 02:52:01 | statement/sql/show_databases | show databases                            |  196 |
|        530 | root | localhost |        9 | 2020-09-01 02:52:01 | statement/sql/show_tables    | show tables                               |  196 |
|        530 | root | localhost |       10 | 2020-09-01 02:52:01 | statement/com/Field List     | NULL                                      |  196 |
|        530 | root | localhost |       11 | 2020-09-01 02:52:01 | statement/sql/show_tables    | show tables                               |  196 |
|        530 | root | localhost |       12 | 2020-09-01 02:52:01 | statement/sql/select         | select * from t_test                      |  196 |
|        530 | root | localhost |       13 | 2020-09-01 02:52:01 | statement/sql/begin          | begin                                     |  196 |
|        530 | root | localhost |       14 | 2020-09-01 02:52:01 | statement/sql/update         | update t_test set paymont=100 where id=99 |  196 |
|        530 | root | localhost |       15 | 2020-09-01 02:52:01 | statement/sql/select         | select * from t_test where id=98          |  196 |
|        530 | root | localhost |       16 | 2020-09-01 02:52:01 | statement/sql/update         | update t_test set paymont=101 where id=97 |  196 |
+------------+------+-----------+----------+---------------------+------------------------------+-------------------------------------------+------+
10 rows in set (0.34 sec)
复制


复制

可是有的朋友会问，我生产之前没有部署这些监控，如果想看一下你的业务是否有大事务，也可以用以下方法查看一下

[mysql@localhost binlog]$ mysqlbinlog u02/log/3308/binlog/binlog.000032 | grep "GTID$(printf '\t')last_committed" -B 1  | egrep -E '^# at|^#20' | awk '{print $1,$2,$3}' | sed 's/server//' | sed 'N;s/\n/ /' | awk 'NR==1 {tmp=$1} NR>1 {print $4,$NF,($3-tmp);tmp=$3}' | sort -k 3 -n -r | head -n 20
#200831 11:38:10 1040062
#200831 11:38:10 1040062
#200831 11:38:10 1040062
#200831 11:38:10 1040062
#200831 11:38:10 1040062
#200831 11:38:10 1040062
#200831 11:38:10 1040062
#200831 11:38:09 1040062
#200831 11:38:09 1040062
#200831 11:38:09 1040062
#200831 11:38:09 1040062
#200831 11:38:09 1040062
#200831 11:38:09 1040062
#200831 11:38:09 1040062
#200831 11:38:09 1040062
#200831 11:38:08 1040062
#200831 11:38:08 1040062
#200831 11:38:08 1040062
#200831 11:38:08 1040062
#200831 11:38:08 1040062
复制

从上面结果来看，最大的事务1040062/1024/1024=0.99188042，大约在1M左右。

mysql

文章转载自落叶说Mysql数据库运维，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

一文带你定位生产MySQL数据库大事务

评论

相关阅读