| 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 |
`test`.`infoschema_buffer_test` idx 2 32 FILE_PAGE INDEX |
| 28 |
SELECT space, is_stale FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE |
| 29 |
WHERE space = {space_id} GROUP BY space, is_stale;; |
| 30 |
space is_stale |
| 31 |
{space_id} NO |
| 32 |
DROP TABLE infoschema_buffer_test; |
| 33 |
SELECT TABLE_NAME, INDEX_NAME, NUMBER_RECORDS, DATA_SIZE, PAGE_STATE, PAGE_TYPE |
| 34 |
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE |
| 35 |
WHERE TABLE_NAME like "%infoschema_buffer_test%"; |
| 36 |
TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE PAGE_STATE PAGE_TYPE |
| 37 |
SELECT space, is_stale FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE |
| 38 |
WHERE space = {space_id} GROUP BY space, is_stale;; |
| 39 |
space is_stale |
| 40 |
{space_id} YES |
| 41 |
# Emptying InnoDB buffer pool: begin |
| 42 |
# Buffer pool size: 25165824 bytes |
| 43 |
# Creating and dropping a table with size: 50331648 bytes |
| 44 |
# Emptying InnoDB buffer pool: end |
| 45 |
SELECT space, is_stale FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE |
| 46 |
WHERE space = {space_id} GROUP BY space, is_stale;; |
| 47 |
space is_stale |
| 48 |
CREATE TABLE infoschema_parent (id INT NOT NULL, PRIMARY KEY (id)) |
| 49 |
ENGINE=INNODB; |
| 50 |
CREATE TABLE infoschema_child (id INT, parent_id INT, INDEX par_ind (parent_id), |
| 51 |
FOREIGN KEY (parent_id) |
| 52 |
REFERENCES infoschema_parent(id) |
| 53 |
ON DELETE CASCADE) |
| 54 |
ENGINE=INNODB; |
| 55 |
SELECT count(*) |
| 56 |
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE |
| 57 |
WHERE TABLE_NAME like "%infoschema_child%" and PAGE_STATE="file_page" |
| 58 |
and PAGE_TYPE="index"; |
| 59 |
count(*) |
| 60 |
2 |
| 61 |
DROP TABLE infoschema_child; |
| 62 |
DROP TABLE infoschema_parent; |