How to clear database cache in mysql 8 innodb

Question

I tried running the same query multiple times on the same mysql 8 database and table.

I need to conduct experiments to determine if tuning the query and/or the table itself can improve performance. However, after the first try, the response time is much faster, I think because the data is cached.

mysql 8 innodb

What options do I have to clear the cache to get the data from scratch.

---

It appears that the previously proposed answers are all related to mysql 5, not mysql 8. Most commands appear to be deprecated now.

Clear the MySQL query cache without restarting the server

Answer 1

If the query is one that is run frequently in production, then I think your goal is behind the scenes. I suggest that you should run the query twice and use the timing of the second query. This way, it's closer to what production will see - the data is already in cache.

(Bill explained that "query cache" is irrelevant, "buffer pool" is relevant.)

This is a technique I like for testing queries without having to build a table large enough to see changes over time:

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

These numbers are the actual number of reads/writes of data or index rows. The number might look like the number of output rows, the number of rows in a table, etc. This lets you know if there is, for example, a full table (or index) scan. A non-zero "Handler_write%" value indicates that a temporary table (or tables) is required.

If available for your version, see also EXPLAIN ANALYZE.

More Tips:INDEX RECIPE

Answer 2

The question you linked to is about the query cache, which has been removed in MySQL 8.0, so there is no need to clear it anymore.

Your wording indicates that you are asking about the buffer pool, which is different from the query cache. The buffer pool caches data and index pages, while the query cache (if present) caches query results.

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

The buffer pool is located in RAM, so if the MySQL server process is restarted, its contents will be cleared. Therefore, if you want to start from scratch, you need to restart the process (you don't need to restart the entire operating system, just restart the MySQL service).

It should be noted that in MySQL 8.0, the contents of the buffer pool will not be completely cleared when restarting. A percentage of the buffer pool contents is saved during shutdown and automatically reloaded on startup. This feature is enabled by default, but you can choose to disable it.

Learn more about this:

• https://dev.mysql .com/doc/refman/8.0/en/innodb-preload-buffer-pool.html
• https://dev.mysql.com /doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_dump_at_shutdown