0

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?

1
  • 1
    Can you show us your query along with the execution plans? – Colin 't Hart Nov 5 '13 at 13:15
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.

enter image description here

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

2
  • Thank you! On the db host, the script performs constant at about real 0m0.005s and on the rails host at about real 0m0.220s. Which is about the difference between the slow and the fast execution times. Seems to be a network issue? – user30173 Nov 5 '13 at 11:21
  • One way to check, would be to use an application like bwm-ng that you can install via yum or apt-get. Just updated my answer. Looks like it is a network issue though, if you are getting the same difference in execution times after every test. – Craig Efrein Nov 5 '13 at 11:33
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.