1
SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;
2
SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;
3
SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE;
4
SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE;
5
CREATE TABLE infoschema_buffer_test (col1 INT) ENGINE = INNODB;
6
INSERT INTO infoschema_buffer_test VALUES(9);
7
SELECT TABLE_NAME, INDEX_NAME, NUMBER_RECORDS, DATA_SIZE, PAGE_STATE, PAGE_TYPE
8
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
9
WHERE TABLE_NAME like "%infoschema_buffer_test"
10
and PAGE_STATE="file_page" and PAGE_TYPE="index";
11
TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE PAGE_STATE PAGE_TYPE
12
test/infoschema_buffer_test GEN_CLUST_INDEX 1 29 FILE_PAGE INDEX
13
INSERT INTO infoschema_buffer_test VALUES(19);
14
SELECT TABLE_NAME, INDEX_NAME, NUMBER_RECORDS, DATA_SIZE, PAGE_STATE, PAGE_TYPE
15
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
16
WHERE TABLE_NAME like "%infoschema_buffer_test"
17
and PAGE_STATE="file_page" and PAGE_TYPE="index";
18
TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE PAGE_STATE PAGE_TYPE
19
test/infoschema_buffer_test GEN_CLUST_INDEX 2 58 FILE_PAGE INDEX
20
CREATE INDEX idx ON infoschema_buffer_test(col1);
21
SELECT TABLE_NAME, INDEX_NAME, NUMBER_RECORDS, DATA_SIZE, PAGE_STATE, PAGE_TYPE
22
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
23
WHERE TABLE_NAME like "%infoschema_buffer_test"
24
and PAGE_STATE="file_page" and INDEX_NAME = "idx" and PAGE_TYPE="index";
25
TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE PAGE_STATE PAGE_TYPE
26
test/infoschema_buffer_test idx 2 32 FILE_PAGE INDEX
27
DROP TABLE infoschema_buffer_test;
28
SELECT TABLE_NAME, INDEX_NAME, NUMBER_RECORDS, DATA_SIZE, PAGE_STATE, PAGE_TYPE
29
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
30
WHERE TABLE_NAME like "%infoschema_buffer_test";
31
TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE PAGE_STATE PAGE_TYPE
32
CREATE TABLE infoschema_parent (id INT NOT NULL, PRIMARY KEY (id))
34
CREATE TABLE infoschema_child (id INT, parent_id INT, INDEX par_ind (parent_id),
35
FOREIGN KEY (parent_id)
36
REFERENCES infoschema_parent(id)
40
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
41
WHERE TABLE_NAME like "%infoschema_child" and PAGE_STATE="file_page"
42
and PAGE_TYPE="index";
45
DROP TABLE infoschema_child;
46
DROP TABLE infoschema_parent;
47
show create table information_schema.innodb_buffer_page;
49
INNODB_BUFFER_PAGE CREATE TEMPORARY TABLE `INNODB_BUFFER_PAGE` (
50
`BLOCK_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
51
`SPACE` bigint(21) unsigned NOT NULL DEFAULT '0',
52
`PAGE_NUMBER` bigint(21) unsigned NOT NULL DEFAULT '0',
53
`PAGE_TYPE` varchar(64) DEFAULT NULL,
54
`FLUSH_TYPE` bigint(21) unsigned NOT NULL DEFAULT '0',
55
`FIX_COUNT` bigint(21) unsigned NOT NULL DEFAULT '0',
56
`IS_HASHED` varchar(3) DEFAULT NULL,
57
`NEWEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT '0',
58
`OLDEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT '0',
59
`ACCESS_TIME` bigint(21) unsigned NOT NULL DEFAULT '0',
60
`TABLE_NAME` varchar(1024) DEFAULT NULL,
61
`INDEX_NAME` varchar(1024) DEFAULT NULL,
62
`NUMBER_RECORDS` bigint(21) unsigned NOT NULL DEFAULT '0',
63
`DATA_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0',
64
`COMPRESSED_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0',
65
`PAGE_STATE` varchar(64) DEFAULT NULL,
66
`IO_FIX` varchar(64) DEFAULT NULL,
67
`IS_OLD` varchar(3) DEFAULT NULL,
68
`FREE_PAGE_CLOCK` bigint(21) unsigned NOT NULL DEFAULT '0'
69
) ENGINE=MEMORY DEFAULT CHARSET=utf8
70
show create table information_schema.innodb_buffer_page_lru;
72
INNODB_BUFFER_PAGE_LRU CREATE TEMPORARY TABLE `INNODB_BUFFER_PAGE_LRU` (
73
`LRU_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',
74
`SPACE` bigint(21) unsigned NOT NULL DEFAULT '0',
75
`PAGE_NUMBER` bigint(21) unsigned NOT NULL DEFAULT '0',
76
`PAGE_TYPE` varchar(64) DEFAULT NULL,
77
`FLUSH_TYPE` bigint(21) unsigned NOT NULL DEFAULT '0',
78
`FIX_COUNT` bigint(21) unsigned NOT NULL DEFAULT '0',
79
`IS_HASHED` varchar(3) DEFAULT NULL,
80
`NEWEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT '0',
81
`OLDEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT '0',
82
`ACCESS_TIME` bigint(21) unsigned NOT NULL DEFAULT '0',
83
`TABLE_NAME` varchar(1024) DEFAULT NULL,
84
`INDEX_NAME` varchar(1024) DEFAULT NULL,
85
`NUMBER_RECORDS` bigint(21) unsigned NOT NULL DEFAULT '0',
86
`DATA_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0',
87
`COMPRESSED_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0',
88
`COMPRESSED` varchar(3) DEFAULT NULL,
89
`IO_FIX` varchar(64) DEFAULT NULL,
90
`IS_OLD` varchar(3) DEFAULT NULL,
91
`FREE_PAGE_CLOCK` bigint(21) unsigned NOT NULL DEFAULT '0'
92
) ENGINE=MEMORY DEFAULT CHARSET=utf8
93
show create table information_schema.innodb_buffer_pool_stats;
95
INNODB_BUFFER_POOL_STATS CREATE TEMPORARY TABLE `INNODB_BUFFER_POOL_STATS` (
96
`POOL_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0',
97
`FREE_BUFFERS` bigint(21) unsigned NOT NULL DEFAULT '0',
98
`DATABASE_PAGES` bigint(21) unsigned NOT NULL DEFAULT '0',
99
`OLD_DATABASE_PAGES` bigint(21) unsigned NOT NULL DEFAULT '0',
100
`MODIFIED_DATABASE_PAGES` bigint(21) unsigned NOT NULL DEFAULT '0',
101
`PENDING_DECOMPRESS` bigint(21) unsigned NOT NULL DEFAULT '0',
102
`PENDING_READS` bigint(21) unsigned NOT NULL DEFAULT '0',
103
`PENDING_FLUSH_LRU` bigint(21) unsigned NOT NULL DEFAULT '0',
104
`PENDING_FLUSH_LIST` bigint(21) unsigned NOT NULL DEFAULT '0',
105
`PAGES_MADE_YOUNG` bigint(21) unsigned NOT NULL DEFAULT '0',
106
`PAGES_NOT_MADE_YOUNG` bigint(21) unsigned NOT NULL DEFAULT '0',
107
`PAGES_MADE_YOUNG_RATE` double NOT NULL DEFAULT '0',
108
`PAGES_MADE_NOT_YOUNG_RATE` double NOT NULL DEFAULT '0',
109
`NUMBER_PAGES_READ` bigint(21) unsigned NOT NULL DEFAULT '0',
110
`NUMBER_PAGES_CREATED` bigint(21) unsigned NOT NULL DEFAULT '0',
111
`NUMBER_PAGES_WRITTEN` bigint(21) unsigned NOT NULL DEFAULT '0',
112
`PAGES_READ_RATE` double NOT NULL DEFAULT '0',
113
`PAGES_CREATE_RATE` double NOT NULL DEFAULT '0',
114
`PAGES_WRITTEN_RATE` double NOT NULL DEFAULT '0',
115
`NUMBER_PAGES_GET` bigint(21) unsigned NOT NULL DEFAULT '0',
116
`HIT_RATE` bigint(21) unsigned NOT NULL DEFAULT '0',
117
`YOUNG_MAKE_PER_THOUSAND_GETS` bigint(21) unsigned NOT NULL DEFAULT '0',
118
`NOT_YOUNG_MAKE_PER_THOUSAND_GETS` bigint(21) unsigned NOT NULL DEFAULT '0',
119
`NUMBER_PAGES_READ_AHEAD` bigint(21) unsigned NOT NULL DEFAULT '0',
120
`NUMBER_READ_AHEAD_EVICTED` bigint(21) unsigned NOT NULL DEFAULT '0',
121
`READ_AHEAD_RATE` double NOT NULL DEFAULT '0',
122
`READ_AHEAD_EVICTED_RATE` double NOT NULL DEFAULT '0',
123
`LRU_IO_TOTAL` bigint(21) unsigned NOT NULL DEFAULT '0',
124
`LRU_IO_CURRENT` bigint(21) unsigned NOT NULL DEFAULT '0',
125
`UNCOMPRESS_TOTAL` bigint(21) unsigned NOT NULL DEFAULT '0',
126
`UNCOMPRESS_CURRENT` bigint(21) unsigned NOT NULL DEFAULT '0'
127
) ENGINE=MEMORY DEFAULT CHARSET=utf8