Bug #59550 "Innodb_buffer_pool_pages_misc" goes wrong
Submitted: 17 Jan 2011 12:57 Modified: 30 Nov 2011 19:38
Reporter: liang xie Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.1.48, 5.1.60, 5.5.18 OS:Linux (Red Hat Enterprise Linux Server release 5.4 (Tikanga))
Assigned to: CPU Architecture:Any
Tags: Huge Innodb_buffer_pool_pages_misc value

[17 Jan 2011 12:57] liang xie
Description:
The Innodb_buffer_pool_pages_misc is out-of-bound under some conditions.

(gdb) b srv_export_innodb_status
...
(gdb) p buf_pool->curr_size
$6 = 32000
(gdb) p buf_pool->free
$7 = {count = 18309, start = 0x2aaaced16668, end = 0x2aaacee1f0d0}
(gdb) p buf_pool->LRU
$8 = {count = 19542, start = 0x2aaacee2a9e0, end = 0x2aaae8a63880}
(gdb) p 32000-18309-19542
$11 = -5851
(gdb) n
1936            export_vars.innodb_os_log_pending_fsyncs = fil_n_pending_log_flushes;
(gdb) p export_vars.innodb_buffer_pool_pages_misc
$12 = 18446744073709545765

How to repeat:
0) Some important parameters from my.cnf:
default-storage-engine = INNODB
sort_buffer_size = 100K
join_buffer_size = 100K
thread_concurrency = 16
thread_cache_size = 100
thread_stack = 128K
max_heap_table_size = 100M
tmp_table_size = 100M
ignore_builtin_innodb
lugin_load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so
innodb_file_format=Barracuda
innodb_strict_mode=0
innodb_additional_mem_pool_size = 20M
innodb_buffer_pool_size = 500M

1) Seed 10m records with sysbench as following:
./sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --mysql-socket=/home/wuyun.xl/mysql/run/mysql.sock --oltp-table-size=10000000 --mysql-db=test --num-threads=8 prepare

2) alter table sbtest row_format=compressed key_block_size=8

3) mysql> show status like 'innodb_buffer_pool_page%';
+----------------------------------+----------------------+
| Variable_name                    | Value                |
+----------------------------------+----------------------+
| Innodb_buffer_pool_pages_data    | 19542                |
| Innodb_buffer_pool_pages_dirty   | 0                    |
| Innodb_buffer_pool_pages_flushed | 613570               |
| Innodb_buffer_pool_pages_free    | 18309                |
| Innodb_buffer_pool_pages_misc    | 18446744073709545765 |
| Innodb_buffer_pool_pages_total   | 32000                |
+----------------------------------+----------------------+
[17 Jan 2011 20:30] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior. Additionally version 5.1.48 is out of date. Please try with current version and inform us if problem still exists.
[18 Feb 2011 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[15 Sep 2011 8:44] Thomas Parrott
Hi,

I too am seeing this problem, I have two database servers with the same table schemas. However one has databases where I have converted them to compressed format, whereas the other server has been created with compressed tables.

reportdb01:

| Innodb_buffer_pool_pages_data            | 10235                |
| Innodb_buffer_pool_pages_dirty           | 1154                 |
| Innodb_buffer_pool_pages_flushed         | 4513080              |
| Innodb_buffer_pool_pages_free            | 0                    |
| Innodb_buffer_pool_pages_misc            | 18446744073709549572 |
| Innodb_buffer_pool_pages_total           | 8191                 |
| Innodb_buffer_pool_read_ahead            | 318792               |
| Innodb_buffer_pool_read_ahead_evicted    | 3065573              |
| Innodb_buffer_pool_read_requests         | 4171817632           |
| Innodb_buffer_pool_reads                 | 11749026             |

reportdb02:

| Innodb_buffer_pool_pages_data            | 6576        |
| Innodb_buffer_pool_pages_dirty           | 628         |
| Innodb_buffer_pool_pages_flushed         | 4472380     |
| Innodb_buffer_pool_pages_free            | 0           |
| Innodb_buffer_pool_pages_misc            | 1615        |
| Innodb_buffer_pool_pages_total           | 8191        |
| Innodb_buffer_pool_read_ahead            | 1337885     |
| Innodb_buffer_pool_read_ahead_evicted    | 140122      |
| Innodb_buffer_pool_read_requests         | 1565209682  |
| Innodb_buffer_pool_reads                 | 1336124     |
[15 Sep 2011 8:45] Thomas Parrott
I am seeing huge memory growth on the reportdb01 server, which is not happening on the reportdb02 server.

See this issue: http://bugs.mysql.com/bug.php?id=61829
[15 Sep 2011 8:46] Thomas Parrott
Worth noting I am using partitions and MySQL 5.5.15 on CentOS 64.
[15 Sep 2011 9:37] Thomas Parrott
I have been able to re-create this exact situation in MySQL 5.5.15.

Before: Innodb_buffer_pool_pages_misc            | 9 

I then used sysbench with the command used above.
Then altered table to use compressed tables.

After: Innodb_buffer_pool_pages_misc            | 18446744073709550120

Will this affect memory usage?
[18 Nov 2011 10:59] Marti Raudsepp
Also experienced this on openSUSE 11.3, MySQL 5.5.16-log, after converting lots of tables to compressed InnoDB format.

I'm not using ALTER TABLE, but a script like:
CREATE TABLE foo_new LIKE foo;
INSERT INTO foo_new SELECT * FROM foo WHERE ...;

+---------------------------------------+----------------------+
| Variable_name                         | Value                |
+---------------------------------------+----------------------+
| Innodb_buffer_pool_pages_data         | 279103               |
| Innodb_buffer_pool_pages_dirty        | 131130               |
| Innodb_buffer_pool_pages_flushed      | 219913418            |
| Innodb_buffer_pool_pages_free         | 0                    |
| Innodb_buffer_pool_pages_misc         | 18446744073709509313 |
| Innodb_buffer_pool_pages_total        | 236800               |
...
[29 Nov 2011 20:02] Sveta Smirnova
Thank you for the feedback.

I noticed one more anomaly here: Innodb_buffer_pool_pages_data is greater than Innodb_buffer_pool_pages_total for all who experience this bug.
[29 Nov 2011 20:06] Marti Raudsepp
One more interesting fact: the pages_misc number goes back to normal during our nightly backup. But after the backup is done, it reverts back to this huge value.
[30 Nov 2011 19:38] Sveta Smirnova
Finally verified as described using original test case.

Probably action sequence matters, so will record it:

1. Install x86_64 Linux tar.gz package
2. Create empty datadir
3. Start mysqld as 

./bin/mysqld --defaults-file=support-files/my-small.cnf --basedir=. --datadir=./data --socket=/tmp/mysql_ssmirnova.sock --port=33051 --log-error   --thread_stack=1280000 --innodb_file_per_table --innodb_file_format=barracuda &

or

./bin/mysqld --defaults-file=support-files/my-small.cnf --basedir=. --datadir=./data --socket=/tmp/mysql_ssmirnova.sock --port=33051 --log-error   --thread_stack=1280000 --innodb_file_per_table --innodb_file_format=barracuda --ignore_builtin_innodb --plugin_load="innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so" &

4. Create database sbtest
5. prepare sysbench test as described:

 sysbench --test=/home/ssmirnov/blade12/src/sysbench/sysbench/tests/db/oltp_simple.lua --mysql-table-engine=innodb --oltp-table-size=10000000 --mysql-socket=/tmp/mysql_ssmirnova.sock --mysql-user=root --num-threads=8 prepare

6. Run ALTER, followed by SHOW STATUS as described.
[1 Mar 2013 14:38] Johan De Meersman
Seeing same on 5.5.24 on Debian Squeeze, with compression enabled on one huge table.

| Innodb_buffer_pool_pages_data         | 116915               |
| Innodb_buffer_pool_pages_dirty        | 206                  |
| Innodb_buffer_pool_pages_misc         | 18446744073709532994 |
| Innodb_buffer_pool_pages_total        | 98304                |

Calculating Innodb_buffer_pool_pages_total from Innodb_buffer_pool_size / Innodb_page_size shows that that value is correct.
[18 Apr 2013 8:16] Johan De Meersman
I've set up munin graphing of a number of innodb compression metrics since my last comment, and I see a very clear correlation between the wrongly-reported values and the number of compressed pages reported in the information_schema.

See the attached screenshot; note that the misc value graphed is calculated as specified in http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html#statvar_Innodb_buffer_... instead of taken from the buggy misc value - I only realised afterwards that _data is also buggy.

I'm thinking now that the values reported are not wrong-wrong, but possibly include the count of compressed pages as if they were normal ones, or something along those lines. The reporting of those two types of pages should be entirely separate, though, imho.
[18 Apr 2013 8:17] Johan De Meersman
Correlation InnoDB wrong bp pages vs compressed pages

Attachment: Correlation InnoDB wrong bp pages vs compressed pages.png (image/png, text), 95.86 KiB.

[9 Apr 2014 8:51] Marko Mäkelä
Posted by developer:
 
This is caused by compressed tables (row_format=compressed).

When we have compressed tables, we may discard the uncompressed page frame and only keep the compressed page in the buffer pool. When this happens, the length of the buf_pool->LRU list (which contains all file pages) can exceed the total number of buf_block_t allocated in the buffer pool. This is because the buf_page_t descriptors for compressed-only pages will be created and discarded on demand, not as part of buffer pool initialization.

The Innodb_buffer_pool_pages_data includes all pages in the LRU list. When there are compressed-only pages in the buffer pool, then we have more pages_data than the total number of buf_block_t page frames that are allocated in the buffer pool.

If the pages_misc is counted as the difference of total pages minus everything else, the compressed-only pages should not perhaps be subtracted, or they should only be subtracted as bytes, and divided by innodb_page_size later.

Note that the compressed-only page frames will be allocated from the buffer pool as BUF_BLOCK_MEMORY blocks (say, there can be four 4k compressed-only page frames within a single 16k block).

Note that we can have Innodb_buffer_pool_pages_data > Innodb_buffer_pool_pages_total for the same reason. I do not think that that "error" can be corrected. But, we can try to calculate the Innodb_buffer_pool_pages_misc in a better way.
[17 Jul 2014 22:14] Sveta Smirnova
Bug #72423 was marked as duplicate of this one.
[12 Jul 2017 16:07] Michael Riediger
Reproduced in 5.6.35.
[21 Sep 2017 2:56] Kenan Yao
TXSQL team at Tencent resolves this issue by computing innodb_buffer_pool_pages_data as:

export_vars.innodb_buffer_pool_pages_data = (ulint) ceil(export_vars.innodb_buffer_pool_bytes_data/(double)UNIV_PAGE_SIZE);

and by computing innodb_buffer_pool_pages_misc as:

export_vars.innodb_buffer_pool_pages_misc = buf_pool_get_n_pages() - export_vars.innodb_buffer_pool_pages_data - free_len;

According to the implementation of buddy allocator, there can be at most 4 pages of UNIV_PAGE_SIZE(let's say it is default value 16k) which can be not full, and each must have 1k, 2k, 4k and 8k space free respectively, so in total, at most 15k additional space can be allocated from buffer pool but not accounted in innodb_buffer_pool_bytes_data, so ceil(innodb_buffer_pool_bytes_data/UNIV_PAGE_SIZE) must be the accurate number of pages used by LRU and unzip_LRU.
[15 Jul 2019 12:03] Vinicius Malvestio Grippa
Version 5.7.26 still being affected.
[23 Jun 2020 6:10] Jiri Sula
8.0.19 confirming too.