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
Name:
Status:
=====================================
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/dev01.pid
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/dev01.pid --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
-----------------
BACKGROUND THREAD
-----------------
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 ：
thread
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
innodb_read_io_threadsinnodb_write_io_threads
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
innodb_purge_threads
Page Cleaner Thread
implement buffer pool Refresh dirty pages inside , Can be adjusted , Default is 1, Up to 64
innodb_page_cleaners
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 ：
-----------------
BACKGROUND THREAD
-----------------
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：
-----------------
BACKGROUND THREAD
-----------------
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 ：
FILE I/O
--------
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.)
Technology
Daily Recommendation