how to fix slow query of mysql on Amazon and CPU 100% on the small rds instance

Question

I am quite new to the both MySQL and AWS RDS service. I just find my query to the MySQL on a small RDS instance takes roughly more than 5 seconds. Besides, I notice with the CloudWatch that the CPU of the rds is always 100% consumed. The size of my database is around 1 million now. Can any tell me what I can do to fix this?

One query is like this:

mysql> EXPLAIN SELECT * FROM iApps;
+----+-------------+--------------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows    | Extra |
+----+-------------+--------------+------+---------------+------+---------+------+---------+-------+
|  1 | SIMPLE      | iApps | ALL  | NULL          | NULL | NULL    | NULL | 1165255 |       |
+----+-------------+--------------+------+---------------+------+---------+------+---------+-------+
1 row in set (0.04 sec)

Here is another query:

mysql> EXPLAIN SELECT * FROM iApps WHERE familyName='Thompson' AND firstName='David';
+----+-------------+--------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | iApps | ALL  | NULL          | NULL | NULL    | NULL | 1166070 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.08 sec)

The time here is quite small, but it's quite slow with my code. I use python package mysqldb for all the operations, could that be the problem?

I did search online for this issue. But I don't really get the solutions from the pages. I'm entry level for the database.

"1m" means what? 1 megabyte? 1 million rows? What happens if you `EXPLAIN` the query? — ceejayoz, Feb 03 '13 at 01:16
@ceejayoz sorry about that, it's 1 million. I mean writing and reading for the query. — David Thompson, Feb 03 '13 at 02:06
first you need to post the query and then put explain in front of the query and post that — Mike, Feb 03 '13 at 02:06
@MichaelHampton what do you mean by indexes? Sorry, I am really a newbie to the database — David Thompson, Feb 03 '13 at 02:10
@Mike I post one query with EXPLAIN here, the time seems quite small, does it mean the server is ok? — David Thompson, Feb 03 '13 at 02:35
You're really doing queries that select all 1.1 million rows? Of course it's going to be slow. — Michael Hampton, Feb 03 '13 at 02:42
@MichaelHampton I have added another query, which is executed in my python code. — David Thompson, Feb 03 '13 at 03:44
this is really off topic.. its not a server problem.. you just don't know sql at all. — Mike, Feb 03 '13 at 03:45
@Mike Sorry, the first one is just an example I could come up then. The second one is what I really use in my code — David Thompson, Feb 03 '13 at 03:45
@Mike I don't really know anything about mysql, could you please point out any good resource for me to learn sql? Thanks a lot. — David Thompson, Feb 03 '13 at 03:47

RolandoMySQLDBA · Accepted Answer · 2013-02-03T04:27:30.493

3

The second query's EXPLAIN plan give it away. You need an index.

The clause WHERE familyName='Thompson' AND firstName='David' is the clue.

Simply run the following:

ALTER TABLE iApps ADD INDEX familyName_firstName_ndx (familyName,firstName);

I can assure you this will speed up this particular query. Other queries that have different WHERE clauses will require different indexes.

As for the CPU spike, that should not be a surprise. A full table scan on a 1 million row table will push data in and out of the InnoDB Buffer Pool. Keep in mind that the Transaction Logs in Amazon RDS are always the same size (innodb_log_file_size = 128M) for all seven RDS models. Transacitons that are passing through the same rows you are selecting would also have bearing on CPU usage as it manipulate the Transaction Logs as well.

edited Feb 03 '13 at 04:27

answered Feb 03 '13 at 04:04

RolandoMySQLDBA

16,544
3
48
84

Thanks a lot for answering. Can you also explain why the cpu of RDS instance is 100% consumed? – David Thompson Feb 03 '13 at 04:14
1

Because every time you look for a single row, it's having to look at all one million rows. If you had indexes on the right columns, it'd only have to look at a few dozen or hundred. – ceejayoz Feb 03 '13 at 13:10
@ceejayoz thanks for explaining this with plain English. – David Thompson Feb 03 '13 at 16:07

how to fix slow query of mysql on Amazon and CPU 100% on the small rds instance

1 Answers1