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

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, specially select statement, is performing very slow. 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.

我似乎，我的所有查询，特别是选择语句，执行速度非常慢。如果我在我的本地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, buffer etc). I don't know if I am missing something, but it didn't worked for me - performance didn't increase.

我已经正确调整了所有表索引，并根据需要进行了规范化/反规范化。我已经在RDS自定义参数组上进行了所有必要的设置（例如，max_connection，buffer等）。我不知道我是否遗漏了一些东西，但它对我没有用 - 性能没有增加。

So, can someone please help me with this issue?

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

7 个解决方案

#1

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.

值得注意的是，无论出于何种原因，MySQL查询缓存在RDS中默认为OFF。我们本周了解到了自己的困难。

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

这不会有助于初始查询的执行，但它可能会加快速度。

To re-enable query cache:

要重新启用查询缓存：

Log in to the RDS Console
登录RDS控制台
Click on your RDS instance to view it's details
单击您的RDS实例以查看其详细信息
Edit the Database Parameter Group
编辑数据库参数组
Be sure to set both query_cache_size and query_cache_type
请务必同时设置query_cache_size和query_cache_type

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

（免责声明：我不是DBA所以我可能还有其他的东西在这里丢失）

#2

It is important to have your RDS and EC2 instances not in the same region but 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无法使用。

#3

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

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

P1) Use Larger instance types, they come with better NW bandwidth. Example AWS Quadrapule EXL comes with 1000Mbps bandwith.

P1）使用较大的实例类型，它们具有更好的NW带宽。示例AWS Quadrapule EXL带有1000Mbps带宽。

P2) Use PIOPS storage you can extract 12500 IOPS of 16KB from MySQL DB

P2）使用PIOPS存储，您可以从MySQL DB中提取12500 IOPS的16KB

P3) If lots of read is performed, add one or more Read Replica's to increase read performance

P3）如果执行大量读取，请添加一个或多个只读副本以提高读取性能

P4) Apply standard practices like : Tune the queries, apply the indexes etc

P4）应用以下标准做法：调整查询，应用索引等

#4

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

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

#5

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

您可以通过使用分析来查看查询占用时间的位置。使用以下查询：

set profiling=1
设置分析= 1
execute your select query
执行您的选择查询
show profile
显示个人资料

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.

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

#6

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 then where needed.

对我来说，它与MySQL无关，而是我在t2.medium上的实例类型。问题是我没有CPU积分，因为数据库上的负载太高而且余额一直持续下降，直到最后，我在每小时得到的信用额少得多。

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

以下是我在CPU信用使用情况下在RDS CloudWatch中看到的内容：

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/

Hope this helps.

希望这可以帮助。

#7

-1

I had the exact same issue. I think the problem is that MariaDB and MySQL both default to InnoDB instead of MyISAM! (Can't stand anything about InnoDB). Initial testing shows a 15x improvement for my queries.

我有完全相同的问题。我认为问题是MariaDB和MySQL都默认使用InnoDB而不是MyISAM！（对InnoDB无能为力）。初步测试显示我的查询提高了15倍。

#1