How to disable MySQL Query Caching

Question

I am trying to do some benchmarking of different queries on different columns but MySQL just won't let me do it. After the first execution of a query, I can never get the same execution time for that query again. For example if the query executes in 0.062 secs the first time, I can never get the same execution time for the 2nd, 3rd etc runs. It becomes either 0 seconds or something like 0.015.

I have read many posts on disabling and clearing MySQL query cache but none of them has been of any help for me.

like this SO post here

No matter what I do, MySQL seems to insist on using cached results.

I restart the MySQL Workbench then I run;

set global query_cache_type=0;
set global query_cache_size=0;
flush query cache;
reset query cache;

The execution time keeps showing 0 secs.

Only server variable that I haven't been able to change is "have_query_cache". Its value is "yes" and when I try to set it to "no" Workbench says it is read-only.

I also do;

set profiling=1;
run my select query
show profile for query 2;

Profiling result shows this:

'starting', '0.000077'
'checking permissions', '0.000007'
'Opening tables', '0.000016'
'init', '0.000035'
'System lock', '0.000009'
'optimizing', '0.000013'
'statistics', '0.000094'
'preparing', '0.000008'
'executing', '0.000002'
'Sending data', '0.000016'
'end', '0.000002'
'query end', '0.000003'
'closing tables', '0.000005'
'freeing items', '0.000139'
'cleaning up', '0.000009'

If I am not wrong this shows that no caches are being used right? But I stil see 0 secs. for the execution time.

Edit: The query I am running is a SELECT query using "SQL_NO_CACHE" like so:

SELECT SQL_NO_CACHE col1,now() from mytable where col2="some_value"

(I added now() function to help me prevent query caching)

Edit2: I am using innoDB, MySQL 5.6.10

Could someone please help me cause I can't see what is going on here.

Thanks a lot!

Is it possible that sometimes the query runs for more than 0 seconds if the load to the database is high? — Slowcoder, Apr 16 '13 at 19:06
@Slowcoder I don't understand what you mean but there is no load on the database right now. I am doing these tests on my local development machine. — honor, Apr 16 '13 at 20:07
I tried to run those 4 first commands in your post, after each query..and it seems to work. Queries now take almost the same time when run more than once successively. — MohamedEzz, Apr 16 '14 at 23:59

score 31 · Answer 1 · answered Apr 16 '13 at 18:06

31

This could be due to the cache of the data itself, and not the query cache.

To make sure, you can disable the query cache for a single statement by adding SQL_NO_CACHE after your SELECT statement.

Ex:

SELECT SQL_NO_CACHE field FROM table.

answered Apr 16 '13 at 18:06

ESG

8,988
3
35
52

1

I am sorry for not mentioning my use of SQL_NO_CACHE in my query already. Edited my question. – honor Apr 16 '13 at 20:09
1

Your query is not using the query cache at all then. It's either using the buffer pool (if using innodb) or the os cache (if using myisam). – ESG Apr 16 '13 at 20:12
I am using innoDB. I think even if it is the buffer pool (as Quassnoi also mentioned below) I shouldn't be getting 0 secs execution time for 2 million rows right? And is there a way to disable/reset buffer pool to check this option out? – honor Apr 16 '13 at 21:04
3

The only way to purge the buffer pool is to restart the MySQL instance. – Bill Karwin Apr 16 '13 at 21:39
That flat out does not work (at least for my connection to a MySQL database. The only way for me to get accurate results is to "RECONNECT" before rerunning a query. – Jaime Nov 20 '15 at 21:54
@Jaime The query cache is persisted and shared across connections. If reconnecting fixes what ails you, then you have a different problem. – ESG Nov 21 '15 at 05:14

score 10 · Accepted Answer · answered Apr 16 '13 at 18:07

10

Running the query for the first time makes InnoDB buffer pool populate with your tables' relevant blocks.

Since re-running the query requires exactly same blocks, it spares the query from the need to read them from disk when it's re-run, making it significantly faster.

answered Apr 16 '13 at 18:07

Quassnoi

413,100
91
616
614

Would it be so much faster that it would go from 0.062 secs to 0! secs in 2 million rows? – honor Apr 16 '13 at 20:12
3

@sr yet it would. Disks are very slow. Loading data from memory is ridiculously quicker than from disk.. – ESG Apr 16 '13 at 20:51
1

@Quassnoi, when is mysql buffer pool invalidated? I'm wondering about inconsistency when retrieving results from obsolete blocks. – Sebas Apr 17 '13 at 00:17
@Quassnoi, with your direction about innoDB buffer pool I made some research and shared my findings and ideas on an answer below. Can you confirm that answer? If so I believe I should mark your answer as accepted and also give some credits to TheVedge. – honor Apr 17 '13 at 09:09
@Sebas: never, it always holds valid data. – Quassnoi Apr 17 '13 at 18:33
@Sebas mysql buffer pool is not invalidated, but it is you may say "updated/refreshed" considering some innoDM parameters. For more info you can check this link out: http://dev.mysql.com/doc/refman/4.1/en/innodb-buffer-pool.html – honor Apr 18 '13 at 10:20

Bill Karwin · Answer 3 · 2013-04-17T18:40:57.217

7

You can verify the difference between buffer pool page reads versus page reads that had to go to disk to get the page:

mysql> SHOW SESSION STATUS LIKE 'Innodb_buffer_pool_read%';
mysql> ...run a query...
mysql> SHOW SESSION STATUS LIKE 'Innodb_buffer_pool_read%';

Compare these values from the report, and note how much they grow:

+---------------------------------------+----------+
| Variable_name                         | Value    |
+---------------------------------------+----------+
| Innodb_buffer_pool_read_requests      | 10327490 |
| Innodb_buffer_pool_reads              | 1133     |
+---------------------------------------+----------+

The read_requests are logical page reads, which may read from pages already in the buffer pool. If this number grows after your query, but reads doesn't grow, then your query got its result solely from the buffer pool.

The reads are the number of page reads that had to go out to disk and incur the cost of I/O to copy pages into the buffer pool. If this number grows after your query, then it had to load data from disk.

For more on the difference between the performance of memory access versus disk access, read http://everythingisdata.wordpress.com/2009/10/17/numbers-everyone-should-know/

Re your comment:

The "Opening tables" is related to an in-memory cache of InnoDB tables, kind of like a data dictionary. Tables are opened the first time they are referenced after a restart. A given table's entry in the in-memory data dictionary stays in memory indefinitely, so that memory usage can grow pretty large if you have thousands of tables. In MySQL 5.6, they have some ne tuning variables to put a cap on that memory usage, and evict infrequently-used tables. But this whole mechanism is separate from the buffer pool, which stores pages of data and indexes.

The "statistics" is also separate from the buffer pool. InnoDB maintains in-memory statistics about data and indexes, which it uses to guide the query optimizer. Statistics are refreshed automatically when you first access an InnoDB table, when you run SHOW TABLE STATUS or run certain queries against the INFORMATION_SCHEMA, when you run ANALYZE TABLE, and also when the size of the table changes significantly. InnoDB generates these statistics reading a fixed number of pages from the table at random, and this is likely to hit the disk if you have a cold buffer pool.

edited Apr 17 '13 at 18:40

answered Apr 17 '13 at 00:11

Bill Karwin

538,548
86
673
828

Bill, thanks for the info I will try that soon as I can (CMMI appraisal is taking my whole day right now). What do you think about my own answer above? Do you think I am on the right track, did I get the buffer pool right? Do you see any points that I am wrong in that answer of mine? Thanks a lot. – honor Apr 17 '13 at 10:15
@Bill, So what's the way to stop all these InnoDB buffering and caching? – Pacerier Jan 19 '15 at 10:43
@Pacerier, I'm not sure why you're asking. You *want* caching of the open tables and index statistics, because the alternative is to read it from disk every time you do a query. – Bill Karwin Jan 20 '15 at 02:36
1

@BillKarwin, When doing measurements, I'd like to disable it so that we can measure apples against apples. – Pacerier Jan 22 '15 at 04:00
@Pacerier, ah, got it. No, there's no way to purge the buffer pool manually. You'll have to restart mysqld before running your tests. – Bill Karwin Jan 22 '15 at 06:48
@BillKarwin, Is `net stop mysql` sufficient? Or do we still need to run any `flush`es and etc? – Pacerier Jan 23 '15 at 03:54
@Pacerier, yes, `net stop mysql` shuts down the service. That stops the process and flushes the buffer pool. Of course then you have to `net start mysql` before you can do the work again. Also if you're using MySQL 5.6, you have to make sure you're not automatically re-loading the pages formerly in the buffer pool on startup (see http://dev.mysql.com/doc/refman/5.6/en/innodb-preload-buffer-pool.html). – Bill Karwin Jan 23 '15 at 05:04
@billkarwin Could an alternative be to SELECT SQL_NO_CACHE ....... and it will treat your query as if you have just started and go all the way to your disk? – Wilson Hauck Dec 08 '17 at 18:51
1

@WilsonHauck, No. SQL_NO_CACHE makes sure the query result won't be read from the query cache. But the InnoDB buffer pool is a different caching mechanism. The buffer pool stores InnoDB pages. The query cache stores complete query results. They are used for different things. There's no query option to bypass the InnoDB buffer pool. – Bill Karwin Jan 08 '18 at 00:14

score 4 · Answer 4 · answered Apr 16 '13 at 22:42

OK. So I have been diggin' on this innoDB buffer pool issue. Thank you @Quassnoi, @TheVedge for mentioning that. It has been very useful for the following work.

I was trying to find a way to disable or reset innoDB buffer pool but I haven't been able to find a way other than restarting MySQL server. (To restart the server on a windows box you can either use services.msc or command prompt.)

I guess all the configuration variable settings and the use of "SQL_NO_CACHE" was more than enough to avoid cache use, and also checking the profiling results I was not seeing any cache reference neither (check the original post).

Then I decided to check the profiling for the same query's 1st and 2nd runs after restarting MySQL server. This way I would be able to see what was going on during the query execution step by step.

1st run after server restart yielded the following steps and timings:

'starting', '0.000078'
'checking permissions', '0.000005'
'Opening tables', '0.120329'
'init', '0.000107'
'System lock', '0.000012'
'optimizing', '0.000024'
'statistics', '0.065317'
'preparing', '0.000026'
'executing', '0.000003'
'Sending data', '0.000038'
'end', '0.000005'
'query end', '0.000006'
'closing tables', '0.000014'
'freeing items', '0.000463'
'cleaning up', '0.000039'

And these are the results of the 2nd run:

'starting', '0.000068'
'checking permissions', '0.000006'
'Opening tables', '0.000019'
'init', '0.000046'
'System lock', '0.000007'
'optimizing', '0.000010'
'statistics', '0.000073'
'preparing', '0.000009'
'executing', '0.000002'
'Sending data', '0.000035'
'end', '0.000003'
'query end', '0.000003'
'closing tables', '0.000006'
'freeing items', '0.000181'
'cleaning up', '0.000015'

As you can see there is a huge difference for the durations of "opening tables" and "statistics" actions between the two executions and none of the executions reference any cache.

The two actions seem to be the most time consuming parts for the 1st execution (I believe as a part of the creation of innoDB buffer pool), and once the pool is generated these two actions not performed as heavily hence the much shorter execution time for the query after the buffer pool creation.

When you sum up the execution times of the individual steps for 2nd run we get 0.000483 seconds and I think MySQL workbench shows this result as 0.000 secs which might also trick you to think that the second run was result of cache using but actually it seems like it is not. This is just a display precision issue for the execution durations of queries.

As a result what I have figured out so far is I actually have been able to disable the use of caches and it is the completion of innoDB buffer pool that speeds up the second query so much. This also means that I can find a record in 2 million rows in 0.000483 seconds.

Would you agree?

score 1 · Answer 5 · answered Aug 24 '17 at 12:21

In your .ini/.cfg in [mysqld] section, add a line have_query_cache=0 # to prevent using resources from default of yes ccyy/mm/dd your initials. With your other changes, close/restart MySQL. You should NOT observe any QC activity in profile research.

How to disable MySQL Query Caching

5 Answers5