Database operations are IO-intensive arguments

First, Iostat

The background is that we are doing a lot of data update operations to see the status of the disks.
We look at disk IO, and this is mostly done through the Iostat command.

-D indicates that the display device (disk) Usage status, removed can be displayed with the CPU status;
-K Some columns that use block units are forced to use kilobytes, instead of-m (shown in megabytes);
1 5 indicates that the data display is refreshed every 1 seconds and is displayed for a total of 10 times.

[[email protected] ~]# iostat-d-K 1 5Linux 3.10.0-514.el7.x86_64 06/09/2018 _x86_64_ (CPU) Devic E:tps kb_read/s kb_wrtn/s kb_read kb_wrtnsda 10.53 319.13 488.33 87834222         13440316927dm-0 0.16 0.03 1.35 757398 37188411dm-1 0.00 0.00            0.01 117580 301216dm-2 10.41 319.10 486.97 8782517912 13402825288Device: TPS kb_read/s kb_wrtn/s kb_read kb_wrtnsda 1028.00 18624.00 71374.50 18624 71374d          M-0 0.00 0.00 0.00 0 0dm-1 0.00 0.00 0.00     0 0dm-2 1033.00 19392.00 71630.50 19392 71630device:tps kb_read/s              KB_WRTN/S kb_read kb_wrtnsda 854.00 24464.00 70580.50 24464 70580dm-0      0.00 0.00   0.00 0 0dm-1 0.00 0.00 0.00 0 0dm-2 855.00             23696.00 70674.00 23696 70674device:tps kb_read/s kb_wrtn/s kb_read KB_WRTNSDA          849.00 13312.00 87219.50 13312 87219dm-0 0.00 0.00 0.00     0 0dm-1 0.00 0.00 0.00 0 0dm-2 876.00 14336.00 87750.00 14336 87750device:tps kb_read/s kb_wrtn/s kb_read KB_WRTNSDA 770.0 0 19456.00 70206.50 19456 70206dm-0 0.00 0.00 0.00 0 0dm- 1 0.00 0.00 0.00 0 0dm-2 745.00 19456.00 69771.00 1 9456 69771

KB_READ/S: The amount of data read from the device (drive expressed) per second;
KB_WRTN/S: The amount of data written to the device (drive expressed) per second;
Kb_read: The total amount of data read;
KB_WRTN: The total amount of data written, these units are kilobytes.

Parameter-K

[[email protected] ~]# iostat-d-x-k 1 Linux 3.10.0-514.el7.x86_64 06/09/2018 _x86_64_ (CPU) Devic               e:rrqm/s wrqm/s r/s w/s rkb/s wkb/s avgrq-sz avgqu-sz await r_await w_await SVCTM%UTILSDA              0.00 0.04 6.07 4.47 319.23 488.61 153.32 0.01 1.12 1.05 1.23 0.33 0.35dm-0              0.00 0.00 0.00 0.16 0.03 1.35 17.12 0.00 0.19 1.78 0.17 0.03 0.00dm-1              0.00 0.00 0.00 0.00 0.00 0.01 8.01 0.00 0.43 0.42 0.43 0.03 0.00dm-2         0.00 0.00 6.06 4.35 319.20 487.25 154.87 0.01 1.14 1.05 1.28 0.34 0.35Device:               rrqm/s wrqm/s r/s w/s rkb/s wkb/s avgrq-sz avgqu-sz await r_await w_await SVCTM%UTILSDA              0.00 0.00 10.00 467.00 2080.00 10256.00 51.72 155.43 342.20 334.70 342.36 2.10 100.00dm-0 0.00 0.00 0.0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00dm-1 0.00 0.00 0. XX 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00dm-2 0.00 0.00 7.0      0 489.00 1312.00 11232.00 50.58 160.96 339.23 614.29 335.29 2.02 100.00device:rrqm/s wrqm/s r/s  w/s rkb/s wkb/s avgrq-sz avgqu-sz await r_await w_await SVCTM%UTILSDA 0.00 0.00 6.00    343.00 1056.00 10408.00 65.70 146.90 374.37 300.83 375.65 2.87 100.00dm-0 0.00 0.00 0.00    0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00DM-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00dm-2 0.00 0.00 8.00 32     2.00 1808.00 6704.00 51.59 152.25 407.81 336.75 409.57 3.03 100.00device:rrqm/s wrqm/s r/s w/s rkb/s wkb/s avGrq-sz Avgqu-sz await r_await w_await SVCTM%UTILSDA 0.00 0.00 8.00 429.00 2048.00 4576.00     30.32 146.42 341.64 278.25 342.82 2.29 100.00dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00DM-1 0.00 0.00 0.00 0.00 0.00 0.00 0 .0.00 0.00 0.00 0.00 0.00 0.00dm-2 0.00 0.00 5.00 448.00 1280.00 7992.00 40. 94 150.86 340.85 623.60 337.69 2.21 100.00device:rrqm/s wrqm/s r/s w/s rkb/s wkb/s avgrq-s    Z avgqu-sz await r_await w_await SVCTM%UTILSDA 0.00 0.00 4.00 447.00 1024.00 10622.00 51.65     146.52 336.33 506.50 334.81 2.22 100.00dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00     0.00 0.00 0.00 0.00 0.00 0.00DM-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00   0.00 0.00 0.00dm-2 0.00 0.00 5.00 454.00 1040.00 11302.00 53.78 151.71 340.31 464.20 338.94 2.18 100.00device:rrqm/s wrqm/s r/s w/s rkb/s wkb/s avgrq-sz avgqu-sz await r_await w_ Await SVCTM%UTILSDA 0.00 0.00 7.00 435.00 1072.00 13248.00 64.80 146.15 351.56 252.86 35 3.15 2.26 100.00dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 .0.00 0.00dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0. XX 0.00 0.00dm-2 0.00 0.00 11.00 416.00 1856.00 9192.00 51.75 152.02 378.61 290.09 380.9 5 2.34 100.00

You can see the average response time of the disk >300ms, and the disk usage is >100. The disk is not responding properly and the IO is fully loaded.

RRQM/S: The number of read operations per second for the merge. That is Delta (rmerge)/s
WRQM/S: The number of write operations per second for the merge. That is, Delta (wmerge)/s
R/S: Number of Read I/O devices completed per second. Delta (RIO)/s
W/S: Number of write I/O devices completed per second. Delta (WIO)/s
RSEC/S: Number of Read sectors per second. Delta (rsect)/s
WSEC/S: Number of Write sectors per second. Delta (wsect)/s
rkb/s: Read K bytes per second. Is half of the rsect/s because the size of each sector is 512 bytes. (Calculation required)
wkb/s: Writes K bytes per second. It's half the wsect/s. (Calculation required)
AVGRQ-SZ: The data size (sector) of the average Per device I/O operation. Delta (Rsect+wsect)/delta (Rio+wio)
Avgqu-sz: Average I/O queue length. That is Delta (AVEQ)/s/1000 (because Aveq is in milliseconds).
Await: The average wait time (in milliseconds) for each device I/O operation. Delta (Ruse+wuse)/delta (Rio+wio)
SVCTM: The average service time (in milliseconds) per device I/O operation. Delta (use)/delta (RIO+WIO)
%util: How much time in a second is spent on I/O operations, or how many times in a second I/O queues are non-empty. That is, Delta (use)/s/1000 (because the use is in milliseconds)

If%util is close to 100%, it indicates that there are too many I/O requests, the I/O system is full, and the disk may have bottlenecks.
Idle less than 70% io pressure is larger, the general reading speed has more wait.

CPU load is very low, the disk is close to 100%, so a lot of database update operation bottleneck is disk IO.

If SVCTM is closer to await, I/O has almost no waiting time;
If the await is much larger than SVCTM, the I/O queue is too long and the application gets slower response times

Ii. examples

For example, how do we decide which checkout to go to when we queue up in the supermarket? The first is to look at the number of rows, 5 people than 20 people faster? We often look at the number of people in front of the purchase of things, if there is a shopping for a week in front of the aunt, then you can consider changing the platoon. There is the speed of the cashier, if the money is not clear to the novice, then there are waiting. In addition, the timing is also very important, perhaps 5 minutes before the overcrowded cash table, now is empty, this time the payment is very cool Ah, of course, the premise is that the past 5 minutes to do things than queued to make sense (but I have not found anything more boring than queuing).

I/O systems also have many similarities with supermarket queues:

r/s+w/s similar to the total number of people who have been
Average Queue Length (AVGQU-SZ) is similar to the number of average queueing people in a unit time
Average service time (SVCTM) is similar to the cashier's payment speed
Average wait time (await) is similar to the average wait time per person
Average I/O data (AVGRQ-SZ) is similar to the average number of things each person buys
The I/O operation rate (%util) is similar to the time scale at which a person is queued at the cashier.
We can analyze the mode of I/O requests based on these data, and the speed and response time of I/O.

The following is an analysis of the output of this parameter written by someone else:

  [[email protected] ~]# iostat-x-M 1 Linux 3.10.0-514.el7.x86_64 06/09/2018 _x86_64_ ( CPU) Avg-cpu:%user%nice%system%iowait%steal%idle 3.64 0.00 3.95 6.15 0.00 86.25Devic             e:rrqm/s wrqm/s r/s w/s rmb/s wmb/s avgrq-sz avgqu-sz await r_await w_await SVCTM%UTILSDA 923.00 20.00 290.00 20.00 9.70 0.16 65.08 1.21 3.91 3.62 8.05 3.19 98.90  

The Iostat output above indicates that there are 310 device I/O operations per second: Total io (IO)/s = r/s (read) +w/s (write) = 290+20 = 310 (Times/sec) where the write operation takes up the body (w:r = 29:2).
The average Per Device I/O operation takes only 5ms to complete, but each I/O request needs to wait for 78ms, why? Because there are too many I/O requests (about 29 per second), assuming that these requests are issued at the same time, the average wait time can be computed like this:
Average wait time = single I/O service time (1 + 2 + ... + total requests-1)/Total requests
Apply to the above example: Average wait time = 5ms (1+2+...+28)/29 = 70ms, and the average wait time for 78ms given by Iostat is very close. This in turn indicates that I/O is initiated concurrently.
The number of I/O requests per second (about 29), the average queue is not long (only 2 or so), indicating that the arrival of these 29 requests is uneven, most of the time I/O is idle.
14.29% of the time in a second I/O queue is requested, that is, 85.71% of the time I/O system has nothing to do, all 29 I/O requests are processed within 142 milliseconds.
Delta (ruse+wuse)/delta (IO) = await = 78.21 = Delta (ruse+wuse)/s=78.21 Delta (IO)/s = 78.2128.57 = 2232.8, indicating I/O requests in total need to wait 2232.8ms. So the average queue length should be 2232.8ms/1000ms = 2.23, while the average queue Length (Avgqu-sz) given by Iostat is 22.35, why? Because the Bug,avgqu-sz value in Iostat should be 2.23, not 22.35.

※ with instructions, I use Iostat to the server detection, the general use of iostat-d command, and the results returned, I am concerned about the general TPs, BLK_READ/S, blk_wrth/s These three items, I generally take three different models of the server in the same environment under the comparison test, Such performance differences, all of a sudden come out.

Database operations are IO-intensive arguments