TiDB 性能分析和优化

本文介绍了基于数据库时间的系统优化方法，以及如何利用 TiDB Performance Overview 面板 进行性能分析和优化。

通过本文中介绍的方法，你可以从全局、自顶向下的角度分析用户响应时间和数据库时间，确认用户响应时间的瓶颈是否在数据库中。如果瓶颈在数据库中，你可以通过数据库时间概览和 SQL 延迟的分解，定位数据库内部的瓶颈点，并进行针对性的优化。

基于数据库时间的性能优化方法

TiDB 对 SQL 的处理路径和数据库时间进行了完善的测量和记录，方便定位数据库的性能瓶颈。即使在用户响应时间的性能数据缺失的情况下，基于 TiDB 数据库时间的相关性能指标，你也可以达到以下两个性能分析目标：

1. 通过对比 SQL 处理平均延迟和事务中 TiDB 连接的空闲时间，确定整个系统的瓶颈是否在 TiDB 中。
2. 如果瓶颈在 TiDB 内部，根据数据库时间概览、颜色优化法、关键指标和资源利用率、自上而下的延迟分解，定位到性能瓶颈具体在整个分布式系统的哪个模块。

确定整个系统的瓶颈是否在 TiDB 中

• 如果事务中 TiDB 连接的平均空闲时间比 SQL 平均处理延迟高，说明应用的事务处理中，主要的延迟不在数据库中，数据库时间占用户响应时间比例小，可以确认瓶颈不在数据库中。

  在这种情况下，需要关注数据库外部的组件，比如应用服务器硬件资源是否存在瓶颈，应用到数据库的网络延迟是否过高等。

• 如果 SQL 平均处理延迟比事务中 TiDB 连接的平均空闲时间高，说明事务中主要的瓶颈在 TiDB 内部，数据库时间占用户响应时间比例大。

如果瓶颈在 TiDB 内部，如何定位

一个典型的 SQL 的处理流程如下所示，TiDB 的性能指标覆盖了绝大部分的处理路径，对数据库时间进行不同维度的分解和上色，用户可以快速的了解负载特性和数据库内部的瓶颈。

数据库时间是所有 SQL 处理时间的总和。通过以下三个维度对数据库时间进行分解，可以帮助你快速定位 TiDB 内部瓶颈：

• 按 SQL 处理类型分解，判断哪种类型的 SQL 语句消耗数据库时间最多。对应的分解公式为：

  DB Time = Select Time + Insert Time + Update Time + Delete Time + Commit Time + ...

• 按 SQL 处理的 4 个步骤（即 get_token/parse/compile/execute）分解，判断哪个步骤消耗的时间最多。对应的分解公式为：

  DB Time = Get Token Time + Parse Time + Compile Time + Execute Time

• 对于 execute 耗时，按照 TiDB 执行器本身的时间、TSO 等待时间、KV 请求时间和重试的执行时间，判断执行阶段的瓶颈。对应的分解公式为：

  Execute Time ~= TiDB Executor Time + KV Request Time + PD TSO Wait Time + Retried execution time

利用 Performance Overview 面板进行性能分析和优化

本章介绍如何利用 Grafana 中的 Performance Overview 面板进行基于数据库时间的性能分析和优化。

Performance Overview 面板按总分结构对 TiDB、TiKV、PD 的性能指标进行编排组织，包括以下三个部分：

• 数据库时间和 SQL 执行时间概览：通过颜色标记不同 SQL 类型，SQL 不同执行阶段、不同请求的数据库时间，帮助你快速识别数据库负载特征和性能瓶颈。
• 关键指标和资源利用率：包含数据库 QPS、应用和数据库的连接信息和请求命令类型、数据库内部 TSO 和 KV 请求 OPS、TiDB 和 TiKV 的资源使用概况。
• 自上而下的延迟分解：包括 Query 延迟和连接空闲时间对比、Query 延迟分解、execute 阶段 TSO 请求和 KV 请求的延迟、TiKV 内部写延迟的分解等。

数据库时间和 SQL 执行时间概览

Database Time 指标为 TiDB 每秒处理 SQL 的延迟总和，即 TiDB 集群每秒并发处理应用 SQL 请求的总时间(等于活跃连接数)。

Performance Overview 面板提供了以下三个面积堆叠图，帮助你了解数据库负载的类型，快速定位数据库时间的瓶颈主要是处理什么语句，集中在哪个执行阶段，SQL 执行阶段主要等待 TiKV 或者 PD 哪种请求类型。

• Database Time By SQL Type
• Database Time By SQL Phase
• SQL Execute Time Overview

颜色优化法

通过观察数据库时间分解图和执行时间概览图，你可以直观地区分正常或者异常的时间消耗，快速定位集群的异常瓶颈点，高效了解集群的负载特征。对于正常的时间消耗和请求类型，图中显示颜色为绿色系或蓝色系。如果非绿色或蓝色系的颜色在这两张图中占据了明显的比例，意味着数据库时间的分布不合理。

• Database Time By SQL Type：蓝色标识代表 Select 语句，绿色标识代表 Update、Insert、Commit 等 DML 语句。红色标识代表 General 类型，包含 StmtPrepare、StmtReset、StmtFetch、StmtClose 等命令。
• Database Time By SQL Phase：execute 执行阶段为绿色，其他三个阶段偏红色系，如果非绿色的颜色占比明显，意味着在执行阶段之外数据库消耗了过多时间，需要进一步分析根源。一个常见的场景是因为无法使用执行计划缓存，导致 compile 阶段的橙色占比明显。
• SQL Execute Time Overview：绿色系标识代表常规的写 KV 请求（例如 Prewrite 和 Commit），蓝色系标识代表常规的读 KV 请求（例如 Cop 和 Get），紫色系标识代表 TiFlash MPP 请求，其他色系标识需要注意的问题。例如，悲观锁加锁请求为红色，TSO 等待为深褐色。如果非蓝色系或者非绿色系占比明显，意味着执行阶段存在异常的瓶颈。例如，当发生严重锁冲突时，红色的悲观锁时间会占比明显；当负载中 TSO 等待的消耗时间过长时，深褐色会占比明显。

示例 1：TPC-C 负载

• Database Time by SQL Type：主要消耗时间的语句为 commit、update、select 和 insert 语句。
• Database Time by SQL Phase：主要消耗时间的阶段为绿色的 execute 阶段。
• SQL Execute Time Overview：执行阶段主要消耗时间的 KV 请求为绿色的 Prewrite 和 Commit。

示例 2：OLTP 读密集负载

• Database Time by SQL Type：主要消耗时间的语句为 select、commit、update 和 insert 语句。其中，select 占据绝大部分的数据库时间。
• Database Time by SQL Phase：主要消耗时间的阶段为绿色的 execute 阶段。
• SQL Execute Time Overview：执行阶段主要消耗时间为深褐色的 pd tso_wait、蓝色的 KV Get 和绿色的 Prewrite 和 Commit。

示例 3：只读 OLTP 负载

• Database Time by SQL Type：几乎所有语句为 select。
• Database Time by SQL Phase：主要消耗时间的阶段为橙色的 compile 和绿色的 execute 阶段。compile 阶段延迟最高，代表着 TiDB 生成执行计划的过程耗时过长，需要根据后续的性能数据进一步确定根源。
• SQL Execute Time Overview：执行阶段主要消耗时间的 KV 请求为蓝色 BatchGet。

示例 4： 锁争用负载

• Database Time by SQL Type：主要为 Update 语句。
• Database Time by SQL Phase：主要消耗时间的阶段为绿色的 execute 阶段。
• SQL Execute Time Overview：执行阶段主要消耗时间的 KV 请求为红色的悲观锁 PessimisticLock，execute time 明显大于 KV 请求的总时间，这是因为应用的写语句锁冲突严重，频繁锁重试导致 Retried execution time 过长。目前 Retried execution time 消耗的时间，TiDB 尚未进行测量。

示例 5： HTAP CH-Benchmark 负载

• Database Time by SQL Type：主要为 Select 语句。
• Database Time by SQL Phase：主要消耗时间的阶段为绿色的 execute 阶段。
• SQL Execute Time Overview：执行阶段主要消耗时间为紫色的 tiflash_mpp 请求，其次是 KV 请求，包括蓝色的 Cop ，以及绿色的 Prewrite 和 Commit 。

TiDB 关键指标和集群资源利用率

Query Per Second、Command Per Second 和 Prepared-Plan-Cache

通过观察 Performance Overview 里的以下三个面板，可以了解应用的负载类型，与 TiDB 的交互方式，以及是否能有效地利用 TiDB 的 执行计划缓存 。

• QPS：表示 Query Per Second，包含应用的 SQL 语句类型执行次数分布。

• CPS By Type：CPS 表示 Command Per Second，Command 代表 MySQL 协议的命令类型。同样一个查询语句可以通过 query 或者 prepared statement 的命令类型发送到 TiDB。

• Queries Using Plan Cache OPS：TiDB 集群每秒执行计划缓存的命中次数（即 avg-hit ） 和未命中次数（即 avg-miss ）。

  StmtExecute 每秒执行次数等于 avg-hit + avg-miss 。执行计划缓存只支持 prepared statement 命令。当 TiDB 开启执行计划缓存时，存在三种使用情况：

  • 完全无法命中执行计划缓存：每秒命中次数 avg-hit 为 0， avg-miss 等于 StmtExecute 命令每秒执行次数。可能的原因包括：
    • 应用使用了 query 命令。
    • 每次 StmtExecute 执行之后，应用调用了 StmtClose 命令，导致缓存的执行计划被清理。
    • StmtExecute 执行的所有语句都不符合 缓存的条件 ，导致无法命中执行计划缓存。
  • 完全命中执行计划缓存：每秒命中次数 avg-hit 等于 StmtExecute 命令每秒执行次数，每秒未命中次数 avg-miss 等于 0。
  • 部分命中执行计划缓存：每秒命中次数 avg-hit 小于 StmtExecute 命令每秒执行次数。执行计划缓存目前存在一些限制，比如不支持子查询，该类型的 SQL 执行计划无法被缓存。

示例 1：TPC-C 负载

TPC-C 负载类型主要以 Update、Select 和 Insert 语句为主。总的 QPS 等于每秒 StmtExecute 的次数，并且 StmtExecute 每秒的数据基本等于 Queries Using Plan Cache OPS 面板的 avg-hits 。这是 OLTP 负载理想的情况，客户端执行使用 prepared statement，并且在客户端缓存了 prepared statement 对象，执行每条 SQL 语句时直接调用 statement 执行。执行时都命中执行计划缓存，不需要重新 compile 生成执行计划。

示例 2：只读 OLTP 负载，使用 query 命令无法使用执行计划缓存

这个负载中，Commit QPS = Rollback QPS = Select QPS。应用开启了 auto-commit 并发，每次从连接池获取连接都会执行 rollback，因此这三种语句的执行次数是相同的。

• QPS 面板中出现的红色加粗线为 Failed Query，坐标的值为右边的 Y 轴。非 0 代表此负载中存在错误语句。
• 总的 QPS 等于 CPS By Type 面板中的 Query，说明应用中使用了 query 命令。
• Queries Using Plan Cache OPS 面板没有数据，因为不使用 prepared statement 接口，无法使用 TiDB 的执行计划缓存，意味着应用的每一条 query，TiDB 都需要重新解析，重新生成执行计划。通常会导致 compile 时间变长以及 TiDB CPU 消耗的增加。

示例 3：OLTP 负载，使用 prepared statement 接口无法使用执行计划缓存

StmtPrepare 次数 = StmtExecute 次数 = StmtClose 次数 ~= StmtFetch 次数，应用使用了 prepare > execute > fetch > close 的 loop，很多框架都会在 execute 之后调用 close，确保资源不会泄露。这会带来两个问题：

• 执行每条 SQL 语句需要 4 个命令，以及 4 次网络往返。
• Queries Using Plan Cache OPS 为 0，无法命中执行计划缓存。StmtClose 命令默认会清理缓存的执行计划，导致下一次 StmtPrepare 命令需要重新生成执行计划。

示例 4：Prepared Statement 存在资源泄漏

StmtPrepare 每秒执行次数远大于 StmtClose，说明应用程序存在 prepared statement 对象泄漏。

• QPS 面板中出现的红色加粗线为 Failed Query，坐标的值为右边的 Y 轴。每秒错误语句为 74.6 条。
• CPS By Type 面板中的 StmtPrepare 每秒执行次数远大于 StmtClose，说明应用程序存在 prepared statement 对象泄漏。
• Queries Using Plan Cache OPS 面板中的 avg-miss 几乎等于 CPS By Type 面板中的 StmtExecute，说明几乎所有的 SQL 执行都未命中执行计划缓存。

KV/TSO Request OPS 和 KV Request Time By Source

• 在 KV/TSO Request OPS 面板中，你可以查看 KV 和 TSO 每秒请求的数据统计。其中， kv request total 代表 TiDB 到 TiKV 所有请求的总和。通过观察 TiDB 到 PD 和 TiKV 的请求类型，可以了解集群内部的负载特征。
• 在 KV Request Time By Source 面板中，你可以查看每种 KV 请求和请求来源的时间占比。
  • kv request total time 是每秒总的 KV 和 TiFlash 请求处理时间
  • 每种 KV 请求和请求来源组成柱状堆叠图， external 标识正常业务的请求， internal 标识内部活动的请求（比如 DDL、auto analyze 等请求）。

示例 1：繁忙的负载

在此 TPC-C 负载中：

• 每秒总的 KV 请求的数量为 79.7 K。按请求数量排序，最高的请求类型为 Prewrite 、 Commit 、 PessimisticsLock 和 BatchGet 等。
• KV 处理时间来源主要为 Commit-external_Commit 、 Prewrite-external_Commit ，说明消耗时间最高的 KV 请求为 Commit 和 Prewrite ，并且来源于外部的 Commit 语句。

示例 2：Analyze 负载

集群中只有 analyze 语句运行：

• 每秒总的 KV 请求数据是 35.5，Cop 请求次数是每秒 9.3。
• KV 处理时间主要来源为 Cop-internal_stats ，说明 Cop 请求来源于内部的 analyze 操作。

CPU 和内存使用情况

在 TiDB、TiKV 和 PD 的 CPU/Memory 面板中，你可以监控它们各自的逻辑 CPU 使用率和内存消耗情况，例如平均 CPU 利用率、最大 CPU 利用率、CPU 利用率差值（最大 CPU 使用率减去最小 CPU 使用率）、CPU Quota（可以使用的 CPU 核数）以及最大内存使用率。基于这些指标，你可以确定 TiDB、TiKV 和 PD 的整体资源使用情况。

• 根据 delta 值，你可以判断 TiDB 或 TiKV 的 CPU 使用是否存在不均衡的情况。对于 TiDB，较高的 delta 值通常意味着应用程序的连接在 TiDB 实例之间分布不均衡；对于 TiKV，较高的 delta 值通常意味着集群中存在读写热点。
• 通过 TiDB、TiKV 和 PD 的资源使用概览，你可以快速判断集群是否存在资源瓶颈，以及是否需要对 TiKV、TiDB 或 PD 进行扩容或者硬件配置升级。

示例 1：TiKV 资源使用率高

在以下 TPC-C 负载中，每个 TiDB 和 TiKV 配置了 16 核 CPU，PD 配置了 4 核 CPU。

• TiDB 的平均、最大和 delta CPU 使用率分别为 761%、934% 和 322%。最大内存使用率为 6.86 GiB。
• TiKV 的平均、最大和 delta CPU 使用率分别为 1343%、1505% 和 283%。最大内存使用率为 27.1 GiB。
• PD 的最大 CPU 使用率为 59.1%。最大内存使用率为 221 MiB。

显然，TiKV 消耗了更多的 CPU，在 TPC-C 这样的写密集场景中，这是符合预期的。建议通过扩容 TiKV 来提升性能。

数据流量

Read traffic 和 Write traffic 面板可以帮助你深入分析 TiDB 集群内部的流量模式，全面监控从客户端到数据库以及内部组件之间的数据流情况。

• Read traffic （读流量）

  • TiDB -> Client ：从 TiDB 到客户端的出站流量统计
  • Rocksdb -> TiKV ：TiKV 在存储层读操作期间从 RocksDB 读取的数据流量

• Write traffic （写流量）

  • Client -> TiDB ：从客户端到 TiDB 的入站流量统计

  • TiDB -> TiKV: general ：前台事务从 TiDB 写入到 TiKV 的速率

  • TiDB -> TiKV: internal ：内部事务从 TiDB 写入到 TiKV 的速率

  • TiKV -> Rocksdb ：从 TiKV 到 RocksDB 的写操作流量

  • RocksDB Compaction ：RocksDB compaction 操作产生的总读写 I/O 流量。如果 RocksDB Compaction 明显高于 TiKV -> Rocksdb ，且你的平均行大小高于 512 字节，则可以进行以下配置以减少 compaction I/O 流量：启用 Titan，将 min-blob-size 设置为 "512B" 或 "1KB" ，将 blob-file-compression 设置为 "zstd" 。

    [rocksdb.titan]
    enabled = true
    [rocksdb.defaultcf.titan]