MySQL Study-MySQL architecture in-depth analysis, mysql-mysql

MySQL Study-MySQL architecture in-depth analysis, mysql-mysql
MySQL Study-in-depth analysis of MySQL Architecture

MySQL Architecture

 

It consists of connection pool components, Management Service and logging tool components, SQL interface components, query analyzer components, optimizer components, buffer components, plug-in storage engines, and physical memory files. Mysql is a unique plug-in architecture. Each storage engine has its own characteristics.

MySQL memory structure:

Mysql process structure

Unlike oracle, Mysql implements its functions through multiple processes. By default, the InnoDB Storage engine has seven background threads:

Four IO threads,

1 master thread,

1 lock monitoring thread,

1 error monitoring thread;

The number of default I/O threads is increased in the InnoDB Plugin version. The default read thread and write thread are increased to four, respectively, and the innodb_file _ io_threads parameter is no longer used, the innodb_read_io_threads and innodb_write_io_threads parameters are used respectively.

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 2OS WAIT ARRAY INFO: signal count 2Mutex spin waits 0, rounds 0, OS waits 0RW-shared spins 2, rounds 60, OS waits 2RW-excl spins 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 initshow engine innodb status--------FILE I/O--------<strong>I/O thread 0 state: waiting for completedaio requests (insert buffer 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 completedaio requests (read thread)I/O thread 5 state: waiting for completedaio requests (read thread)I/O thread 6 state: waiting for completedaio requests (write thread)I/O thread 7 state: waiting for completedaio requests (write thread)I/O thread 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, delete 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 flushed up to 1611537Last checkpoint at  16115370 pending log writes, 0 pending chkp writes8 log i/o's done, 0.00 log i/o's/second----------------------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, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 152, 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 InnoDBMain 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

[root@mysrv ~]# 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) 2000, 2011, Oracle and/or itsaffiliates. All rights reserved. Oracle is a registered trademark of OracleCorporation and/or itsaffiliates. Other names may be 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)

There are three main functions of background threads:

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

2. Refresh the modified data file to the disk file.

3. In case of database exceptions, InnoDB can be restored to normal

Master thread has the highest priority. It consists of several loops: Main loop, backgroundloop, flush loop, and suspend loop ). The master thread switches between loop, background loop, flush loop, and suspend loop Based on the database running status. Loop is called the main loop, because most operations are in this loop. There are two major operations: operations per second and operations per 10 seconds.

Loop is the main loop. If there is no user activity, switch to the backgroundloop, and then jump to the loop. If you still need flush refresh, switch to the flush loop. If it is idle in the flush Loop, switch to the suspend loop and suspend the master thread to wait for the occurrence of the event.

 

Mysql process structure

Loop:

Operations per second include:

The log buffer is refreshed to the disk even if the transaction has not been committed (always ).

Merge insert buffer (possible ).

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

If no user activity exists, switch to the background loop (possibly ).

Next, let's look at the operations every 10 seconds, including the following content::

Refresh 100 dirty pages to the disk (possibly ).

Merge up to five insert buffers (always ).

Refresh the log buffer to the disk (always ).

Delete useless Undo pages (always ).

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

Generate a checkpoint (always ).

The Background loop performs the following operations:

Delete useless Undo pages (always ).

Merge 20 insert buffers (always ).

Jump back to the main loop (always ).

Refresh the 100 pages until they meet the conditions (possibly, jump to the flush loop ).

Mysql process structure -- control disk io Parameters

Innodb_io_capacity

Used to indicate the disk I/O throughput. The default value is 200. For the number of refresh to the disk (dirty buffer), the page will be refreshed according to the percentage of innodb_io_capacity

When merging insert buffering, the number of merged insert buffering is 5% of the innodb_io_capacity value.

When refreshing dirty pages from the buffer zone, the number of dirty pages refreshed is innodb_io_capacity.

 Innodb_max_dirty_pages_pct

The default value is 75%. Speed up dirty page refresh frequency, reduce recovery time, and ensure disk I/O load.

Innodb_adaptive_flushing:

In innodbplugin. This value affects the number of dirty pages refreshed every 1 second. The original refresh rule is: if the proportion of dirty pages in the buffer pool is less than innodb_max_dirty_pages_pct, the dirty pages are not refreshed. When the value is greater than innodb_max_dirty_pages_pct, 100 dirty pages are refreshed, while the innodb_adaptive_flushing parameter is introduced, the InnoDB Storage engine uses a function named buf_flush_get_desired_flush_rate to judge the speed at which redo logs are generated to determine the most suitable number of dirty pages to be refreshed. Therefore, when the proportion of dirty pages is less than that of innodb_max_dirty_pages_pct, a certain amount of dirty pages will also be refreshed.

There are two other parameters related to mysql startup and shutdown.

Innodb_fast_shutdown

This parameter affects the behaviors of the innodb table. This parameter can be set to 0, 1, or 2.

0: when MySQL is disabled, InnoDB needs to complete all full purge and merge insert buffer operations. Time consumed

Relatively long.

1: The default value. Indicates that the full purge and merge insert buffer operations are not required,

Some dirty data pages will be refreshed to the disk.

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

But writes logs to log files. MySQL will resume the next time it starts.

Innodb_force_recovery

It affects the recovery status of InnoDB. The default value is 0, indicating that all recovery operations are performed when recovery is required. If it cannot be effectively restored, MySQL may be down and the error message will be written into the error log file. And 1 ~ 6.

You can set it as needed.

0: Normally shut down and started, and no forced recovery is performed;

1: Skip the error page to continue running the mysqld service. Skipping error index records and storage pages allows you to perform backup operations

2: Stop the InnoDB main thread from running. If the mysqld service crashes during cleaning, data recovery is blocked;

3: No transaction rollback is performed during recovery;

4: prevents the merge operation of the INSERT buffer. No merge operation is performed to prevent the mysqld service from crashing. Do not calculate table statistics

5: When the mysqld service is started, the rollback log is not checked: the InnoDB Engine treats every uncertain transaction as the committed transaction;

6: transaction log rollback and recovery operations are not performed;

 

Mysql memory structure-buffer pool

The memory of the InnoDB Storage engine is mainly composed of several parts: the buffer pool (bufferpool), the redo log buffer (redo log buffer), and the additional memory pool (additional memory pool)

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

The buffer pool is managed through three lists:

1) free list

2) lru list

3) flush list

The minimum unit in the buffer pool is page. Three pages are defined in innodb.

1) free page: This page is not used. This type of page is located in the free linked list.

2) clean page: This page is used and corresponds to a page in the data file, but the page is not modified. This type

The page is in the lru linked list.

3) dirty page: This page is used and corresponds to a page in the data file, but the page has been modified. This type of page

Located in the lru and flush linked lists

 

Insert Buffer:

Not a part of the buffer pool, InsertBuffer is an integral part of a physical page, which improves InnoDB performance. According to the features of the B + algorithm (as described below), primary key indexes are ordered during data insertion, and database reads are not performed randomly, for non-clustered indexes (secondary indexes), the insertion of leaf nodes is no longer sequential. In this case, you need to access non-clustered indexes discretely, and the insertion performance is reduced here. InnoDB introduces the insert buffer to determine whether the non-clustered index page is in the buffer pool. If it is in the buffer pool, it will be inserted directly; if not, it will be placed in the insert buffer zone first. Then, according to the master thread described above, insert buffering will be merged at a certain frequency. In addition, secondary indexes cannot be unique because index pages are not searched when inserted into the insert buffer. Otherwise, random reading is still caused and the significance of inserting the buffer is lost. The insert buffer may occupy the memory in the buffer pool. By default, it can also occupy 1/2. Therefore, you can reduce the value to 1/3. It is set through IBUF_POOL_SIZE_PER_MAX_SIZE. 2 indicates 1/2, and 3 indicates 1/3.

Two writes: 

It provides the reliability of InnoDB data. If the write failure occurs, you can restore the log by redoing the log. However, the redoing log records the physical operations on the page. If the page itself is damaged, redo it. Therefore, a copy of the page is required before the application redo log. When the write failure occurs, the page is restored through the copy of the page and then redone. This is doublewire.
Data Recovery = page copy + redo log

 

Mysql memory structure-log buffer and Management pool

Log Buffer:

The log buffer puts the redo log information into this buffer first, and then refreshes it to the redo log file at a certain frequency. This value does not need to be set to a large value, because the redo log buffer is usually refreshed to the log file every second, therefore, we only need to ensure that the transaction volume generated per second is within the buffer size.

Additional memory pool:

Additional memory pools are equally important. In the InnoDB Storage engine, memory management is performed in a way called memory heap. It is used to cache the data dictionary information and internal data structure of the InnoDB engine, when allocating memory to some data structures, you need to apply from the extra memory pool. When the memory in this region is insufficient, you will apply from the buffer pool, if the number of table objects in the mysqld service is large, the InnoDB engine has a large amount of data, and the innodb_buffer_pool_size value is set to large, the innodb_additional_mem_pool_size value should be adjusted as appropriate. If the memory in the cache area is insufficient, the system will apply for memory allocation directly from the operating system and write warning information to the MySQL error log file.

Difference between binlog and redolog:

1) binary log, which records all MySQL-related log records, including Innodb, MyISAM, Heap, and other storage Indexes

While the innodb Storage engine redolog records only the transaction logs related to it.

2) The record content is different:
Binary log: Whether you set the record format of the binary log file to STATEMENT, ROW, or

MIXED records the specific operations of a transaction.
Redo log: records the physical changes of each page of innodb

3) The write time is different:
Binary log: records before the transaction is committed.
Redo log: a transaction is constantly written during the process.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.