tps tmb，tidb update性能

今天我们将讨论优化数据库性能。第一部分简要介绍性能优化的一般方法，第二部分描述实际案例。只有一条语句是性能优化的核心。用户响应时间到哪里去了？性能优化之所以困难，是因为确定用户响应时间在不同模块之间的分布是困难的，从底层硬件、CPU、IO、网络开始，一直到顶层。必须测量和分析系统的组成部分。层应用程序架构、应用程序代码与数据库的交互所有方法都必须涉及。

用户响应时间性能优化中的第一个概念是用户响应时间。用户响应时间是指在使用业务系统时，从用户发起请求到请求返回的总时间，即用户响应时间。典型的用户响应时间分布是：

从时序图来看，用户响应时间可以包括：

用户请求到达应用服务器时的网络时间应用服务器本身业务逻辑处理所花费的时间应用服务器与数据库服务器交互所消耗的网络时间数据库处理SQL多次所花费的时间应用服务器使用用户数据时查看整个网络时间链路，包括网络、应用服务器、数据库等关键组件。了解每个模块的用户响应时间分布可以让您识别瓶颈并执行有针对性的优化。在实践中，识别性能瓶颈是非常困难的。大多数应用程序没有部署APM等工具，因此您可以跟踪应用程序请求跨链接所花费的时间。大多数场景中的性能优化工作都是在没有全局时间分布的情况下进行的。推荐基于时间的数据库性能优化作为可靠的性能优化方法。

数据库时间数据库时间是指数据库在单位时间内提供的服务时间。通过将数据库时间与应用程序的总用户响应时间进行比较，您可以确定数据库是否是应用程序系统中的瓶颈。对于应用系统来说，T内提供的总服务时间可以通过平均服务TPS乘以平均响应时间来计算。 T时间中数据库时间的算法有很多种。

平均TPS X 平均事务延迟X T 平均QPS X 平均延迟您可以执行优化。将数据库时间除以总用户响应时间。

当它接近0 时，数据库时间占总服务时间的比例非常小，表明瓶颈不在数据库内。如果接近1，说明整个应用系统的瓶颈在数据库。工程师通过减少数据库时间来进行性能优化，例如优化SQL执行计划、解决数据库中的热点冲突等。

真实案例背景此示例是与合作伙伴合作完成的一个项目，旨在共同优化银行在分布式数据库和国家ARM 服务器上的核心应用程序。系统硬件采用ARM服务器，每台服务器有16个Numas，每台机器有1个NVMe磁盘。该银行的核心应用负载是“读重”，查询语句占负载的66%。该应用程序涵盖四种混合交易。

TPS为1到30 当我们在合作伙伴的实验室跑这个结果时，业务的TPS只有1左右，远低于预期。

业务方报超时错误（协处理器任务因错过截止时间而终止）。这种情况通常是由次优执行计划引起的，例如缺少索引，需要全表扫描。从TiDB仪表板我们可以看到数据库QPS只有100左右，txn上的80和90延迟都超过一分钟。如果您查看Top SQL，您将看到Top SQL 正在运行。由于缺少索引，因此扫描了整个表。

第一个SQL优化示例解决了缺失索引的问题，第二个SQL优化示例解决了未使用索引的问题。业务系统使用OR条件，即使OR两端的过滤字段都有索引，默认也会进行全量扫描。在对执行计划建立索引之前，您必须手动开启索引合并功能（设置@@global.TiDB_enable_index_merge=on）。

对这两类慢SQL进行优化后，TPS提升至30以上。

TPS从30上升到320 接下来，我们检查集群拓扑以提高资源利用率。测试环境为6台ARM服务器，每台16个Numa，每个Numa为8C 16GB。现有拓扑部署3个TiDB+3个TiKV。 TiDB 绑定了Numa 0-4，所以全机能力没有得到充分利用。我们调整了这种组网方式，部署了36 TiDB + 6 TiKV。每个TiDB 绑定2 个Numas，每个TiKV 分配4 个Numas。更改组网方式后，TPS 提升至320。

在TPS 320压力下从320到600观察到的现象是数据库CPU占用率较低。每个TiDB 绑定两个Numas，有16 核CPU，但CPU 利用率为100% - 520。 %，使用大约1 到5 个逻辑CPU。同时应用服务器的CPU占用率低于10%。查询80 的延迟为3.84 毫秒。这是一个非常典型的情况，数据库的负载不是很高，应用服务器的CPU占用率看起来很小，但是整体的TPS并没有增加。你现在的硬件资源肯定是足够的，但是你不知道整个系统的瓶颈在哪里。根据上述用户响应时间与数据库时间的比例关系，应用系统每秒响应时间：应用TPS 300 次平均延迟1 秒=300 秒TiDB 数据库每秒时间：QPS 30000 次平均延迟1.3 ms=39 秒数据库时间只占用户响应时间的13%。 TiDB 有一个更直观的方法称为Connection Idle Duration，它记录应用程序连接发送SQL 的间隔。在此示例中，SQL 处理延迟的第80 个百分位为3.84 毫秒，事务内SQL 提交之间的间隔的第80 个百分位为25 毫秒。数据库花了近4ms处理完这条SQL后，必须等待25ms才能接收下一条SQL。所以很明显瓶颈不在数据库中。

在确认瓶颈不在数据库之后，我们对火焰图和整个网络进行了分析。在下面的帧图中可以看到，系统整体CPU的20%被一个名为finish_task_switch的进程消耗，用于进程切换和调度相关的系统调用，这导致了资源争用并序列化到系统的内核态，可以看到有是一个点。由于有16 个Numa，每个Numa 有8 个核心，总共128 个核心。当我使用mpstat -P ALL 5命令检查所有CPU的使用情况时，我发现了一个有趣的现象：所有网卡（soft）的——个软中断，全部针对第一个Numa（CPU 0 -7）被命中。由于业务本身网络流量较大，因此CPU 0~7的软中断处理（%soft）使用率为38%~94%。我还在第一个Numa 上运行TiDB、PD、HAProxy 等，所以我的用户的CPU (%usr) 从2% 到接近40%，并且第一个Numa 的CPU 完全被占用(%usr)。附近）。 （最多0）。其他Numa使用率只有55%左右。我与一位ARM 制造商机器上的工程师进行了交谈，并确认ARM 服务器默认使用第一个Numa 来处理网卡的软中断。 SQL 提交之间的时间过长是由网卡流量的处理瓶颈造成的。

整个系统框架图

mpstat -P ALL 5 命令的输出此外，在帧图中观察到，未绑定到核心的程序—— PD 和HAProxy 具有非常高的系统调用百分比，例如内存访问和内存锁定。在打开Numa 的系统上，CPU 访问内存的速度实际上是不同的。访问远程Numa 的内存延迟通常比访问本地Numa 内存高10 倍。此外，硬件制造商建议不要跨Numas 部署应用程序，因为ARM 服务器上的跨Numa 内存访问会增加延迟并显着影响程序执行性能。

PD-Server进程perf top命令的输出根据上面的分析，我们调整了网络方式。对于6台机器，1）第一个Numa被释放来处理网络软中断并且没有程序运行；2）所有程序必须与一个核心关联。每个TiDB 只有一个Numa 与之关联，其数据被TiDB 加倍，PD 和HAProxy 也进行核心绑定。进行此调整后，应用程序的TPS提高到600。连接空闲期的80-in-TXN 延迟从26 毫秒减少到5 毫秒。

从TPS 600到880，数据库最大连接数保持稳定在2000，并发连接数并没有随着应用数量的增加而增加。使用mysql连接HAProxy地址报错。 HAProxy 的默认限制为2,000 个单代理后台会话，可以通过将HAProxy 从多线程模式更改为多进程模式来解除该限制。更改后连接数增加到4400，TPS增加到880。

Load RunnerTPS 抖动解决方案TPS 880 会经历应用程序的高波动以及事务处理延迟的高波动。从仪表板中也可以看到相同的QPS 波动，以及P999 延迟的小峰值。是不是数据库导致了应用的性能变化呢？带着这个问题，我们修改了Promtheus的监控表达式，观察了P9999的延迟情况，发现变化很大，而且比P999更明显。Ta。您可以调整时间点和走鹃数据。如果查看TiKV-Detail 监控，发现TiKV 实例已重启，并且系统信息确认TiKV OOM（内存不足）。 OOM 的原因是扩容后留下了3 个TiKV 实例，仅成为TombStones，并未清除，而现有的TiKV 实例则OOM。

期间P9999

Grafana TiKV-Detail 面板中观察到OOM 重启

TiKV.log 日志显示了OOMSQL 执行计划的稳定性。统计数据永远不准确。在一次特定的压力测试中，应用程序的TPS下降到0。从TiDB 仪表板中，我们找到了最上面的SQL。发现此SQL 执行计划发生更改，并显示两个执行计划。 MQ_PRODUCER_MSG 是消息队列表。该查询包含两个过滤条件：flow_id和status。 flow_id 和status 都有单列索引。典型的执行计划使用上述flow_id 索引，平均执行时间为62 ms。问题发生时，优化器选择了状态列索引，执行时间为38秒。

在错误的执行计划中，如果条件status=1，优化器会假设该行为0，因此选择status列上的索引。当我尝试重现并在status=1的条件下进行Explain Analysis时，估计值超过了40,000，并且没有出现估计值为0的情况。

然后我们分析了慢日志，发现有63 个TiDB 实例有这个错误的执行计划，总共有94 个连接执行了错误的执行计划。换句话说，这个不正确的执行计划是通过每个TiDB 实例的一到两个连接来执行的。选择实例并从information_schema.cluster_slow_query 运行count(*)。这里像“%MQ_STATUS_INDEX%”这样的索引名称是按实例分组的。

select conn_id,instance, count(*) from information_schema.cluster_slow_query 其中index_names是'%MQ_STATUS_INDEX%'并且digest=像'cca85ee01e54b3b37775c8b07c2808f306177d28fd0376b2d8c5dd5663f488ec'这样的实例，conn按_分组。 ID;

根据以上分析，我们认为这个错误的估计与TiDB 异步加载统计数据的行为有关。统计延迟加载具有有关列（例如直方图/cm_sketch）和后台任务的详细统计信息，这些统计信息在第一次使用之前不会异步加载。为了验证，我重新启动了TiDB 实例，并对status=1 进行了解释分析，但无法重现将status=1 假设为0 的情况。

您可以通过检查stats_histograms.update_time 中的最后一次统计更新时间来验证表的统计信息是否在执行加载之前自动更新（注意：stats_meta.update_time 并不代表最后一次统计更新时间）。然而，统计数据仍然不准确。这是为什么呢？一次偶然的机会，我发现status=1只在load执行时存在。执行加载后，表中没有status=1的数据。因此，如果自动统计的话，最后一次加载已经完成，所以status=1的数据已经处理完毕，表中没有status=1的数据，所以status=1的值估计会为0 。状态列只有一个唯一值（NDV，不同值的数量）。根据正确的统计，NDV 为2。

左边统计信息不正确，右边统计信息正确由于业务中消息中间表的数据经常变化，统计信息是否具有代表性取决于获取统计信息时数据的状态，会更新。针对这种情况，TiDB 优化器必须支持手动锁定统计信息，以防止自动化分析任务在错误的时间收集异常统计信息。当前版本中，必须通过SQL绑定手动绑定执行计划，以保证选择正确的执行计划。 TPS 880-1200+ 数据库优化后，应用的TPS与应用JVM的数量成正比。最后，我们使用具有16 个Numas 的ARM 服务器部署了15 个应用程序，每个应用程序jvm 绑定到Numas 并连接到TiDB 集群。应用程序的最佳并发数约为1200，应用程序的最大TPS约为1250。应用服务器和数据库服务器CPU资源利用率约为70%。

优化概述从这个例子中我们学到了什么？首先，ARM服务器上的一切都与Numa绑定，包括所有应用jvm、Haproxy、TiDB(PD、TiDB、TiKV)的组件。其次，性能优化的一个核心问题是时间都去哪儿了？挑战在于瓶颈可以在任何地方，但是如何观察和识别它们？这种情况下，可以比较用户响应时间和数据库时间来确定瓶颈是否在数据库中。我已确定是否在数据库内部或在数据库之外。我们也直接使用TiDB 指标Connection Idle Duration（数据库连接发送SQL 间隔）来判断。第三，在本例中，我们大量使用TiDB 仪表板、grafana 等内置监控来进行SQL 优化和关键指标分析。我们还使用frame graph、mpstat等系统工具来观察CPU、网络、资源等资源。 I.O.

TiDB 性能和稳定性挑战银行核心事务应用读取负载较高，事务包含数百个小查询，如何保持高性能和稳定性是一大挑战。跟踪TiDB 实例时，对于相同的SQL 执行，单行组合表查询的延迟范围为1.5 毫秒到15 毫秒。大多数执行的时间大约为2.5 毫秒，但最大延迟为15 毫秒。分析最大15ms延迟信息可以看出，在SQL执行过程中，需要频繁切换goroutines来执行gc mark asist等操作，影响了SQL的处理延迟。

分析TiDB 的火焰图，CompilePreparedStatements 占用了TiDB CPU 的18%，按照alloc_objects 排序，大约36% 的TiDB 内存申请操作来自CompilePreparedStatements 中的planner.Oplimzer。当启用执行计划缓存（Prepared Plan Cache）时，为什么优化器会解析准备语句并生成执行计划，消耗大量内存和CPU？

Grafana监控显示，Prepared Plan缓存的命中率为72.7%，有27.3%的Prepared语句没有命中计划缓存中的SQL，通过重复分析确认了执行计划的生成。本次测试使用的是v5.1.1版本，所以prepared-plan-cache仍是一个实验性功能，部分SQL语句尚不支持缓存执行计划。

计划缓存的查询OPS=33.3kStmtExecute=45.8k 准备计划缓存命中率=33.3/46.8=72.7% 在最近的新版本v5.3.0 中，准备计划缓存功能现已正式GA，执行计划丢失的问题已得到解决。之前的一些语句无法被缓存，这样就消除了重复解析SQL和生成执行计划带来的CPU和内存消耗。与在Oracle 上运行的任何OLTP 应用程序一样，使用绑定变量和软解析可以显着提高性能。借助预备计划缓存功能的GA，TiDB 在银行核心负载下实现了显着的性能和稳定性。此外，使用预准备语句接口的应用程序还可以有效防止SQL注入攻击，提高系统整体安全性。