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
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;
POOL_ID POOL_SIZE FREE_BUFFERS DATABASE_PAGES OLD_DATABASE_PAGES MODIFIED_DATABASE_PAGES PENDING_DECOMPRESS PENDING_READS PENDING_FLUSH_LRU PENDING_FLUSH_LIST PAGES_MADE_YOUNG PAGES_NOT_MADE_YOUNG PAGES_MADE_YOUNG_RATE PAGES_MADE_NOT_YOUNG_RATE NUMBER_PAGES_READ NUMBER_PAGES_CREATED NUMBER_PAGES_WRITTEN PAGES_READ_RATE PAGES_CREATE_RATE PAGES_WRITTEN_RATE NUMBER_PAGES_GET HIT_RATE YOUNG_MAKE_PER_THOUSAND_GETS NOT_YOUNG_MAKE_PER_THOUSAND_GETS NUMBER_PAGES_READ_AHEAD NUMBER_READ_AHEAD_EVICTED READ_AHEAD_RATE READ_AHEAD_EVICTED_RATE LRU_IO_TOTAL LRU_IO_CURRENT UNCOMPRESS_TOTAL UNCOMPRESS_CURRENT
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
CREATE TABLE infoschema_buffer_test (col1 INT) ENGINE = INNODB;
INSERT INTO infoschema_buffer_test VALUES(9);
SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME LIKE '%infoschema_buffer_test%' AND PAGE_TYPE='index';
POOL_ID BLOCK_ID SPACE PAGE_NUMBER PAGE_TYPE FLUSH_TYPE FIX_COUNT IS_HASHED NEWEST_MODIFICATION OLDEST_MODIFICATION ACCESS_TIME TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE COMPRESSED_SIZE PAGE_STATE IO_FIX IS_OLD FREE_PAGE_CLOCK
0 # # 3 INDEX 0 FIX AHI LSN LSN TIME `test`.`infoschema_buffer_test` GEN_CLUST_INDEX 1 29 0 FILE_PAGE IO_FIX OLD #
INSERT INTO infoschema_buffer_test VALUES(19);
CREATE INDEX idx ON infoschema_buffer_test(col1);
SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME LIKE '%infoschema_buffer_test%' AND PAGE_TYPE='index';
POOL_ID BLOCK_ID SPACE PAGE_NUMBER PAGE_TYPE FLUSH_TYPE FIX_COUNT IS_HASHED NEWEST_MODIFICATION OLDEST_MODIFICATION ACCESS_TIME TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE COMPRESSED_SIZE PAGE_STATE IO_FIX IS_OLD FREE_PAGE_CLOCK
0 # # 3 INDEX 0 FIX AHI LSN LSN TIME `test`.`infoschema_buffer_test` GEN_CLUST_INDEX 2 58 0 FILE_PAGE IO_FIX OLD #
0 # # 4 INDEX 0 FIX AHI LSN LSN TIME `test`.`infoschema_buffer_test` idx 2 32 0 FILE_PAGE IO_FIX OLD #
DROP TABLE infoschema_buffer_test;
SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME LIKE '%infoschema_buffer_test%';
POOL_ID BLOCK_ID SPACE PAGE_NUMBER PAGE_TYPE FLUSH_TYPE FIX_COUNT IS_HASHED NEWEST_MODIFICATION OLDEST_MODIFICATION ACCESS_TIME TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE COMPRESSED_SIZE PAGE_STATE IO_FIX IS_OLD FREE_PAGE_CLOCK
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 * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME LIKE '%infoschema_child%';
POOL_ID BLOCK_ID SPACE PAGE_NUMBER PAGE_TYPE FLUSH_TYPE FIX_COUNT IS_HASHED NEWEST_MODIFICATION OLDEST_MODIFICATION ACCESS_TIME TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE COMPRESSED_SIZE PAGE_STATE IO_FIX IS_OLD FREE_PAGE_CLOCK
0 # # 3 INDEX 0 FIX AHI LSN LSN TIME `test`.`infoschema_child` GEN_CLUST_INDEX 0 0 0 FILE_PAGE IO_FIX OLD #
0 # # 4 INDEX 0 FIX AHI LSN LSN TIME `test`.`infoschema_child` par_ind 0 0 0 FILE_PAGE IO_FIX OLD #
DROP TABLE infoschema_child;
DROP TABLE infoschema_parent;
|
