Testing performance of queries in mysql

Question

I am trying to setup a script that would test performance of queries on a development mysql server. Here are more details:

• I have root access
• I am the only user accessing the server
• Mostly interested in InnoDB performance
• The queries I am optimizing are mostly search queries (SELECT ... LIKE '%xy%')

What I want to do is to create reliable testing environment for measuring the speed of a single query, free from dependencies on other variables.

Till now I have been using SQL_NO_CACHE, but sometimes the results of such tests also show caching behaviour - taking much longer to execute on the first run and taking less time on subsequent runs.

If someone can explain this behaviour in full detail I might stick to using SQL_NO_CACHE; I do believe that it might be due to file system cache and/or caching of indexes used to execute the query, as this post explains. It is not clear to me when Buffer Pool and Key Buffer get invalidated or how they might interfere with testing.

So, short of restarting mysql server, how would you recommend to setup an environment that would be reliable in determining if one query performs better then the other?

Answer 1

Assuming that you can not optimize the LIKE operation itself, you should try to optimize the base query without them minimizing number of rows that should be checked.

Some things that might be useful for that:

rows column in EXPLAIN SELECT ... result. Then,

mysql> set profiling=1; mysql> select sql_no_cache * from mytable;  ... mysql> show profile; +--------------------+----------+ | Status             | Duration | +--------------------+----------+ | starting           | 0.000063 | | Opening tables     | 0.000009 | | System lock        | 0.000002 | | Table lock         | 0.000005 | | init               | 0.000012 | | optimizing         | 0.000002 | | statistics         | 0.000007 | | preparing          | 0.000005 | | executing          | 0.000001 | | Sending data       | 0.001309 | | end                | 0.000003 | | query end          | 0.000001 | | freeing items      | 0.000016 | | logging slow query | 0.000001 | | cleaning up        | 0.000001 | +--------------------+----------+ 15 rows in set (0.00 sec) 

Then,

mysql> FLUSH STATUS; mysql> select sql_no_cache * from mytable; ... mysql> SHOW SESSION STATUS LIKE 'Select%'; +------------------------+-------+ | Variable_name          | Value | +------------------------+-------+ | Select_full_join       | 0     | | Select_full_range_join | 0     | | Select_range           | 0     | | Select_range_check     | 0     | | Select_scan            | 1     | +------------------------+-------+ 5 rows in set (0.00 sec) 

And another interesting value is last_query_cost, which shows how expensive the optimizer estimated the query (the value is the number of random page reads):

mysql> SHOW STATUS LIKE 'last_query_cost'; +-----------------+-------------+ | Variable_name   | Value       | +-----------------+-------------+ | Last_query_cost | 2635.399000 | +-----------------+-------------+ 1 row in set (0.00 sec) 

MySQL documentation is your friend.

Answer 2

Cited from this page: SQL_NO_CACHE options affect caching of query results in the query cache. If your table is quite small, it is possible, that the table itself is already cached. Since you just avoid caching of the results and not the tables you get the described behavior sometimes. So, as told in the other postings, you should flush your tables in between the queries.