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.

ufk
  • 323
  • 3
  • 7
  • 26
  • http://dev.mysql.com/doc/refman/4.1/en/query-cache-in-select.html – c4f4t0r Mar 18 '14 at 08:39
  • query_cache_type is set to 0, which means off. are there other caching mechanisms to be considered here? – ufk Mar 18 '14 at 08: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 '14 at 08: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 '14 at 09:01

1 Answers1

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

Community
  • 1
RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80