Amazon RDS MySQL 实例执行速度非常慢

Amazon RDS MySQL instance performs very slow

提问by Manish Sapkal

I have published my website on Amazon EC2 (Singapore region) and I have used MySQL RDS instance for the data storage. Everything is working very fine except performance.

我已经在 Amazon EC2（新加坡地区）上发布了我的网站，并且我已经使用 MySQL RDS 实例进行数据存储。除了性能之外，一切都很好。

I seems that, my all queries, especially the select statement, is performing very slowly. If I check this issue on my local PC, there it is working very well. But when I am trying to get data from RDS instance, it is very slow. Some of the select statements takes 2-3 seconds to fetch data.

我似乎，我所有的查询，尤其是 select 语句，执行得非常缓慢。如果我在本地 PC 上检查此问题，则它运行良好。但是当我尝试从 RDS 实例获取数据时，速度非常慢。一些 select 语句需要 2-3 秒来获取数据。

I have properly tuned up all table indexes, and normalized/de-normalized as required. I have made all necessary settings on RDS custom parameter group (eg. max_connection, bufferetc). I don't know if I am missing something, but it didn't work for me - performance didn't increase.

我已经正确调整了所有表索引，并根据需要进行了规范化/反规范化。我已经对 RDS 自定义参数组（例如max_connection、缓冲区等）进行了所有必要的设置。我不知道我是否遗漏了什么，但它对我不起作用 - 性能没有提高。

So, can someone please help me with this issue?

那么，有人可以帮我解决这个问题吗？

回答by DOOManiac

It is worth noting that, for whatever reason, MySQL query cache is OFF by default in RDS. We learned that the hard way ourselves this week.

值得注意的是，无论出于何种原因，RDS 中默认情况下 MySQL 查询缓存是关闭的。本周我们自己也艰难地了解到了这一点。

This won't help performance of your initial query, but it may speed things up in general.

这对您的初始查询的性能没有帮助，但总体上可能会加快速度。

To re-enable query cache:

要重新启用查询缓存：

1. Log in to the RDS Console
2. Click on your RDS instance to view it's details
3. Edit the Database Parameter Group
4. Be sure to set both query_cache_sizeand query_cache_type

1. 登录RDS控制台
2. 单击您的 RDS 实例以查看其详细信息
3. 编辑数据库参数组
4. 一定要同时设置query_cache_size和query_cache_type

(Disclaimer: I am not a DBA so there may be additional things I'm missing here)

（免责声明：我不是 DBA，所以我在这里可能还缺少其他东西）

回答by Roovian

It is important to have your RDS and EC2 instances not just in the same region but also in the same availability zone to minimize the latency.

重要的是让您的 RDS 和 EC2 实例不仅位于同一区域，而且位于同一可用区，以最大程度地减少延迟。

I had an API hosted in Ireland on EC2 and moved the Database to a MySQL cluster in Virginia USA that we had set up for another project and the round trip on every SQL query made the API unusable.

我在爱尔兰的 EC2 上托管了一个 API，并将数据库移至美国弗吉尼亚州的 MySQL 集群，我们为另一个项目设置了该集群，每次 SQL 查询的往返都使 API 无法使用。

回答by radtek

For me, it was nothing to do with MySQL but rather the instance type I was on t2.medium. The problem is I ran out of CPU credits because the load on the DB was too high and the balance kept going down until finally, I was getting far fewer credits hourly than were needed.

对我来说，这与 MySQL 无关，而是与我所在的实例类型有关t2.medium。问题是我用完了 CPU 积分，因为数据库上的负载太高，余额一直在下降，直到最后，我每小时获得的积分远远少于需要的积分。

Here is what I saw in RDS CloudWatch under CPU Credit Usage:

这是我在 RDS CloudWatch 的 CPU Credit Usage 下看到的：

If you have the same problem it may be time to switch to a different instance. Here is the list of instance types:

如果您遇到同样的问题，可能是时候切换到不同的实例了。以下是实例类型列表：

https://aws.amazon.com/rds/instance-types/

https://aws.amazon.com/rds/instance-types/

Hope this helps.

希望这可以帮助。

回答by Harish Ganesan

RDS MySQL performance can be increased in following ways assuming the system has more read ratio:

假设系统具有更高的读取率，可以通过以下方式提高 RDS MySQL 性能：

1. Use Larger instance types, they come with better NW bandwidth. Example AWS Quadruple EXL comes with 1,000 Mbps bandwidth.
2. Use PIOPS storage you can extract 12,500 IOPS of 16KB from MySQL DB
3. If lots of read is performed, add one or more Read Replica's to increase read performance
4. Apply standard practices like: Tune the queries, apply the indexes etc

1. 使用更大的实例类型，它们具有更好的 NW 带宽。示例 AWS Quadruple EXL 具有 1,000 Mbps 带宽。
2. 使用 PIOPS 存储您可以从 MySQL DB 中提取 16KB 的 12,500 IOPS
3. 如果执行大量读取，请添加一个或多个只读副本以提高读取性能
4. 应用标准实践，例如：调整查询、应用索引等

回答by Hiyasat

First i highly recommend to look over these queries using

首先，我强烈建议使用以下方法查看这些查询

SHOW FULL PROCESSLIST

显示完整的进程列表

You can read more about it on SHOW FULL PROCESSLIST

您可以在SHOW FULL PROCESSLIST上阅读更多相关信息

This will show you the time each query take.

这将显示每个查询所需的时间。

Then you can use

然后你可以使用

EXPLAIN

解释

You can read more about it on EXPLAIN

您可以在EXPLAIN上阅读更多相关信息

This will show you if you need some enhancement on your queries

这将显示您是否需要对查询进行一些改进

回答by Hiyasat

You can check where the query is taking time by making use of profiling. Use the below query:

您可以通过使用分析来检查查询在哪里花费时间。使用以下查询：

1. set profiling=1
2. execute your select query
3. show profile

1. 设置分析=1
2. 执行您的选择查询
3. 显示个人资料

This will tell you about the status of the query and where the query is spending its time. If the sum of all the time returned by the profiling is less than the actual execution time of the query, then maybe other factors like Network bandwidth may be the cause of it.

这将告诉您查询的状态以及查询花费的时间。如果分析返回的所有时间总和小于查询的实际执行时间，那么可能是网络带宽等其他因素造成的。