MySQL Technology Insider-InnoDB storage engine-reading notes (I) as a php developer, using mysql is always an indispensable blog link http: itsongnetarticles466html Chapter 1 MySQL architecture and storage engine MySQL is designed as a single-entry MySQL technology-InnoDB storage engine-reading notes (I)
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. cnf allows you to view the locations where the configuration file is located when the mysql database instance is started. The configuration file contains a datadir parameter that specifies the path of the database. The default value is/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, read operations do not generate locks for MyISAM storage engines, and transactions, table locks, and full-text index for unix domain sockets to access mysql, -S/tmp/mysql. sock. Chapter 2 InnoDB storage engine has 7 background threads, 4 IO, 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 page-dirty page in the buffer pool, and then refresh the dirty page to flush to the file show engine innodb status to check 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 Master Cycle. every 10 seconds, 100 dirty pages are refreshed to the disk (possibly ), merge up to five insert buffers (always), refresh the log buffer to the disk (always), and delete useless undo pages (always ), refresh 100 or 10 dirty pages to the disk (always) to 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. Adjusting innodb_max_dirty_pages_pct can speed up the frequency of refreshing dirty pages and ensure the disk I/O load. for example, key features of-80: insert buffer, double write twice, and 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.
