In the previous articles of the column , We summarized the buffer pool , Cache pages ,redo log,undo log, And how data pages and rows are stored at the bottom , Table spaces are introduced later , paragraph , Area and other concepts . This section is special , It's about and Linux About the interaction principle , Because most mysql It's all deployed in linux On your server , This section will briefly introduce linux How to deal with mysql Requested by , as well as linux What problems will the system bring
mysql When performing addition, deletion, modification and query , The data page will be read from the disk file of the table space , First of all, we need to understand , Although the data page of the disk file in the table space is 「 Random, speaking, reading and writing 」, But it includes 「Redo log」、 「bin log」 Wait, these log files are actually on disk 「 Sequential reading and writing 」 Of .
Random reading a data page to the cache is a random read operation on the disk , Because the position of the data page is not fixed, it may be anywhere on the disk , So the whole read-write performance is very poor , According to the previous introduction , When the data page is read, it is placed in buffer pool Inside .
in order to IO Performance of , Here you need to understand two important performance indicators ：「IOPS」 and 「 Response delay 」.IOPS It refers to how many disk read and write operations the storage system can perform per second , The underlying disk supports execution per second 1000 A random read-write and 200 There is a big gap . The response delay is about once IO How long does it take to respond after the request , It also has a great impact , One second 200 Reading and writing is 10MS Complete and once 200 Reading and writing is 1S The gap between completion and response is also very large .
Although the disk is read and written randomly to the buffer pool , But after the cache page is updated , This data needs to be written in order redo log The log , Sequential writing means that you can also read sequentially when reading .（ About redo log The details will be introduced in the following articles ） The performance of disk sequential write is actually very important , To some extent, high-speed sequential disk access can almost catch up with the reading and writing performance of memory , Especially in the database 「os cache Mechanism 」 Under the circumstances , That is to say redo log Before sequentially writing to disk , First, enter 「os cache」, It is managed by the operating system 「 Memory cache 」 in .
About os cache You can turn over the previous articles , He also has a role if innodb Set in the engine ：「innodb_flush_log_at_trx_commit」 by 2, Will be written in redo When logging, the data is displayed and written to os cache among , And in 1S after （ Probably ） Then refresh the data back to redo log Disk files for , But that's what's going on mysql The hidden danger of data loss will occur during downtime ....
In addition, I found out about os cache as well as application cache Speed consumption comparison , In the answer, the validity criteria of cache are discussed , And when to use cache. If you are interested, you can check the specific content of the article ：
Finally, we summarize the following figure according to the above text ：
Follow the instructions above , We can roughly summarize the following two points ：
Now let's get to the point , Simply speaking Linux The storage system is divided into 「VFS layer 、 File system layer 、Page Cache Cache layer 、 Universal Block layer 、IO Scheduling layer 、Block Device drive Dynamic layer 、Block Equipment layer 」, Here's the picture ：
Here is mysql and llinux The general process of interaction ：
CFS Scheduler ： A completely fair scheduling algorithm is adopted , Introduce the concept of virtual runtime ;
Deadline Scheduler ： Use 「 Red and black trees 」, Sort processes according to absolute deadlines , Select the smallest process for scheduling
In practice, multiple database requests are executed sql At the same time io, Therefore, it is suggested that the production environment io The scheduling algorithm is replaced by 「deadline Dispatch 」, The core idea is any one io You can't wait as long as you want , stay 「 Specified scope 」 He must execute .*
Block Device driver layer ： At this point, you can execute io Your request will be handed over to block Device driver layer , Finally, give the driver to the equipment layer
Block Equipment layer ： The hardware device has completed IO After the read-write operation, either write or read , Finally, the response is returned in reverse order through the above level .
Final MySQL You can get this IO The result of a read / write operation , The final structure diagram is as follows ：
Here is a more common linux Related issues .
In the first introduction of the column, we know that the database has the concept of connection pool , But the capacity of the connection pool is limited , This error indicates that the connection pool is full .
By default, the default connection pool size for each machine is 「200」, Each station java The system can establish at most 200 A connection , If it is 2 Taiwan machine , most 400 A connection .
The key parameters for this setting are 「max_connections」： For example, the maximum number of connections is 800.
actually , During operation, it is usually found that ：
show variables like 'max_connections' The settings on the line are only 「214」, What's going on here ？ Why? mysql It will reset automatically ？ Because 「linux The number of open handles is 1024 Only... Can be connected 214 individual 」.
stay linux among ： Carry out orders
Ulimit -HSn 65535 You can modify the configuration of the handle , To verify success , You can view the configuration of the handle through the following command ：
For the sake of insurance , It can also be in
my.cnf The interior of the ensures
mac_connections Parameter configuration .
linux By default, you will limit the use of machine resources by each process , Including restrictions on file handles that can be opened , Limit on the number of child processes that can be opened , Limitations of network cache , Maximum lockable memory size .
In order to make mysql Make the most of , The setting value of the handle is usually 「65535」.
So we can usually use `ulimit` Command to set the amount of resources each process is restricted to use , use ulimit -a You can see the various resources that the process is restricted to use Amount of source, such as core file size The size limit of the dump file when the representative process crashes ,max locked memory Is the maximum locked memory size ,open files Is the maximum number of file handles that can be opened ,max user processes Is the maximum number of child processes you can have .
After setting , We need to make sure that the changes are implemented to /etc/security/limits.conf In the document , Permanently set the resource limit of the process, so execute
ulimit -HSn 65535 After the command , Use the following command to check whether it has landed in the configuration file .
cat /etc/security/limits.conf as well as
After understanding the system hierarchy, let's talk Raid Storage architecture .
mysql To put it bluntly, it is also a programming language , So it also depends on the operating system , He needs to use cpu Memory and hard disk are used for storage .
「 Disk redundant array is easy to understand ：」
Redundant array of disks , The simplest understanding is to use one hard disk to manage multiple hard disks , Use multiple disks for management at the storage level . So disk RAID What's the use of your hard drive ？
about RAID Hard disk , There is usually a piece called SDRAM Space , Memory cache used to store the operating system , But what if this memory like thing loses power , In this way, if the data is not refreshed to the hard disk, it will not be lost ？ therefore RAID be based on 「 Lithium battery powered 」 Run the disk that writes the cached data to the disk array .
However, lithium batteries also need to rest , There is also performance degradation , So you need to be in 30 - 90 Automatically charge and discharge once a day , This can prolong the life of lithium battery and calibrate the battery capacity .
Due to the problem of charge and discharge , Usually, when charging and discharging, you need to write the data to the disk rather than to the cache to prevent data loss , But also because of this problem, the performance will fluctuate greatly due to regular charging and discharging , In the actual experience, there are regular online fluctuations in the database .
As for how to solve the above problems , Let's put it below , Let's add raid The relationship between .
stay raid In the hard drive , There is a problem with a hard disk , It will be in 1 and 0 Form a data redundancy between , One disk is broken, and there will be data on the other .raid Disk means that every two disks form an architecture that mirrors each other , There are three groups , So it will form 3 Data redundancy for group mirroring .
This section also focuses on theory , A brief introduction mysql Disk random write and disk sequential write , And a brief introduction to linux Hierarchical logical structure of the system , Finally, we learned Raid Storage architecture , as well as RAID Details of lithium battery .
This article is also more basic , If you have any comments or suggestions, welcome guidance .