This is the number one of the study notes  1893  Articles

Let's take a look first InnoDB Architecture diagram of .

This picture is divided into three parts , Above is the cache layer , In the middle is the thread layer , Below is the system file layer .

In each layer, it will be subdivided continuously , stay MySQL The unit stored in it is page , The size is 16k.

The cache layer is a large number of cache structures , A lot of data in it is used as cache , Can improve access efficiency .

The system layer is the corresponding data dictionary , Data files and log files , among binlog yes MySQL Server Stratified , It's here because of and InnoDB There is a close relationship .

The multithreaded design is InnoDB A highlight of , Through multithreading, the cache layer can be , The operation of system file layer is organized efficiently , bring InnoDB Can provide data services .

study InnoDB Need to be clear :InnoDB Is a table based storage engine , I see , We're right InnoDB There are essential differences in the analysis of States .

see InnoDB State tips

MySQL If you want to view InnoDB Status of , The highly recommended method is command show engine innodb status.

For this command , The first paragraph is the header , as follows :

mysql> show engine innodb status\G

*************************** 1. row *******************

  Type: InnoDB




2019-02-16 09:42:27 0x7f20b0690700 INNODB MONITOR OUTPUT


Per second averages calculated from the last 34 seconds


Includes the current date and time , And the length of time since the last output

You can see from the time and description that the output of this command is not a real-time result .

Of course, there are several ways to view , such as information_schema in INNODB_XX Data dictionary for ( such as INNODB_BUFFER_POOL_STATS and INNODB_BUFFER_PAGE_LRU) And sys schema, It can provide some InnoDB Information of different dimensions , But by comparison ,show engine innodb status Command output is much richer . 

  At present, there seems to be no special tool to interpret commands  show engine innodb status Output information of , Without these reporting tools , We need to read InnoDB The state of is no doubt triggered by a command , Most of the time, we've executed orders , Then turn up and down the screen to find the corresponding information , Obviously, we didn't keep it ,show engine innodb status The results are not real-time , What to do if you want to see the result of the last command , Here's a tip . 

     We passed mysqld To find the handle information at the system level .

First look mysqld Process number of .

# ps -ef|grep mysqld|grep -v grep 

root      2122     1  0 19:54 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/

mysql     2382  2122  0 19:54 ?        00:00:13 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysqld.log --pid-file=/data/mysql/ --socket=/tmp/mysql.sock

Here it is mysqld Process number of , Namely 2382

At the operating system level, let's look at the handle information , You can see that the output is a list .

# ll /proc/2382/fd|grep deleted

lrwx------ 1 root root 64 Sep 12 23:29 11 -> /tmp/ibq9KpG4 (deleted)

lrwx------ 1 root root 64 Sep 12 23:29 4 -> /tmp/ibuuKHaH (deleted)

lrwx------ 1 root root 64 Sep 12 23:29 5 -> /tmp/ibET4ZCa (deleted)

lrwx------ 1 root root 64 Sep 12 23:29 6 -> /tmp/ib4nyi5D (deleted)

lrwx------ 1 root root 64 Sep 12 23:29 7 -> /tmp/ib1XzG2A (deleted)

In so many documents , We see that the documents are all serial numbers , Will map to the specified directory . 

So that file is what we're looking for ? We passed lsof To confirm indirectly . 

You can see that according to lsof To output handle information . 

# lsof -c mysqld|grep deleted

mysqld    2382 mysql    4u   REG              253,0      3942 1576539 /tmp/ibuuKHaH (deleted)

mysqld    2382 mysql    5u   REG              253,0         0 1576540 /tmp/ibET4ZCa (deleted)

mysqld    2382 mysql    6u   REG              253,0         0 1576541 /tmp/ib4nyi5D (deleted)

mysqld    2382 mysql    7u   REG              253,0         0 1576542 /tmp/ib1XzG2A (deleted)

mysqld    2382 mysql   11u   REG              253,0         0 1576543 /tmp/ibq9KpG4 (deleted)

Attention should be paid to 7 column , This is the only handle whose content is not empty , In this scenario show engine innodb status Output of , I.e. documents /tmp/ibuuKHaH Mapped to 4 Document No .

# ll 4

lrwx------ 1 root root 64 Sep 12 23:29 4 -> /tmp/ibuuKHaH (deleted)

If you want to view the full contents of a command , What you need to see is 4 Document No .

# cat 4


2018-09-12 23:28:26 0x7f8e7bf74700 INNODB MONITOR OUTPUT


Per second averages calculated from the last 22 seconds




srv_master_thread loops: 6 srv_active, 0 srv_shutdown, 12793 srv_idle

srv_master_thread log flush and writes: 12799


More customization and analysis can be done based on these contents in the future . 

InnoDB Multithreading technology of

As I said before InnoDB It's a multithreaded design , How to reflect it in the report . 

We need to talk InnoDB Background thread for , You can use the following brain map to explain this

InnoDB The threads of are mainly divided into 4 class ,Master Thread,IO Thread,Purge Thread,Page Cleaner Thread

Master Thread yes InnoDB Core thread of , It did a lot of early things , It's a full stack thread , And then gradually split it up , since MySQL5.5 It's starting to introduce purge thread, take purge  Task from master Independent in thread , since MySQL 5.6.2 It's starting to introduce Page cleaner thread.

The functions and descriptions of these threads are as follows :


Function description

Related database parameters

Master Thread

Is the core background thread , Mainly responsible for asynchronous refresh and data consistency processing

IO Thread

Asynchronous used IO Model , Responsible for handling different types of IO Request callback


Purge Thread

Recycle the used and allocated undo page , Number of threads from 1 Up to 4, Speed up marking as obsolete undo Page recycle speed


Page Cleaner Thread

implement buffer pool Refresh dirty pages inside , Can be adjusted , Default is 1, Up to 64


Maybe it's not clear enough , We can remember our little goals , Match information by command :

among Master Thread The information of is as follows in the command output :




srv_master_thread loops: 21 srv_active, 0 srv_shutdown, 91981 srv_idle

srv_master_thread log flush and writes: 92002

This is the output of a test environment , No load , among srv_master_thread loops yes Master Number of cycles for a thread , Each cycle selects a state (active,shutdown,idle) implement , among Active The increase in quantity is related to changes in data , Not related to query , Through srv_active and srv_idle The difference can be seen , by force of contrast active and idle Value of , To obtain the overall load of the system , If Active The greater the value of , The busier the certification service .

The output of a relatively busy database is as follows , You can see Active The data is much higher than idle:




srv_master_thread loops: 14921578 srv_active, 0 srv_shutdown, 277461 srv_idle

srv_master_thread log flush and writes: 15199037

And for IO thread Relatively simple and clear , They're all asynchronous IO request , It's clear in the log , We can see the relevant IO Threads and number :



I/O thread 0 state: waiting for completed aio requests (insert buffer thread)

I/O thread 1 state: waiting for completed aio requests (log thread)

I/O thread 2 state: waiting for completed aio requests (read thread)

I/O thread 3 state: waiting for completed aio requests (read thread)

I/O thread 4 state: waiting for completed aio requests (read thread)

I/O thread 5 state: waiting for completed aio requests (read thread)

I/O thread 6 state: waiting for completed aio requests (write thread)

I/O thread 7 state: waiting for completed aio requests (write thread)

I/O thread 8 state: waiting for completed aio requests (write thread)

I/O thread 9 state: waiting for completed aio requests (write thread)

among read thread default 4 individual ,write thread default 4 individual ,log thread and insert buffer thread various 1 individual ,read and wrtie Threads can be adjusted according to parameters ..

about Purge thread, On by default 4 Threads , Improved recycling efficiency , But there are some side effects ,MySQL For spatial reuse mechanisms and Oracle Different databases , If implemented truncate and drop operation , Because there are multiple purge thread To reclaim space , As time goes on, the difficulty of data recovery will be greatly increased .

And for Page Cleaner thread, The default value is 1, If MySQL See the following information in the log , Explain our Cleaner Thread Need to adjust .

2019-02-14T23:50:00.501209Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 28469710ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)

©2019-2020 Toolsou All rights reserved,
Solve in servlet The Chinese output in is a question mark C String function and character function in language MySQL management 35 A small coup optimization Java performance —— Concise article Seven sorting algorithms (java code ) use Ansible Batch deployment SSH Password free login to remote host according to excel generate create Build table SQL sentence Spring Source code series ( sixteen )Spring merge BeanDefinition Principle of Virtual machine installation Linux course What are the common exception classes ?