<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-configuring-io-capacity">
      </a>
      17.8.7 Configuring InnoDB I/O Capacity
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045162677200">
  </a>
  <a class="indexterm" name="idm46045162676128">
  </a>
  <p>
   The
   <code class="literal">
    InnoDB
   </code>
   master thread and other threads
      perform various tasks in the background, most of which are I/O
      related, such as flushing dirty pages from the buffer pool and
      writing changes from the change buffer to the appropriate
      secondary indexes.
   <code class="literal">
    InnoDB
   </code>
   attempts to perform
      these tasks in a way that does not adversely affect the normal
      working of the server. It tries to estimate the available I/O
      bandwidth and tune its activities to take advantage of available
      capacity.
  </p>
  <p>
   The
   <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
    <code class="literal">
     innodb_io_capacity
    </code>
   </a>
   variable
      defines the overall I/O capacity available to
   <code class="literal">
    InnoDB
   </code>
   . It should be set to approximately the
      number of I/O operations that the system can perform per second
      (IOPS). When
   <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
    <code class="literal">
     innodb_io_capacity
    </code>
   </a>
   is set,
   <code class="literal">
    InnoDB
   </code>
   estimates the I/O bandwidth
      available for background tasks based on the set value.
  </p>
  <p>
   You can set
   <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
    <code class="literal">
     innodb_io_capacity
    </code>
   </a>
   to
      a value of 100 or greater. The default value is
   <code class="literal">
    10000
   </code>
   . Typically, faster hard drives, RAID
      configurations, and solid state drives (SSDs) benefit from higher
      values than do lower-end storage devices, such as hard drives up
      to 7200 RPMs.
  </p>
  <p>
   Ideally, keep the setting as low as practical, but not so low that
      background activities fall behind. If the value is too high, data
      is removed from the buffer pool and change buffer too quickly for
      caching to provide a significant benefit. For busy systems capable
      of higher I/O rates, you can set a higher value to help the server
      handle the background maintenance work associated with a high rate
      of row changes. Generally, you can increase the value as a
      function of the number of drives used for
   <code class="literal">
    InnoDB
   </code>
   I/O. For example, you can increase the
      value on systems that use multiple disks or SSDs.
  </p>
  <p>
   Although you can specify a high value such as a million, in
      practice such large values have little benefit. Generally, a value
      higher than 20000 is not recommended unless you are certain that
      lower values are insufficient for your workload. See also the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity_max">
    <code class="literal">
     innodb_io_capacity_max
    </code>
   </a>
   option
      that automatically increases this value when flushing falls
      behind.
  </p>
  <p>
   Consider write workload when tuning
   <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
    <code class="literal">
     innodb_io_capacity
    </code>
   </a>
   . Systems with
      large write workloads are likely to benefit from a higher setting.
      A lower setting may be sufficient for systems with a small write
      workload.
  </p>
  <p>
   The
   <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
    <code class="literal">
     innodb_io_capacity
    </code>
   </a>
   setting is
      not a per buffer pool instance setting. Available I/O capacity is
      distributed equally among buffer pool instances for flushing
      activities.
  </p>
  <p>
   You can set the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
    <code class="literal">
     innodb_io_capacity
    </code>
   </a>
   value in the
      MySQL option file (
   <code class="literal">
    my.cnf
   </code>
   or
   <code class="literal">
    my.ini
   </code>
   ) or modify it at runtime using a
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET GLOBAL
    </code>
   </a>
   statement, which requires privileges sufficient to set global
      system variables. See
   <a class="xref" href="system-variable-privileges.html" title="7.1.9.1 System Variable Privileges">
    Section 7.1.9.1, “System Variable Privileges”
   </a>
   .
  </p>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="innodb-ignore-io-capacity">
       </a>
       Ignoring I/O Capacity at Checkpoints
      </h4>
     </div>
    </div>
   </div>
   <p>
    The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_flush_sync">
     <code class="literal">
      innodb_flush_sync
     </code>
    </a>
    variable,
        which is enabled by default, causes the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
     <code class="literal">
      innodb_io_capacity
     </code>
    </a>
    setting to
        be ignored during bursts of I/O activity that occur at
    <a class="link" href="glossary.html#glos_checkpoint" title="checkpoint">
     checkpoints
    </a>
    . To adhere to
        the I/O rate defined by the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
     <code class="literal">
      innodb_io_capacity
     </code>
    </a>
    and
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity_max">
     <code class="literal">
      innodb_io_capacity_max
     </code>
    </a>
    settings, disable
    <a class="link" href="innodb-parameters.html#sysvar_innodb_flush_sync">
     <code class="literal">
      innodb_flush_sync
     </code>
    </a>
    .
   </p>
   <p>
    You can set the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_flush_sync">
     <code class="literal">
      innodb_flush_sync
     </code>
    </a>
    value in the
        MySQL option file (
    <code class="literal">
     my.cnf
    </code>
    or
    <code class="literal">
     my.ini
    </code>
    ) or modify it at runtime using a
    <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
     <code class="literal">
      SET
        GLOBAL
     </code>
    </a>
    statement, which requires privileges sufficient
        to set global system variables. See
    <a class="xref" href="system-variable-privileges.html" title="7.1.9.1 System Variable Privileges">
     Section 7.1.9.1, “System Variable Privileges”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="innodb-io-capacity-maximum">
       </a>
       Configuring an I/O Capacity Maximum
      </h4>
     </div>
    </div>
   </div>
   <p>
    If flushing activity falls behind,
    <code class="literal">
     InnoDB
    </code>
    can
        flush more aggressively, at a higher rate of I/O operations per
        second (IOPS) than defined by the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
     <code class="literal">
      innodb_io_capacity
     </code>
    </a>
    variable.
        The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity_max">
     <code class="literal">
      innodb_io_capacity_max
     </code>
    </a>
    variable defines a maximum number of IOPS performed by
    <code class="literal">
     InnoDB
    </code>
    background tasks in such situations.
   </p>
   <p>
    If you specify an
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
     <code class="literal">
      innodb_io_capacity
     </code>
    </a>
    setting at
        startup but do not specify a value for
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity_max">
     <code class="literal">
      innodb_io_capacity_max
     </code>
    </a>
    ,
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity_max">
     <code class="literal">
      innodb_io_capacity_max
     </code>
    </a>
    defaults
        to twice the value of
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
     <code class="literal">
      innodb_io_capacity
     </code>
    </a>
    .
   </p>
   <p>
    When configuring
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity_max">
     <code class="literal">
      innodb_io_capacity_max
     </code>
    </a>
    , twice
        the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
     <code class="literal">
      innodb_io_capacity
     </code>
    </a>
    is often
        a good starting point. As with the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
     <code class="literal">
      innodb_io_capacity
     </code>
    </a>
    setting,
        keep the setting as low as practical, but not so low that
    <code class="literal">
     InnoDB
    </code>
    cannot sufficiently extend rate of
        IOPS beyond the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
     <code class="literal">
      innodb_io_capacity
     </code>
    </a>
    setting.
   </p>
   <p>
    Consider write workload when tuning
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity_max">
     <code class="literal">
      innodb_io_capacity_max
     </code>
    </a>
    . Systems
        with large write workloads may benefit from a higher setting. A
        lower setting may be sufficient for systems with a small write
        workload.
   </p>
   <p>
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity_max">
     <code class="literal">
      innodb_io_capacity_max
     </code>
    </a>
    cannot
        be set to a value lower than the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
     <code class="literal">
      innodb_io_capacity
     </code>
    </a>
    value.
   </p>
   <p>
    Setting
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity_max">
     <code class="literal">
      innodb_io_capacity_max
     </code>
    </a>
    to
    <code class="literal">
     DEFAULT
    </code>
    using a
    <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
     <code class="literal">
      SET
     </code>
    </a>
    statement (
    <code class="literal">
     SET GLOBAL
        innodb_io_capacity_max=DEFAULT
    </code>
    ) sets
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity_max">
     <code class="literal">
      innodb_io_capacity_max
     </code>
    </a>
    to the
        default value. Before MySQL 8.4, this set it to the maximum
        value instead of the default value.
   </p>
   <p>
    The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity_max">
     <code class="literal">
      innodb_io_capacity_max
     </code>
    </a>
    limit applies to all buffer pool instances. It is not a per
        buffer pool instance setting.
   </p>
  </div>
 </div>
 <br/>
</div>
