2

I am attempting to run the same query multiple times on the same mysql 8 database and table.

I need to carry out experiments to determine if tweaking the query and or table itself improves performance. However after the first attempt the response time is much faster, i assume becuase the data is cached.

mysql 8 innodb

What options do i have to clear the cache so the data is fetched from scratch.

It appears the answers that have been proposed before are all related to mysql 5 and not mysql 8. Most of the commands seem to now be deprecated.

Clear MySQL query cache without restarting server

Kay
  • 17,906
  • 63
  • 162
  • 270

2 Answers2

3

The question you link to is about the query cache, which is removed in MySQL 8.0 so there's no need to clear it anymore.

Your wording suggests you are asking about the buffer pool, which is not the same as the query cache. The buffer pool caches data and index pages, whereas the query cache (when it existed) cached results of queries.

There is no command to clear the buffer pool without restarting the MySQL Server. Pages remain cached in the buffer pool until they are evicted by other pages.

The buffer pool is in RAM so its contents are cleared if you restart the MySQL Server process. So if you want to start from scratch, you would need to restart that process (you don't need to reboot the whole OS, just restart the MySQL service).

The caveat is that in MySQL 8.0, the contents of the buffer pool are not entirely cleared when you restart. A percentage of the content of the buffer pool is saved during shutdown, and reloaded automatically on startup. This feature is enabled by default, but you can optionally disable it.

Read more about this:

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

If the query is one that will be run frequently in production, then I claim that your goal is backward. I suggest that you should run the query twice, and use the timing from the second one. That way, it is closer to what the production will see -- data already in cache.

(Bill explains that the "Query cache" is irrelevant and that the "buffer pool" is relevant.)

Here's a technique that I like for testing queries without needing to build a big enough table to see timing changes:

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';

The numbers are actual reads/writes of data or index rows. Numbers may look like the number of output rows, the number of rows in the table, etc. This gives you a clue of whether there was, for example, a full table (or index) scan. Non-zero "Handler_write%" values indicate a temp table (or tables) was needed.

See also EXPLAIN ANALYZE if that is available on your version.

More tips: Index Cookbook

Rick James
  • 135,179
  • 13
  • 127
  • 222