Home / server / Questions / 582807
Accepted
ufk
Asked: 2014-03-19 00:26:45 +0800 CST

how to disable all caches types in mysql server in order to do performance testing for my queries

  • 772

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.

mysql

1 Answers

  1. Best Answer

    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 !!!

    • 3