MySQL 在 EC2 上非常慢 [英] MySQL is extremely slow on EC2

问题描述

我发现 EC2 (Ubuntu 12.10) 上的 MySQL 可能非常慢.在我的本地 PC(Windows 7)上执行一组 SQL 查询只需 700 毫秒，而在 EC2 上则需要 13 秒以上.

I have found out that MySQL on EC2 (Ubuntu 12.10) could be extremely slow. It takes just 700ms for a certain set of SQL queries to perform on my local PC (Windows 7), whereas on EC2 it requires more than 13sec.

数据库非常小，只有 12MB.查询过程中几乎没有磁盘IO.然而，EC2 实例慢了 20 倍.所有数据库都基于相同的转储:相同的表和相同的索引.查询返回相同的结果.唯一的区别是执行时间.

The database is very small, just 12MB. There is almost no disk IO during the query. Nevertheless, EC2 instance is 20 times slower. All the databases are based on the same dump: same tables and same indexes. The queries return the same results. The only difference is the execution time.

我尝试了 M1.small 和 M2.xlarge(其计算能力是 M1.small 的 7 倍) - 结果是一样的:查询在两台服务器上花费的时间几乎相同，而且都非常慢.

I tried M1.small and M2.xlarge (which has 7 times more computing power than M1.small) - the outcome is the same: queries take almost the same time on both servers and both are extremely slow.

为什么会发生这种情况?

Why could this happen?

推荐答案

问题在于 MySQL 5.5 执行子查询效率低下.我的家用电脑运行 MySQL 5.6，在这方面要好得多.所以我将 EC2 上的 MySQL 升级到 5.6 版，它几乎和我的家用 PC 一样快(就只有一个同时查询而言).

The problem was with MySQL 5.5 which executes subqueries inefficiently. My home PC run MySQL 5.6 which is far better in this regard. So I upgraded MySQL on EC2 to version 5.6 and it became almost as fast as my home PC (as far as only one simultaneous query is concerned).

这篇关于MySQL 在 EC2 上非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！