Brief Analysis on the architecture of the INNODB Storage Engine

1. the INNODB Storage engine of background processes consists of four I/O threads, one master thread, and one lock monitoring thread, with one error monitoring thread. The following describes that the innodb_file_io_threads parameter value is 8 and the default value is 4. It indicates that the innodb_file_io_threads parameter value is invalid in linux. In innodb plugin, The innodb_file_io_threads parameter is not used, but the innodb_read_io_threads and innodb_write_io_threads values are used instead. Www.2cto.com // mysql5.1.50root @ test 17:54> select version (); + ------------ + | version () | + ------------ + | 5.1.50-log | + ------------ + root @ test 17:54> show variables like 'innodb _ file_io_threads '; + bytes + ------- + | Variable_name | Value | + ------------------------ + ------- + | innodb_file_io_threads | 8 | + bytes + ------- + // view the engine status root @ test 17:56> show engine innodb status \ g; F The IO thread section displayed by ILE -------- file I/O--------I/O thread 0 state: waiting for I/o request (insert buffer thread) I/O thread 1 state: waiting for I/o request (log thread) I/O thread 2 state: waiting for I/o request (read thread) I/O thread 3 state: waiting for I/o request (write thread) Pending normal aio reads: 0, aio writes: 0, www.2cto.com ibuf aio reads: 0, log I/o's: 0, sync I/o's: 0 Pending flushes (fsync) Log: 0; buffer pool: 0323 OS file reads, 165433 OS file writes, 150609 OS fsyncs0.00 reads/s, 0 avg bytes/read, 6.20 writes/s, 3.40 fsyncs/s ----------------------------------- // mysql5.5 file I/O the following mysql> select version (); + ------------ + | version () | + ------------ + | 5.5.21-log | + ------------ + 1 row in set (0.00 sec) mysql> show variables like 'innodb _ version'; + ---------------- + ------- + | Variable_name | Value | + ---------------- + ------- + | innodb_version | 1.1.8 | + ---------------- + ------- + FILE displays the MySQL 5.5 IO thread, which has four read threads and four write threads, one insert thread and one log thread -------- file I/O--------I/O thread 0 state: waiting for I/o request (insert buffer thread) I/O thread 1 state: waiting for I/o request (log thread) I/O thread 2 state: waiting for I/o request (read thread) I/O thread 3 state: waiting for I/o request (read thread) I/O Thread 4 state: waiting for I/o request (read thread) I/O thread 5 state: waiting for I/o request (read thread) I/O thread 6 state: waiting for I/o request (write thread) I/O thread 7 state: waiting for I/o request (write thread) I/O thread 8 state: waiting for I/o request (write thread) I/O thread 9 state: waiting for I/o request (write thread) Pending normal aio reads: 0 [0, 0, 0, 0], aio writes: 0 [0, 0, 0, 0], ibuf aio reads: 0, log I/o's: 0, sync I/o's: 0 www.2cto.com Pending flushes (fsync) log: 0; buffer pool: 0478 OS file reads, 3 OS file writes, 3 OS fsyncs0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s --------------------------------- 2. the INNODB memory consists of three parts: the buffer pool, redo log buffer, and additional memory pool) // buffer pool, 2GBroot @ test 18:13> sho W variables like 'innodb _ buffer_pool_size '; + rows + ------------ + | Variable_name | Value | + ------------------------- + ------------ + | innodb_buffer_pool_size | 2147483648 | + rows + ------------ + 1 row in set (0.00 sec) // redo log buffer, 16MBroot @ test 18:13> show variables like 'innodb _ log_buffer_size '; + ---------------------- + ---------- + | Variable_name | Value | + bytes + ---------- + | innodb_log_buffer_size | 16777216 | + ------------------------ + ---------- + 1 row in set (0.00 sec) // additional memory pool (additional memory pool), 32 MB, in innodb, the default value is 1 MB, and the default value of innodb plugin is 8 MB. used to store data dictionaries and internal data structures. Root @ test 18:14> show variables like 'innodb _ additional_mem_pool_size '; + rows + ---------- + | Variable_name | Value | + records + ---------- + | innodb_additional_mem_pool_size | 33554432 | + records + ---------- + 1 row in set (0.00 sec) www.2cto.com is visible on the top, the vast majority of memory blocks of the Data Buffer Pool station. The data buffer pool (innodb_buffer_pool) includes: data page, index page, undo page, and insert buffer ), adaptive hash index, lock info, and data dictionary ). Iii. About innodb master thread in mysql5.1 if the innodb plug-in engine compiled by the system does not exist. The default innodb engine of the system is mainly used by a master thread. There is a thread pool in the innodb plugin engine, but this function is not available in the mysql5.5 Community version. In the mysql official documentation, the use of the thread pool is available in the commercial version. Operations per second:
1. The log buffer is refreshed to the disk. Even if this transaction has not been committed, this design will cause a large transaction commit (commit) to be very fast. 2. Merge the insert buffer. You can perform the insert buffer operation when judging that the number of I/O operations is less than 5. 3. the INNODB Storage engine refresh up to 100 dirty pages to the disk each time. Whether or not to refresh each second depends on the proportion of dirty pages. If it exceeds the value set by innodb_max_dirty_pages_pct, 100 dirty pages will be flushed into the file. Root @ (none) 22:46> show variables like 'innodb _ max_dirty_pages_pct '; + logical + ------- + | Variable_name | Value | + ---------------------------- + ------- + | bytes | 60 | + ---------------------------- + ------- + the logical storage structure of the innodb Storage engine, by default, the tablespace is stored in the ibdata1 space. When innodb_file_per_table is defined, it is stored in the "table name ". idb includes data, index, and insert buffer; undo files, system transaction information, and secondary write buffer are stored in ibdata1. The table space on www.2cto.com consists of segments, segments (extent), and pages. segment consists of data segments, index segments, and rollback segments. Extent consists of 64 consecutive pages. The size of each page is 16 KB, that is, the size is 1 MB. page contains B-tree pages, undo pages, system pages, and transaction system pages ), insert buffer bitmap, insert buffer free list, uncompressed blob page ), compressed Binary Large Object page (compressed blob page) by alang85