MySQL Technology insider-INNODB Storage Engine-Reading notes (one)
As PHP development, the use of MySQL is always indispensable
Blog Link http://itsong.net/articles/466.html
Chapter One MySQL architecture and storage engine
- MySQL is designed as a database of single-process multithreaded architectures
./mysql --help | grep my.cnfYou can see where the MySQL DB instance will look for the configuration file when it starts.- There is a datadir parameter in the configuration file that specifies the path where the database resides. The default is
/usr/local/mysql/data .
- Architecture, MySQL consists of the following parts: The connection pool component, the Management Service and the tool component, the SQL interface component, the query parser component, the optimizer component, the buffer cache component, the plug-in storage engine, the physical file.
- InnoDB storage engine, support transaction, row lock, foreign key support, default read operation does not generate lock
- MyISAM storage engine, transaction not supported, table lock, full-text index
- UNIX domain sockets access to MySQL
-S /tmp/mysql.sock .
Chapter II InnoDB Storage Engine
- There are 7 background threads, 4 io,1 master thread, one lock monitoring thread, and 1 error monitoring thread. The master thread implements almost all functions.
- 4 IO threads for, insert buffer thread,log thread,read thread,write thread.
- Engine memory is divided into: Buffer pool (max), redo log buffer pool redo log buffer, and additional memory pool additional.
- By page (16K per page) The buffer pool is read, and the LRU algorithm retains the cached data. Change the buffer pool to the page-dirty page, and then at a certain frequency to flush the dirty page to the file
show engine innodb status can look at the buffer pool specific usage.
- Buffer pool Cache Data page types are: index page, data page, undo page, insert buffer, Adaptive Hash index, INNODB store lock information, data dictionary information, etc. index pages and data pages are the largest.
- The log buffer places the redo log information into this buffer and flushes it to the Redo log file at a certain frequency.
- The master thread has the highest priority, divided into several loops: main loop, background loop, refresh loop, pause loop.
- The main loop, once per second, includes logging slow flushing to disk, even if the transaction is not committed (so large transactions are fast), merge insert buffers (possibly), flush up to 100 InnoDB buffer pool dirty pages to disk (possibly), and if there is no user activity, switch to the background loop (possibly).
- The merge Insert buffer does not occur every second, determining whether the number of IO times in the current second is less than 5, and the low IO pressure performs a merge insert buffer.
- Dirty pages in buffer pool are larger than 90%, do disk synchronization, write 100 dirty pages to disk
- The main loop, every 10 seconds operation, refreshes 100 dirty pages to disk (possibly), merges up to 5 insert buffers (always), flushes the log buffers to disk (always), deletes useless undo pages (always), refreshes 100 or 10 dirty pages to disk (always), produces a checkpoint (always).
- Determine if disk IO has been less than 200 times in the past 10 seconds, and OK brushes 100 dirty pages to disk.
- Look at the dirty page scale, more than 70%, refresh 100 dirty pages, if less than 70%, refresh 10 dirty pages.
- Background loops, delete useless undo pages (always), merge 20 insert buffers (always), jump back to the main loop (always), refresh 100 pages continuously until you meet the criteria.
- Adjustments
innodb_max_dirty_pages_pct can speed up the flushing of dirty pages, guaranteeing disk IO load, such as 75-80
- Key Features: Insert buffer, two times write double write, Adaptive Hash index adaptive hash.
- The use of the insert buffer satisfies two criteria: The index is a secondary index and the index is not unique.
MySQL Technology insider-INNODB Storage Engine-Reading notes (one)
