<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="optimizing-innodb-diskio">
      </a>
      10.5.8 Optimizing InnoDB Disk I/O
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045226026784">
  </a>
  <a class="indexterm" name="idm46045226025296">
  </a>
  <a class="indexterm" name="idm46045226024224">
  </a>
  <a class="indexterm" name="idm46045226023152">
  </a>
  <p>
   If you follow best practices for database design and tuning
        techniques for SQL operations, but your database is still slow
        due to heavy disk I/O activity, consider these disk I/O
        optimizations. If the Unix
   <code class="filename">
    top
   </code>
   tool or the
        Windows Task Manager shows that the CPU usage percentage with
        your workload is less than 70%, your workload is probably
        disk-bound.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Increase buffer pool size
     </p>
     <p>
      When table data is cached in the
      <code class="literal">
       InnoDB
      </code>
      buffer pool, it can be accessed repeatedly by queries
            without requiring any disk I/O. Specify the size of the
            buffer pool with the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_buffer_pool_size">
       <code class="literal">
        innodb_buffer_pool_size
       </code>
      </a>
      option. This memory area is important enough that it is
            typically recommended that
      <a class="link" href="innodb-parameters.html#sysvar_innodb_buffer_pool_size">
       <code class="literal">
        innodb_buffer_pool_size
       </code>
      </a>
      is
            configured to 50 to 75 percent of system memory. For more
            information see,
      <a class="xref" href="memory-use.html" title="10.12.3.1 How MySQL Uses Memory">
       Section 10.12.3.1, “How MySQL Uses Memory”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Adjust the flush method
     </p>
     <p>
      In some versions of GNU/Linux and Unix, flushing files to
            disk with the Unix
      <code class="literal">
       fsync()
      </code>
      call and
            similar methods is surprisingly slow. If database write
            performance is an issue, conduct benchmarks with the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_flush_method">
       <code class="literal">
        innodb_flush_method
       </code>
      </a>
      parameter set to
      <code class="literal">
       O_DSYNC
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Configure a threshold for operating system flushes
     </p>
     <p>
      By default, when
      <code class="literal">
       InnoDB
      </code>
      creates a new
            data file, such as a new log file or tablespace file, the
            file is fully written to the operating system cache before
            it is flushed to disk, which can cause a large amount of
            disk write activity to occur at once. To force smaller,
            periodic flushes of data from the operating system cache,
            you can use the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_fsync_threshold">
       <code class="literal">
        innodb_fsync_threshold
       </code>
      </a>
      variable to define a threshold value, in bytes. When the
            byte threshold is reached, the contents of the operating
            system cache are flushed to disk. The default value of 0
            forces the default behavior, which is to flush data to disk
            only after a file is fully written to the cache.
     </p>
     <p>
      Specifying a threshold to force smaller, periodic flushes
            may be beneficial in cases where multiple MySQL instances
            use the same storage devices. For example, creating a new
            MySQL instance and its associated data files could cause
            large surges of disk write activity, impeding the
            performance of other MySQL instances that use the same
            storage devices. Configuring a threshold helps avoid such
            surges in write activity.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use fdatasync() instead of fsync()
     </p>
     <p>
      On platforms that support
      <code class="literal">
       fdatasync()
      </code>
      system calls, the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_use_fdatasync">
       <code class="literal">
        innodb_use_fdatasync
       </code>
      </a>
      variable permits using
      <code class="literal">
       fdatasync()
      </code>
      instead of
      <code class="literal">
       fsync()
      </code>
      for operating system
            flushes. An
      <code class="literal">
       fdatasync()
      </code>
      system call does
            not flush changes to file metadata unless required for
            subsequent data retrieval, providing a potential performance
            benefit.
     </p>
     <p>
      A subset of
      <a class="link" href="innodb-parameters.html#sysvar_innodb_flush_method">
       <code class="literal">
        innodb_flush_method
       </code>
      </a>
      settings such as
      <code class="literal">
       fsync
      </code>
      ,
      <code class="literal">
       O_DSYNC
      </code>
      , and
      <code class="literal">
       O_DIRECT
      </code>
      use
      <code class="literal">
       fsync()
      </code>
      system calls. The
      <a class="link" href="innodb-parameters.html#sysvar_innodb_use_fdatasync">
       <code class="literal">
        innodb_use_fdatasync
       </code>
      </a>
      variable is applicable when using those settings.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use a noop or deadline I/O scheduler with native AIO on
            Linux
     </p>
     <p>
      <code class="literal">
       InnoDB
      </code>
      uses the asynchronous I/O
            subsystem (native AIO) on Linux to perform read-ahead and
            write requests for data file pages. This behavior is
            controlled by the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_use_native_aio">
       <code class="literal">
        innodb_use_native_aio
       </code>
      </a>
      configuration option, which is enabled by default. With
            native AIO, the type of I/O scheduler has greater influence
            on I/O performance. Generally, noop and deadline I/O
            schedulers are recommended. Conduct benchmarks to determine
            which I/O scheduler provides the best results for your
            workload and environment. For more information, see
      <a class="xref" href="innodb-linux-native-aio.html" title="17.8.6 Using Asynchronous I/O on Linux">
       Section 17.8.6, “Using Asynchronous I/O on Linux”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Use direct I/O on Solaris 10 for x86_64 architecture
     </p>
     <p>
      When using the
      <code class="literal">
       InnoDB
      </code>
      storage engine on
            Solaris 10 for x86_64 architecture (AMD Opteron), use direct
            I/O for
      <code class="literal">
       InnoDB
      </code>
      -related files to avoid
            degradation of
      <code class="literal">
       InnoDB
      </code>
      performance. To use
            direct I/O for an entire UFS file system used for storing
      <code class="literal">
       InnoDB
      </code>
      -related files, mount it with the
      <code class="literal">
       forcedirectio
      </code>
      option; see
      <code class="literal">
       mount_ufs(1M)
      </code>
      . (The default on Solaris
            10/x86_64 is
      <span class="emphasis">
       <em>
        not
       </em>
      </span>
      to use this option.)
            To apply direct I/O only to
      <code class="literal">
       InnoDB
      </code>
      file
            operations rather than the whole file system, set
      <a class="link" href="innodb-parameters.html#sysvar_innodb_flush_method">
       <code class="literal">
        innodb_flush_method =
            O_DIRECT
       </code>
      </a>
      . With this setting,
      <code class="literal">
       InnoDB
      </code>
      calls
      <code class="literal">
       directio()
      </code>
      instead of
      <code class="literal">
       fcntl()
      </code>
      for I/O to data files (not for
            I/O to log files).
     </p>
    </li>
    <li class="listitem">
     <p>
      Use raw storage for data and log files with Solaris 2.6 or
            later
     </p>
     <p>
      When using the
      <code class="literal">
       InnoDB
      </code>
      storage engine with
            a large
      <a class="link" href="innodb-parameters.html#sysvar_innodb_buffer_pool_size">
       <code class="literal">
        innodb_buffer_pool_size
       </code>
      </a>
      value on any release of Solaris 2.6 and up and any platform
            (sparc/x86/x64/amd64), conduct benchmarks with
      <code class="literal">
       InnoDB
      </code>
      data files and log files on raw
            devices or on a separate direct I/O UFS file system, using
            the
      <code class="literal">
       forcedirectio
      </code>
      mount option as
            described previously. (It is necessary to use the mount
            option rather than setting
      <a class="link" href="innodb-parameters.html#sysvar_innodb_flush_method">
       <code class="literal">
        innodb_flush_method
       </code>
      </a>
      if you
            want direct I/O for the log files.) Users of the Veritas
            file system VxFS should use the
      <code class="literal">
       convosync=direct
      </code>
      mount option.
     </p>
     <p>
      Do not place other MySQL data files, such as those for
      <code class="literal">
       MyISAM
      </code>
      tables, on a direct I/O file
            system. Executables or libraries
      <span class="emphasis">
       <em>
        must
            not
       </em>
      </span>
      be placed on a direct I/O file system.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use additional storage devices
     </p>
     <p>
      Additional storage devices could be used to set up a RAID
            configuration. For related information, see
      <a class="xref" href="disk-issues.html" title="10.12.1 Optimizing Disk I/O">
       Section 10.12.1, “Optimizing Disk I/O”
      </a>
      .
     </p>
     <p>
      Alternatively,
      <code class="literal">
       InnoDB
      </code>
      tablespace data
            files and log files can be placed on different physical
            disks. For more information, refer to the following
            sections:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <a class="xref" href="innodb-init-startup-configuration.html" title="17.8.1 InnoDB Startup Configuration">
          Section 17.8.1, “InnoDB Startup Configuration”
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="xref" href="innodb-create-table-external.html" title="17.6.1.2 Creating Tables Externally">
          Section 17.6.1.2, “Creating Tables Externally”
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="xref" href="general-tablespaces.html#general-tablespaces-creating" title="Creating a General Tablespace">
          Creating a General Tablespace
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="xref" href="innodb-migration.html" title="17.6.1.4 Moving or Copying InnoDB Tables">
          Section 17.6.1.4, “Moving or Copying InnoDB Tables”
         </a>
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      Consider non-rotational storage
     </p>
     <p>
      Non-rotational storage generally provides better performance
            for random I/O operations; and rotational storage for
            sequential I/O operations. When distributing data and log
            files across rotational and non-rotational storage devices,
            consider the type of I/O operations that are predominantly
            performed on each file.
     </p>
     <p>
      Random I/O-oriented files typically include
      <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">
       file-per-table
      </a>
      and
      <a class="link" href="glossary.html#glos_general_tablespace" title="general tablespace">
       general
            tablespace
      </a>
      data files,
      <a class="link" href="glossary.html#glos_undo_tablespace" title="undo tablespace">
       undo tablespace
      </a>
      files, and
      <a class="link" href="glossary.html#glos_temporary_tablespace" title="temporary tablespace">
       temporary
            tablespace
      </a>
      files. Sequential I/O-oriented files
            include
      <code class="literal">
       InnoDB
      </code>
      <a class="link" href="glossary.html#glos_system_tablespace" title="system tablespace">
       system
            tablespace
      </a>
      files, doublewrite files, and log files
            such as
      <a class="link" href="glossary.html#glos_binary_log" title="binary log">
       binary log
      </a>
      files and
      <a class="link" href="glossary.html#glos_redo_log" title="redo log">
       redo log
      </a>
      files.
     </p>
     <p>
      Review settings for the following configuration options when
            using non-rotational storage:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <a class="link" href="innodb-parameters.html#sysvar_innodb_checksum_algorithm">
          <code class="literal">
           innodb_checksum_algorithm
          </code>
         </a>
        </p>
        <p>
         The
         <code class="literal">
          crc32
         </code>
         option uses a faster
                checksum algorithm and is recommended for fast storage
                systems.
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="innodb-parameters.html#sysvar_innodb_flush_neighbors">
          <code class="literal">
           innodb_flush_neighbors
          </code>
         </a>
        </p>
        <p>
         Optimizes I/O for rotational storage devices. Disable it
                for non-rotational storage or a mix of rotational and
                non-rotational storage. It is disabled by default.
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="innodb-parameters.html#sysvar_innodb_idle_flush_pct">
          <code class="literal">
           innodb_idle_flush_pct
          </code>
         </a>
        </p>
        <p>
         Permits placing a limit on page flushing during idle
                periods, which can help extend the life of
                non-rotational storage devices.
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
          <code class="literal">
           innodb_io_capacity
          </code>
         </a>
        </p>
        <p>
         The default setting of 10000 is generally sufficient.
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity_max">
          <code class="literal">
           innodb_io_capacity_max
          </code>
         </a>
        </p>
        <p>
         The default value of (2 *
         <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
          <code class="literal">
           innodb_io_capacity
          </code>
         </a>
         ) is
                intended for most workloads.
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="innodb-parameters.html#sysvar_innodb_log_compressed_pages">
          <code class="literal">
           innodb_log_compressed_pages
          </code>
         </a>
        </p>
        <p>
         If redo logs are on non-rotational storage, consider
                disabling this option to reduce logging. See
         <a class="link" href="optimizing-innodb-diskio.html#innodb-disable-log-compressed-pages">
          Disable
                logging of compressed pages
         </a>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="innodb-parameters.html#sysvar_innodb_log_file_size">
          <code class="literal">
           innodb_log_file_size
          </code>
         </a>
         (deprecated)
        </p>
        <p>
         If redo logs are on non-rotational storage, configure
                this option to maximize caching and write combining.
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="innodb-parameters.html#sysvar_innodb_redo_log_capacity">
          <code class="literal">
           innodb_redo_log_capacity
          </code>
         </a>
        </p>
        <p>
         If redo logs are on non-rotational storage, configure
                this option to maximize caching and write combining.
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
          <code class="literal">
           innodb_page_size
          </code>
         </a>
        </p>
        <p>
         Consider using a page size that matches the internal
                sector size of the disk. Early-generation SSD devices
                often have a 4KB sector size. Some newer devices have a
                16KB sector size. The default
         <code class="literal">
          InnoDB
         </code>
         page size is 16KB. Keeping the page size close to the
                storage device block size minimizes the amount of
                unchanged data that is rewritten to disk.
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="replication-options-binary-log.html#sysvar_binlog_row_image">
          <code class="literal">
           binlog_row_image
          </code>
         </a>
        </p>
        <p>
         If binary logs are on non-rotational storage and all
                tables have primary keys, consider setting this option
                to
         <code class="literal">
          minimal
         </code>
         to reduce logging.
        </p>
       </li>
      </ul>
     </div>
     <p>
      Ensure that TRIM support is enabled for your operating
            system. It is typically enabled by default.
     </p>
    </li>
    <li class="listitem">
     <p>
      Increase I/O capacity to avoid backlogs
     </p>
     <p>
      If throughput drops periodically because of
      <code class="literal">
       InnoDB
      </code>
      <a class="link" href="glossary.html#glos_checkpoint" title="checkpoint">
       checkpoint
      </a>
      operations, consider increasing the value of the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
       <code class="literal">
        innodb_io_capacity
       </code>
      </a>
      configuration option. Higher values cause more frequent
      <a class="link" href="glossary.html#glos_flush" title="flush">
       flushing
      </a>
      , avoiding the
            backlog of work that can cause dips in throughput.
     </p>
    </li>
    <li class="listitem">
     <p>
      Lower I/O capacity if flushing does not fall behind
     </p>
     <p>
      If the system is not falling behind with
      <code class="literal">
       InnoDB
      </code>
      <a class="link" href="glossary.html#glos_flush" title="flush">
       flushing
      </a>
      operations,
            consider lowering the value of the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_io_capacity">
       <code class="literal">
        innodb_io_capacity
       </code>
      </a>
      configuration option. Typically, you keep this option value
            as low as practical, but not so low that it causes periodic
            drops in throughput as mentioned in the preceding bullet. In
            a typical scenario where you could lower the option value,
            you might see a combination like this in the output from
      <a class="link" href="show-engine.html" title="15.7.7.16 SHOW ENGINE Statement">
       <code class="literal">
        SHOW ENGINE
            INNODB STATUS
       </code>
      </a>
      :
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         History list length low, below a few thousand.
        </p>
       </li>
       <li class="listitem">
        <p>
         Insert buffer merges close to rows inserted.
        </p>
       </li>
       <li class="listitem">
        <p>
         Modified pages in buffer pool consistently well below
         <a class="link" href="innodb-parameters.html#sysvar_innodb_max_dirty_pages_pct">
          <code class="literal">
           innodb_max_dirty_pages_pct
          </code>
         </a>
         of the buffer pool. (Measure at a time when the server
                is not doing bulk inserts; it is normal during bulk
                inserts for the modified pages percentage to rise
                significantly.)
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          Log sequence number - Last checkpoint
         </code>
         is at less than 7/8 or ideally less than 6/8 of the
                total size of the
         <code class="literal">
          InnoDB
         </code>
         <a class="link" href="glossary.html#glos_log_file" title="log file">
          log files
         </a>
         .
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      Store system tablespace files on Fusion-io devices
     </p>
     <p>
      You can take advantage of a doublewrite buffer-related I/O
            optimization by storing the files that contain the
            doublewrite storage area on Fusion-io devices that support
            atomic writes. (The doublewrite buffer storage area resides
            in doublewrite files. See
      <a class="xref" href="innodb-doublewrite-buffer.html" title="17.6.4 Doublewrite Buffer">
       Section 17.6.4, “Doublewrite Buffer”
      </a>
      .) When
            doublewrite storage area files are placed on Fusion-io
            devices that support atomic writes, the doublewrite buffer
            is automatically disabled and Fusion-io atomic writes are
            used for all data files. This feature is only supported on
            Fusion-io hardware and is only enabled for Fusion-io NVMFS
            on Linux. To take full advantage of this feature, an
      <a class="link" href="innodb-parameters.html#sysvar_innodb_flush_method">
       <code class="literal">
        innodb_flush_method
       </code>
      </a>
      setting
            of
      <code class="literal">
       O_DIRECT
      </code>
      is recommended.
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       Because the doublewrite buffer setting is global, the
              doublewrite buffer is also disabled for data files that do
              not reside on Fusion-io hardware.
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      <a name="innodb-disable-log-compressed-pages">
      </a>
      Disable logging of compressed pages
     </p>
     <p>
      When using the
      <code class="literal">
       InnoDB
      </code>
      table
      <a class="link" href="glossary.html#glos_compression" title="compression">
       compression
      </a>
      feature,
            images of re-compressed
      <a class="link" href="glossary.html#glos_page" title="page">
       pages
      </a>
      are written to the
      <a class="link" href="glossary.html#glos_redo_log" title="redo log">
       redo log
      </a>
      when changes
            are made to compressed data. This behavior is controlled by
      <a class="link" href="innodb-parameters.html#sysvar_innodb_log_compressed_pages">
       <code class="literal">
        innodb_log_compressed_pages
       </code>
      </a>
      ,
            which is enabled by default to prevent corruption that can
            occur if a different version of the
      <code class="literal">
       zlib
      </code>
      compression algorithm is used during recovery. If you are
            certain that the
      <code class="literal">
       zlib
      </code>
      version is not
            subject to change, disable
      <a class="link" href="innodb-parameters.html#sysvar_innodb_log_compressed_pages">
       <code class="literal">
        innodb_log_compressed_pages
       </code>
      </a>
      to reduce redo log generation for workloads that modify
            compressed data.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
