MySQL是在EC2上极其缓慢 [英] MySQL is extremely slow on EC2

问题描述

我还发现，MySQL在EC2（Ubuntu的12.10）可能是极其缓慢。 它只需700毫秒的一组特定的SQL查询到本地PC（Windows 7中）上执行，而在EC2它需要超过13sec。

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

我试过的m1.small和M2.xlarge（其中有7次的计算能力比的m1.small） - 的结果是一样的：查询需要几乎同时在两台服务器上，无一不是极其缓慢的。

为什么会发生这种事？

解决方案

问题是与MySQL 5.5，执行效率不高的子查询。 我家电脑运行MySQL 5.6，远远好于这方面。 所以，我在EC2上的MySQL升级到5.6版本，并成为几乎一样快，我的家用电脑（尽可能只有一个同时查询而言）。

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.

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.

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?

解决方案

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屋！