<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="optimizing-innodb-configuration-variables">
      </a>
      10.5.9 Optimizing InnoDB Configuration Variables
     </h3>
    </div>
   </div>
  </div>
  <p>
   Different settings work best for servers with light, predictable
        loads, versus servers that are running near full capacity all
        the time, or that experience spikes of high activity.
  </p>
  <p>
   Because the
   <code class="literal">
    InnoDB
   </code>
   storage engine performs
        many of its optimizations automatically, many performance-tuning
        tasks involve monitoring to ensure that the database is
        performing well, and changing configuration options when
        performance drops. See
   <a class="xref" href="innodb-performance-schema.html" title="17.16 InnoDB Integration with MySQL Performance Schema">
    Section 17.16, “InnoDB Integration with MySQL Performance Schema”
   </a>
   for information
        about detailed
   <code class="literal">
    InnoDB
   </code>
   performance monitoring.
  </p>
  <p>
   The main configuration steps you can perform include:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Controlling the types of data change operations for which
      <code class="literal">
       InnoDB
      </code>
      buffers the changed data, to avoid
            frequent small disk writes. See
      <a class="xref" href="innodb-change-buffer.html#innodb-change-buffer-configuration" title="Configuring Change Buffering">
       Configuring Change Buffering
      </a>
      .
            Enabling change buffering can give better performance on
            IO-bound workloads, but can cause issues during recovery,
            bulk load, or during buffer pool resizing. Having it
            disabled (default as of MySQL 8.4) helps ensure stability
            even if it may lower performance.
     </p>
    </li>
    <li class="listitem">
     <p>
      Turning the adaptive hash indexing feature on and off using
            the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_adaptive_hash_index">
       <code class="literal">
        innodb_adaptive_hash_index
       </code>
      </a>
      option. See
      <a class="xref" href="innodb-adaptive-hash.html" title="17.5.3 Adaptive Hash Index">
       Section 17.5.3, “Adaptive Hash Index”
      </a>
      for more
            information. You might change this setting during periods of
            unusual activity, then restore it to its original setting.
     </p>
    </li>
    <li class="listitem">
     <p>
      Setting a limit on the number of concurrent threads that
      <code class="literal">
       InnoDB
      </code>
      processes, if context switching is
            a bottleneck. See
      <a class="xref" href="innodb-performance-thread_concurrency.html" title="17.8.4 Configuring Thread Concurrency for InnoDB">
       Section 17.8.4, “Configuring Thread Concurrency for InnoDB”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Controlling the amount of prefetching that
      <code class="literal">
       InnoDB
      </code>
      does with its read-ahead
            operations. When the system has unused I/O capacity, more
            read-ahead can improve the performance of queries. Too much
            read-ahead can cause periodic drops in performance on a
            heavily loaded system. See
      <a class="xref" href="innodb-performance-read_ahead.html" title="17.8.3.4 Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)">
       Section 17.8.3.4, “Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Increasing the number of background threads for read or
            write operations, if you have a high-end I/O subsystem that
            is not fully utilized by the default values. See
      <a class="xref" href="innodb-performance-multiple_io_threads.html" title="17.8.5 Configuring the Number of Background InnoDB I/O Threads">
       Section 17.8.5, “Configuring the Number of Background InnoDB I/O Threads”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Controlling how much I/O
      <code class="literal">
       InnoDB
      </code>
      performs
            in the background. See
      <a class="xref" href="innodb-configuring-io-capacity.html" title="17.8.7 Configuring InnoDB I/O Capacity">
       Section 17.8.7, “Configuring InnoDB I/O Capacity”
      </a>
      . You might
            scale back this setting if you observe periodic drops in
            performance.
     </p>
    </li>
    <li class="listitem">
     <p>
      Controlling the algorithm that determines when
      <code class="literal">
       InnoDB
      </code>
      performs certain types of
            background writes. See
      <a class="xref" href="innodb-buffer-pool-flushing.html" title="17.8.3.5 Configuring Buffer Pool Flushing">
       Section 17.8.3.5, “Configuring Buffer Pool Flushing”
      </a>
      . The algorithm
            works for some types of workloads but not others, so you
            might disable this feature if you observe periodic drops in
            performance.
     </p>
    </li>
    <li class="listitem">
     <p>
      Taking advantage of multicore processors and their cache
            memory configuration, to minimize delays in context
            switching. See
      <a class="xref" href="innodb-performance-spin_lock_polling.html" title="17.8.8 Configuring Spin Lock Polling">
       Section 17.8.8, “Configuring Spin Lock Polling”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Preventing one-time operations such as table scans from
            interfering with the frequently accessed data stored in the
      <code class="literal">
       InnoDB
      </code>
      buffer cache. See
      <a class="xref" href="innodb-performance-midpoint_insertion.html" title="17.8.3.3 Making the Buffer Pool Scan Resistant">
       Section 17.8.3.3, “Making the Buffer Pool Scan Resistant”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Adjusting log files to a size that makes sense for
            reliability and crash recovery.
      <code class="literal">
       InnoDB
      </code>
      log files have often been kept small to avoid long startup
            times after a crash. Optimizations introduced in MySQL 5.5
            speed up certain steps of the crash
      <a class="link" href="glossary.html#glos_crash_recovery" title="crash recovery">
       recovery
      </a>
      process.
            In particular, scanning the
      <a class="link" href="glossary.html#glos_redo_log" title="redo log">
       redo log
      </a>
      and applying
            the redo log are faster due to improved algorithms for
            memory management. If you have kept your log files
            artificially small to avoid long startup times, you can now
            consider increasing log file size to reduce the I/O that
            occurs due recycling of redo log records.
     </p>
    </li>
    <li class="listitem">
     <p>
      Configuring the size and number of instances for the
      <code class="literal">
       InnoDB
      </code>
      buffer pool, especially important
            for systems with multi-gigabyte buffer pools. See
      <a class="xref" href="innodb-multiple-buffer-pools.html" title="17.8.3.2 Configuring Multiple Buffer Pool Instances">
       Section 17.8.3.2, “Configuring Multiple Buffer Pool Instances”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Increasing the maximum number of concurrent transactions,
            which dramatically improves scalability for the busiest
            databases. See
      <a class="xref" href="innodb-undo-logs.html" title="17.6.6 Undo Logs">
       Section 17.6.6, “Undo Logs”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Moving purge operations (a type of garbage collection) into
            a background thread. See
      <a class="xref" href="innodb-purge-configuration.html" title="17.8.9 Purge Configuration">
       Section 17.8.9, “Purge Configuration”
      </a>
      . To effectively
            measure the results of this setting, tune the other
            I/O-related and thread-related configuration settings first.
     </p>
    </li>
    <li class="listitem">
     <p>
      Reducing the amount of switching that
      <code class="literal">
       InnoDB
      </code>
      does between concurrent threads,
            so that SQL operations on a busy server do not queue up and
            form a
      <span class="quote">
       “
       <span class="quote">
        traffic jam
       </span>
       ”
      </span>
      . Set a value for the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_thread_concurrency">
       <code class="literal">
        innodb_thread_concurrency
       </code>
      </a>
      option, up to approximately 32 for a high-powered modern
            system. Increase the value for the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_concurrency_tickets">
       <code class="literal">
        innodb_concurrency_tickets
       </code>
      </a>
      option, typically to 5000 or so. This combination of options
            sets a cap on the number of threads that
      <code class="literal">
       InnoDB
      </code>
      processes at any one time, and
            allows each thread to do substantial work before being
            swapped out, so that the number of waiting threads stays low
            and operations can complete without excessive context
            switching.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
