[MDEV-26083] InnoDB Buffer Pool Statistics status completely wrong Created: 2021-07-02  Updated: 2021-07-02

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.5.8
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Oli Sennhauser Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Linux, Ubuntu 18.04, n.a.

Attachments: PNG File Screenshot_20210702_234533.png    

 Description   

When we dropped the Primary Key of some partitioned tables the InnoDB Buffer Pool Statistics status became completely wrong:

1. Restart the database to resize the InnoDB Buffer Pool
2. Run the DDL:

SQL> alter table history drop primary key, add index (`itemid`,`clock`);
Query OK, 79946045 rows affected (15 min 9.689 sec)
Records: 79946045 Duplicates: 0 Warnings: 0

SQL> alter table history_str drop primary key, add index (`itemid`,`clock`);
Query OK, 119857625 rows affected (1 hour 34 min 14.117 sec)
Records: 119857625 Duplicates: 0 Warnings: 0

SQL> alter table history_uint drop primary key, add index (`itemid`,`clock`);
Query OK, 171818919 rows affected (36 min 14.557 sec)
Records: 171818919 Duplicates: 0 Warnings: 0

3. Get Status:

SQL> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pag%';
-------------------------------------------------------------+

Variable_name Value

-------------------------------------------------------------+

Innodb_buffer_pool_pages_data 1924783 -> data cannot be bigger than pages total!
Innodb_buffer_pool_pages_free 1
Innodb_buffer_pool_pages_misc 18446744073709175312 --> negative number
Innodb_buffer_pool_pages_total 1548480 24G / 16k should be: 1572864
-------------------------------------------------------------+

So all the number for a proper monitoring are completely wrong!

 Comments   
Comment by Oli Sennhauser [ 2021-07-02 ]

The system was NOT swapping. So the InnoDB number cannot be correct:

free -h
total used free shared buff/cache available
Mem: 30G 25G 1.2G 71M 3.9G 4.6G
Swap: 15G 3.0M 15G

Generated at Thu Feb 08 09:42:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.