1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
|
# Exercise the code path for INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS
# and INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
-- disable_result_log
SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;
# How many buffer pools we have
SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE;
# This gives the over all buffer pool size
SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE;
-- enable_result_log
# Create a table and check its page info behave correctly in the pool
CREATE TABLE infoschema_buffer_test (col1 INT) ENGINE = INNODB;
INSERT INTO infoschema_buffer_test VALUES(9);
# We should be able to see this table in the buffer pool if we check
# right away
SELECT TABLE_NAME, INDEX_NAME, NUMBER_RECORDS, DATA_SIZE, PAGE_STATE, PAGE_TYPE
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME like "%infoschema_buffer_test%"
and PAGE_STATE="file_page" and PAGE_TYPE="index";
# The NUMBER_RECORDS and DATA_SIZE should check with each insertion
INSERT INTO infoschema_buffer_test VALUES(19);
SELECT TABLE_NAME, INDEX_NAME, NUMBER_RECORDS, DATA_SIZE, PAGE_STATE, PAGE_TYPE
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME like "%infoschema_buffer_test%"
and PAGE_STATE="file_page" and PAGE_TYPE="index";
CREATE INDEX idx ON infoschema_buffer_test(col1);
SELECT TABLE_NAME, INDEX_NAME, NUMBER_RECORDS, DATA_SIZE, PAGE_STATE, PAGE_TYPE
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME like "%infoschema_buffer_test%"
and PAGE_STATE="file_page" and INDEX_NAME = "idx" and PAGE_TYPE="index";
let $space_id = `SELECT space FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME like "%infoschema_buffer_test%" GROUP BY space`;
let $query = SELECT space, is_stale FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE space = $space_id GROUP BY space, is_stale;
# There should be no stale pages.
--replace_result $space_id {space_id}
--eval $query;
# Check the buffer after dropping the table
DROP TABLE infoschema_buffer_test;
SELECT TABLE_NAME, INDEX_NAME, NUMBER_RECORDS, DATA_SIZE, PAGE_STATE, PAGE_TYPE
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME like "%infoschema_buffer_test%";
# There should be only stale pages.
--replace_result $space_id {space_id}
--eval $query;
--source suite/innodb/include/evict_everything_from_buffer_pool.inc
# There should no space pages now, neither stale nor not.
--replace_result $space_id {space_id}
--eval $query;
# Do one more test
#--replace_regex /'*[0-9]*'/'NUM'/
CREATE TABLE infoschema_parent (id INT NOT NULL, PRIMARY KEY (id))
ENGINE=INNODB;
CREATE TABLE infoschema_child (id INT, parent_id INT, INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES infoschema_parent(id)
ON DELETE CASCADE)
ENGINE=INNODB;
SELECT count(*)
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME like "%infoschema_child%" and PAGE_STATE="file_page"
and PAGE_TYPE="index";
DROP TABLE infoschema_child;
DROP TABLE infoschema_parent;
|
