[MDEV-11454] quick start/stop with innodb_buffer_pool_dump_pct < 100 results in suboptimal dump file Created: 2016-12-02  Updated: 2022-02-15

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

Type: Bug Priority: Major
Reporter: Daniel Black Assignee: Daniel Black
Resolution: Unresolved Votes: 0
Labels: contribution, foundation


 Description   

With innodb_buffer_pool_dump_pct say 25% (the default since 10.2.2), a server started will restore 25% of the buffer pool size with the expectation that over time the rest of the buffer pool will be populated. Then on shutdown 25% will be saved.

If a server is started and then is shutdown a) without much activity occurring b) is started as a hot spare and shutdown before being used, then 6.25% (25% of 25%) of the buffer pool is saved.

Proposed solution:

Rather than innodb_buffer_pool_dump_pct referring to the percentage of hot data in the buffer pool, it refers to the entire buffer pool size. This means that a completed load followed by a shutdown will write the exact same data.

This will generate bigger dump files for users who don't have a full innodb_buffer_pool however a realistic scenario is a buffer pool should be completely used.

 Comments   
Comment by Daniel Black [ 2017-02-15 ]

Approved by jplindst on github. Can this be merged?

Comment by Marko Mäkelä [ 2017-02-24 ]

Sorry for the delay. I had totally forgotten about this one.

Comment by Marko Mäkelä [ 2017-02-24 ]

I disabled the added test sys_vars.innodb_buffer_pool_dump_pct_function, because it is unstable on buildbot. Can you try to fix it?

Comment by Daniel Black [ 2017-02-25 ]

Ack, can you show me some of the failed urls please? I'm having trouble finding the applicable ones.

Comment by Marko Mäkelä [ 2017-02-28 ]

These two are displaying unstable results.
http://buildbot.askmonty.org/buildbot/builders/p8-rhel7-bintar-debug/builds/2193
http://buildbot.askmonty.org/buildbot/builders/kvm-fulltest2/builds/7238

Comment by Daniel Black [ 2017-03-01 ]

http://buildbot.askmonty.org/buildbot/builders/p8-rhel7-bintar-debug/builds/2193/steps/test/logs/mysqld.1.err.3 shows 128 pages dumped. Not sure why only 124 pages where loaded yet.

http://buildbot.askmonty.org/buildbot/builders/kvm-fulltest2/builds/7238/steps/test_3/logs/mysqld.1.err.2 shows only 127 pages dumped so that needs to be fixed too.

Thanks marko. I am still looking at this.

Comment by Marko Mäkelä [ 2021-09-07 ]

danblack, are you still looking at this? I noticed that the test is still disabled, while waiting for the results for a MDEV-26547 fixup, to address a failure of the test sys_vars.innodb_buffer_pool_dump_abort_loads.

Comment by Daniel Black [ 2021-09-07 ]

Obviously haven't for a while. Maybe some of the page/chunk sizing it has magicly made it better. I'll take a look tomorrow.

Comment by Daniel Black [ 2022-02-09 ]

10.2 still varying results

innodb_buffer_pool_dump_pct_function - 10.2.43-MariaDB

 
 - SSL connections supported
 
 - binaries built with wsrep patch
 
Collecting tests...
 
 - sys_vars.innodb_buffer_pool_dump_pct_function wil be run although it's been disabled
 
   due to 'MDEV-11454 follow-up needed (unstable)'
 
Installing system database...
 
 
 
==============================================================================
 
 
 
TEST                                      RESULT   TIME (ms) or COMMENT
 
--------------------------------------------------------------------------
 
 
 
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
 
sys_vars.innodb_buffer_pool_dump_pct_function 'innodb' [ pass ]   5225
 
sys_vars.innodb_buffer_pool_dump_pct_function 'innodb' [ fail ]
 
        Test ended at 2022-02-09 12:44:12
 
 
 
CURRENT_TEST: sys_vars.innodb_buffer_pool_dump_pct_function
 
--- /home/dan/repos/mariadb-server-10.2_second/mysql-test/suite/sys_vars/r/innodb_buffer_pool_dump_pct_function.result	2020-10-21 11:53:36.123736898 +1100
 
+++ /home/dan/repos/mariadb-server-10.2_second/mysql-test/suite/sys_vars/r/innodb_buffer_pool_dump_pct_function.reject	2022-02-09 12:44:12.631033650 +1100
 
@@ -34,11 +34,11 @@
 
 # Restart server
 
 SET GLOBAL innodb_buffer_pool_load_now=1;
 
 # Case 1: Limit from innodb_buffer_pool_dump_pct
 
-SELECT 256 as 'Previous pool pages',
 
+SELECT 464 as 'Previous pool pages',
 
 ROUND(25 / 100 * @@innodb_buffer_pool_size / @@innodb_page_size) as 'Limit',
 
-128 as 'Pages saved';
 
+127 as 'Pages saved';
 
 Previous pool pages	Limit	Pages saved
 
-256	128	128
 
+464	128	127
 
 # Case 2: blog_pages is the minimum.
 
 # load buffer pool with entire table.
 
 select * from t1;
 
 
 
mysqltest: Result content mismatch
 
 
 
 - saving '/home/dan/repos/build-mariadb-server-10.2_second/mysql-test/var/log/sys_vars.innodb_buffer_pool_dump_pct_function-innodb/' to '/home/dan/repos/build-mariadb-server-10.2_second/mysql-test/var/log/sys_vars.innodb_buffer_pool_dump_pct_function-innodb/'
 
sys_vars.innodb_buffer_pool_dump_pct_function 'innodb' [ pass ]   5109
 
sys_vars.innodb_buffer_pool_dump_pct_function 'innodb' [ fail ]
 
        Test ended at 2022-02-09 12:44:23
 
 
 
CURRENT_TEST: sys_vars.innodb_buffer_pool_dump_pct_function
 
--- /home/dan/repos/mariadb-server-10.2_second/mysql-test/suite/sys_vars/r/innodb_buffer_pool_dump_pct_function.result	2020-10-21 11:53:36.123736898 +1100
 
+++ /home/dan/repos/mariadb-server-10.2_second/mysql-test/suite/sys_vars/r/innodb_buffer_pool_dump_pct_function.reject	2022-02-09 12:44:23.694048987 +1100
 
@@ -34,11 +34,11 @@
 
 # Restart server
 
 SET GLOBAL innodb_buffer_pool_load_now=1;
 
 # Case 1: Limit from innodb_buffer_pool_dump_pct
 
-SELECT 256 as 'Previous pool pages',
 
+SELECT 460 as 'Previous pool pages',
 
 ROUND(25 / 100 * @@innodb_buffer_pool_size / @@innodb_page_size) as 'Limit',
 
-128 as 'Pages saved';
 
+125 as 'Pages saved';
 
 Previous pool pages	Limit	Pages saved
 
-256	128	128
 
+460	128	125
 
 # Case 2: blog_pages is the minimum.
 
 # load buffer pool with entire table.
 
 select * from t1;
 
 
 
mysqltest: Result content mismatch
 
 
 
 - saving '/home/dan/repos/build-mariadb-server-10.2_second/mysql-test/var/log/sys_vars.innodb_buffer_pool_dump_pct_function-innodb/' to '/home/dan/repos/build-mariadb-server-10.2_second/mysql-test/var/log/sys_vars.innodb_buffer_pool_dump_pct_function-innodb/'
 
sys_vars.innodb_buffer_pool_dump_pct_function 'innodb' [ pass ]   5096
 
sys_vars.innodb_buffer_pool_dump_pct_function 'innodb' [ fail ]
 
        Test ended at 2022-02-09 12:44:34
 
 
 
CURRENT_TEST: sys_vars.innodb_buffer_pool_dump_pct_function
 
--- /home/dan/repos/mariadb-server-10.2_second/mysql-test/suite/sys_vars/r/innodb_buffer_pool_dump_pct_function.result	2020-10-21 11:53:36.123736898 +1100
 
+++ /home/dan/repos/mariadb-server-10.2_second/mysql-test/suite/sys_vars/r/innodb_buffer_pool_dump_pct_function.reject	2022-02-09 12:44:34.567064059 +1100
 
@@ -34,11 +34,11 @@
 
 # Restart server
 
 SET GLOBAL innodb_buffer_pool_load_now=1;
 
 # Case 1: Limit from innodb_buffer_pool_dump_pct
 
-SELECT 256 as 'Previous pool pages',
 
+SELECT 467 as 'Previous pool pages',
 
 ROUND(25 / 100 * @@innodb_buffer_pool_size / @@innodb_page_size) as 'Limit',
 
-128 as 'Pages saved';
 
+127 as 'Pages saved';
 
 Previous pool pages	Limit	Pages saved
 
-256	128	128
 
+467	128	127
 
 # Case 2: blog_pages is the minimum.
 
 # load buffer pool with entire table.
 
 select * from t1;
 
 
 
mysqltest: Result content mismatch
 
 
 
 - saving '/home/dan/repos/build-mariadb-server-10.2_second/mysql-test/var/log/sys_vars.innodb_buffer_pool_dump_pct_function-innodb/' to '/home/dan/repos/build-mariadb-server-10.2_second/mysql-test/var/log/sys_vars.innodb_buffer_pool_dump_pct_function-innodb/'
 
sys_vars.innodb_buffer_pool_dump_pct_function 'innodb' [ fail ]
 
        Test ended at 2022-02-09 12:44:40
 
 
 
CURRENT_TEST: sys_vars.innodb_buffer_pool_dump_pct_function
 
--- /home/dan/repos/mariadb-server-10.2_second/mysql-test/suite/sys_vars/r/innodb_buffer_pool_dump_pct_function.result	2020-10-21 11:53:36.123736898 +1100
 
+++ /home/dan/repos/mariadb-server-10.2_second/mysql-test/suite/sys_vars/r/innodb_buffer_pool_dump_pct_function.reject	2022-02-09 12:44:40.148071797 +1100
 
@@ -36,9 +36,9 @@
 
 # Case 1: Limit from innodb_buffer_pool_dump_pct
 
 SELECT 256 as 'Previous pool pages',
 
 ROUND(25 / 100 * @@innodb_buffer_pool_size / @@innodb_page_size) as 'Limit',
 
-128 as 'Pages saved';
 
+127 as 'Pages saved';
 
 Previous pool pages	Limit	Pages saved
 
-256	128	128
 
+256	128	127
 
 # Case 2: blog_pages is the minimum.
 
 # load buffer pool with entire table.
 
 select * from t1;
 
 
 
mysqltest: Result content mismatch
 
 
 
 - saving '/home/dan/repos/build-mariadb-server-10.2_second/mysql-test/var/log/sys_vars.innodb_buffer_pool_dump_pct_function-innodb/' to '/home/dan/repos/build-mariadb-server-10.2_second/mysql-test/var/log/sys_vars.innodb_buffer_pool_dump_pct_function-innodb/'
 
sys_vars.innodb_buffer_pool_dump_pct_function 'innodb' [ pass ]   5124
 
sys_vars.innodb_buffer_pool_dump_pct_function 'innodb' [ fail ]
 
        Test ended at 2022-02-09 12:44:51
 
 
 
CURRENT_TEST: sys_vars.innodb_buffer_pool_dump_pct_function
 
--- /home/dan/repos/mariadb-server-10.2_second/mysql-test/suite/sys_vars/r/innodb_buffer_pool_dump_pct_function.result	2020-10-21 11:53:36.123736898 +1100
 
+++ /home/dan/repos/mariadb-server-10.2_second/mysql-test/suite/sys_vars/r/innodb_buffer_pool_dump_pct_function.reject	2022-02-09 12:44:51.029086880 +1100
 
@@ -34,11 +34,11 @@
 
 # Restart server
 
 SET GLOBAL innodb_buffer_pool_load_now=1;
 
 # Case 1: Limit from innodb_buffer_pool_dump_pct
 
-SELECT 256 as 'Previous pool pages',
 
+SELECT 465 as 'Previous pool pages',
 
 ROUND(25 / 100 * @@innodb_buffer_pool_size / @@innodb_page_size) as 'Limit',
 
-128 as 'Pages saved';
 
+127 as 'Pages saved';
 
 Previous pool pages	Limit	Pages saved
 
-256	128	128
 
+465	128	127
 
 # Case 2: blog_pages is the minimum.
 
 # load buffer pool with entire table.
 
 select * from t1;
 
 
 
mysqltest: Result content mismatch
 
 
 
 - saving '/home/dan/repos/build-mariadb-server-10.2_second/mysql-test/var/log/sys_vars.innodb_buffer_pool_dump_pct_function-innodb/' to '/home/dan/repos/build-mariadb-server-10.2_second/mysql-test/var/log/sys_vars.innodb_buffer_pool_dump_pct_function-innodb/'
 
sys_vars.innodb_buffer_pool_dump_pct_function 'innodb' [ fail ]
 
        Test ended at 2022-02-09 12:44:56
 
 
 
CURRENT_TEST: sys_vars.innodb_buffer_pool_dump_pct_function
 
--- /home/dan/repos/mariadb-server-10.2_second/mysql-test/suite/sys_vars/r/innodb_buffer_pool_dump_pct_function.result	2020-10-21 11:53:36.123736898 +1100
 
+++ /home/dan/repos/mariadb-server-10.2_second/mysql-test/suite/sys_vars/r/innodb_buffer_pool_dump_pct_function.reject	2022-02-09 12:44:56.782094854 +1100
 
@@ -36,9 +36,9 @@
 
 # Case 1: Limit from innodb_buffer_pool_dump_pct
 
 SELECT 256 as 'Previous pool pages',
 
 ROUND(25 / 100 * @@innodb_buffer_pool_size / @@innodb_page_size) as 'Limit',
 
-128 as 'Pages saved';
 
+127 as 'Pages saved';
 
 Previous pool pages	Limit	Pages saved
 
-256	128	128
 
+256	128	127
 
 # Case 2: blog_pages is the minimum.
 
 # load buffer pool with entire table.
 
 select * from t1;
 
 
 
mysqltest: Result content mismatch
 
 
 
 - saving '/home/dan/repos/build-mariadb-server-10.2_second/mysql-test/var/log/sys_vars.innodb_buffer_pool_dump_pct_function-innodb/' to '/home/dan/repos/build-mariadb-server-10.2_second/mysql-test/var/log/sys_vars.innodb_buffer_pool_dump_pct_function-innodb/'
 
--------------------------------------------------------------------------
 
The servers were restarted 1 times
 
Spent 20.554 of 64 seconds executing testcases
 
 
 
Completed: Failed 6/10 tests, 40.00% were successful.
 
 
 
Failing test(s): sys_vars.innodb_buffer_pool_dump_pct_function

select count from information_schema.INNODB_BUFFER_PAGE WHERE PAGE_TYPE='BLOB' is always empty resulting in syntax error. Manual testing doesn't exhibit same results.

10.5.14 always failing (5/5)

 
CURRENT_TEST: sys_vars.innodb_buffer_pool_dump_pct_function
 
mysqltest: At line 70: query 'SELECT $blob_pages as 'Previous pool pages',
 
ROUND($PCT / 100 * @@innodb_buffer_pool_size / @@innodb_page_size) as 'Limit',
 
$loaded_blob_pages as 'Pages saved'' failed: ER_PARSE_ERROR (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as 'Pages saved'' at line 3

Generated at Thu Feb 08 07:50:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.