Mysql学习_InnoDB In-Memory Structures

下图为Innodb Architecture

今天先来了解下In-Memory Structures这部分

Buffer Pool:
The memory area that holds cached InnoDB data for both tables and indexes. For efficiency of high-volumeread operations, the buffer pool is divided into pages that can potentially hold multiple rows. For efficiency ofcache management, the buffer pool is implemented as a linked list of pages; data that is rarely used is agedout of the cache, using a variation of the LRU algorithm. On systems with large memory, you can improve concurrency by dividing the buffer pool into multiple buffer pool instances
 

相关参数：
innodb_buffer_pool_size：
Dynamic YES(>= 5.7.5)、DEFAULT VALUE 134217728(128M)、Minimum Value 5242880(5M)、Maximum Value (64-bit platforms) 2**64-1
The size in bytes of the buffer pool,When the size of the buffer pool is greater than 1GB, setting innodb_buffer_pool_instances to a value greater than 1 can improve the scalability on a busy server

innodb_buffer_pool_chunk_size：
Dynamic No、DEFAULT VALUE 134217728(128M)、Minimum Value 1048576(1M)、Maximum Value innodb_buffer_pool_size /innodb_buffer_pool_instances
defines the chunk size for InnoDB buffer pool resizing operations.
If you alter innodb_buffer_pool_chunk_size,innodb_buffer_pool_size is automatically rounded to a value that is equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances
To avoid potential performance issues, the number of chunks (innodb_buffer_pool_size /innodb_buffer_pool_chunk_size) should not exceed 1000

innodb_buffer_pool_instances：
Dynamic No、DEFAULT VALUE 8 (or 1 if innodb_buffer_pool_size <1GB)、Minimum Value 1、Maximum Value 64
The number of regions that the InnoDB buffer pool is divided into.For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency.
by reducing contention as different threads read and write to cached pages. Each page that is stored in or read from the buffer pool is assigned to one of the buffer pool instances randomly, using a hashing function. Each buffer pool manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool, and is protected by its own buffer pool mutex.

Buffer Pool LRU Algorithm(待补充)

Change Buffer:
The change buffer is a special data structure that caches changes to secondary index pages when those pages are not in the buffer pool.The buffered changes, which may result from INSERT, UPDATE or DELETE operations (DML), are merged later when the pages are loaded into the buffer pool by other read operations.

Unlike clustered indexes, secondary indexes are usually nonunique, and inserts into secondary indexes happen in a relatively random order. Similarly, deletes and updates may affect secondary index pages that are not adjacently located in an index tree. Merging cached changes at a later time,when affected pages are read into the buffer pool by other operations, avoids substantial random access I/O that would be required to read secondary index pages into the buffer pool from disk.
Periodically, the purge operation that runs when the system is mostly idle, or during a slow shutdown,writes the updated index pages to disk. The purge operation can write disk blocks for a series of index values more efficiently than if each value were written to disk immediately.
Change buffer merging may take several hours when there are many affected rows and numerous secondary indexes to update. During this time, disk I/O is increased, which can cause a significant slowdown for disk-bound queries. Change buffer merging may also continue to occur after a transaction is committed, and even after a server shutdown and restart.
In memory, the change buffer occupies part of the buffer pool. On disk, the change buffer is part of the system tablespace, where index changes are buffered when the database server is shut down.
 

相关参数：
innodb_change_buffering：
Dynamic Yes、DEFAULT VALUE ALL
Whether InnoDB performs change buffering, an optimization that delays write operations to secondary indexes so that the I/O operations can be performed sequentially. Permitted values are described in the following table.

Value	Description
none	Do not buffer any operations.
inserts	Buffer insert operations.
deletes	Buffer delete marking operations; strictly speaking, the writes that mark index records for later deletion during a purge operation.
changes	Buffer inserts and delete-marking operations.
purges	Buffer the physical deletion operations that happen in the background.
all	The default. Buffer inserts, delete-marking operations, and purges.

innodb_change_buffer_max_size：
Dynamic Yes、DEFAULT VALUE 25、Minimum Value 0、Maximum Value 50
Maximum size for the InnoDB change buffer, as a percentage of the total size of the buffer pool.
You might increase this value for a MySQL server with heavy insert, update, and delete activity,or decrease it for a MySQL server with unchanging data used for reporting.

Adaptive Hash Index:
Based on the observed pattern of searches, a hash index is built using a prefix of the index key. The prefix can be any length, and it may be that only some values in the B-tree appear in the hash index.Hash indexes are built on demand for the pages of the index that are accessed often.
If a table fits almost entirely in main memory, a hash index can speed up queries by enabling direct lookup of any element, turning the index value into a sort of pointer. InnoDB has a mechanism that monitors index searches. If InnoDB notices that queries could benefit from building a hash index, it does so automatically.

相关参数：
innodb_adaptive_hash_index:
Dynamic Yes、DEFAULT VALUE ON
Whether the InnoDB adaptive hash index is enabled or disabled. It may be desirable, depending on your workload, to dynamically enable or disable adaptive hash indexing to improve query performance.

innodb_adaptive_hash_index_parts:
Dynamic No、DEFAULT VALUE 8、Minimum Value 1、Maximum Value 512(Introduced 5.7.8)
Partitions the adaptive hash index search system. Each index is bound to a specific partition, with each partition protected by a separate latch.
In MySQL 5.7, the adaptive hash index feature is partitioned. Each index is bound to a specific partition, and each partition is protected by a separate latch. Partitioning is controlled by the innodb_adaptive_hash_index_parts variable.

Log Buffer:
The log buffer is the memory area that holds data to be written to the log files on disk.(redo)

相关参数：
innodb_log_buffer_size:
Dynamic No、Default Value (>= 5.7.6) 16MB、Default Value (<= 5.7.5) 8MB、Minimum Value (>= 5.7.6) 1MB、Minimum Value (<= 5.7.5) 256K、Maximum Value 4294967295(4G-1B)
The size in bytes of the buffer that InnoDB uses to write to the log files on disk.
A large log buffer enables large transactions to run without the need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, increasing the size of the log buffer saves disk I/O.

innodb_flush_log_at_trx_commit:
Dynamic Yes、DEFAULT VALUE 1、valid values(0、1、2)
The variable controls how the contents of the log buffer are written and flushed to disk.
Controls the balance between strict ACID compliance for commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value but then you can lose transactions in a crash.
    The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.
     With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
  With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
  For settings 0 and 2, once-per-second flushing is not 100% guaranteed. Flushing may occur more frequently due to DDL changes and other internal InnoDB activities that cause logs to be flushed independently of the innodb_flush_log_at_trx_commit setting, and sometimes less frequently due to scheduling issues. If logs are flushed once per second, up to one second of transactions can be lost in a crash. If logs are flushed more or less frequently than once per second, the amount of transactions that can be lost varies accordingly.
  Log flushing frequency is controlled by innodb_flush_log_at_timeout, which allows you to set log flushing frequency to N seconds (where N is 1 ... 2700, with a default value of 1).However, any mysqld process crash can erase up to N seconds of transactions.
     DDL changes and other internal InnoDB activities flush the log independently of the innodb_flush_log_at_trx_commit setting.
    InnoDB crash recovery works regardless of the innodb_flush_log_at_trx_commit setting.Transactions are either applied entirely or erased entirely.

innodb_flush_log_at_timeout：
Dynamic Yes、DEFAULT VALUE 1、Minimum Value 1、Maximum Value 2700
Write and flush the logs every N seconds.innodb_flush_log_at_timeout allows the timeout period between flushes to be increased in order to reduce flushing and avoid impacting performance of binary log group commit.

 

(参考：
https://www.cnblogs.com/wade-luffy/p/6288656.html
https://blog.csdn.net/bohu83/article/details/81837872）