| Bug #65030 | Innodb_buffer_pool_pages_flushed reports incorrect number of flushed pages | ||
|---|---|---|---|
| Submitted: | 19 Apr 2012 4:14 | Modified: | 15 May 2012 21:25 |
| Reporter: | Davi Arnaut (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.5.21, 5.5.23 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | innodb, Innodb_buffer_pool_pages_flushed, SHOW GLOBAL STATUS | ||
[19 Apr 2012 6:35]
Valeriy Kravchuk
Thank you for the bug report and code contributed. Similar results with 5.5.23:
C:\Program Files\MySQL\MySQL Server 5.5\bin>net start mysql55
The MySQL55 service is starting..
The MySQL55 service was started successfully.
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.23-log MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_pages_written' OR
-> Variable_name LIKE 'Innodb_buffer_pool_pages_flushed';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_pages_written | 0 |
+----------------------------------+-------+
2 rows in set (0.00 sec)
mysql> create table t(c1 int) engine=InnoDB;
Query OK, 0 rows affected (0.11 sec)
mysql> SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_pages_written' OR
-> Variable_name LIKE 'Innodb_buffer_pool_pages_flushed';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| Innodb_buffer_pool_pages_flushed | 20 |
| Innodb_pages_written | 10 |
+----------------------------------+-------+
2 rows in set (0.00 sec)
mysql> SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_pages_written' OR
-> Variable_name LIKE 'Innodb_buffer_pool_pages_flushed';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| Innodb_buffer_pool_pages_flushed | 20 |
| Innodb_pages_written | 10 |
+----------------------------------+-------+
2 rows in set (0.00 sec)
mysql> insert into t values(1);
Query OK, 1 row affected (0.06 sec)
... wait few seconds
mysql> SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_pages_written' OR
-> Variable_name LIKE 'Innodb_buffer_pool_pages_flushed';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| Innodb_buffer_pool_pages_flushed | 28 |
| Innodb_pages_written | 14 |
+----------------------------------+-------+
2 rows in set (0.00 sec)
[15 May 2012 21:25]
John Russell
Added to changelog for 5.5.25: The Innodb_buffer_pool_pages_flushed status variable was incorrectly set to twice the value it should be. Its value should never exceed the value of Innodb_pages_written.
Description: The value of Innodb_buffer_pool_pages_flushed is twice the value it should as flushed pages are being counted twice. The internal srv_buf_pool_flushed counter is being increased twice for each flush batch, once in buf_flush_batch and once in buf_flush_common. How to repeat: mysql> SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_pages_written' OR Variable_name LIKE 'Innodb_buffer_pool_pages_flushed'; +----------------------------------+-------+ | Variable_name | Value | +----------------------------------+-------+ | Innodb_buffer_pool_pages_flushed | 20 | | Innodb_pages_written | 10 | +----------------------------------+-------+ 2 rows in set (0.00 sec) mysql> INSERT INTO t1 VALUES (1); Query OK, 1 row affected (0.00 sec) mysql> SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_pages_written' OR Variable_name LIKE 'Innodb_buffer_pool_pages_flushed'; +----------------------------------+-------+ | Variable_name | Value | +----------------------------------+-------+ | Innodb_buffer_pool_pages_flushed | 32 | | Innodb_pages_written | 16 | +----------------------------------+-------+ 2 rows in set (0.00 sec) Suggested fix: --- a/storage/innobase/buf/buf0flu.c +++ b/storage/innobase/buf/buf0flu.c @@ -1749,8 +1749,6 @@ buf_flush_batch( } #endif /* UNIV_DEBUG */ - srv_buf_pool_flushed += count; - return(count); }