CODEWITHSUNDEEP
mysqlperformance-testingmysql-8.0
supertrip86
supertrip86

Reputation: 31

Reset performance of all queries in mysql 8

I am working with MySQL 8 for learning purposes. I created a covering index for a query that significantly increased its speed. Now I dropped that index and restarted my computer, but it seems like the query performance remained more or less the same. Is there any way to reset the state of MySql 8.0, so that I can test again all my queries as if they are being run for the first time?

Upvotes: 0

Views: 633

Answers (1)

Rick James
Rick James

Reputation: 142453

Turn these OFF:

mysql> show variables like 'innodb_buffer_pool%at%';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| innodb_buffer_pool_dump_at_shutdown | ON    |
| innodb_buffer_pool_load_at_startup  | ON    |
+-------------------------------------+-------+

When they are ON, which might be the default for MySQL 8, the buffer_pool (InnoDB's main caching mechanism) is reloaded even after a reboot.

So, change your settings to have them off, then restart only mysqld to get MySQL into a "cold" state.

I take a different approach... The important timing is what will happen in a "production" environment -- where the buffer_pool is actively used, not cleared. So, I prefer to run a query twice and use the second timing.

Also, it is wise to keep the Query cache off.

More things for your learning exercise:

Upvotes: 1

Related Questions