MySQL Study--mysql Architecture in-depth analysis

MySQL Study--mysql Architecture in-depth analysis

MySQL Architecture

Consists of the connection pool component, the management service and the? Tools component, the SQL interface component, the Query Analyzer component , the optimizer component, the buffer component, the plug-in storage engine, and the physical file. MySQL is a unique plug-in architecture, and each storage engine has its own characteristics.

MySQL memory structure:

MYSQL process Structure

MySQL does not perform its functions as a multi-process, as Oracle does. By default, the InnoDB storage engine has a background thread of 7:

4 IO Thread,

1 Master Thread,

1 locks (lock) monitoring thread,

1 Error monitoring threads;

At the beginning of the InnoDB plugin version, the number of default IO thread was increased, the default read thread and write thread increased to 4, and the Innodb_file_ io_threads parameters were no longer used. Instead, use the innodb_read_io_threads and innodb_write_io_threads parameters separately.

View MySQL Thread:

<strong><span style= "FONT-SIZE:14PX;" >mysql> Show variables like ' innodb_%version% '; </span></strong>+----------------+-------+| Variable_name  | Value |+----------------+-------+| innodb_version | 1.2.4 |+----------------+-------+1 row in Set (0.00 sec)

<strong><span style= "FONT-SIZE:14PX;" >mysql> Show engine InnoDB status;</span></strong>=====================================150709 15:26:40 INNODB MONITOR output=====================================per second averages calculated from Thelast 12 Seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops:0 srv_active, 0srv_shutdown, 804 Srv_idlesrv_master_thread log flush and writes:804----------semaphores----------OS WAIT ARRAY info:reservation Count 2O S WAIT ARRAY info:signal count 2Mutex spin waits 0, rounds 0, OS waits 0rw-shared spins 2, rounds, OS waits 2rw-excl s Pins 0, Rounds 0, OS waits 0Spin rounds per wait:0.00 mutex, 30.00rw-shared, 0.00 rw-excl------------Transactions--------  ----TRX ID counter 5377Purge done for Trx ' s N:o < 4872 undo n:o< 0History List length 1LIST of transactions for each SESSION:---TRANSACTION 0, not startedmysql thread ID 3, OS thread handle0x7f1656c75700, query ID 5 localhost root initsho W ENgine InnoDB status--------FILE I/o--------<strong>i/o thread 0 state:waiting for Completedaio requests (insert Buf Fer thread) I/O thread 1 state:waiting for Completedaio requests (log thread) I/O thread 2 state:waiting for Completedaio Requests (read thread) I/O thread 3 state:waiting for Completedaio requests (read thread) I/O thread 4 state:waiting for C Ompletedaio requests (read thread) I/O thread 5 state:waiting for Completedaio requests (read thread) I/O thread 6 state:w Aiting for Completedaio requests (write thread) I/O thread 7 state:waiting for Completedaio requests (write thread) I/O thr EAD 8 state:waiting for Completedaio requests (write thread) I/O thread 9 state:waiting for Completedaio requests (write Thread) Pending normal AIO reads:0 [0, 0, 0, 0], Aio writes:0 [0, 0, 0, 0],</strong> Ibufaio reads:0, log I/O ' s: 0, Sync I/O ' s:0pending flushes (fsync) log:0; bufferpool:0283 OS file reads, 5 OS file writes, 5 OSfsyncs0.00 reads/s, 0 avg bytes/read, 0.00WRITES/S, 0.00 fsyncs/s-------------------------------------INSERT BUFFER and ADAPTIVE HASH INDEX-------------------------------------ibuf:size 1, free list len 0, seg size 2,0 mergesmerged Operations:insert 0, D Elete Mark 0, delete 0discarded operations:insert 0, delete mark 0, delete 0Hash table size 276707, node heap has 0buffer (s) 0.00 Hash searches/s, 0.00 NON-HASHSEARCHES/S---Log---log sequence number 1611537Log flushed up to 1611537Pages flush Ed up to 1611537Last checkpoint at 16115370 pending log writes, 0 pending CHKP writes8 log I/O ' s done, 0.00 log I/O ' s/sec Ond----------------------BUFFER POOL and memory----------------------total memory allocated 137363456;      Inadditional Pool allocated 0Dictionary memory allocated 39010Buffer pool size 8192Free buffers 8040Database pages 152OLD database pages 0Modified db pages 0Pending reads 0Pending WRITES:LRU 0, flush list 0 Singlepage 0Pages made Young 0, isn't young 00.00 youngs/s, 0.00 Non-youngs/spages Read, created 0, written 10.00 reads/s, 0.00 CREATES/S, 0.00 Writes/sno Buffer Pool page gets since the lastprintoutpages read ahead 0. 00/S, evicted Withoutaccess 0.00/s, Random read ahead 0.00/slru len:152, Unzip_lru len:0i/o sum[0]:cur[0], unzip sum[0]: Cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in Queue0 read views open inside InnoDB  Main thread Process No. 2461, id139733873489664, state:sleepingnumber of rows inserted 0, updated 0,deleted 0, read 00.00 INSERTS/S, 0.00 updates/s, 0.00DELETES/S, 0.00 reads/s----------------------------END of INNODB MONITOR OUTPUT

[[email protected] ~]# mysql-u root-penter password:welcome to the MySQL monitor. Commands End With;  or \g.your MySQL connection ID is 3Server version:5.6.4-m7-log sourcedistribution Copyright (c) *, Oracle and/or Itsaffiliates. All rights reserved. Oracle is a registered trademark of oraclecorporation and/or itsaffiliates. Names may trademarksof their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' toclear the current input statement. <strong><span style= "FONT-SIZE:14PX;" >mysql> Show variables like ' innodb_%io% '; </span></strong>+---------------------------------+--- ------+| variable_name | Value |+---------------------------------+---------+| Innodb_additional_mem_pool_size | 8388608| | innodb_io_capacity | 200 | | Innodb_read_io_threads | 4 | | Innodb_replication_delay | 0 | | Innodb_use_native_aio | On | | innodb_version | 1.2.4   || Innodb_write_io_threads | 4 |+---------------------------------+---------+7 rows in Set (0.01 sec)

Background threads have three main functions:

1. Refresh the data in the memory pool to ensure that the memory in the buffer pool is cached with the most recent data

2. Flush the modified data file to the disk file

3. In case of abnormal database, ensure InnoDB can return to normal operation state

The thread of master thread has the highest priority level. It consists of several loops (loop), a main loop (loop), a background loop (Backgroundloop), a refresh loop (flush loop), and a pause loop (suspend loop). The master thread switches between loops, background loops, flush loops, and suspend loops based on the state of the database running. Loop is called the main loop because most of the operations are in this loop, and there are two major operations: operations per second and operations every 10 seconds.

Loop is the main loop, if there is no user activity, switch to Backgroundloop, then jump to the transfer loop, if you still need flush refresh, then go to flush loop, if it is idle in flush loop, then switch to suspend Loop, Suspends the master thread to wait for the event to occur.

MYSQL process Structure

Loop:

The operations once per second include:

? The log buffers are flushed to disk, even though the transaction has not yet been committed (always).

? Merge Insert buffer (possible).

? Refreshes up to 100 InnoDB of dirty pages in the buffer pool to disk (possibly).

? If there is currently no user activity, switch to background loop (possible).

then look at the operation every 10 seconds, including the following :

? Refresh 100 dirty pages to disk (possible).

? Merges up to 5 insert buffers (always).

? Flushes the log buffer to disk (always).

? Delete the useless undo page (always).

? Refresh 100 or 10% dirty pages to disk (always).

? Produces a checkpoint (always).

Background Loop performs the following actions

? Delete the useless undo page (always).

? Merges 20 insert buffers (always).

? Jumps back to the main loop (always).

? Refresh the 100 pages continuously until you meet the criteria (possibly, jump to the flush loop to complete).

Mysql Process Structure -- Control Disk io The Parameters

? Innodb_io_capacity

? Used to represent the throughput of disk IO, the default value is 200. For the number of flushes to disk (dirty buffering), the relative number of pages is refreshed according to the percentage of innodb_io_capacity

? When you merge insert buffers, the number of merge insert buffers is 5% of the innodb_io_capacity value.

? When a dirty page is flushed from a buffer, the number of dirty pages refreshed is innodb_io_capacity.

? innodb_max_dirty_pages_pct

? The default value is 75%. Speed up dirty page refreshes, reduce recovery time, and ensure disk IO load.

? Innodb_adaptive_flushing:

? In the Innodbplugin. This value affects the number of dirty pages that are refreshed every 1 seconds. The original refresh rule is that dirty pages are not flushed if the dirty page is less than innodb_max_dirty_pages_pct in the buffer pool. When larger than innodb_max_dirty_pages_pct, 100 dirty pages are refreshed, and the innodb_adaptive_flushing parameter is introduced, and the InnoDB storage engine passes a name of Buf_flush_get_desired_ The Flush_rate function determines the rate at which the redo log is generated to determine the most appropriate number of dirty pages to flush. Therefore, when the proportion of dirty pages is less than innodb_max_dirty_pages_pct, a certain amount of dirty pages will also be refreshed.

? There are also two parameters related to MySQL startup and shutdown

? Innodb_fast_shutdown

? This parameter affects the behavior of the InnoDB table, which can be set to 0,1,2

? 0: Indicates that when MySQL shuts down, InnoDB needs to complete all full purge and merge insert buffer operations. Take

? relatively long.

? 1: Is the default value. Indicates that full purge and merge insert buffer operations do not need to be completed, but one in the buffer pool

? Some data dirty pages are flushed to disk.

? 2: Indicates that full purge and merge insert buffer operations are not completed, and data dirty pages in the buffer pool are not written back to the magnetic

? Instead, writes the log to the log file. The next time MySQL starts, a restore operation is performed.

? Innodb_force_recovery

? affect the recovery status of InnoDB. The default is 0, which means that all recovery operations are performed when recovery is required. If the recovery is not valid, MySQL may be down and the error message will be written to the error log file. There are different values such as 1~6, according to

? Need to be able to set.

? 0: Normal shutdown and startup, will not do any forced recovery operation;

? 1: Skip the error page and let the MYSQLD service continue running. Skipping error index records and storage pages to perform backup operations

? 2: Prevents the main thread of the InnoDB from running. The MYSQLD service crashes when the cleanup operation prevents data recovery operations;

? 3: When recovering, do not perform transaction rollback;

? 4: Prevents the merge operation of the insert buffer. Does not do a merge operation to prevent the MYSQLD service from crashing. Do not calculate statistics for tables

? The rollback log is not checked when the 5:mysqld service starts: The InnoDB engine treats each indeterminate transaction as if it were a committed transaction;

? 6: Do not do transaction log roll-forward recovery operation;

MySQL Memory structure-buffer pool

InnoDB storage engine memory consists mainly of several parts: buffer pool (bufferpool), redo log buffer pool (redo log buffer), and additional memory pool (additional)

The buffer pool is the largest part of memory and is used to store various caches of data. Because the InnoDB storage engine works, it always reads the database files by page (16K per page) to the buffer pool and then retains the cached data in the buffer pool by the least recently used (LRU) algorithm. If the database file needs to be modified, the pages in the cache pool are always first modified (the page is dirty after the modification), and then the dirty pages of the buffer pool are flushed (flush) to the file at a certain frequency.

The buffer pool is managed by three lists:

1) Free list

2) LRU List

3) Flush List

The smallest unit in the buffer pool is page, which defines three page types in InnoDB

1) Free page: This page is not used, this type of page is in the free list

2) Clean page: This page is used to correspond to a page in the data file, but the page has not been modified, this type

Page is in the LRU linked list

3) Dirty page: This page is used to correspond to a page in the data file, but the page has been modified, this type page

In the LRU linked list and flush linked list

Insert buffer:

Not part of the buffer pool, Insertbuffer is an integral part of the physical page that leads to improved INNODB performance. Based on the characteristics of the B + algorithm (mentioned below), the primary key index is sequential when inserting data, does not cause random database reads, and for nonclustered indexes (that is, the secondary index), the leaf node insertion is no longer sequential, when the non-clustered index needs to be accessed in a discrete way, the insertion performance is reduced here. InnoDB introduces insert buffering to determine if a nonclustered index page is in the buffer pool, and if it is inserted directly; not, it is placed in the insert buffer first. Then, as described in the master thread above, a certain frequency will be inserted into the buffer merge. In addition, the secondary index cannot be unique, as inserting into the buffer does not look for the index page, otherwise it will still cause random reads, losing the meaning of the insertion buffer. The insert buffer may account for memory in the buffer pool, which can account for 1/2 by default, so you can turn this value down to 1/3. Set by Ibuf_pool_size_per_max_size, 2 means 1/2,3 represents 1/3.

two times written:

It brings the reliability of INNODB data. If the write fails, it can be recovered by redo the log, but the redo log records the physical operation of the page, and if the page itself is corrupted, it makes no sense to redo it. Therefore, before the redo log is applied, a copy of the page is required, and when the write invalidation occurs, the page is restored by a duplicate of the page, which is doublewire.
Recover data = page Copy + redo log

Mysql Memory Structure- Log Buffer with the management pool

Log buffers:

The log buffer places the redo log information into this buffer, and then flushes it to the Redo log file at a certain frequency. This value generally does not need to be set to large, because the redo log buffers are normally flushed to the log file every second, so we only need to ensure that the amount of transactions generated per second is within this buffer size.

Additional Memory Pools:

The additional memory pool is also important. In the InnoDB storage engine, the management of memory is done in a way known as the Memory Heap (heap), which is used to cache the InnoDB engine's data dictionary information and internal data structures, and when allocating memory to some data structures itself, it needs to be requested from an additional pool of memory when there is not enough memory in the region , will be requested from the buffer pool, if the MYSQLD service on the number of table objects, InnoDB engine data is large, and Innodb_buffer_pool_size value settings are large, you should adjust the Innodb_additional_mem_pool_ The value of size. If there is not enough memory in the buffer, the memory allocation will be applied directly to the operating system, and a warning message will be written to the MySQL error log file.

Binlog and Redolog differences:

1) binary log records all MySQL-related logs, including Innodb, MyISAM, heap, and other storage

InnoDB the storage engine's redo log (redolog), logging only the transaction log about itself

2) The contents of the record are different:
Binary log: Regardless of whether you set the record format of the binary logging file to statement, row, or

MIXED, whose records are all about the specific operation of a transaction.
Redo log: Physical condition that records changes to InnoDB per page

3) Different write times:
Binary log: Record before transaction commit
Redo log: The thing is in the process of continuous writing.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL Study--mysql Architecture in-depth analysis