mysql - Reset performance of all queries in mysql 8

Question

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?

标签： mysqlperformance-testingmysql-8.0

Answer 1

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:

• Indexing: http://mysql.rjweb.org/doc.php/index_cookbook_mysql
• "Handler" counts: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts
• Slowlog: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog