<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="optimizing-innodb-logging">
      </a>
      10.5.4 Optimizing InnoDB Redo Logging
     </h3>
    </div>
   </div>
  </div>
  <p>
   Consider the following guidelines for optimizing redo logging:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Increase the size of your redo log files. When
      <code class="literal">
       InnoDB
      </code>
      has written redo log files full,
            it must write the modified contents of the buffer pool to
            disk in a
      <a class="link" href="glossary.html#glos_checkpoint" title="checkpoint">
       checkpoint
      </a>
      .
            Small redo log files cause many unnecessary disk writes.
     </p>
     <p>
      The redo log file size is determined by
      <a class="link" href="innodb-parameters.html#sysvar_innodb_redo_log_capacity">
       <code class="literal">
        innodb_redo_log_capacity
       </code>
      </a>
      .
      <code class="literal">
       InnoDB
      </code>
      tries to maintain 32 redo log
            files of the same size, with each file equal to 1/32 *
      <code class="literal">
       innodb_redo_log_capacity
      </code>
      . Therefore,
            changing the
      <code class="literal">
       innodb_redo_log_capacity
      </code>
      setting changes the size of the redo log files.
     </p>
     <p>
      For information about modifying your redo log file
            configuration, see
      <a class="xref" href="innodb-redo-log.html" title="17.6.5 Redo Log">
       Section 17.6.5, “Redo Log”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Consider increasing the size of the
      <a class="link" href="glossary.html#glos_log_buffer" title="log buffer">
       log buffer
      </a>
      . A large
            log buffer enables large
      <a class="link" href="glossary.html#glos_transaction" title="transaction">
       transactions
      </a>
      to run
            without a need to write the log to disk before the
            transactions
      <a class="link" href="glossary.html#glos_commit" title="commit">
       commit
      </a>
      .
            Thus, if you have transactions that update, insert, or
            delete many rows, making the log buffer larger saves disk
            I/O. Log buffer size is configured using the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_log_buffer_size">
       <code class="literal">
        innodb_log_buffer_size
       </code>
      </a>
      configuration option, which can be dynamically configured.
     </p>
    </li>
    <li class="listitem">
     <p>
      Configure the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_log_write_ahead_size">
       <code class="literal">
        innodb_log_write_ahead_size
       </code>
      </a>
      configuration option to avoid
      <span class="quote">
       “
       <span class="quote">
        read-on-write
       </span>
       ”
      </span>
      .
            This option defines the write-ahead block size for the redo
            log. Set
      <a class="link" href="innodb-parameters.html#sysvar_innodb_log_write_ahead_size">
       <code class="literal">
        innodb_log_write_ahead_size
       </code>
      </a>
      to match the operating system or file system cache block
            size. Read-on-write occurs when redo log blocks are not
            entirely cached to the operating system or file system due
            to a mismatch between write-ahead block size for the redo
            log and operating system or file system cache block size.
     </p>
     <p>
      Valid values for
      <a class="link" href="innodb-parameters.html#sysvar_innodb_log_write_ahead_size">
       <code class="literal">
        innodb_log_write_ahead_size
       </code>
      </a>
      are multiples of the
      <code class="literal">
       InnoDB
      </code>
      log file
            block size (2
      <sup>
       n
      </sup>
      ). The minimum
            value is the
      <code class="literal">
       InnoDB
      </code>
      log file block size
            (512). Write-ahead does not occur when the minimum value is
            specified. The maximum value is equal to the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
       <code class="literal">
        innodb_page_size
       </code>
      </a>
      value. If
            you specify a value for
      <a class="link" href="innodb-parameters.html#sysvar_innodb_log_write_ahead_size">
       <code class="literal">
        innodb_log_write_ahead_size
       </code>
      </a>
      that is larger than the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
       <code class="literal">
        innodb_page_size
       </code>
      </a>
      value, the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_log_write_ahead_size">
       <code class="literal">
        innodb_log_write_ahead_size
       </code>
      </a>
      setting is truncated to the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
       <code class="literal">
        innodb_page_size
       </code>
      </a>
      value.
     </p>
     <p>
      Setting the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_log_write_ahead_size">
       <code class="literal">
        innodb_log_write_ahead_size
       </code>
      </a>
      value too low in relation to the operating system or file
            system cache block size results in read-on-write. Setting
            the value too high may have a slight impact on
      <code class="literal">
       fsync
      </code>
      performance for log file writes due
            to several blocks being written at once.
     </p>
    </li>
    <li class="listitem">
     <p>
      MySQL provides dedicated log writer threads for writing redo
            log records from the log buffer to the system buffers and
            flushing the system buffers to the redo log files. You can
            enable or disable log writer threads using the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_log_writer_threads">
       <code class="literal">
        innodb_log_writer_threads
       </code>
      </a>
      variable. Dedicated log writer threads can improve
            performance on high-concurrency systems, but for
            low-concurrency systems, disabling dedicated log writer
            threads provides better performance.
     </p>
    </li>
    <li class="listitem">
     <p>
      Optimize the use of spin delay by user threads waiting for
            flushed redo. Spin delay helps reduce latency. During
            periods of low concurrency, reducing latency may be less of
            a priority, and avoiding the use of spin delay during these
            periods may reduce energy consumption. During periods of
            high concurrency, you may want to avoid expending processing
            power on spin delay so that it can be used for other work.
            The following system variables permit setting high and low
            watermark values that define boundaries for the use of spin
            delay.
     </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_log_wait_for_flush_spin_hwm">
          <code class="literal">
           innodb_log_wait_for_flush_spin_hwm
          </code>
         </a>
         :
                Defines the maximum average log flush time beyond which
                user threads no longer spin while waiting for flushed
                redo. The default value is 400 microseconds.
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="innodb-parameters.html#sysvar_innodb_log_spin_cpu_abs_lwm">
          <code class="literal">
           innodb_log_spin_cpu_abs_lwm
          </code>
         </a>
         :
                Defines the minimum amount of CPU usage below which user
                threads no longer spin while waiting for flushed redo.
                The value is expressed as a sum of CPU core usage. For
                example, The default value of 80 is 80% of a single CPU
                core. On a system with a multi-core processor, a value
                of 150 represents 100% usage of one CPU core plus 50%
                usage of a second CPU core.
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="innodb-parameters.html#sysvar_innodb_log_spin_cpu_pct_hwm">
          <code class="literal">
           innodb_log_spin_cpu_pct_hwm
          </code>
         </a>
         :
                Defines the maximum amount of CPU usage above which user
                threads no longer spin while waiting for flushed redo.
                The value is expressed as a percentage of the combined
                total processing power of all CPU cores. The default
                value is 50%. For example, 100% usage of two CPU cores
                is 50% of the combined CPU processing power on a server
                with four CPU cores.
        </p>
        <p>
         The
         <a class="link" href="innodb-parameters.html#sysvar_innodb_log_spin_cpu_pct_hwm">
          <code class="literal">
           innodb_log_spin_cpu_pct_hwm
          </code>
         </a>
         configuration option respects processor affinity. For
                example, if a server has 48 cores but the
         <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
          <span class="command">
           <strong>
            mysqld
           </strong>
          </span>
         </a>
         process is pinned to only four
                CPU cores, the other 44 CPU cores are ignored.
        </p>
       </li>
      </ul>
     </div>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
