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:
None 
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 4:14] Davi Arnaut
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);
 }
[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.