RDS MySQL Insights：热门查询“提交”

Question

我们最终能够解决这个“提交”负载问题。

关于我们的系统的一些事实：

我们的 SQL 查询中大约 95% 是读取
我们的写入查询中大约有 5% 是写入
我们的大多数查询都是通过自动提交完成的，而不是通过显式事务完成的

经过进一步调查，我们发现：

性能洞察中的“提交”负载来自将查询写入磁盘上的 binlog 所需的时间
由于我们的大多数写入查询都是使用自动提交执行的，因此所有这些查询都会执行提交，从而触发一些 binlog 刷新
读取查询不会增加此“提交”负载，很可能是因为它们不需要写入 binlog
因此，执行写入的 5% 查询造成了大部分数据库负载！

对于我们来说，最有效的解决方法是将写入操作批量处理到单个事务中。我们执行的许多写入操作不需要立即写入数据库，丢失其中一些写入操作也无妨（我们的大多数写入操作都与统计或跟踪有关）。因此，我们当前的解决方案是将统计/跟踪写入操作存储到缓存中，并在单个事务中偶尔将其刷新到数据库。这减少了提交次数，从而有效降低了负载。

此解决方案可能不适用于所有人，或者可能需要进行一些调整。据我了解，有一些替代解决方案，例如：

对数据库进行分片，并将导致大量写入负载的表移动到另一个实例
减少写入查询的数量
使用 binlog 刷新设置。默认情况下，它会在每次提交后将 binlog 刷新到磁盘，但可以进行不同的配置。但是，如果数据库崩溃，这可能会带来一些可恢复性问题！
使用更快的磁盘

我也在下面回答了我原来的问题。

希望这对其他人有帮助。我发现网上与此相关的信息很少。

首先，我们启用了自动提交。此“COMMIT”语句是否包含来自自动提交配置的自动 COMMIT 语句，还是仅在我们的应用程序执行事务时才独有显式 COMMIT 语句？

此“COMMIT”语句包括使用自动提交执行的查询。

此外，在我们的上下文中，没有任何读取查询包含在此“COMMIT”语句中，很可能是因为读取查询后不需要将 binlog 刷新到磁盘。

我检查了更多，似乎这个“init”状态与数据库将 binlog 刷新到磁盘有关（https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html)。这可能是有道理的，因为我们的主服务器上有 sync_binlog=1。但这个猜测正确吗，还是可能涉及更多内容？

对于我们来说，只有 binlog 刷新才会在此初始化状态下造成高负载。

如果是这种情况，我读过有关调整 sync_binlog（例如 sync_binlog=0 或 sync_binlog>1）的信息，但如果发生硬件故障，风险会更大（https://aws.amazon.com/blogs/database/best-practices-for-configuring-parameters-for-amazon-rds-for-mysql-part-2-parameters-related-to-replication/)。对于这部分，我不清楚在扩展具有一个主服务器和一些只读副本的 RDS 时什么是一个好的做法：在主服务器上触及此设置是一种标准做法，还是我们应该更多地关注减少从应用程序执行的事务量？

标准做法是保留sync_binlog=1主服务器以避免崩溃后出现可恢复性问题，特别是如果您配置了只读副本。

还有其他方法可以解决这个问题，如我上面回答中所列。

运行 SHOW FULL PROCESSLIST 时，我能够看到这些“COMMIT”语句停留在初始化状态。但是，我想知道是否有办法查看与该 COMMIT 语句相关的查询。否则，似乎我们必须猜测哪个事务是根本原因。

正如这篇博客文章中所述，这是可行的：https://www.psce.com/en/blog/2015/01/22/tracking-mysql-query-history-in-long-running-transactions/

但是，它需要启用 events_statements_history。据我所知，在 RDS 上此功能已禁用，并且不允许启用它。

Answer 1