| 1 |
# populate with data |
| 2 |
CREATE TABLE t1 ( |
| 3 |
c01 blob, c02 blob, c03 blob, c04 blob, c05 blob, |
| 4 |
c06 blob, c07 blob, c08 blob, c09 blob, c10 blob, |
| 5 |
c11 blob, c12 blob, c13 blob, c14 blob, c15 blob, |
| 6 |
c16 blob, c17 blob, c18 blob, c19 blob, c20 blob, |
| 7 |
c21 blob, c22 blob, c23 blob, c24 blob, c25 blob, |
| 8 |
c26 blob, c27 blob, c28 blob, c29 blob, c30 blob, |
| 9 |
c31 blob, c32 blob, c33 blob, c34 blob, c35 blob, |
| 10 |
c36 blob, c37 blob, c38 blob, c39 blob, c40 blob, |
| 11 |
c41 blob, c42 blob, c43 blob, c44 blob, c45 blob, |
| 12 |
c46 blob, c47 blob, c48 blob, c49 blob, c50 blob, |
| 13 |
c51 blob, c52 blob, c53 blob, c54 blob, c55 blob, |
| 14 |
c56 blob, c57 blob, c58 blob, c59 blob, c60 blob, |
| 15 |
c61 blob, c62 blob, c63 blob, c64 blob |
| 16 |
) ROW_FORMAT=dynamic; |
| 17 |
SET @a = repeat('a', 16 * 1024); |
| 18 |
INSERT INTO t1 VALUES (@a,@a,@a,@a,@a, |
| 19 |
@a,@a,@a,@a,@a, |
| 20 |
@a,@a,@a,@a,@a, |
| 21 |
@a,@a,@a,@a,@a, |
| 22 |
@a,@a,@a,@a,@a, |
| 23 |
@a,@a,@a,@a,@a, |
| 24 |
@a,@a,@a,@a,@a, |
| 25 |
@a,@a,@a,@a,@a, |
| 26 |
@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 |
| 31 |
); |
| 32 |
insert into t1 select * from t1; |
| 33 |
SET GLOBAL innodb_buffer_pool_dump_pct=25,GLOBAL innodb_fast_shutdown=0, GLOBAL innodb_buffer_pool_dump_at_shutdown=1; |
| 34 |
# Restart server |
| 35 |
SET GLOBAL innodb_buffer_pool_load_now=1; |
| 36 |
# Case 1: Limit from innodb_buffer_pool_dump_pct |
| 37 |
SELECT 256 as 'Previous pool pages', |
| 38 |
ROUND(25 / 100 * @@innodb_buffer_pool_size / @@innodb_page_size) as 'Limit', |
| 39 |
128 as 'Pages saved'; |
| 40 |
Previous pool pages Limit Pages saved |
| 41 |
256 128 128 |
| 42 |
# Case 2: blog_pages is the minimum. |
| 43 |
# load buffer pool with entire table. |
| 44 |
select * from t1; |
| 45 |
SET GLOBAL innodb_buffer_pool_dump_pct=75, global innodb_buffer_pool_dump_at_shutdown=1; |
| 46 |
# Restart server |
| 47 |
SET GLOBAL innodb_buffer_pool_load_now=1; |
| 48 |
# now loaded |
| 49 |
SELECT 256 as 'Previous pool pages', |
| 50 |
ROUND(75 / 100 * @@innodb_buffer_pool_size / @@innodb_page_size) as 'Limit', |
| 51 |
256 as 'Pages saved'; |
| 52 |
Previous pool pages Limit Pages saved |
| 53 |
256 384 256 |
| 54 |
DROP TABLE t1; |