<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-compression-internals">
      </a>
      17.9.1.5 How Compression Works for InnoDB Tables
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045161484112">
  </a>
  <p>
   This section describes some internal implementation details
        about
   <a class="link" href="glossary.html#glos_compression" title="compression">
    compression
   </a>
   for
        InnoDB tables. The information presented here may be helpful in
        tuning for performance, but is not necessary to know for basic
        use of compression.
  </p>
  <h5>
   <a name="innodb-compression-internals-algorithms">
   </a>
   Compression Algorithms
  </h5>
  <a class="indexterm" name="idm46045161479904">
  </a>
  <p>
   Some operating systems implement compression at the file system
        level. Files are typically divided into fixed-size blocks that
        are compressed into variable-size blocks, which easily leads
        into fragmentation. Every time something inside a block is
        modified, the whole block is recompressed before it is written
        to disk. These properties make this compression technique
        unsuitable for use in an update-intensive database system.
  </p>
  <p>
   MySQL implements compression with the help of the well-known
   <a class="ulink" href="http://www.zlib.net/" target="_blank">
    zlib library
   </a>
   , which
        implements the LZ77 compression algorithm. This compression
        algorithm is mature, robust, and efficient in both CPU
        utilization and in reduction of data size. The algorithm is
   <span class="quote">
    “
    <span class="quote">
     lossless
    </span>
    ”
   </span>
   , so that the original uncompressed data
        can always be reconstructed from the compressed form. LZ77
        compression works by finding sequences of data that are repeated
        within the data to be compressed. The patterns of values in your
        data determine how well it compresses, but typical user data
        often compresses by 50% or more.
  </p>
  <p>
   Unlike compression performed by an application, or compression
        features of some other database management systems, InnoDB
        compression applies both to user data and to indexes. In many
        cases, indexes can constitute 40-50% or more of the total
        database size, so this difference is significant. When
        compression is working well for a data set, the size of the
        InnoDB data files (the
   <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">
    file-per-table
   </a>
   tablespace or
   <a class="link" href="glossary.html#glos_general_tablespace" title="general tablespace">
    general
        tablespace
   </a>
   <code class="literal">
    .ibd
   </code>
   files) is 25% to 50%
        of the uncompressed size or possibly smaller. Depending on the
   <a class="link" href="glossary.html#glos_workload" title="workload">
    workload
   </a>
   , this smaller
        database can in turn lead to a reduction in I/O, and an increase
        in throughput, at a modest cost in terms of increased CPU
        utilization. You can adjust the balance between compression
        level and CPU overhead by modifying the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_compression_level">
    <code class="literal">
     innodb_compression_level
    </code>
   </a>
   configuration option.
  </p>
  <h5>
   <a name="innodb-compression-internals-storage">
   </a>
   InnoDB Data Storage and Compression
  </h5>
  <a class="indexterm" name="idm46045161468512">
  </a>
  <p>
   All user data in InnoDB tables is stored in pages comprising a
   <a class="link" href="glossary.html#glos_b_tree" title="B-tree">
    B-tree
   </a>
   index (the
   <a class="link" href="glossary.html#glos_clustered_index" title="clustered index">
    clustered index
   </a>
   ). In
        some other database systems, this type of index is called an
   <span class="quote">
    “
    <span class="quote">
     index-organized table
    </span>
    ”
   </span>
   . Each row in the index node
        contains the values of the (user-specified or system-generated)
   <a class="link" href="glossary.html#glos_primary_key" title="primary key">
    primary key
   </a>
   and all the
        other columns of the table.
  </p>
  <p>
   <a class="link" href="glossary.html#glos_secondary_index" title="secondary index">
    Secondary indexes
   </a>
   in
        InnoDB tables are also B-trees, containing pairs of values: the
        index key and a pointer to a row in the clustered index. The
        pointer is in fact the value of the primary key of the table,
        which is used to access the clustered index if columns other
        than the index key and primary key are required. Secondary index
        records must always fit on a single B-tree page.
  </p>
  <p>
   The compression of B-tree nodes (of both clustered and secondary
        indexes) is handled differently from compression of
   <a class="link" href="glossary.html#glos_overflow_page" title="overflow page">
    overflow pages
   </a>
   used to
        store long
   <code class="literal">
    VARCHAR
   </code>
   ,
   <code class="literal">
    BLOB
   </code>
   ,
        or
   <code class="literal">
    TEXT
   </code>
   columns, as explained in the
        following sections.
  </p>
  <h5>
   <a name="innodb-compression-internals-storage-btree">
   </a>
   Compression of B-Tree Pages
  </h5>
  <a class="indexterm" name="idm46045161457008">
  </a>
  <p>
   Because they are frequently updated, B-tree pages require
        special treatment. It is important to minimize the number of
        times B-tree nodes are split, as well as to minimize the need to
        uncompress and recompress their content.
  </p>
  <p>
   One technique MySQL uses is to maintain some system information
        in the B-tree node in uncompressed form, thus facilitating
        certain in-place updates. For example, this allows rows to be
        delete-marked and deleted without any compression operation.
  </p>
  <p>
   In addition, MySQL attempts to avoid unnecessary uncompression
        and recompression of index pages when they are changed. Within
        each B-tree page, the system keeps an uncompressed
   <span class="quote">
    “
    <span class="quote">
     modification log
    </span>
    ”
   </span>
   to record changes made to the
        page. Updates and inserts of small records may be written to
        this modification log without requiring the entire page to be
        completely reconstructed.
  </p>
  <p>
   When the space for the modification log runs out, InnoDB
        uncompresses the page, applies the changes and recompresses the
        page. If recompression fails (a situation known as a
   <a class="link" href="glossary.html#glos_compression_failure" title="compression failure">
    compression
        failure
   </a>
   ), the B-tree nodes are split and the process is
        repeated until the update or insert succeeds.
  </p>
  <p>
   To avoid frequent compression failures in write-intensive
        workloads, such as for
   <a class="link" href="glossary.html#glos_oltp" title="OLTP">
    OLTP
   </a>
   applications, MySQL sometimes reserves some empty space
        (padding) in the page, so that the modification log fills up
        sooner and the page is recompressed while there is still enough
        room to avoid splitting it. The amount of padding space left in
        each page varies as the system keeps track of the frequency of
        page splits. On a busy server doing frequent writes to
        compressed tables, you can adjust the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_compression_failure_threshold_pct">
    <code class="literal">
     innodb_compression_failure_threshold_pct
    </code>
   </a>
   ,
        and
   <a class="link" href="innodb-parameters.html#sysvar_innodb_compression_pad_pct_max">
    <code class="literal">
     innodb_compression_pad_pct_max
    </code>
   </a>
   configuration options to fine-tune this mechanism.
  </p>
  <p>
   Generally, MySQL requires that each B-tree page in an InnoDB
        table can accommodate at least two records. For compressed
        tables, this requirement has been relaxed. Leaf pages of B-tree
        nodes (whether of the primary key or secondary indexes) only
        need to accommodate one record, but that record must fit, in
        uncompressed form, in the per-page modification log. If
   <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode">
    <code class="literal">
     innodb_strict_mode
    </code>
   </a>
   is
   <code class="literal">
    ON
   </code>
   , MySQL checks the maximum row size during
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   or
   <a class="link" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
    <code class="literal">
     CREATE INDEX
    </code>
   </a>
   . If the row does not
        fit, the following error message is issued:
   <code class="literal">
    ERROR
        HY000: Too big row
   </code>
   .
  </p>
  <p>
   If you create a table when
   <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode">
    <code class="literal">
     innodb_strict_mode
    </code>
   </a>
   is OFF, and
        a subsequent
   <code class="literal">
    INSERT
   </code>
   or
   <code class="literal">
    UPDATE
   </code>
   statement attempts to create an index
        entry that does not fit in the size of the compressed page, the
        operation fails with
   <code class="literal">
    ERROR 42000: Row size too
        large
   </code>
   . (This error message does not name the index for
        which the record is too large, or mention the length of the
        index record or the maximum record size on that particular index
        page.) To solve this problem, rebuild the table with
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   and select a larger
        compressed page size (
   <code class="literal">
    KEY_BLOCK_SIZE
   </code>
   ),
        shorten any column prefix indexes, or disable compression
        entirely with
   <code class="literal">
    ROW_FORMAT=DYNAMIC
   </code>
   or
   <code class="literal">
    ROW_FORMAT=COMPACT
   </code>
   .
  </p>
  <p>
   <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode">
    <code class="literal">
     innodb_strict_mode
    </code>
   </a>
   is not
        applicable to general tablespaces, which also support compressed
        tables. Tablespace management rules for general tablespaces are
        strictly enforced independently of
   <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode">
    <code class="literal">
     innodb_strict_mode
    </code>
   </a>
   . For more
        information, see
   <a class="xref" href="create-tablespace.html" title="15.1.21 CREATE TABLESPACE Statement">
    Section 15.1.21, “CREATE TABLESPACE Statement”
   </a>
   .
  </p>
  <h5>
   <a name="innodb-compression-internals-storage-blobs">
   </a>
   Compressing BLOB, VARCHAR, and TEXT Columns
  </h5>
  <a class="indexterm" name="idm46045161428032">
  </a>
  <a class="indexterm" name="idm46045161426544">
  </a>
  <p>
   In an InnoDB table,
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     BLOB
    </code>
   </a>
   ,
   <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
    <code class="literal">
     VARCHAR
    </code>
   </a>
   , and
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     TEXT
    </code>
   </a>
   columns that are not part of
        the primary key may be stored on separately allocated
   <a class="link" href="glossary.html#glos_overflow_page" title="overflow page">
    overflow pages
   </a>
   . We
        refer to these columns as
   <a class="link" href="glossary.html#glos_off_page_column" title="off-page column">
    off-page columns
   </a>
   .
        Their values are stored on singly-linked lists of overflow
        pages.
  </p>
  <p>
   For tables created in
   <code class="literal">
    ROW_FORMAT=DYNAMIC
   </code>
   or
   <code class="literal">
    ROW_FORMAT=COMPRESSED
   </code>
   , the values of
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     BLOB
    </code>
   </a>
   ,
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     TEXT
    </code>
   </a>
   , or
   <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
    <code class="literal">
     VARCHAR
    </code>
   </a>
   columns may be stored
        fully off-page, depending on their length and the length of the
        entire row. For columns that are stored off-page, the clustered
        index record only contains 20-byte pointers to the overflow
        pages, one per column. Whether any columns are stored off-page
        depends on the page size and the total size of the row. When the
        row is too long to fit entirely within the page of the clustered
        index, MySQL chooses the longest columns for off-page storage
        until the row fits on the clustered index page. As noted above,
        if a row does not fit by itself on a compressed page, an error
        occurs.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    For tables created in
    <code class="literal">
     ROW_FORMAT=DYNAMIC
    </code>
    or
    <code class="literal">
     ROW_FORMAT=COMPRESSED
    </code>
    ,
    <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
     <code class="literal">
      TEXT
     </code>
    </a>
    and
    <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
     <code class="literal">
      BLOB
     </code>
    </a>
    columns that are less than
          or equal to 40 bytes are always stored in-line.
   </p>
  </div>
  <p>
   Tables that use
   <code class="literal">
    ROW_FORMAT=REDUNDANT
   </code>
   and
   <code class="literal">
    ROW_FORMAT=COMPACT
   </code>
   store the first 768 bytes
        of
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     BLOB
    </code>
   </a>
   ,
   <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
    <code class="literal">
     VARCHAR
    </code>
   </a>
   , and
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     TEXT
    </code>
   </a>
   columns in the clustered
        index record along with the primary key. The 768-byte prefix is
        followed by a 20-byte pointer to the overflow pages that contain
        the rest of the column value.
  </p>
  <p>
   When a table is in
   <code class="literal">
    COMPRESSED
   </code>
   format, all
        data written to overflow pages is compressed
   <span class="quote">
    “
    <span class="quote">
     as
        is
    </span>
    ”
   </span>
   ; that is, MySQL applies the zlib compression
        algorithm to the entire data item. Other than the data,
        compressed overflow pages contain an uncompressed header and
        trailer comprising a page checksum and a link to the next
        overflow page, among other things. Therefore, very significant
        storage savings can be obtained for longer
   <code class="literal">
    BLOB
   </code>
   ,
   <code class="literal">
    TEXT
   </code>
   , or
   <code class="literal">
    VARCHAR
   </code>
   columns if the data is highly
        compressible, as is often the case with text data. Image data,
        such as
   <code class="literal">
    JPEG
   </code>
   , is typically already compressed
        and so does not benefit much from being stored in a compressed
        table; the double compression can waste CPU cycles for little or
        no space savings.
  </p>
  <p>
   The overflow pages are of the same size as other pages. A row
        containing ten columns stored off-page occupies ten overflow
        pages, even if the total length of the columns is only 8K bytes.
        In an uncompressed table, ten uncompressed overflow pages occupy
        160K bytes. In a compressed table with an 8K page size, they
        occupy only 80K bytes. Thus, it is often more efficient to use
        compressed table format for tables with long column values.
  </p>
  <p>
   For
   <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">
    file-per-table
   </a>
   tablespaces, using a 16K compressed page size can reduce storage
        and I/O costs for
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     BLOB
    </code>
   </a>
   ,
   <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
    <code class="literal">
     VARCHAR
    </code>
   </a>
   , or
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     TEXT
    </code>
   </a>
   columns, because such data
        often compress well, and might therefore require fewer overflow
        pages, even though the B-tree nodes themselves take as many
        pages as in the uncompressed form. General tablespaces do not
        support a 16K compressed page size
        (
   <code class="literal">
    KEY_BLOCK_SIZE
   </code>
   ). For more information, see
   <a class="xref" href="general-tablespaces.html" title="17.6.3.3 General Tablespaces">
    Section 17.6.3.3, “General Tablespaces”
   </a>
   .
  </p>
  <h5>
   <a name="innodb-compression-internals-pool">
   </a>
   Compression and the InnoDB Buffer Pool
  </h5>
  <a class="indexterm" name="idm46045161388160">
  </a>
  <a class="indexterm" name="idm46045161386656">
  </a>
  <p>
   In a compressed
   <code class="literal">
    InnoDB
   </code>
   table, every
        compressed page (whether 1K, 2K, 4K or 8K) corresponds to an
        uncompressed page of 16K bytes (or a smaller size if
   <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
    <code class="literal">
     innodb_page_size
    </code>
   </a>
   is set). To
        access the data in a page, MySQL reads the compressed page from
        disk if it is not already in the
   <a class="link" href="glossary.html#glos_buffer_pool" title="buffer pool">
    buffer pool
   </a>
   , then
        uncompresses the page to its original form. This section
        describes how
   <code class="literal">
    InnoDB
   </code>
   manages the buffer pool
        with respect to pages of compressed tables.
  </p>
  <p>
   To minimize I/O and to reduce the need to uncompress a page, at
        times the buffer pool contains both the compressed and
        uncompressed form of a database page. To make room for other
        required database pages, MySQL can
   <a class="link" href="glossary.html#glos_eviction" title="eviction">
    evict
   </a>
   from the buffer pool
        an uncompressed page, while leaving the compressed page in
        memory. Or, if a page has not been accessed in a while, the
        compressed form of the page might be written to disk, to free
        space for other data. Thus, at any given time, the buffer pool
        might contain both the compressed and uncompressed forms of the
        page, or only the compressed form of the page, or neither.
  </p>
  <p>
   MySQL keeps track of which pages to keep in memory and which to
        evict using a least-recently-used
        (
   <a class="link" href="glossary.html#glos_lru" title="LRU">
    LRU
   </a>
   ) list, so that
   <a class="link" href="glossary.html#glos_hot" title="hot">
    hot
   </a>
   (frequently accessed) data
        tends to stay in memory. When compressed tables are accessed,
        MySQL uses an adaptive LRU algorithm to achieve an appropriate
        balance of compressed and uncompressed pages in memory. This
        adaptive algorithm is sensitive to whether the system is running
        in an
   <a class="link" href="glossary.html#glos_io_bound" title="I/O-bound">
    I/O-bound
   </a>
   or
   <a class="link" href="glossary.html#glos_cpu_bound" title="CPU-bound">
    CPU-bound
   </a>
   manner. The goal
        is to avoid spending too much processing time uncompressing
        pages when the CPU is busy, and to avoid doing excess I/O when
        the CPU has spare cycles that can be used for uncompressing
        compressed pages (that may already be in memory). When the
        system is I/O-bound, the algorithm prefers to evict the
        uncompressed copy of a page rather than both copies, to make
        more room for other disk pages to become memory resident. When
        the system is CPU-bound, MySQL prefers to evict both the
        compressed and uncompressed page, so that more memory can be
        used for
   <span class="quote">
    “
    <span class="quote">
     hot
    </span>
    ”
   </span>
   pages and reducing the need to
        uncompress data in memory only in compressed form.
  </p>
  <h5>
   <a name="innodb-compression-internals-log">
   </a>
   Compression and the InnoDB Redo Log Files
  </h5>
  <a class="indexterm" name="idm46045161372080">
  </a>
  <p>
   Before a compressed page is written to a
   <a class="link" href="glossary.html#glos_data_files" title="data files">
    data file
   </a>
   , MySQL writes a
        copy of the page to the redo log (if it has been recompressed
        since the last time it was written to the database). This is
        done to ensure that redo logs are usable for
   <a class="link" href="glossary.html#glos_crash_recovery" title="crash recovery">
    crash recovery
   </a>
   , even
        in the unlikely case that the
   <code class="literal">
    zlib
   </code>
   library is
        upgraded and that change introduces a compatibility problem with
        the compressed data. Therefore, some increase in the size of
   <a class="link" href="glossary.html#glos_log_file" title="log file">
    log files
   </a>
   , or a need for
        more frequent
   <a class="link" href="glossary.html#glos_checkpoint" title="checkpoint">
    checkpoints
   </a>
   , can be
        expected when using compression. The amount of increase in the
        log file size or checkpoint frequency depends on the number of
        times compressed pages are modified in a way that requires
        reorganization and recompression.
  </p>
  <p>
   To create a compressed table in a file-per-table tablespace,
   <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
    <code class="literal">
     innodb_file_per_table
    </code>
   </a>
   must be
        enabled. There is no dependence on the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
    <code class="literal">
     innodb_file_per_table
    </code>
   </a>
   setting
        when creating a compressed table in a general tablespace. For
        more information, see
   <a class="xref" href="general-tablespaces.html" title="17.6.3.3 General Tablespaces">
    Section 17.6.3.3, “General Tablespaces”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
