Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Tree: 48996cad34
Fetching contributors…

Cannot retrieve contributors at this time

135 lines (132 sloc) 5.563 kB
#
# Bug#67718: InnoDB drastically under-fills pages in certain conditions
#
SET @old_innodb_file_per_table = @@GLOBAL.innodb_file_per_table;
SET GLOBAL innodb_file_per_table = ON;
SET @old_innodb_index_page_split_mode = @@GLOBAL.innodb_index_page_split_mode;
SET GLOBAL innodb_index_page_split_mode = "symmetric,lower,upper";
CREATE TABLE page_split_test
(
id BIGINT UNSIGNED NOT NULL,
payload1 CHAR(255) NOT NULL,
payload2 CHAR(255) NOT NULL,
payload3 CHAR(255) NOT NULL,
payload4 CHAR(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB;
# Fill up the root page, but don't split it.
INSERT INTO page_split_test VALUES (1, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
INSERT INTO page_split_test VALUES (2, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
INSERT INTO page_split_test VALUES (3, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
INSERT INTO page_split_test VALUES (4, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
INSERT INTO page_split_test VALUES (5, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
INSERT INTO page_split_test VALUES (6, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
INSERT INTO page_split_test VALUES (7, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
INSERT INTO page_split_test VALUES (8, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
INSERT INTO page_split_test VALUES (9, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
INSERT INTO page_split_test VALUES (10, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
INSERT INTO page_split_test VALUES (11, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
INSERT INTO page_split_test VALUES (12, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
INSERT INTO page_split_test VALUES (13, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
INSERT INTO page_split_test VALUES (14, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
# The next insert will trigger the root page to be split into a root
# non-leaf page and two leaf pages, both about half full.
INSERT INTO page_split_test VALUES (15, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
# Insert enough records to fill up the right-most leaf page, but don't
# split it.
INSERT INTO page_split_test VALUES (16, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
INSERT INTO page_split_test VALUES (17, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
INSERT INTO page_split_test VALUES (18, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
INSERT INTO page_split_test VALUES (19, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
INSERT INTO page_split_test VALUES (20, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
INSERT INTO page_split_test VALUES (21, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
# Increase the id by some amount, and insert a few records in descending
# order from that point. Each row inserted would split the "full" page
# from above, and create a new page for the single record.
INSERT INTO page_split_test VALUES (30, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
INSERT INTO page_split_test VALUES (29, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
INSERT INTO page_split_test VALUES (28, REPEAT("A", 255), REPEAT("B", 255),
REPEAT("C", 255), REPEAT("D", 255));
# Dump the page information about the test table. The right-most page
# has only one record as the record (key 30) was inserted in ascending
# order. The remaining nodes are split in half and filled by the later
# inserts.
SELECT page_number, page_type, number_records
FROM information_schema.innodb_buffer_page
WHERE table_name = "test/page_split_test" AND index_name = "PRIMARY"
ORDER BY page_number;
page_number page_type number_records
3 INDEX 4
4 INDEX 7
5 INDEX 7
6 INDEX 1
7 INDEX 9
DROP TABLE page_split_test;
#
# Test page split behavior when keys are being inserted in order.
#
CREATE TABLE page_split_test
(
id BIGINT UNSIGNED NOT NULL,
payload CHAR(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB;
# Populate table with 300 rows in ascending key order.
# Space utilization is the same across leaf pages, except at the end
# since it wasn't completely filled up.
SELECT page_number, page_type, number_records
FROM information_schema.innodb_buffer_page
WHERE table_name = "test/page_split_test" AND index_name = "PRIMARY"
ORDER BY page_number;
page_number page_type number_records
3 INDEX 7
4 INDEX 27
5 INDEX 54
6 INDEX 54
7 INDEX 54
8 INDEX 54
9 INDEX 54
10 INDEX 3
TRUNCATE TABLE page_split_test;
# Populate table with 300 rows in descending key order.
# Space utilization is the same across leaf pages, except at the end
# since it wasn't completely filled up.
SELECT page_number, page_type, number_records
FROM information_schema.innodb_buffer_page
WHERE table_name = "test/page_split_test" AND index_name = "PRIMARY"
ORDER BY page_number;
page_number page_type number_records
3 INDEX 7
4 INDEX 54
5 INDEX 27
6 INDEX 54
7 INDEX 54
8 INDEX 54
9 INDEX 54
10 INDEX 3
DROP TABLE page_split_test;
SET GLOBAL innodb_file_per_table = @old_innodb_file_per_table;
SET GLOBAL innodb_index_page_split_mode = @old_innodb_index_page_split_mode;
Jump to Line
Something went wrong with that request. Please try again.