长事务排查:从 information_schema 到代码

长事务产生原因排查

1.
长事务例行检查
根据例行检查告警中的长事务SQL语句( trx.trx_query ), 再根据SQL语句查询应用日志, 定位具体业务操作

          
SELECT
  trx.trx_id                                      AS `事务ID`,
  trx.trx_mysql_thread_id                         AS `PROCESS_ID`,
  l.user                                          AS `用户`,
  l.host                                          AS `客户端`,
  l.db                                            AS `库`,
  to_seconds(now()) - to_seconds(trx.trx_started) AS `事务持续时间(秒)`,
  trx.trx_query                                   AS `语句`,
  trx.trx_started                                 AS `事务开始时间`,
  trx.trx_state                                   AS `事务状态`,
  trx.trx_isolation_level                         AS `事务隔离级别`,
  trx.trx_requested_lock_id                       AS `事务需要等待的资源`,
  trx.trx_wait_started                            AS `事务开始等待时间`,
  trx.trx_tables_in_use                           AS `事务使用表`,
  trx.trx_tables_locked                           AS `事务拥有锁`,
  trx.trx_rows_locked                             AS `事务锁定行`,
  trx.trx_rows_modified                           AS `事务更改行`
FROM
  information_schema.innodb_trx trx
  LEFT JOIN information_schema.PROCESSLIST l ON l.id = trx.trx_mysql_thread_id
  ORDER BY to_seconds(now()) - to_seconds(trx.trx_started) DESC

          
-- 事务执行的所有SQL(默认记录10行记录)
SHOW GLOBAL VARIABLES LIKE'%performance_schema';
SET @PROCESS_ID = 7013583;
SELECT
  esh.EVENT_ID                    AS `事件`,
  esh.event_name                  AS `事件名称`,
  esh.sql_text                    AS `SQL语句`,
  esh.timer_wait                  AS `SQL语句耗时(皮秒)`,
  trx.trx_id                      AS `事务ID`,
  trx.trx_mysql_thread_id         AS `PROCESS_ID`,
  t.THREAD_ID                     AS `THREAD_ID`,
  t.THREAD_OS_ID                  AS `THREAD_OS_ID`,
  l.user                          AS `用户`,
  l.host                          AS `主机`,
  l.db                            AS `数据库`,
  trx.trx_started                 AS `事务开始时间`,
  to_seconds(now()) - to_seconds(trx.trx_started) AS `事务持续时间(秒)`,
  trx.trx_state                   AS `事务状态`,
  trx.trx_isolation_level         AS `事务隔离级别`
FROM
  performance_schema.events_statements_history esh
  JOIN performance_schema.threads t ON esh.thread_id = t.thread_id
  JOIN information_schema.PROCESSLIST l ON l.id = t.processlist_id AND l.id = @PROCESS_ID
  LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = l.id
WHERE
  trx.trx_id IS NOT NULL AND l.USER != 'SYSTEM_USER'
ORDER BY esh.EVENT_ID DESC;

2.
查看操作记录表( operation_record )中耗时久的请求

所有访问系统的请求( 包括浏览器HTTP,OPEN_FEIGN,MQ等 )都会被记录

          
-- 操作记录表
CREATE TABLE `operation_record` (
   `record_id` bigint NOT NULL COMMENT '主键',
   `enterprise_id` bigint DEFAULT NULL COMMENT '企业ID',
   `account_id` bigint DEFAULT NULL COMMENT '操作人ID',
   `user_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '操作人名称',
   `real_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '操作人名称',
   `handle_status` tinyint DEFAULT '1' COMMENT '处理状态. 1:成功 2:失败',
   `handle_status_desc` char(2) COLLATE utf8mb4_general_ci DEFAULT '成功' COMMENT '处理状态. 1:成功 2:失败',
   `header` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '操作请求头',
   `in_body` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '操作请求内容',
   `out_body` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '操作响应内容',
   `business_no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '业务单号',
   `business_id` bigint DEFAULT NULL COMMENT '业务ID',
   `message_id` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'MQ消息ID',
   `service_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '服务名称',
   `module_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '模块名称',
   `method` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '接口名称',
   `remark` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '功能描述',
   `desc` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '描述信息',
   `source_type` int unsigned NOT NULL COMMENT '来源方式类型',
   `source_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '来源方式名称',
   `user_agent` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'User-Agent',
   `domain` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '域名',
   `source_ip` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '来源IP',
   `handle_ip` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '事件处理节点的IP',
   `trace_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '观测云traceId',
   `create_time` datetime(3) NOT NULL COMMENT '创建时间',
   `update_time` datetime(3) NOT NULL COMMENT '修改时间',
   PRIMARY KEY (`record_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='操作记录表'

          
-- 近30分钟请求超过 60 秒
SELECT
(UNIX_TIMESTAMP(t.update_time) - UNIX_TIMESTAMP(t.create_time)) AS cost,
t.*
FROM
operation_record t
WHERE
t.create_time >= DATE_ADD(NOW(), INTERVAL -30 MINUTE)
AND (UNIX_TIMESTAMP(t.update_time) - UNIX_TIMESTAMP(t.create_time)) > 60 ORDER BY t.create_time DESC;

3.
通过 Grafana 查看耗时久的HTTP请求