MySQL optimization basics (4) Linux disk IO_MySQL

1. disk IO often becomes a bottleneck of the system during IO processing, especially for systems running databases. Data is read from the disk to the memory, stored in the CPU cache and registers, then processed, and finally written back to the disk. There are a lot of processes in the middle. it is a write as 1. IO processing process.

Disk IO often becomes a bottleneck of the system, especially for systems running databases. Data is read from the disk to the memory, stored in the CPU cache and registers, then processed, and finally written back to the disk. There are many processes in the middle, it is a Linux disk I/O subsystem architecture using write as an example:

I/O operations are divided into four layers:

1) file system cache: data processing must first read from the disk to the cache, then modify, and then fl the disk. Cache refreshing involves two parameters: vm. dirty_background_ratio and vm. dirty_ratio. In addition, the bio structure is used for refresh and write-back. The bio structure is composed of adjacent blocks on the disk. Therefore, the bio structure is optimized here.

2) block layer: this layer involves the IO scheduling algorithm, which is an important optimization method on the mysql server. IO operations applied by all processes in the system are all queued here, waiting for scheduling, and then written back to the disk. There are four scheduling algorithms:

1> Anticipatory: applicable to personal PCs and single disk systems;

2> CFQ (Complete Fair Queuing): The default I/O scheduling algorithm and completely Fair Queuing scheduling algorithm. IO requests of each process are arranged into a dedicated IO queue, and I/O is scheduled fairly according to the process Group, that is, IO is scheduled among each process Group in a fair manner. Obviously, it is suitable for multi-user systems, but it is extremely unsuitable for I/O scheduling algorithms for database systems. because it is obvious that in database systems, database processes must be one of the most IO process groups, however, it can only obtain as many IO scheduling opportunities as other processes. This is obviously unreasonable. The scheduling algorithm is never used in the database system.

3> Deadline: it cyclically schedules various IO queues according to the Deadline, so it provides a near real-time IO system, and the disk throughput is also good, and will not cause starvation. this scheduling algorithm is recommended for mysql systems.

4> NOOP: a simple FIFO queue for scheduling. No operation means that it does not perform any extra operations to merge neighboring IO, so it rarely uses CPU. This scheduling algorithm is particularly suitable for SSD. Because SSD treats sequential IO and random IO with no difference. Therefore, it does not need to merge adjacent IO. This avoids the use of CPU by merge operations.

Therefore, for mysql systems, if SSD is used, the NOOP scheduling algorithm should be used. if it is a disk, the Deadline scheduling algorithm should be used.

View and modify the IO scheduling algorithm:

Temporary modification:

[Root @ localhost ~] # Cat/sys/block/sda/queue/schedue

Noop anticipatory deadline [cfq]

[Root @ localhost ~] # Echo noop>/sys/block/sda/queue/schedue

[Root @ localhost ~] # Cat/sys/block/sda/queue/schedue

[Noop] anticipatory deadline cfq

Permanent modification:

# Vi/boot/grub/menu. lst

Change to the following content:

Kernel/boot/vmlinuz-2.6.18-8.el5 ro root = LABEL =/elevator = deadline rhgb quiet

After the restart, view the scheduling method:

# Cat/sys/block/sda/queue/schedue

Noop anticipatory [deadline] cfq

It's already deadline.

3) disk driver layer: for sequential read systems, the bandwidth of the disk interface layer is easily the bottleneck;

4) disks: for systems with multiple random reads, disks can easily become bottlenecks. the general optimization is to use RAID or SSD;

2. IO bottleneck detection

2.1 Use iostat to View disk IO

Display Unit problem: iostat is a disk block by default. you can also use-k to specify kilobytes as the unit, or use-m to specify megabytes as the unit;

Statistics start time: the default iostat is similar to vmstat. by default, the first time/Line is a data item from the boot to the present. you can use the-y option to remove the first time/line data;

CPU and disk: iostat displays cpu and disk data by default. if you only need cpu data, you can use the-c option. if you only need disk data, you can use the-d option;

Interval and repetition count: [interval [times] indicates the interval and number of disk statistics;

-X: This option displays the specific extension information;

[root@localhost ~]# iostatLinux 2.6.32-504.el6.i686 (localhost.localdomain)       10/09/2015      _i686_  (1 CPU)avg-cpu:  %user   %nice %system %iowait  %steal   %idle           0.60    0.00    7.80    0.31    0.00   91.30Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtnscd0              0.02         0.21         0.00        536          0sda               2.00        78.60         8.43     198702      21312[root@localhost ~]# iostat -cLinux 2.6.32-504.el6.i686 (localhost.localdomain)       10/09/2015      _i686_  (1 CPU)avg-cpu:  %user   %nice %system %iowait  %steal   %idle           0.48    0.00    6.51    0.25    0.00   92.76[root@localhost ~]# iostat -d -kLinux 2.6.32-504.el6.i686 (localhost.localdomain)       10/09/2015      _i686_  (1 CPU)Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtnscd0              0.02         0.08         0.00        268          0sda               1.69        31.17         4.15      99363      13224[root@localhost ~]# iostat -d -mLinux 2.6.32-504.el6.i686 (localhost.localdomain)       10/09/2015      _i686_  (1 CPU)Device:            tps    MB_read/s    MB_wrtn/s    MB_read    MB_wrtnscd0              0.02         0.00         0.00          0          0sda               1.69         0.03         0.00         97         12[root@localhost ~]# iostat -d -m -xLinux 2.6.32-504.el6.i686 (localhost.localdomain)       10/09/2015      _i686_  (1 CPU)Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %utilscd0              0.02     0.00    0.02    0.00     0.00     0.00    10.94     0.00    4.96   4.88   0.01sda               1.22     0.48    1.13    0.56     0.03     0.00    41.66     0.01    6.83   5.27   0.89[root@localhost ~]# iostat -d -m -x 2 3Linux 2.6.32-504.el6.i686 (localhost.localdomain)       10/09/2015      _i686_  (1 CPU)Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %utilscd0              0.02     0.00    0.01    0.00     0.00     0.00    10.94     0.00    4.96   4.88   0.01sda               1.19     0.48    1.10    0.55     0.03     0.00    41.52     0.01    6.81   5.25   0.87Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %utilscd0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %utilscd0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00sda               0.00     0.00    0.00    0.51     0.00     0.00     8.00     0.00    3.00   3.00   0.15[root@localhost ~]# iostat -y -d -m -x 2 3Linux 2.6.32-504.el6.i686 (localhost.localdomain)       10/09/2015      _i686_  (1 CPU)Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %utilscd0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %utilscd0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %utilscd0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Field description:

Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn indicates the number of blocks read per second, the number of blocks written per second, the total number of blocks read, and the total number of blocks written

Tps: Indicate the number of transfers per second that were issued to the device. A transfer is an I/O request to the device. multiple logical requests can be combined into a single I/O request to the device. A transfer is of indeterminate size. (that is, the number of IO operations requested per second on the disk)

Rrqm/s wrqm/s r/s w/s RMB/s wMB/s avgrq-sz avgqu-sz await svctm % util

Rrqm/s wrqm/s indicates the number of merge operations performed on the adjacent disk per second during disk read and write; rrqm: read request merge; wrqm: write request merge

R/s w/s indicates the number of reads and writes per second;

RMB/s wMB/s indicates the number of MB read per second and the number of MB written

Avgrq-sz: The average size (in sectors) of the requests that were issued to the device. average number of sector involved in an IO request

Avgqu-sz: The average queue length of the requests that were issued to the device. IO queue average length, this value is very important.

Await: The average time (in milliseconds) for I/O requests issued to the device to be served. this parameter des the time spent by the requests in queue and the time spent servicing them. the average number of milliseconds (including the queue time and read/write operation time in the IO queue) of each IO ). It can be understood as the IO response time. Generally, the system IO response time should be less than 5 ms. if it is greater than 10 ms, it will be relatively large.

Svctm: Deprecated

% Util: Percentage of CPU time during which I/O requests were issued to the device (bandwidth utilization for the device ). device saturation occurs when this value is close to 100%. all IO processing time within the statistical time, divided by the total statistical time. For example, if the statistical interval is 1 second, the device processes IO for 0.8 seconds, and the device is idle for 0.2 seconds, % util = 0.8/1 = 80%, therefore, this parameter implies the degree to which the device is busy. Generally, if this parameter is set to 100%, it indicates that the device is nearing full load (of course, if it is a multi-disk, even if % util is 100%, because of the disk concurrency, so the disk usage may not be a bottleneck ).

CPU % iowait io wait high;

The avgqu-sz value of the disk is very large, The await value is very high, and the % util value is very high. both may indicate a disk bottleneck or disk problems or faults.

2.2 Use iostat to view the IO of each disk partition

I/O information of the entire disk is shown above. the following command can be used to view the I/O information of all the partitions of a specific disk:

[root@localhost ~]# iostat -x -d -m -p sda 2 3Linux 2.6.32-504.el6.i686 (localhost.localdomain)       10/09/2015      _i686_  (1 CPU)Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %utilsda               0.82     0.43    0.76    0.52     0.02     0.00    38.49     0.01    6.27   4.82   0.62sda1              0.80     0.42    0.53    0.51     0.02     0.00    45.09     0.01    6.92   5.50   0.57sda2              0.01     0.02    0.12    0.01     0.00     0.00     9.70     0.00    2.95   2.79   0.04sda3              0.01     0.00    0.07    0.00     0.00     0.00     8.67     0.00    3.72   3.65   0.03Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %utilsda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00sda1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00sda2              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00sda3              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %utilsda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00sda1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00sda2              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00sda3              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00[root@localhost ~]# iostat -d -m -p sda 2 3Linux 2.6.32-504.el6.i686 (localhost.localdomain)       10/09/2015      _i686_  (1 CPU)Device:            tps    MB_read/s    MB_wrtn/s    MB_read    MB_wrtnsda               1.25         0.02         0.00         97         18sda1              1.02         0.02         0.00         92         17sda2              0.13         0.00         0.00          2          0sda3              0.07         0.00         0.00          1          0Device:            tps    MB_read/s    MB_wrtn/s    MB_read    MB_wrtnsda               0.00         0.00         0.00          0          0sda1              0.00         0.00         0.00          0          0sda2              0.00         0.00         0.00          0          0sda3              0.00         0.00         0.00          0          0Device:            tps    MB_read/s    MB_wrtn/s    MB_read    MB_wrtnsda               0.00         0.00         0.00          0          0sda1              0.00         0.00         0.00          0          0sda2              0.00         0.00         0.00          0          0sda3              0.00         0.00         0.00          0          0

2.3 Use vmstat to View disk IO

[root@localhost ~]# vmstat 2 4procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu----- r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st 0  0      0 454900  21808  76776    0    0    20     4   97   81  0  5 95  0  0 0  0      0 454892  21808  76772    0    0     0     8   83  106  0  3 97  0  0 1  0      0 454760  21816  76772    0    0     0    44  101  153  1  5 94  0  0 0  0      0 454760  21816  76784    0    0     0     0   57   68  0  1 99  0  0

Bi: Blocks received from a block device (blocks/s). The number of Blocks read per second to the memory.

Bo: Blocks sent to a block device (blocks/s). how many Blocks are written to the disk per second in the memory?

2.4 View disk IO using sar-B

[root@localhost ~]# sar -b 2 4Linux 2.6.32-504.el6.i686 (localhost.localdomain)       10/09/2015      _i686_  (1 CPU)03:53:21 PM       tps      rtps      wtps   bread/s   bwrtn/s03:53:23 PM      0.00      0.00      0.00      0.00      0.0003:53:25 PM      0.00      0.00      0.00      0.00      0.0003:53:27 PM      0.00      0.00      0.00      0.00      0.0003:53:29 PM      0.00      0.00      0.00      0.00      0.00Average:         0.00      0.00      0.00      0.00      0.00

Tps: described above; rtps: indicates the read tps; wtps: indicates the written tps;

Bread/s: the number of blocks read per second; bwrtn/s: the number of blocks written per second;

2.5 Use iotop to find the process/thread with the most IO

Iotop is similar to the top command, which is sorted by IO by default:

Iotop:

Iotop is interactive:

Use the left and right arrows to change the sorting, r to reverse the sorting order, o to toggle the -- only

Option, p to toggle the -- processes option, a to toggle the -- accumulated option, q to quit or I to change

Priority of a thread or a process' thread (s). Any other key will force a refresh.

1) you can use the left and right keys to select the sorting fields. by default, the fields are sorted by IO> inverted order. you can use the left and right direction keys to sort the fields such as SWAPIN and disk write;

2) The p key can be used to switch between display by process and display by thread;

3) The r key can change the direction of sorting: descending order and order.

View the IO of mysqld:

Iotop-k-u mysql (-k indicates KB,-u mysql indicates displaying IO of all processes of mysql users ):

3. instance analysis

$iostat -d -k 1 |grep sda10Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtnsda10            60.72        18.95        71.53  395637647 1493241908sda10           299.02      4266.67       129.41       4352        132sda10           483.84      4589.90      4117.17       4544       4076sda10           218.00      3360.00       100.00       3360        100sda10           546.00      8784.00       124.00       8784        124sda10           827.00     13232.00       136.00      13232        136

As shown above, the average number of disk transfers per second is about 400; the disk reads about 5 MB per second, and writes about 1 MB.

Iostat-d-x-k 1

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm % util

Sda 1.56 28.31 7.84 31.50 43.65 3.16 21.82 1.58 1.19 0.03 0.80 2.61 10.29

Sda 1.98 24.75 419.80 6.93 13465.35 253.47 6732.67 126.73 32.15 2.00 4.70 2.00 85.25

Sda 3.06 41.84 444.90 54.08 14204.08 2048.98 7102.04 1024.49 32.57 2.10 4.21 1.85 92.24

The average response time of the disk is <5 ms, and the disk usage is> 80. The disk response is normal, but it is busy.

4. disk IO optimization

Before disk I/O optimization, you must first find out the system I/O conditions, such as random I/O or sequential I/O, large file I/O, i/O of small files (I/O of small files is generally random I/O ). For example, if there are many random I/O operations, you can use RAID technology to optimize them by adding disks. if sequential I/O encounters a bottleneck, it may be because the bandwidth of the disk driver has a bottleneck, you can change to a faster disk controller. Find out whether there is a bottleneck on the disk or the bandwidth bottleneck on the disk drive.

Since disk I/O operations are divided into four layers, I/O optimization can also begin with these four aspects:

1) When optimizing the mysql system, you also need to select the correct IO scheduling algorithm. for SSD, select NOOP scheduling algorithm. for disk, select deadline scheduling algorithm;

2) for mysql, it is clear that you can use maser-slave to perform disk IO optimization for read/write splitting.

3) In addition, increasing the memory can cache more disk files and reduce the I/O pressure.

4) the mount option noatime and nodiratime of the file system can also reduce the IO pressure. In addition, selecting the correct file system can also improve the disk tps. XFS is recommended for mysql databases.

Note: the disk IO Data shown above is mostly 0, because the data comes from the Linux system in the virtual machine.