1 2# innodb_buffer_pool_load_incomplete defaults 0 3SELECT variable_name, variable_value 4FROM information_schema.global_status 5WHERE LOWER(variable_name) = 'innodb_buffer_pool_load_incomplete'; 6variable_name variable_value 7INNODB_BUFFER_POOL_LOAD_INCOMPLETE OFF 8 9# populate with data 10CREATE TABLE t1 ( 11c01 blob, c02 blob, c03 blob, c04 blob, c05 blob, 12c06 blob, c07 blob, c08 blob, c09 blob, c10 blob, 13c11 blob, c12 blob, c13 blob, c14 blob, c15 blob, 14c16 blob, c17 blob, c18 blob, c19 blob, c20 blob, 15c21 blob, c22 blob, c23 blob, c24 blob, c25 blob, 16c26 blob, c27 blob, c28 blob, c29 blob, c30 blob, 17c31 blob, c32 blob, c33 blob, c34 blob, c35 blob, 18c36 blob, c37 blob, c38 blob, c39 blob, c40 blob, 19c41 blob, c42 blob, c43 blob, c44 blob, c45 blob, 20c46 blob, c47 blob, c48 blob, c49 blob, c50 blob, 21c51 blob, c52 blob, c53 blob, c54 blob, c55 blob, 22c56 blob, c57 blob, c58 blob, c59 blob, c60 blob, 23c61 blob, c62 blob, c63 blob, c64 blob 24) ROW_FORMAT=dynamic; 25SET @a = repeat('a', 16 * 1024); 26INSERT INTO t1 VALUES (@a,@a,@a,@a,@a, 27@a,@a,@a,@a,@a, 28@a,@a,@a,@a,@a, 29@a,@a,@a,@a,@a, 30@a,@a,@a,@a,@a, 31@a,@a,@a,@a,@a, 32@a,@a,@a,@a,@a, 33@a,@a,@a,@a,@a, 34@a,@a,@a,@a,@a, 35@a,@a,@a,@a,@a, 36@a,@a,@a,@a,@a, 37@a,@a,@a,@a,@a, 38@a,@a,@a,@a 39); 40SET GLOBAL innodb_buffer_pool_dump_now=1; 41 42# Restart server 43 44# Abort after 16 pages 45SET GLOBAL innodb_buffer_pool_load_pages_abort=16, 46GLOBAL innodb_buffer_pool_load_now=1, 47GLOBAL innodb_buffer_pool_dump_at_shutdown=1; 48SELECT variable_name, SUBSTR(variable_value, 1, 38) as VALUE 49FROM information_schema.global_status 50WHERE LOWER(variable_name) IN ('innodb_buffer_pool_load_incomplete','innodb_buffer_pool_load_status') 51ORDER BY variable_name; 52variable_name VALUE 53INNODB_BUFFER_POOL_LOAD_INCOMPLETE ON 54INNODB_BUFFER_POOL_LOAD_STATUS Buffer pool(s) load aborted on request 55 56# Restart server 57 58# Load buffer pool 59SET GLOBAL innodb_buffer_pool_load_now=1; 60 61# Should be more than previous as we didn't overwrite our save file 62select count(*) > Previous_loaded as Loaded_more from information_schema.INNODB_BUFFER_PAGE WHERE PAGE_TYPE='BLOB' group by PAGE_TYPE;; 63Loaded_more 641 65 66# Successful, so innodb_buffer_pool_load_incomplete should be FALSE 67SELECT variable_name, SUBSTR(variable_value, 1, 33) as VALUE 68FROM information_schema.global_status 69WHERE LOWER(variable_name) IN ('innodb_buffer_pool_load_incomplete','innodb_buffer_pool_load_status') 70ORDER BY variable_name; 71variable_name VALUE 72INNODB_BUFFER_POOL_LOAD_INCOMPLETE OFF 73INNODB_BUFFER_POOL_LOAD_STATUS Buffer pool(s) load completed at 74 75# innodb_buffer_pool_dump_now=1 should reset the innodb_buffer_pool_load_incomplete status 76SET GLOBAL innodb_buffer_pool_dump_now=1; 77SELECT variable_name, SUBSTR(variable_value, 1, 33) as VALUE 78FROM information_schema.global_status 79WHERE LOWER(variable_name) IN ('innodb_buffer_pool_load_incomplete', 'innodb_buffer_pool_dump_status'); 80variable_name VALUE 81INNODB_BUFFER_POOL_DUMP_STATUS Buffer pool(s) dump completed at 82INNODB_BUFFER_POOL_LOAD_INCOMPLETE OFF 83 84# Restart server 85 86# Load buffer pool 87SET GLOBAL innodb_buffer_pool_load_now=1; 88 89# Should be same amount 90select abs(Previously_dumped - count(*)) <= 2 as Loaded_about_same_size from information_schema.INNODB_BUFFER_PAGE WHERE PAGE_TYPE='BLOB' group by PAGE_TYPE;; 91Loaded_about_same_size 921 93 94# Clean up 95DROP TABLE t1; 96