2

I'm using Mysql Server 5.6.15 and i have a huge database with many complex queries.

it seems that when I run the query the first time it takes X amount of time.

when I execute it again it takes less then X time. which means that something was cached.

I want to be able to performance test my queries in order to improve them regardles of caching.

is there a way to disable all caching types in mysql in order for me to properly test the execution time of my queries?

any information regarding the issue would be greatly appreciated.

Improve this question
4
  • dev.mysql.com/doc/refman/4.1/en/query-cache-in-select.html
    – c4f4t0r
    Mar 18, 2014 at 8:39
  • query_cache_type is set to 0, which means off. are there other caching mechanisms to be considered here?
    – ufk
    Mar 18, 2014 at 8:52
  • example here that after running a complex query for the first time it took 23 seconds, using SQL_CACHE and SQL_NO_CACHE make no difference, the next running of the query took less then a second.
    – ufk
    Mar 18, 2014 at 8:56
  • i read that OS and disk cache may also impact performance. but i can't believe that's the cause of the query execution time to be x23 faster
    – ufk
    Mar 18, 2014 at 9:01

1 Answer 1

Reset to default
3

What you are really looking for is to have query cache misses close to 100%. I think I have what looking for. On July 09, 2011, I answered this post in the DBA StackExchange : Testing Query Speed.

Here is that answer:

You may want to impose a stress test environment to get query results to be as real time as possible. For example, the MyISAM Key Buffer (size governed by key_buffer_size) by default is 8MB, and the minimum value is 8 (that's right, 8 bytes). It is responsible for holding index pages from .MYI files. Set this value to 8 and every keyed lookup in MyISAM must be read over and over again.

This would also work in prinicple with InnoDB. The default InnoDB Buffer Pool Size (governed by innodb_buffer_pool_size) is 128MB and minimum is 1MB (in MySQL 5.5). It is responsible for holding data and index pages from .ibd and/or ibdata1 files. Set this value to 1M and every keyed lookup in InnoDB (which always includes an additional lookup in the clustered row index (gen_clust_index)) must be read over and over again.

For added stress, set the query_cache_type to 0 to force queries not to be cached.

Just add those three(3) minimum values into /etc/my.cnf

[mysqld]
innodb_buffer_pool_size=1M
key_buffer_size=8
query_cache_type=0

and restart MySQL.

Doing this should make all queries perform at its bare minimum best (or worst) every time.

Give it a Try !!!

Well, don't just sit there staring at the monitor...

Give it a Try !!!

Improve this answer
1
  • This does not seem to work for Mysql 5.7. The minimum value it seems to accept for the innodb_buffer_pool_size is 5M. After making these changes I still see a huge reduction of processing time between the first and second queries (10s -> 0.8s).
    – Daniel Black
    Nov 4, 2022 at 20:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .