![RDS MySQL Insights:热门查询“提交”](https://linux22.com/image/756061/RDS%20MySQL%20Insights%EF%BC%9A%E7%83%AD%E9%97%A8%E6%9F%A5%E8%AF%A2%E2%80%9C%E6%8F%90%E4%BA%A4%E2%80%9D.png)
我们正在尝试通过性能洞察来减少 AWS RDS 实例的负载。
洞察中列出的顶级 SQL 语句是“提交”:
具体来说,提交语句大部分时间处于“初始化”状态:
当按状态查看总体负载时,此“初始”状态确实构成了负载的很大一部分:
所以,现在,我们不太确定从这些信息中可以得出什么结论。
- 首先,我们启用了自动提交。此“COMMIT”语句是否包含来自自动提交配置的自动 COMMIT 语句,还是仅在我们的应用程序执行事务时才独有显式 COMMIT 语句?
- 我检查了更多,似乎这个“init”状态与数据库将 binlog 刷新到磁盘有关(https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html)。这可能说得通,因为我们有
sync_binlog=1
master。但这个猜测正确吗,或者可能涉及更多内容? - 如果是这种情况,我读过有关调整 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 时什么是一个好的做法:在主服务器上触及此设置是一种标准做法,还是我们应该更多地关注减少从应用程序执行的事务量?
- 在运行时,我能够看到这些“COMMIT”语句停留在初始化状态
SHOW FULL PROCESSLIST
。但是,我想知道是否有办法查看与该 COMMIT 语句相关的查询是什么。否则,似乎我们必须猜测哪个事务是根本原因。
答案1
我们最终能够解决这个“提交”负载问题。
关于我们的系统的一些事实:
- 我们的 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 上此功能已禁用,并且不允许启用它。