Percona Server moved to https://jira.percona.com/projects/PS

innodb_adaptive_flushing_method=estimate and innodb_io_capacity

Bug #996545 reported by Jay Janssen on 2012-05-08

This bug affects 2 people

Affects		Status	Importance	Assigned to	Milestone
	Percona Server moved to https://jira.percona.com/projects/PS	Fix Released	Medium	Hrvoje Matijakovic
	5.5	Fix Released	Medium	Hrvoje Matijakovic

Bug Description

The documentation states that the 'innodb_io_capacity' variable has no effect when 'estimate' is set for the flushing method. However, this is untrue. While I believe 'estimate' disables Innodb's 1 second loop for dirty buffer flushing, I do not believe it disables the 10 second loop. The result is bursty 10 second writes in the case where innodb_io_capacity is set.

In some cases this provides enough dirty buffer flushing to prevent the 'estimate' method from ever kicking in (i.e., because the Checkpoint age never reaches 50% of the max checkpoint age). Or, if adaptive flushing is in play, it prevents the algorithm from smoothly adjusting write IO.

For example, this is output from my 'myq_gadgets' tool that shows adaptive flushing in action:

Innodb Flushing Checkpoint Pages
time meth %dirt flush ckpt trgt max wops wsize
13:28:12 estim 2.33% 4.0 332M 27.6% 26.7% 2.0 32.0K
13:28:13 estim 2.33% 4.0 332M 27.6% 26.7% 2.0 32.0K
13:28:14 estim 2.34% 10.0 332M 27.6% 26.7% 5.0 80.0K
13:28:15 estim 2.32% 1.8k 330M 27.4% 26.6% 892.0 13.9M
13:28:16 estim 2.32% 4.0 331M 27.4% 26.6% 2.0 32.0K
13:28:17 estim 2.32% 10.0 331M 27.5% 26.6% 5.0 80.0K
13:28:18 estim 2.33% 10.0 331M 27.5% 26.6% 5.0 80.0K
13:28:19 estim 2.33% 10.0 331M 27.5% 26.6% 5.0 80.0K
13:28:20 estim 2.33% 20.0 331M 27.5% 26.6% 10.0 160K
13:28:21 estim 2.33% 8.0 331M 27.5% 26.7% 4.0 64.0K
13:28:22 estim 2.33% 2.0 332M 27.5% 26.7% 1.0 16.0K
13:28:23 estim 2.33% 10.0 332M 27.5% 26.7% 5.0 80.0K
13:28:24 estim 2.33% 8.0 332M 27.6% 26.7% 4.0 64.0K
13:28:25 estim 2.32% 894.0 331M 27.4% 26.6% 447.0 6.98M
13:28:27 estim 2.32% 6.0 331M 27.5% 26.6% 3.0 48.0K

This output shows the flushing method ('estimate'), the amount of the buffer pool that is dirty (%dirt), the rate of innodb_buffer_pool_pages_flushed (flush), the checkpoint age (ckpt), how close the checkpoint age is to the checkpoint_age_target (trgt), how close the checkpoint age is to the max checkpoint age ('max'), the rate of 'innodb_pages_written' (wops) and the amount of data that is (innodb_pages_written * innodb_page_size).

Notice places where the amount of data jumps significantly, at 13:28:14 and again at 13:28:25. This pattern suggests to me a 10 second loop.

I *believe* it would be ideal if 'estimate' truly ignore the innodb_io_capacity as the doc indicates, however I would agree that it will likely change write characteristics on a lot of production servers.

Related branches

lp://staging/~hrvojem/percona-server/bug1059987-5.5

Merged into lp://staging/percona-server/5.5 at revision 466

Alexey Kopytov (community): Approve on 2013-03-08

Revision history for this message

Laurynas Biveinis (laurynas-biveinis) wrote on 2012-05-09:

Assigning this as a documentation bug to Hrvoje.

If code changes / new options to control behavior etc. are desired, I believe they best discussed separately from this one.

Changed in percona-server:
assignee:	nobody → Hrvoje Matijakovic (hrvojem)

Stewart Smith (stewart) on 2012-06-14

Changed in percona-server:
status:	New → Triaged
importance:	Undecided → Medium

Revision history for this message

Aurimas Mikalauskas (aurimas-mikalauskas) wrote on 2013-01-08:

For the record, I'm attaching email covering investigation on innodb_io_capacity that Laurynas was doing:

Aurimas, this is to answer your question, based on Percona Server 5.5:

  Only the master thread uses innodb_io_capacity in its background
  tasks. The master thread can be in one of the following loops
  depending on the server state and time:
  - Busy server loop that is executed once per one second.
  - Busy server loop that is executed once per ten seconds.
  - Idle server loop.

  Each of these loops does something that is I/O capacity related. I'm
  omitting other tasks that do I/O but do not use capacity (i.e. purge).
  The I/O-capacity-related tasks of the 1s busy loop:
  - If I/Os issued in the last 1s is less than 5% of capacity and
    currently outstanding I/Os are less than 3% of capacity, perform
    ibuf merge. The ibuf merges here and below also use
    innodb_io_capacity in determining how much work to do, I'm
    omitting this for simplicity because of ibuf_accel_rate.
  - If innodb_max_dirty_pages_pct is violated, flush io_capacity
    number of pages.
  - Otherwise, if adaptive flushing "estimate" method is used, flush
    at least io_capacity pages. ("at least" means I am omitting the
    details of flushing method adaptiveness here). Also note that
    this is the only adaptive flushing method that pays attention to
    I/O capacity at all.

  The tasks of the 10s busy loop:
  - If there were less than 200% of I/O capacity requests issued in
    the last 10s and currently outstanding I/O requests are less
    than 3% of capacity, flush io_capacity number of pages.
  - Unconditional ibuf merge.
  - If dirty pages in the buffer > 70%, flush io_capacity number of
    pages, otherwise io_capacity/10 pages.

  The tasks of the idle server loop. If there is server activity
  during any of these, the server goes back to the busy 1s loop.
  - Unconditional ibuf merge.
  - Unconditional flushing of io_capacity number of pages in a loop
    at least once or until innodb_max_dirty_pages_pct is not
    violated.

For the record, I'm attaching email covering investigation on innodb_io_capacity that Laurynas was doing:

Aurimas, this is to answer your question, based on Percona Server 5.5:

Only the master thread uses innodb_io_capacity in its background
  tasks.  The master thread can be in one of the following loops
  depending on the server state and time:
  - Busy server loop that is executed once per one second.
  - Busy server loop that is executed once per ten seconds.
  - Idle server loop.

Each of these loops does something that is I/O capacity related. I'm
  omitting other tasks that do I/O but do not use capacity (i.e. purge).
  The I/O-capacity-related tasks of the 1s busy loop:
  - If I/Os issued in the last 1s is less than 5% of capacity and
    currently outstanding I/Os are less than 3% of capacity, perform
    ibuf merge. The ibuf merges here and below also use
    innodb_io_capacity in determining how much work to do, I'm
    omitting this for simplicity because of ibuf_accel_rate.
  - If innodb_max_dirty_pages_pct is violated, flush io_capacity
    number of pages.
  - Otherwise, if adaptive flushing "estimate" method is used, flush
    at least io_capacity pages. ("at least" means I am omitting the
    details of flushing method adaptiveness here).  Also note that
    this is the only adaptive flushing method that pays attention to
    I/O capacity at all.

The tasks of the idle server loop.  If there is server activity
  during any of these, the server goes back to the busy 1s loop.
  - Unconditional ibuf merge.
  - Unconditional flushing of io_capacity number of pages in a loop
    at least once or until innodb_max_dirty_pages_pct is not
    violated.

Hrvoje Matijakovic (hrvojem) on 2013-03-13

Changed in percona-server:
status:	Fix Committed → Fix Released

Revision history for this message

Shahriyar Rzayev (rzayev-sehriyar) wrote on 2018-01-25:

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1248

Report a bug

This report contains Public information

Everyone can see this information.

You are

Subscribing...

Edit bug mail

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.