MySQL DBA system learning (INNODB) MySQL storage engine

MySQL Storage engine InnoDB

1, Master architecture:

Default 7 Background threads, 4 IO thread (insert buffer, log, read, write), 1 master thread (highest priority), 1 lock (lock) monitor thread, 1 error monitoring thread. You can view it by show engine InnoDB status. The new version has been increased to 4 for the default read thread and write thread, which can be viewed by show variables like ' innodb_io_thread% '.

Storage Engine Composition:

Buffer pool, redo log buffer pool (redo log buffer), and additional memory pools (additional memory pool). Specific configuration can be made by show variables like ' Innodb_buffer_ Pool_size ', show variables like

' Innodb_log_buffer_size ', show variables like ' innodb_additional_mem_pool_size ' to see.

Buffer pool:

The largest chunk of memory, the cache used to hold a variety of data includes index pages, data pages, undo pages, insert buffers, adaptive hash Indices, lock information for INNODB storage, data dictionary information, and so on. The working method always reads the database file to the buffer pool by page (16k per page) and then retains the cached data in the buffer pool according to the least recently used (LRU) algorithm. If the database file needs to be modified, always first modify the page in the Cache pool (Dirty page after modification), and then refresh the buffer pool's dirty pages to the file at a certain frequency. By command show engine InnoDB status;

Log buffering:

The Redo log information is first placed in this buffer and then flushed to the Redo log file at a certain frequency.

Innodb_buffer_pool_size General Settings ask the machine for 80% of the physical memory

Innodb_additional_mem_pool_size is generally cached data dictionary and structure of information, should not be too big

2, two times write:

It brings the reliability of INNODB data. If the write fails, you can redo the log to restore, but the redo log is recorded in the physical operation of the page, if the page itself is damaged, and then redo it is meaningless. Therefore, before the application redo log, need a copy of the page, when write failure occurs, first through the page to restore the page, and then redo, this is doublewire.

Recovery data = copy of page + redo log