SQL Query is sometimes slow, sometimes fast

Question

I have two hosts, one with my rails app, one with my MySQL database. Going through my rails Logfile I can see that sometimes queries take much more time than the same query at other times. Think 200ms vs 2ms. The query itself is a select, explain tells me that it uses my index.

The engine is innodb, I already increased the innodb_buffer_pool_size, max_connections and the thread_cache_size. vmstat tells me that there is no paging.

What could be the reason for sometimes slow and sometimes fast queries?

Answer 1

There could be several reasons why the execution time is varying. The first step I would take is to test the query directly using the MySQL client from your MySQL server. This example is for Linux.

1. Create a script to test your query

   vi query_1.sql

   -- SQL_NO_CACHE means do not use the cache so you get the a more accurate execution time SELECT SQL_NO_CACHE column_1, column_2 FROM sometable WHERE column_N = 'somevalue';

2. Save your file \wq

3. Execute the query from the console using the time command

   time mysql -u root -p somedatabase < query_1.sql

   real 0m2.009s

   user 0m0.000s

   sys 0m0.004s

You will get an output showing how long it truly took to execute your query. The times should remain constant.

You can also perform the same test from your rail app host, if you have the mysql client installed on that machine.

time mysql -h address.ip.database.host -u root -p somedatabase < query_1.sql

This might help to identify possible network issues if the query exectuion time is much longer from the Rails app host.

Update 12:25 If this is a network issue, you can follow network activity using a program like bwm-ng Bandwith Monitor to analyze network activity on every network card installed on your system and the rate at which traffic is traveling through those links.

If that is not the case, then we can move forward by looking at other possible factors such as IO, Memory and other applications

Answer 2

If your table has multiple Indexes you should consider using FORCE INDEX to tell MySQL which Index should be used. Sometimes MySQL uses the wrong one and this results in a full table scan.