Description:
Bufferpool Index Stats are not decremented on page free. The current code only decrements the counter when the page is being evicted from LRU. However, when a btree page is freed, it should be decremented. The current logic to increment on btr_page_create keeps incrementing when the freed page is reused and this can lead to wrong stats.
As per https://dev.mysql.com/worklog/task/?id=7170,
There are two places that cause a page to enter the buffer pool:
When an existent page is read from disk. I chose to plant the increment in buf_page_monitor(), called from buf_page_io_complete().
When a new page is created (as a result from an INSERT). I chose to plant the increment in btr_page_create().
Removal of a page from the buffer pool can happen on two occasions too:
When a page is not accessed for some time and is pushed away from the LRU by other pages. Then it is either flushed to the disk if there are modifications to it or just evicted.
When a page is deleted (as a result from DELETE). -------> This is not happening.
How to repeat:
mysql> drop table if exists test.t1;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> create table test.t1(id varchar(200));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test.t1 values ("sdfasdfsdfsdafasdfasdsafsadfasdfsdafadsfs");
Query OK, 1 row affected (0.01 sec)
mysql> insert into test.t1 select * from test.t1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into test.t1 select * from test.t1;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into test.t1 select * from test.t1;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into test.t1 select * from test.t1;
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> insert into test.t1 select * from test.t1;
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> insert into test.t1 select * from test.t1;
Query OK, 32 rows affected (0.00 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql> insert into test.t1 select * from test.t1;
Query OK, 64 rows affected (0.01 sec)
Records: 64 Duplicates: 0 Warnings: 0
mysql> insert into test.t1 select * from test.t1;
Query OK, 128 rows affected (0.00 sec)
Records: 128 Duplicates: 0 Warnings: 0
mysql> insert into test.t1 select * from test.t1;
Query OK, 256 rows affected (0.01 sec)
Records: 256 Duplicates: 0 Warnings: 0
mysql> insert into test.t1 select * from test.t1;
Query OK, 512 rows affected (0.01 sec)
Records: 512 Duplicates: 0 Warnings: 0
mysql> SELECT
-> tables.NAME AS table_name,
-> indexes.NAME AS index_name,
-> cached.N_CACHED_PAGES AS n_cached_pages
-> FROM
-> INFORMATION_SCHEMA.INNODB_CACHED_INDEXES AS cached,
-> INFORMATION_SCHEMA.INNODB_INDEXES AS indexes,
-> INFORMATION_SCHEMA.INNODB_TABLES AS tables
-> WHERE
-> cached.INDEX_ID = indexes.INDEX_ID
-> AND indexes.TABLE_ID = tables.TABLE_ID;
+------------+-----------------+----------------+
| table_name | index_name | n_cached_pages |
+------------+-----------------+----------------+
| test/t1 | GEN_CLUST_INDEX | 7 |
+------------+-----------------+----------------+
1 row in set (0.00 sec)
mysql> select count(*) from information_schema.innodb_buffer_page where table_name='`test`.`t1`';
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.01 sec)
mysql> delete from test.t1;
Query OK, 1024 rows affected (0.01 sec)
mysql> select count(*) from information_schema.innodb_buffer_page where table_name='`test`.`t1`';
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.01 sec)
mysql> SELECT
-> tables.NAME AS table_name,
-> indexes.NAME AS index_name,
-> cached.N_CACHED_PAGES AS n_cached_pages
-> FROM
-> INFORMATION_SCHEMA.INNODB_CACHED_INDEXES AS cached,
-> INFORMATION_SCHEMA.INNODB_INDEXES AS indexes,
-> INFORMATION_SCHEMA.INNODB_TABLES AS tables
-> WHERE
-> cached.INDEX_ID = indexes.INDEX_ID
-> AND indexes.TABLE_ID = tables.TABLE_ID;
+------------+-----------------+----------------+
| table_name | index_name | n_cached_pages |
+------------+-----------------+----------------+
| test/t1 | GEN_CLUST_INDEX | 7 |
+------------+-----------------+----------------+
1 row in set (0.01 sec)
mysql> insert into test.t1 values ("sdfasdfsdfsdafasdfasdsafsadfasdfsdafadsfs");
Query OK, 1 row affected (0.00 sec)
mysql> insert into test.t1 select * from test.t1;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into test.t1 select * from test.t1;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into test.t1 select * from test.t1;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into test.t1 select * from test.t1;
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> insert into test.t1 select * from test.t1;
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> insert into test.t1 select * from test.t1;
Query OK, 32 rows affected (0.01 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql> insert into test.t1 select * from test.t1;
Query OK, 64 rows affected (0.00 sec)
Records: 64 Duplicates: 0 Warnings: 0
mysql> insert into test.t1 select * from test.t1;
Query OK, 128 rows affected (0.01 sec)
Records: 128 Duplicates: 0 Warnings: 0
mysql> insert into test.t1 select * from test.t1;
Query OK, 256 rows affected (0.00 sec)
Records: 256 Duplicates: 0 Warnings: 0
mysql> insert into test.t1 select * from test.t1;
Query OK, 512 rows affected (0.00 sec)
Records: 512 Duplicates: 0 Warnings: 0
mysql> select count(*) from information_schema.innodb_buffer_page where table_name='`test`.`t1`';
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.02 sec)
mysql> SELECT
-> tables.NAME AS table_name,
-> indexes.NAME AS index_name,
-> cached.N_CACHED_PAGES AS n_cached_pages
-> FROM
-> INFORMATION_SCHEMA.INNODB_CACHED_INDEXES AS cached,
-> INFORMATION_SCHEMA.INNODB_INDEXES AS indexes,
-> INFORMATION_SCHEMA.INNODB_TABLES AS tables
-> WHERE
-> cached.INDEX_ID = indexes.INDEX_ID
-> AND indexes.TABLE_ID = tables.TABLE_ID;
+------------+-----------------+----------------+
| table_name | index_name | n_cached_pages |
+------------+-----------------+----------------+
| test/t1 | GEN_CLUST_INDEX | 13 |
+------------+-----------------+----------------+
1 row in set (0.00 sec)
As you can see here, though the number of pages in buffer is same before and after re-insertion, the bufferpool per index stats denote as it they are doubled. As the current behavior increments on btr_create_page ( which can be called while reusing the existing freed page ) and not decrementing the counter during btr_free_page.
Suggested fix:
Author: Pranay Motupalli <pranay@Pranays-Mac-mini.local>
Date: Wed Nov 13 11:05:23 2024 -0800
Decrement Bufferpool Per Index Stats during btr_free_page
diff --git a/storage/innobase/btr/btr0btr.cc b/storage/innobase/btr/btr0btr.cc
index 061a6f11fd2..eb1fe67f21d 100644
--- a/storage/innobase/btr/btr0btr.cc
+++ b/storage/innobase/btr/btr0btr.cc
@@ -607,6 +607,10 @@ void btr_page_free(dict_index_t *index, /*!< in: index tree */
ut_ad(fil_page_index_page_check(block->frame));
ut_ad(level != ULINT_UNDEFINED);
btr_page_free_low(index, block, level, mtr);
+
+ if (level == 0) {
+ buf_stat_per_index->dec(index_id_t(index->space, index->id));
+ }
}
/** Sets the child node file address in a node pointer. */
