| 1 |
CREATE TABLE t1 (pk INT PRIMARY KEY, data BLOB); |
| 2 |
CREATE PROCEDURE populate() |
| 3 |
BEGIN |
| 4 |
DECLARE p1 INT DEFAULT 1; |
| 5 |
label1: loop |
| 6 |
INSERT INTO t1 VALUES (p1, REPEAT('a', 3200)); |
| 7 |
SET p1 = p1 + 1; |
| 8 |
IF p1 < 10000 THEN |
| 9 |
iterate label1; |
| 10 |
END IF; |
| 11 |
leave label1; |
| 12 |
end loop label1; |
| 13 |
END;| |
| 14 |
CALL populate(); |
| 15 |
# Generate data into temporary file |
| 16 |
SET GLOBAL innodb_segment_reserve_factor = 1.0; |
| 17 |
CREATE TABLE t2 LIKE t1; |
| 18 |
# Load data into t2 from temporary file |
| 19 |
SET GLOBAL innodb_segment_reserve_factor = 30.0; |
| 20 |
CREATE TABLE t3 LIKE t1; |
| 21 |
# Load data into t3 from temporary file |
| 22 |
SET innodb_interpreter = 'init'; |
| 23 |
SET innodb_interpreter = 'count_page_type test/t2'; |
| 24 |
SELECT @@innodb_interpreter_output; |
| 25 |
@@innodb_interpreter_output |
| 26 |
FIL_PAGE_TYPE_ALLOCATED: 628 |
| 27 |
FIL_PAGE_INODE: 1 |
| 28 |
FIL_PAGE_IBUF_BITMAP: 1 |
| 29 |
FIL_PAGE_TYPE_FSP_HDR: 1 |
| 30 |
FIL_PAGE_SDI: 1 |
| 31 |
FIL_PAGE_INDEX: 3208 |
| 32 |
Total: 3840 |
| 33 |
|
| 34 |
SET innodb_interpreter = 'count_used_and_free test/t2'; |
| 35 |
SELECT @@innodb_interpreter_output; |
| 36 |
@@innodb_interpreter_output |
| 37 |
Total= 3840, used=3212, free=628 |
| 38 |
Fill factor= 83.6458, free factor= 16.3542 |
| 39 |
|
| 40 |
SET innodb_interpreter = 'count_page_type test/t3'; |
| 41 |
SELECT @@innodb_interpreter_output; |
| 42 |
@@innodb_interpreter_output |
| 43 |
FIL_PAGE_TYPE_ALLOCATED: 1908 |
| 44 |
FIL_PAGE_INODE: 1 |
| 45 |
FIL_PAGE_IBUF_BITMAP: 1 |
| 46 |
FIL_PAGE_TYPE_FSP_HDR: 1 |
| 47 |
FIL_PAGE_SDI: 1 |
| 48 |
FIL_PAGE_INDEX: 3208 |
| 49 |
Total: 5120 |
| 50 |
|
| 51 |
SET innodb_interpreter = 'count_used_and_free test/t3'; |
| 52 |
SELECT @@innodb_interpreter_output; |
| 53 |
@@innodb_interpreter_output |
| 54 |
Total= 5120, used=3212, free=1908 |
| 55 |
Fill factor= 62.7344, free factor= 37.2656 |
| 56 |
|
| 57 |
SET innodb_interpreter = 'destroy'; |
| 58 |
SET GLOBAL innodb_segment_reserve_factor = DEFAULT; |
| 59 |
DROP TABLE t1, t2, t3; |
| 60 |
DROP PROCEDURE populate; |