如何重置Innodb_buffer_pool_reads与Innodb_buffer_pool_read_requests？除重启外何时归零？

Hey there! Let's tackle your questions about resetting Innodb_buffer_pool_reads and Innodb_buffer_pool_read_requests step by step.

First off, it's important to note that these are global status variables, not configurable parameters—you can't just SET them directly to 0. Here's how you can reset them:

• Use the FLUSH STATUS; command: This is the official way to reset most global status variables in MySQL, including the two you're asking about. A few things to keep in mind:
  • You'll need the PROCESS privilege to run this command.
  • It resets all global status variables, not just these two. If you're monitoring other metrics, this will wipe their counters too, so plan accordingly.
  • After running it, both Innodb_buffer_pool_reads and Innodb_buffer_pool_read_requests will jump back to 0.

If you don't want to reset all status variables, a workaround is to record the current values of these two variables as a baseline, then calculate the delta (new value - baseline) whenever you need to track changes. This avoids messing with other metrics.

Besides a full database restart, there are a couple of common scenarios where these counters get automatically reset to 0:

• Running FLUSH STATUS;: Even if it's triggered automatically by a monitoring script or tool (like some performance monitoring utilities), this command will wipe the counters.
• Dynamically adjusting innodb_buffer_pool_size: Starting in MySQL 5.7, you can modify the buffer pool size without restarting the database using SET GLOBAL innodb_buffer_pool_size = <new_value>;. When this happens, MySQL reinitializes the entire buffer pool, which resets all related statistics—including the two variables you're tracking.
• Rare edge cases: In some very specific scenarios (like a critical InnoDB error that forces a buffer pool reinitialization without a full instance restart), these counters might reset, but this is extremely uncommon and usually tied to underlying issues with the storage engine.

内容的提问来源于stack exchange，提问作者sila