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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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); }