CODEWITHSUNDEEP
mysqlruby-on-railsamazon-s3
joe
joe

Reputation: 237

Mysql hosted in Amazon aws is sometimes very slow

We have a database, and tables contains more than 2 million records. Database is hosted in Amazon aws server.

Sometimes the "select" query execution time is very very slow. What could be the reason behind this slow execution?

One of my query was this :

select UserDID, count(*) from exportusers
group by UserDID 
having count(*) > 1;

The query say "executing query..." and it never ends. I forcefully exit from the query browser.

Here is my Explain on select statement 

mysql> Explain select count(*) from exportusers where status != 'active' and PREndDate < now() - interval 3 month and DTModified < now() - interval 3 month;
+----+-------------+------------+-------+-----------------------------------------------------------------------+--------------------------+---------+------+---------+------------------------------------+
| id | select_type | table      | type  | possible_keys                                                         | key                      | key_len | ref  | rows    | Extra                              |
+----+-------------+------------+-------+-----------------------------------------------------------------------+--------------------------+---------+------+---------+------------------------------------+
|  1 | SIMPLE      | exportusers | range | index_jobs_on_DTModified,index_jobs_on_PREndDate,index_jobs_on_Status | index_jobs_on_DTModified | 5       | NULL | 2377287 | Using index condition; Using where |
+----+-------------+------------+-------+-----------------------------------------------------------------------+--------------------------+---------+------+---------+------------------------------------+
1 row in set (0.30 sec)

Upvotes: 4

Views: 118

Answers (1)

A Smith
A Smith

Reputation: 631

There could be a lot of reasons for a slow query on a larger table. A few:

  • There is no index that can be used to speed up the query.
  • The database does not have the portions of the index needed to process a request already loaded into memory.
  • The requested action is competing with other queries or updates for database processing. In particular, queries and updates don't always get along well.
  • You could be running your database on a micro instance which offers "bursty" performance and can at times balk at "overperforming" given its price point.
  • You could be running your database on an instance that you are also using to provide web services, cron jobs, software development, email or other processing efforts.

NOTE: Some of these items may also be influenced by the database engine in use.

Upvotes: 1

Related Questions