mysql buffer pool (data cache)


//=== https://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html
* for mysql InnoDB storage engine
* buffer pool: a storage area(memory space) for caching data and indexes
* the larger the buffer pool, the more InnoDB acts like an "in-memory database"
* the buffer pool not only caches read data but also "write data" (caused by insert and update operations), so that disk writes can be grouped together for better performance.



* Under 64-bit systems with large memory sizes, you can split the buffer pool into multiple parts [multiple buffer pool instance] to minimize contention among concurrent operations.


//===

* LRU-variant algorithm [LRU: least recently used algorithm ]
* “midpoint insertion strategy” treats the list as two sublists:
* head sublist for new blocks;
* tail sublist for old blocks
* midpoint is the boundary where head sublist meets tail sublist




//=== system variables control the size of the buffer pool

innodb_buffer_pool_size

innodb_buffer_pool_instances
[ innodb_buffer_pool_size at least 1G ]
(Divides the buffer pool into a user-specified number of separate regions, each with its own LRU list and related data structures, to reduce contention during concurrent memory read and write operations.)


innodb_old_blocks_pct
[ The default value is 37 (that is, 3/8 of the pool). ]

innodb_old_blocks_time
?


//=== https://dev.mysql.com/doc/refman/5.6/en/innodb-information-schema-buffer-pool-tables.html
mysql> SHOW ENGINE INNODB STATUS


mysql> show variables like '%INNODB_BUFFER_POOL%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 627048448 |
+-------------------------------------+----------------+



mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_BUFFER%';
+-----------------------------------------------+
| Tables_in_INFORMATION_SCHEMA (INNODB_BUFFER%) |
+-----------------------------------------------+
| INNODB_BUFFER_PAGE_LRU |
| INNODB_BUFFER_PAGE |
| INNODB_BUFFER_POOL_STATS |
+-----------------------------------------------+
3 rows in set (0.00 sec)



mysql> SELECT DISTINCT PAGE_TYPE FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0);

+-------------------+
| PAGE_TYPE |
+-------------------+
| SYSTEM |
| IBUF_BITMAP |
| INODE |
| IBUF_INDEX |
| INDEX |
| TRX_SYSTEM |
| UNDO_LOG |
| FILE_SPACE_HEADER |
| UNKNOWN |
+-------------------+
9 rows in set (0.20 sec)