记一次 TiDB 数据库报错的处理过程

作者： tracy0984 原文来源：https://tidb.net/blog/9f8832ea

概述

本文记录了一次 TiDB 数据库报错的处理过程。其中也列举了查看 TiDB 数据库慢 SQL, 创建索引以及查看 SQL 执行计划等操作的具体步骤。

问题描述

TiDB 版本：v5.3.0

测试使用 kettle 脚本向 TiDB 数据库更新 / 插入数据时, 数据库返回错误信息, 事务回滚。

报错信息

-- kettle日志中找到如下报错信息Caused by: java.sql.SQLException: TTL manager has timed out, pessimistic locks may expire, please commit or rollback this transaction

问题分析

TiDB 官方文档找到报错原因:

TiDB 锁冲突问题处理 | PingCAP Docs

查看 TiDB 数据库中事务相关参数的设置

show config where name = 'performance.max-txn-ttl';

show variables like 'tidb_txn_mode';

tidb_txn_mode = pessimistic ，表示数据库中的事务默认使用悲观锁。

performance.max-txn-ttl=3600000 ，表示悲观锁的 TTL 上限时间为 1 小时。

也就是说，当前数据库中的事务执行时间超过 1 小时，可能就会报错：TTL manager has timed out, pessimistic locks may expire, please commit or rollback this transaction。

查找慢 SQL

尝试重新运行 kettle 脚本。程序运行过程中, 通过 TiDB Dashboard 监控工具，查看到一条等值查询的 SQL, 每次查询耗时 1.4s 左右，并且这条 SQL 语句在近 50 分钟内已经被执行了 2300 多次。

TiDB Dashboard ->SQL Statements

这条 SQL 执行慢的原因为: 表的数据量较大并且查询条件列缺少索引, 执行计划走了全表扫描.

经分析,kettle 脚本执行过程报错原因就是由于这条需要反复执行的 SQL 语句执行时间比较长，导致一小时内事务没有执行完成。

解决方法

对执行较慢的查询语句进行了优化, 优化后, 报错不再出现, 问题顺利解决.

查询条件列的选择性:

select count（*), count(distinct(col1)) from database.table_name;

从查询结果看，查询条件列的选择性很好, 无重复值。

这样，可在查询的条件列上添加了唯一索引来提高查询效率。

创建索引

查看创建索引过程相关参数设置：

show variables like 'tidb_ddl_reorg%';

tidb_ddl_reorg_batch_size：设置创建索引过程中的数据回填阶段的 batch size 大小.batch size 越大，回填数据的速度越快，但是 TiKV 的写入压力会变大，事务冲突的概率也会越大。

tidb_ddl_reorg_priority：设置创建索引过程中的数据回填阶段的执行优先级。设置为 PRIORITY_LOW，表示 DDL 操作优先级低于 DML。

tidb_ddl_reorg_worker_cnt：设置 创建索引过程中的数据回填阶段的并发度。

创建唯一索引命令:

CREATE UNIQUE INDEX idx_uniq_XXX ON database.table_name(col1);

查看索引创建任务执行情况:

admin show ddl jobs;

查看索引完成情况:

SELECT D.JOB_ID,D.ROW_COUNT,TIMESTAMPDIFF(MINUTE,D.START_TIME,D.END_TIME) EXCUTE_MINUTES FROM INFORMATION_SCHEMA.DDL_JOBS D WHERE D.JOB_ID=14094;

索引创建完成后，再次执行 Kettle 脚本，并且通过 TiDB Dashboard ->SQL Statements 界面，确认 SQL 语句执行情况, 之前的查询语句执行计划走唯一索引后, 执行时间大幅缩短, 由 1.4s 变为 767us.

查看 SQL 执行计划

查看 SQL 语句的执行计划有下面两种方法：

方法一：EXPLAIN SQL 语句；

方法二：EXPLAIN ANALYZE SQL 语句；

EXPLAIN 语句仅用于显示查询的执行计划，而不执行查询。EXPLAIN ANALYZE 可执行查询，补充 EXPLAIN 语句。

创建索引后，查看待优化的 SQL 语句的执行计划改全表扫为点查：

EXPLAIN SELECT XX, col1, XX,XX, XX,XX,… FROM database.table_name WHERE ((col = XXXXX));

总结

在数据库运维过程中，当我们遇到未处理过的数据库问题时，可以考虑先到 TiDB 官方文档中搜索一下问题，官方文档中可能已经记录了解决问题的思路或方法。

TiDB 产品文档 | PingCAP Docs