Amazon RDS MySQL 实例执行速度非常慢
我已在 Amazon EC2(新加坡地区)上发布了我的网站，并且我已使用 MySQL RDS 实例进行数据存储.除了性能之外，一切都很好.
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.
我的所有查询，尤其是 select 语句，似乎执行得很慢.如果我在本地 PC 上检查此问题，则它运行良好.但是当我尝试从 RDS 实例获取数据时，速度非常慢.一些 select 语句需要 2-3 秒才能获取数据.
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.
我已经正确调整了所有表索引，并根据需要进行了规范化/反规范化.我已经对 RDS 自定义参数组(例如 max_connection、buffer 等)进行了所有必要的设置.我不知道我是否遗漏了什么，但它对我不起作用 - 性能没有提高.
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 work for me - performance didn't increase.
那么，有人可以帮我解决这个问题吗?
So, can someone please help me with this issue?
推荐答案
值得注意的是，无论出于何种原因，MySQL 查询缓存在 RDS 中默认是关闭的.本周我们自己也艰难地了解到了这一点.
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.
这无助于初始查询的性能，但总体上可能会加快速度.
This won't help performance of your initial query, but it may speed things up in general.
重新启用查询缓存:
- 登录 RDS 控制台
- 点击您的 RDS 实例以查看其详细信息
- 编辑数据库参数组
- 一定要同时设置
query_cache_size和query_cache_type
(免责声明:我不是 DBA，所以这里可能还缺少其他东西)
(Disclaimer: I am not a DBA so there may be additional things I'm missing here)
相关文章