MySQL architecture-from the journal

MySQL three-tier architecture|-----------------------------------------------------------------------------------|| Mysqld-sql Layer | |                              -----------------------------------------------------------------------------------|| mysqld-table engine Adaptation Layer |------------------------------------------------------------------------                        -------------        |                 |            |        |        | |    ----------------       -----------  -------------    ------------       | -------------- |        InnoDB Table Engine | |     BDB Table Engine |    Merge Table Engine | |        Heap Table Engine |    | | MyISAM Table Engine |    ----------------------------------------—-----------|                                                                    |-------------                                                                         | ------------                                                                    | ISAM Table Engine |                                                                    ------------ SQL layer mainly completes the SQL statement lexical analysis, syntax analysis, permission checking and so on, the table adapter layer according to the user SQL statement context, select and activate the user's current operation of the table engine, perform specific statement operations. User can set the default table engine through the configuration file, Default_ Table_type=heap execution of an SQL statement1: User sends SQL statement to SQL Server via client2: The server corresponds to the user's worker thread (established when the database connection is executed) to receive the SQL statement3: Worker thread performs SQL layer permission check4: Worker threads perform SQL layer SQL statement parsing5: Worker threads perform necessary optimizations on SQL layer6: in 3,4A 5-step error occurs when the SQL layer constructs a corresponding error message that is returned by the worker thread to the client7: The worker thread executes the SQL layer operation, activates the table engine adaptation layer, and selects the corresponding engine.8: The worker thread executes on the selected table engine and, with the corresponding table engine system thread, completes the specific statement operation9: Worker threads return results to the client InnoDB the configuration of the table engine based on the execution of the table engine1: Bypass configuration when the MySQL server itself supports InnoDB, but the administrator does not intend to use InnoDB when the configuration is used, the server is not supported even if the database user specifies that the InnoDB table type be created. Saves memory and disk space, and accelerates execution of certain statements (differences between MyISAM and InnoDB tables)2: Thread Configuration2.1: The number of concurrent threads (innodb_thread_concurrency) is used to specify the number of concurrent threads allowed in the InnoDB engine, and when multiple users are simultaneously connected to the server and using the InnoDB engine.        Multiple user worker threads may appear into the Innnodb table engine.    At the same time, the more user worker threads are in the INNODB layer, the higher the system concurrency, the more mutually exclusive conflicts between threads are possible, and when a certain amount is reached, the system performance drops sharply and is configured according to the actual situation. 2.2: Maximum lock wait time (innodb_lock_wait_timeout) This configuration item specifies the maximum time that a user worker thread waits for a lock, and InnoDB automatically rolls back the transaction when a InnoDB transaction waits for a lock to exceed that value. This is important for breaking the deadlock that can occur with InnoDB, especially when the lock tables command is executed, InnoDB will not be able to control this external lock. This can result in a user's direct lock and InnoDB the locking of the internal execution process. This configuration is in seconds, the value is too small,    Transactions that do not occur with deadlocks are frequently rolled back, too large, and the deadlock time is too long, affecting the effective execution efficiency of INNODB. 2.3: i/o number of threads (innodb_file_io_threads) in order to increase the speed, InnoDB first read the data into the memory buffer, and then write to the disk, when reading the data, InnoDB bulk read into the buffer, convenient for later lookup. This creates a read-write problem between the memory buffer and the disk, and InnoDB sets the specific I/o thread to complete the read and write operations between the disks in the memory buffer.3: Cache Management Configuration3.1: Buffer pool Size (innodb_buffer_pool_size) This configuration item is used to specify the size of the buffer pool used by the InnoDB. The buffer pool user stores InnoDB table indexes and table data to cache disk I/o speed. The larger the buffer pool, the more indexes and data are buffered into memory, and the less memory the other processes can use.    In addition, the larger the configuration, the greater the risk of data loss (the data in the cache is not flushed to disk and is permanently lost). 3.2: Metadata buffer pool Size (innodb_additional_mem_pool_size) innodb_buffer_pool_size The specified buffer pool is used to cache table indexes and table data, which specifies metadata such as table definition information in the buffer pool.    This configuration is an initialization configuration that is automatically applied to the operating system if the InnoDB contains metadata larger than the configuration. 3.3: Log buffer size (innodb_log_buffer_size) during transaction execution, InnoDB continuously generates logs to provide data recovery capabilities. When InnoDB generates the log, the log is first written to the buffer and then flushed to disk. This configuration is used to specify    The log buffer size. Too small, will make InnoDB frequently flush logs to disk, while it is difficult to support long transactions. Excessive waste of memory, while prolonging the refresh time, increase the risk of log loss. 

MySQL architecture-from the journal