innodb/r/percona_changed_pages.result

# restart
RESET CHANGED_PAGE_BITMAPS;
DROP TABLE IF EXISTS T1, ICP_COPY;
# restart
CREATE TABLE T1 (F1 CHAR(255)) ENGINE=INNODB;
# restart
1st interval end LSN greater than interval start LSN:
should_be_1
1
# restart
# restart
# restart
log record interval start LSN should remain constant across the previous restarts
should_be_1
1
INSERT INTO T1 VALUES (REPEAT('A', 255));
# restart
log record interval start LSN should remain constant after workload
should_be_1
1
log record interval end LSN should have advanced after workload
should_be_1
1
At least some of the records should be attributed to T1
should_be_1
1
INSERT INTO T1 VALUES (REPEAT('B', 255));
# restart
log record interval start LSN should remain constant after workload
should_be_1
1
log record interval end LSN should have advanced after workload
should_be_1
1
INSERT INTO T1 VALUES (REPEAT('C', 255));
# restart
SELECT VARIABLE_VALUE INTO @start_max_trx_id FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_max_trx_id';
log record interval start LSN should remain constant after workload
should_be_1
1
log record interval end LSN should have advanced after workload
should_be_1
1
At least some of the records should be attributed to T1
should_be_1
1
Total number of changed pages is smaller than reasonable limit (should be 1):
COUNT(DISTINCT PAGE_ID) < 10
1
Check if the maximum page id is less than resonable limit (should be 1):
MAX(PAGE_ID) < 10
1
Check that the combination of the two above does not violate the pigeon hole principle (should be 1):
MAX(PAGE_ID) + 1 >= COUNT(DISTINCT PAGE_ID)
1
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE START_LSN >= END_LSN;
COUNT(*)
0
SELECT * FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE START_LSN < 10 AND END_LSN > 20;
space_id	page_id	start_lsn	end_lsn
ICP tests (all should be 1):
SELECT COUNT(*) = @cond_test_pages_count
FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE END_LSN = @max_end_lsn;
COUNT(*) = @cond_test_pages_count
1
SELECT COUNT(*) = @cond_test_pages_count
FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE
END_LSN > (@max_end_lsn - 1) AND
END_LSN < (@max_end_lsn + 1);
COUNT(*) = @cond_test_pages_count
1
SELECT COUNT(*) = @cond_test_pages_count
FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE
END_LSN >= @max_end_lsn AND
END_LSN <= @max_end_lsn;
COUNT(*) = @cond_test_pages_count
1
SELECT COUNT(*) = @cond_test_pages_count
FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE
(@max_end_lsn - 1) < END_LSN AND
@max_end_lsn >= END_LSN;
COUNT(*) = @cond_test_pages_count
1
SELECT COUNT(*) = @cond_test_pages_count
FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE
START_LSN <= @max_end_lsn AND
END_LSN >= @max_end_lsn;
COUNT(*) = @cond_test_pages_count
1
SELECT COUNT(*) = @cond_test_pages_count
FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE
START_LSN >= @cond_test_max_start_lsn;
COUNT(*) = @cond_test_pages_count
1
SELECT COUNT(*) = @cond_test_pages_count
FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE
START_LSN > (@cond_test_max_start_lsn - 1);
COUNT(*) = @cond_test_pages_count
1
SELECT COUNT(*) = @cond_test_pages_count
FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE
START_LSN >= @cond_test_max_start_lsn AND
END_LSN <= @max_end_lsn;
COUNT(*) = @cond_test_pages_count
1
SELECT COUNT(*) = @cond_test_pages_count
FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE
START_LSN > (@cond_test_max_start_lsn - 1) AND
END_LSN <= @max_end_lsn;
COUNT(*) = @cond_test_pages_count
1
SELECT COUNT(*) = @cond_test_pages_count
FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE
START_LSN >= @cond_test_max_start_lsn AND
END_LSN < (@max_end_lsn + 1);
COUNT(*) = @cond_test_pages_count
1
SELECT COUNT(*) = @cond_test_pages_count
FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE
START_LSN >= @cond_test_max_start_lsn AND
END_LSN >= @cond_test_max_start_lsn;
COUNT(*) = @cond_test_pages_count
1
SELECT COUNT(*) = @cond_test_pages_count
FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE
@cond_test_max_start_lsn <= START_LSN AND
@max_end_lsn >= END_LSN;
COUNT(*) = @cond_test_pages_count
1
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE
START_LSN > @cond_test_max_lsn AND
END_LSN < @cond_test_max_lsn;
COUNT(*)
0
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE
START_LSN > @cond_test_max_lsn + 10 AND
END_LSN < @cond_test_max_lsn - 10;
COUNT(*)
0
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE
START_LSN <= (@min_start_lsn - 1);
COUNT(*)
0
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE
START_LSN < @min_start_lsn;
COUNT(*)
0
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE
END_LSN > @max_end_lsn;
COUNT(*)
0
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE
END_LSN >= (@max_end_lsn + 1);
COUNT(*)
0
SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE START_LSN = (@min_start_lsn - 1);
COUNT(*)
0
SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE START_LSN < @min_start_lsn OR END_LSN > @max_end_lsn;
COUNT(*)
0
SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE START_LSN <= (@min_start_lsn - 1) OR END_LSN > @max_end_lsn;
COUNT(*)
0
SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE START_LSN < @min_start_lsn OR END_LSN >= (@max_end_lsn + 1);
COUNT(*)
0
SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE START_LSN <= (@min_start_lsn - 1) OR END_LSN >= (@max_end_lsn + 1);
COUNT(*)
0
SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE (START_LSN > @cond_test_max_lsn AND END_LSN < @cond_test_max_lsn)
OR (START_LSN > @cond_test_max_lsn + 10 AND END_LSN < @cond_test_max_lsn - 10);
COUNT(*)
0
SELECT @total_pages=@r1_r4_pages AS should_be_1;
should_be_1
1
SELECT @r1_r2_pages+@r2_r3_pages+@r3_r4_pages=@r1_r4_pages AS should_be_1;
should_be_1
1
SELECT @r1_r2_pages+@r2_r3_pages=@r1_r3_pages AS should_be_1;
should_be_1
1
SELECT @r2_r3_pages+@r3_r4_pages=@r2_r4_pages AS should_be_1;
should_be_1
1
SET GLOBAL INNODB_MAX_CHANGED_PAGES = 1;
SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES;
COUNT(*)
1
SET GLOBAL INNODB_MAX_CHANGED_PAGES = 5;
SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES;
COUNT(*)
5
SET GLOBAL INNODB_MAX_CHANGED_PAGES = 1000000;
SELECT VARIABLE_VALUE INTO @end_max_trx_id FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_max_trx_id';
include/assert.inc [Maximum InnoDB transaction ID should not have moved during RO workload]
include/assert.inc [No bitmap data must exist with START_LSN > @max_end_lsn]
include/assert.inc [No bitmap data must exist with END_LSN > @max_end_lsn]
CREATE TABLE ICP_COPY (
space_id INT(11) NOT NULL,
page_id INT(11) NOT NULL,
start_lsn BIGINT(21) NOT NULL,
end_lsn BIGINT(21) NOT NULL,
INDEX page_id(space_id, page_id)) ENGINE=InnoDB;
INSERT INTO ICP_COPY SELECT * FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE END_LSN <= @max_end_lsn;
# restart:--innodb_track_changed_pages=FALSE
SELECT @@global.innodb_track_changed_pages;
@@global.innodb_track_changed_pages
0
SET @max_end_lsn= (SELECT MAX(end_lsn) FROM ICP_COPY);
TRUNCATE TABLE ICP_COPY;
INSERT INTO ICP_COPY SELECT * FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE END_LSN <= @max_end_lsn;
Check that INNODB_CHANGED_PAGES copies checksum the same (should be 1):
should_be_1
1
call mtr.add_suppression("but the last checkpoint LSN is");
# restart
SELECT @@global.innodb_track_changed_pages;
@@global.innodb_track_changed_pages
1
ib_modified_log_4
SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES
WHERE START_LSN > 10000;
ERROR HY000: Can't read record in system table
DROP TABLE T1, ICP_COPY;