MySQL technology insider-InnoDB Storage engine-Reading Notes (1), mysql-innodb

MySQL technology insider-InnoDB Storage engine-Reading Notes (1), mysql-innodb
MySQL technology insider-InnoDB Storage engine-Reading Notes (1)

Mysql is always indispensable for php development.

Blog link http://itsong.net/articles/466.html

Chapter 1 MySQL architecture and storage engine

• MySQL is designed as a single-process multi-thread Database
• ./mysql --help | grep my.cnfYou can view the location where the mysql database instance looks for the configuration file when it is started.
• The configuration file contains a datadir parameter that specifies the path of the database. Default Value:/usr/local/mysql/data.
• Architecture: mysql consists of the following components: Connection Pool components, management services and tool components, SQL interface components, query analyzer components, optimizer components, buffer cache components, and plug-in-type storage engines, physical file.
• The InnoDB Storage engine supports transactions, row locks, and foreign keys. By default, no locks are generated for read operations.
• MyISAM storage engine, which does not support transactions, table locks, and full-text indexes
• Unix domain socket accesses mysql,-S /tmp/mysql.sock.

Chapter 2 InnoDB Storage Engine

• There are 7 Background threads, 4 IO threads, 1 master thread, 1 lock monitoring thread, and 1 error monitoring thread. The master thread implements almost all functions.
• The four IO threads are insert buffer thread, log thread, read thread, and write thread.
• Engine memory is divided into: buffer pool (maximum), redo log buffer pool redo log buffer, and additional memory pool.
• Read the buffer pool by PAGE (16 K per page), and the LRU algorithm keeps the cached data. First Change the pages in the buffer pool-dirty pages, and then refresh the dirty pages to flush to the files at a certain frequency.show engine innodb statusYou can view the usage of the buffer pool.
• The data page types cached by the buffer pool include index page, data page, undo page, insert buffer, adaptive hash index, innodb Storage lock information, and data dictionary information. The maximum number of index pages and data pages.
• The log buffer puts the redo log information into this buffer first, and refreshes the redo log file to a certain frequency.
• The master thread has the highest priority and has several cycles: Main Loop, background loop, refresh loop, and pause loop.
• In the main loop, operations performed once per second include flushing logs to the disk, even if the transaction is not committed (so large transactions are also fast), merging insert buffering (possibly ), refresh up to 100 dirty pages in the innodb buffer pool to the disk (possible). If there is no user activity, switch to the background loop (possible ).
• The merge insert buffer does not occur every second. Check whether the number of io operations in the current second is less than 5. If the io pressure is low, merge insert buffer is executed.
• The proportion of dirty pages in the buffer pool is greater than 90%. During Disk Synchronization, 100 dirty pages are written to the disk.
• In the main loop, operations are performed every 10 seconds. Refresh 100 dirty pages to the disk (possibly), merge up to 5 insert buffers (always), and refresh the log buffer to the disk (always ), delete useless undo pages (always), refresh 100 or 10 dirty pages to the disk (always), and generate a checkpoint (always ).
• Check whether the disk io is less than 200 times in the past 10 seconds. If OK, refresh 100 dirty pages to the disk.
• View the proportion of dirty pages, more than 70%, refresh 100 dirty pages, if less than 70%, refresh 10 dirty pages.
• Background loop, delete useless undo pages (always), merge 20 insert buffers (always), jump back to the main loop (always), and refresh 100 pages continuously until the conditions are met.
• Adjustmentinnodb_max_dirty_pages_pctIt can speed up the refresh of dirty pages and ensure the disk I/O load, such as 75-80
• Key features: insert buffer, double write twice, adaptive hash index.
• The use of the insert buffer must meet two conditions: the index is a secondary index and the index is not unique.