MySQL 服务器负载非常高

Question

我经营的网站拥有~500 名实时访问者、~5 万名每日访问者 和~130 万总用户。我在 AWS 上托管我的服务器，我在其中使用了多个不同类型的实例。当我启动该网站时，不同实例的成本大致相同。当网站开始获取用户时，RDS实例(MySQL DB)CPU不断飙升，不得不多次升级，现在已经开始占据性能和月成本的主要部分(95%左右) (2,8k $/月))。我目前使用具有 16vCPU 和 64GiB RAM 的数据库服务器，我还使用多可用区部署来防止故障。 我想知道数据库这么贵是正常的，还是我做错了什么？

数据库信息

目前我的数据库有 40 个表，其中大部分有 10 万行，一些有 200 万行，1 个有 3000 万行。我有一个系统，当不再需要它们时，它们会超过 21 天。

网站信息

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

网站的大部分功能都是这样运行的:

1. 开始交易
2. 插入行
3. 获取最后插入的id (lastrowid)
4. 做一些计算
5. 更新了插入的行
6. 更新用户
7. 提交交易

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

额外

我做了几件事来尝试降低数据库的负载。例如启用数据库缓存，对某些查询使用 redis 缓存，尝试删除非常慢的查询，尝试将存储类型升级为“Provisioned IOPS SSD”。但似乎没有任何帮助。

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

我考虑过创建一个包含几个较小实例的 MySQL 集群，但我不知道这是否有帮助，而且我也不知道这是否适用于事务。

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

Answer 1

根据我的经验，一旦您提出“如何提高性能？”这个问题你知道你已经超出了 RDS 的范围(编辑:我承认我的经验让我得出这个观点可能已经过时了)。

听起来您的查询负载非常重。大量的插入和更新。如果可以的话，您应该增加 innodb_log_file_size 在您的 RDS 版本上。否则，您可能不得不放弃 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 下载 RDS 慢查询日志CLI 命令。

设置你的long_query_time=0，让它运行一段时间收集信息，然后把long_query_time改回你平时使用的值。收集此日志中的所有查询很重要，因为您可能会发现 75% 的负载来自 2 秒以内的查询，但它们运行得太频繁以至于给服务器造成了负担。

在您知道哪些查询占了负载之后，您可以针对如何解决这些问题制定一些明智的策略:

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