Innodb_io_capacity research on IO stability _ MySQL

Innodb_io_capacity some research on IO stability bitsCN.com

Background: I/O loads on an online server recently discovered the following phenomena:

The 24-hour IO_UTIL curve seems calm and has fewer burrs.

However, when the image is enlarged to half an hour, it is found that IO_UTIL indicates that the disk usage fluctuates regularly. for details, see:

This article will start from this phenomenon and explore the reasons for such regular fluctuations.

Step 1: view the real-time IO load on the server

Use iostat-x 1 to view the I/O usage once every second. We can see that UTIL fluctuates regularly (once every 10 seconds ).

And the main load source is write requests (when the load is high, wsec/s also increases)

Because the server is exclusively occupied by MySQL, it is easier to attribute the cause to the data flushing of MySQL (log/data ).

It is easy to guess that it is the 10_seconds loop in MySQL src_master_thread.

To ensure the consistency of diagnostic ideas, repeat the operation at that time.

root@db-mysql-tg03b.nh:/root>iostat -x 1 vgca0 |grep vgca0
Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %utilvgca0             0.00     0.00 12.96 371.75  1562.56 24314.49    67.26     0.37    0.28   0.28  10.62vgca0             0.00     0.00  2.00  2.00    40.00   138.00    44.50     0.00    1.00   1.00   0.40vgca0             0.00     0.00  1.00  2.00    32.00   170.00    67.33     0.00    1.00   1.00   0.30vgca0             0.00     0.00  3.00  2.00    96.00   130.00    45.20     0.00    0.80   0.80   0.40vgca0             0.00     0.00  4.00  2.00   128.00   178.00    51.00     0.00    0.67   0.67   0.40vgca0             0.00     0.00  1.00  5.00    32.00   170.00    33.67     0.00    0.83   0.83   0.50vgca0             0.00     0.00  0.00 1868.00     0.00 101510.00    54.34     1.28    0.16   0.16  30.20vgca0             0.00     0.00  7.00  2.00   200.00   130.00    36.67     0.10   12.78  12.78  11.50vgca0             0.00     0.00  1.98  1.98    39.60   160.40    50.50     0.00    1.00   1.00   0.40vgca0             0.00     0.00  6.00  2.00   168.00   170.00    42.25     0.00    0.88   0.88   0.70vgca0             0.00     0.00  4.00 26.00   104.00  5026.00   171.00     0.02    0.73   0.73   2.20vgca0             0.00     0.00 10.00  2.00   296.00   164.00    38.33     0.04    3.92   3.92   4.70vgca0             0.00     0.00  1.00  2.00     8.00   138.00    48.67     0.00    1.00   1.00   0.30vgca0             0.00     0.00  2.00  2.00    40.00   162.00    50.50     0.00    1.00   1.00   0.40vgca0             0.00     0.00  3.00  2.00    72.00   170.00    48.40     0.00    0.80   0.80   0.40vgca0             0.00     0.00  2.00  6.00    40.00   172.00    26.50     0.00    0.50   0.50   0.40vgca0             0.00     0.00  2.00 601.00    40.00 32942.00    54.70     0.39    0.17   0.17  10.40vgca0             0.00     0.00  2.00 1179.00    16.00 63932.00    54.15     0.82    0.17   0.17  19.90vgca0             0.00     0.00  3.96  1.98   126.73   144.55    45.67     0.00    1.50   1.50   0.89vgca0             0.00     0.00  3.00  2.00    96.00   170.00    53.20     0.00    1.00   1.00   0.50vgca0             0.00     0.00  4.00  3.00   104.00   162.00    38.00     0.00    0.86   0.86   0.60vgca0             0.00     0.00  1.00  2.00     8.00   138.00    48.67     0.00    0.33   0.33   0.10vgca0             0.00     0.00  6.00  2.00   168.00   178.00    43.25     0.00    1.12   1.12   0.90vgca0             0.00     0.00  5.00  2.00   136.00   138.00    39.14     0.00    0.86   0.86   0.60vgca0             0.00     0.00  4.00  2.00   104.00   170.00    45.67     0.00    0.83   0.83   0.50vgca0             0.00     0.00  2.00  2.00    40.00   162.00    50.50     0.00    1.00   1.00   0.40vgca0             0.00     0.00  1.00  5.00     8.00   187.00    32.50     0.00    0.50   0.50   0.30vgca0             0.00     0.00  7.00 1815.00   104.00 98762.00    54.26     1.31    0.25   0.25  45.50vgca0             0.00     0.00  5.00  2.00   136.00   178.00    44.86     0.00    0.86   0.86   0.60

Step 2: determine the MySQL write IO Source

Currently, we only know that MySQL data is flushed, so we do not know whether it is caused by redo-log or data page flush.

The statistics of write in innodb is still relatively limited. Therefore, a perl script similar to top is roughly written to monitor these parameters.

The script is as follows. if you are interested, read it.

use strict;use warnings;use utf8;use DBI;my $CONFIG_SERVER_IP  ='127.0.0.1';my $CONFIG_SERVER_DB='test';my $CONFIG_SERVER_PORT='3306';my $CONFIG_SERVER_USER='user';my $CONFIG_SERVER_PASS='password';my $conf_dbh = DBI->connect("DBI:mysql:$CONFIG_SERVER_DB;host=$CONFIG_SERVER_IP;port=$CONFIG_SERVER_PORT", $CONFIG_SERVER_USER, $CONFIG_SERVER_PASS,{RaiseError => 1}) || die "Could not connect to database: $DBI::errstr";my %last_value_hash;print "data_write/tdata_written/tdblwr_pages_written/tdblwr_writes/tlog_write_req/tos_log_fsync/tos_log_written/tpages_written/n";while(1){    my $conf_sth = $conf_dbh->prepare("show global status like '%innodb%'") || die "Prepare error";    $conf_sth->execute();    while(my $row=$conf_sth->fetchrow_hashref){        my $name=$row->{Variable_name};        my $value=$row->{Value};        if( $name eq 'Innodb_data_writes' || $name eq 'Innodb_data_written'         || $name eq 'Innodb_dblwr_pages_written' || $name eq 'Innodb_dblwr_writes'         || $name eq 'Innodb_log_write_requests' || $name eq 'Innodb_os_log_fsyncs'         || $name eq 'Innodb_os_log_written' || $name eq 'Innodb_pages_written'){            $last_value_hash{$name}=0 if( !defined($last_value_hash{$name}) );            my $value_step=$value-$last_value_hash{$name};            $last_value_hash{$name}=$value;            print "$value_step/t";        }    }    print "/n";    sleep 1;}

Innodb write monitor

The following figure shows the parameter fluctuations:

We can see that innodb_data_writes/innodb_data_written/innodb_dblwr_pages_written/innodb_pages_written both fluctuate once every 10 seconds like IO_UTIL.

Combined with the large value of wsec/s in iostat, it can be basically determined that the culprit of high IO load is the flush of data page, rather than the flush of redo log.

data_write    data_written    dblwr_pages_written    dblwr_writes    log_write_req    os_log_fsync    os_log_written    pages_written1    79360    0    0    169    1    79360    01    72192    0    0    127    1    72192    01    67072    0    0    139    1    67072    01    69120    0    0    149    1    69120    01    74752    0    0    128    1    74752    01    61952    0    0    134    1    61952    01    71168    0    0    131    1    71168    01    62976    0    0    126    1    62976    01    71168    0    0    109    1    71168    01388    44870144    1367    14    125    6    75776    13671    66048    0    0    158    1    66048    01    73728    0    0    144    1    73728    01    69632    0    0    126    1    69632    01    75776    0    0    172    1    75776    01    88576    0    0    151    1    88576    01    67584    0    0    134    1    67584    01    80384    0    0    155    1    80384    01    86528    0    0    191    1    86528    01    72704    0    0    135    1    72704    01525    49385984    1504    13    154    5    102400    15041    74752    0    0    158    1    74752    0

Step 3: When does Innodb flush dirty data page?

Since the data flushing of innodb is just a little bit unfamiliar, there are also different opinions on the Flushing policies on the internet. it seems that I cannot go into depth after diagnosis.

In fact, the truth is quietly lying there: reading the source code.

The code path for Innodb disk writing is clear and easy to read. The main code is stored in storage/innodb_plugin/Buf/Buf0flu. c.

The code is too long to be pasted here. it mainly describes the calling relationship.

Buf_flush_batch calls buf_flush_try_neighbors (attempts to write the neighbor page)

Buf_flush_try_neighbots calls buf_flush_page (fl a single page)

Buf_flush_page calls buf_flush_write_block_low (actually fl a single page)

Buf_flush_write_block_low call buf_flush_post_to_doublewrite_buf (put the page in the double write buffer and prepare to be written)

Buf_flush_post_to_doublewrite_buf call fil_io (file IO encapsulation)

Fil_io calls OS _aio (aio related operations)

OS _aio calls OS _file_write (actual file write operations)

Buf_flush_batch has only two flushing modes: BUF_FLUSH_LIST and BUF_FLUSH_LRU:

BUF_FLUSH_LIST: it is called in the 1_second/10 second loop of the innodb master thread. There are many trigger conditions (which will be analyzed below)

BUF_FLUSH_LRU: called when the Buffer Pool has no idle page and there is not enough clean page in the old list. Some free pages can be blank after dirty pages are written for BP.

From the trigger frequency, we can see that the call to buf_flush_batch (BUF_FLUSH_LIST) in the 10 second loop is the culprit of the high I/O load once every 10 seconds.

Let's look at the flush logic in a 10-second loop:

The parameter is used to determine whether the IO of the previous loop is idle. if it is idle, the buf_flush_batch (BUF_FLUSH_LIST,PCT_IO (100));

If the proportion of dirty pages exceeds 70, buf_flush_batch (BUF_FLUSH_LIST,PCT_IO (100));

Otherwise, buf_flush_batch (BUF_FLUSH_LIST,PCT_IO (10));

We can see that the SSD responds very quickly to random write requests, resulting in almost no IO accumulation. In this way, innodb mistakenly believes that I/O is idle, and decides to fully write.

PCT_IO (N) = innodb_io_capacity * N %. unit: page. Therefore, innodb refreshes at least 10000 pages every 10 seconds or clears all dirty pages.

Step 4: further analyze and find the solution

After innodb_adaptive_flushing and innodb_adaptive_flushing_method are adjusted multiple times, the phenomenon is not changed.

This is also easy to explain: the reason for the large volume of write Flushing is that innodb considers IO to be relatively idle in a 10-second loop, so it is best to perform full write based on innodb_io_capacity; adaptive only occurs in a one-second loop.

Therefore, adjusting adaptive parameters does not solve the problem.

From the analysis in step 3, we can draw a clear conclusion that the problem can be solved to a large extent after innodb_io_capacity is reduced.

After we adjust innodb_io_capacity from 10000 to 200 and add the following configuration:

Innodb_adaptive_flush = OFF;

Innodb_adaptive_checkpoint = keep_average

We can see that the data written write volume of mysql is greatly reduced and kept stable (see, after, the result of parameter adjustment)

At the same time, from the perspective of flash card hardware writing volume, it also reduces a lot (see)

References:

Http://www.mysqlperformanceblog.com/2011/03/31/innodb-flushing-a-lot-of-memory-and-slow-disk/

Https://www.google.com/reader/view/#stream/feed%2Fhttp%3A%2F%2Fwww.xaprb.com%2Fblog%2Ffeed%2F

Http://www.mysqlperformanceblog.com/2012/09/10/adaptive-flushing-in-mysql-5-6-cont/

Http://dimitrik.free.fr/blog/archives/04-01-2012_04-30-2012.html#143

Https://www.google.com/reader/view/#stream/feed%2Fhttp%3A%2F%2Fplanet.mysql.com%2Frss20.xml

BitsCN.com