MySQL服务器负载很高

Question

我运营一个网站，有约500个实时访问者、约50,000个每日访问者和约1.3百万总用户数。我在AWS上托管我的服务器，在那里我使用了几个不同种类的实例。当我开始运营网站时，不同实例的成本大致相同。当网站开始获得用户时，RDS实例（MySQL DB）的CPU经常达到峰值，我不得不多次升级它，现在它已经开始占据主要性能和月度成本的大部分（约95%的（2,8k$/month））。我目前使用一个具有16vCPU和64GiB RAM的数据库服务器，我还使用Multi-AZ Deployment来防止故障。我想知道数据库是否如此昂贵，或者我做错了什么？

数据库信息

目前我的数据库有40个表，其中大部分表格拥有100k行数据，有些则有约200万行数据，而只有1个表格拥有3000万行数据。我有一个系统，用于归档那些不再需要的超过21天的旧数据行。

网站信息

网站主要使用PHP，但也使用了一些NodeJS和python。

网站的大部分功能工作方式如下：

1. 开始事务
2. 插入行
3. 获取最后插入的ID（lastrowid）
4. 进行某些计算
5. 更新插入的行
6. 更新用户
7. 提交事务

我还运行了大约100个机器人，它们以10-30秒的间隔从数据库中轮询，并且有时也会插入/更新数据库。

其他

我已尝试多种方法来降低数据库的负载，例如启用数据库缓存、为某些查询使用redis缓存、尝试删除非常慢的查询、尝试升级存储类型为“配置型IOPS SSD”。但是没有任何改变似乎能够帮助解决问题。

这是我对设置参数所做的更改：

我曾考虑创建由几个较小实例组成的MySQL集群，但我不知道这是否有帮助，也不知道这是否与事务良好配合。

如果您需要更多信息，请询问，非常感谢对此问题的任何帮助！

- Ranama

---

你说过你尝试升级到预配置的IOPS，但是你已经达到了可用的IOPS上限了吗？你现在还在达到可用的IOPS上限吗？你需要查看你的“每秒读操作”和“每秒写操作”，并查看它们是否受到可用IOPS的限制。 - Mark B

此外，我建议测试Aurora以查看它是否对您的性能表现更好。 - Mark B

Answer 1

根据我的经验，一旦你问“如何提高性能”，就知道你已经超出了RDS的范围（编辑：我承认我的经验可能已经过时）。听起来你的查询负载相当重要。大量插入和更新。如果您可以在您的RDS版本上增加innodb_log_file_size，那么您应该这样做。否则，您可能必须放弃RDS并转移到EC2实例，在那里可以更轻松地调整MySQL。

我还会禁用MySQL查询缓存。每次插入/更新时，MySQL都必须扫描查询缓存以查看是否需要清除缓存的任何结果。如果您具有写入重负载，则这是浪费时间的。将查询缓存增加到2.56GB甚至更糟！将缓存大小设置为0，并将缓存类型设置为0。

我不知道您运行什么查询或您如何优化它们。 MySQL的优化器受限，因此通常情况下可以通过重新设计SQL查询来获得巨大的好处。即更改查询语法以及添加正确的索引。

您应该进行查询审核，以找出哪些查询导致负载高。一个很棒的免费工具是https://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html，它可以根据您的慢查询日志提供报告。使用http://docs.aws.amazon.com/cli/latest/reference/rds/download-db-log-file-portion.html CLI命令下载RDS慢查询日志。

将long_query_time设置为0，让其运行一段时间以收集信息，然后将long_query_time更改回通常使用的值。在此日志中收集所有查询很重要，因为您可能会发现75％的负载来自少于2秒的查询，但它们频繁运行，对服务器构成负担。

在了解哪些查询导致负载之后，您可以制定一些有关如何处理它们的明智策略：

• 查询优化或重新设计
• 在应用程序中进行更多缓存
• 扩展到更多实例

- Bill Karwin

---

我很好奇你为什么认为他们已经“超出了RDS”。似乎在仍然使用RDS的情况下，您建议的所有调整都可以完成。 - Mark B

我的看法可能已经过时了。多年来，RDS 无法调整 InnoDB 日志文件大小，或提供除基于表的日志之外的查询日志。这似乎已经改变了。但我猜我对于不能登录的数据库服务器有偏见！ :-) - Bill Karwin

Answer 2

我认为答案是“你做错了什么”。非常不可能达到RDS的限制，尽管您可能会在某些部分达到限制。

首先启用详细监视。这将为您提供一些操作系统级别的信息，可以帮助确定您真正的限制因素。查看您的慢查询日志和数据库统计数据 - 您可能有一些导致问题的查询。

一旦您了解了问题 - 可能是糟糕的查询、I/O 限制或其他问题 - 然后您就可以解决这些问题。 RDS允许您创建多个读副本，因此您可以将一些读负载移至从库。

您还可以转向Aurora，这应该会提供更好的I/O性能。或者使用PIOPS（或分配更多磁盘，这应该增加性能）。您正在使用SSD存储，对吗？

另一个建议 - 如果您的计算（步骤4以上）需要很长时间，您可能需要考虑将其分成两个或多个事务。

- chris

Answer 3

一个超过50M的query_cache_size是个坏消息。你经常写入 -- 每秒每个表多次？这意味着需要扫描QC很多次/秒来清除更改的表的条目。当QC为2.5GB时，这对系统造成了很大的负荷！

如果您可以证明有使用它的理由，query_cache_type应该是DEMAND。在这种情况下，在SELECTs中使用SQL_CACHE和SQL_NO_CACHE。

由于您已经打开了慢查询日志，请使用pt-query-digest查看输出。前几个查询是什么？

由于您的典型操作涉及写入，我不认为使用只读从库有任何优势。

机器人是在随机时间运行吗？还是他们都在同一时间开始？（后者可能会导致CPU等方面的可怕峰值）

您如何“存档”“旧”记录？最好使用PARTITIONing和“可传输表空间”。使用PARTITION BY RANGE和21个分区（加上几个额外的）。

您的典型事务似乎只处理一行。它可以修改为一次处理10或100行吗？（超过100可能没有成本效益。）与每次查询一个行相比，SQL在一次处理多行时更有效率。向我们展示SQL，我们可以深入了解细节。

插入新行，然后在一个事务中更新它似乎很奇怪。您不能在执行插入之前完全计算它吗？长时间保持插入的ID可能会干扰其他人做同样的事情。 innodb_autoinc_lock_mode的值是多少？

“用户”是否相互交互？如果是，以什么方式？

- Rick James