<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-page-compression">
      </a>
      17.9.2 InnoDB Page Compression
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045161144928">
  </a>
  <a class="indexterm" name="idm46045161143856">
  </a>
  <p>
   <code class="literal">
    InnoDB
   </code>
   supports page-level compression for
      tables that reside in
   <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">
    file-per-table
   </a>
   tablespaces. This feature is referred to as
   <span class="emphasis">
    <em>
     Transparent
      Page Compression
    </em>
   </span>
   . Page compression is enabled by
      specifying the
   <code class="literal">
    COMPRESSION
   </code>
   attribute with
   <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="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   . Supported compression
      algorithms include
   <code class="literal">
    Zlib
   </code>
   and
   <code class="literal">
    LZ4
   </code>
   .
  </p>
  <h4>
   <a name="idm46045161135648">
   </a>
   Supported Platforms
  </h4>
  <p>
   Page compression requires sparse file and hole punching support.
      Page compression is supported on Windows with NTFS, and on the
      following subset of MySQL-supported Linux platforms where the
      kernel level provides hole punching support:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      RHEL 7 and derived distributions that use kernel version
          3.10.0-123 or higher
     </p>
    </li>
    <li class="listitem">
     <p>
      OEL 5.10 (UEK2) kernel version 2.6.39 or higher
     </p>
    </li>
    <li class="listitem">
     <p>
      OEL 6.5 (UEK3) kernel version 3.8.13 or higher
     </p>
    </li>
    <li class="listitem">
     <p>
      OEL 7.0 kernel version 3.8.13 or higher
     </p>
    </li>
    <li class="listitem">
     <p>
      SLE11 kernel version 3.0-x
     </p>
    </li>
    <li class="listitem">
     <p>
      SLE12 kernel version 3.12-x
     </p>
    </li>
    <li class="listitem">
     <p>
      OES11 kernel version 3.0-x
     </p>
    </li>
    <li class="listitem">
     <p>
      Ubuntu 14.0.4 LTS kernel version 3.13 or higher
     </p>
    </li>
    <li class="listitem">
     <p>
      Ubuntu 12.0.4 LTS kernel version 3.2 or higher
     </p>
    </li>
    <li class="listitem">
     <p>
      Debian 7 kernel version 3.2 or higher
     </p>
    </li>
   </ul>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    All of the available file systems for a given Linux distribution
        may not support hole punching.
   </p>
  </div>
  <h4>
   <a name="idm46045161124528">
   </a>
   How Page Compression Works
  </h4>
  <p>
   When a page is written, it is compressed using the specified
      compression algorithm. The compressed data is written to disk,
      where the hole punching mechanism releases empty blocks from the
      end of the page. If compression fails, data is written out as-is.
  </p>
  <h4>
   <a name="idm46045161123120">
   </a>
   Hole Punch Size on Linux
  </h4>
  <p>
   On Linux systems, the file system block size is the unit size used
      for hole punching. Therefore, page compression only works if page
      data can be compressed to a size that is less than or equal to the
   <code class="literal">
    InnoDB
   </code>
   page size minus the file system block
      size. For example, if
   <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
    <code class="literal">
     innodb_page_size=16K
    </code>
   </a>
   and the file
      system block size is 4K, page data must compress to less than or
      equal to 12K to make hole punching possible.
  </p>
  <h4>
   <a name="idm46045161119632">
   </a>
   Hole Punch Size on Windows
  </h4>
  <p>
   On Windows systems, the underlying infrastructure for sparse files
      is based on NTFS compression. Hole punching size is the NTFS
      compression unit, which is 16 times the NTFS cluster size. Cluster
      sizes and their compression units are shown in the following
      table:
  </p>
  <div class="table">
   <a name="idm46045161118208">
   </a>
   <p class="title">
    <b>
     Table 17.13 Windows NTFS Cluster Size and Compression Units
    </b>
   </p>
   <div class="table-contents">
    <table frame="all" summary="Windows NTFS cluster size and compression units.">
     <colgroup>
      <col style="width: 50%"/>
      <col style="width: 50%"/>
     </colgroup>
     <thead>
      <tr>
       <th>
        Cluster Size
       </th>
       <th>
        Compression Unit
       </th>
      </tr>
     </thead>
     <tbody>
      <tr>
       <td>
        512 Bytes
       </td>
       <td>
        8 KB
       </td>
      </tr>
      <tr>
       <td>
        1 KB
       </td>
       <td>
        16 KB
       </td>
      </tr>
      <tr>
       <td>
        2 KB
       </td>
       <td>
        32 KB
       </td>
      </tr>
      <tr>
       <td>
        4 KB
       </td>
       <td>
        64 KB
       </td>
      </tr>
     </tbody>
    </table>
   </div>
  </div>
  <br class="table-break"/>
  <p>
   Page compression on Windows systems only works if page data can be
      compressed to a size that is less than or equal to the
   <code class="literal">
    InnoDB
   </code>
   page size minus the compression unit
      size.
  </p>
  <p>
   The default NTFS cluster size is 4KB, for which the compression
      unit size is 64KB. This means that page compression has no benefit
      for an out-of-the box Windows NTFS configuration, as the maximum
   <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
    <code class="literal">
     innodb_page_size
    </code>
   </a>
   is also 64KB.
  </p>
  <p>
   For page compression to work on Windows, the file system must be
      created with a cluster size smaller than 4K, and the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
    <code class="literal">
     innodb_page_size
    </code>
   </a>
   must be at least
      twice the size of the compression unit. For example, for page
      compression to work on Windows, you could build the file system
      with a cluster size of 512 Bytes (which has a compression unit of
      8KB) and initialize
   <code class="literal">
    InnoDB
   </code>
   with an
   <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
    <code class="literal">
     innodb_page_size
    </code>
   </a>
   value of 16K or
      greater.
  </p>
  <h4>
   <a name="idm46045161096512">
   </a>
   Enabling Page Compression
  </h4>
  <p>
   To enable page compression, specify the
   <code class="literal">
    COMPRESSION
   </code>
   attribute in the
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement. For
      example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa50917523"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span><span class="token punctuation">)</span> <span class="token keyword">COMPRESSION</span><span class="token operator">=</span><span class="token string">"zlib"</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   You can also enable page compression in an
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statement. However,
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE ...
      COMPRESSION
    </code>
   </a>
   only updates the tablespace compression
      attribute. Writes to the tablespace that occur after setting the
      new compression algorithm use the new setting, but to apply the
      new compression algorithm to existing pages, you must rebuild the
      table using
   <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
    <code class="literal">
     OPTIMIZE TABLE
    </code>
   </a>
   .
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa95289515"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">COMPRESSION</span><span class="token operator">=</span><span class="token string">"zlib"</span><span class="token punctuation">;</span>
<span class="token keyword">OPTIMIZE</span> <span class="token keyword">TABLE</span> t1<span class="token punctuation">;</span></code></pre>
  </div>
  <h4>
   <a name="idm46045161086992">
   </a>
   Disabling Page Compression
  </h4>
  <p>
   To disable page compression, set
   <code class="literal">
    COMPRESSION=None
   </code>
   using
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   . Writes to the
      tablespace that occur after setting
   <code class="literal">
    COMPRESSION=None
   </code>
   no longer use page
      compression. To uncompress existing pages, you must rebuild the
      table using
   <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
    <code class="literal">
     OPTIMIZE TABLE
    </code>
   </a>
   after
      setting
   <code class="literal">
    COMPRESSION=None
   </code>
   .
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa65731333"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">COMPRESSION</span><span class="token operator">=</span><span class="token string">"None"</span><span class="token punctuation">;</span>
<span class="token keyword">OPTIMIZE</span> <span class="token keyword">TABLE</span> t1<span class="token punctuation">;</span></code></pre>
  </div>
  <h4>
   <a name="idm46045161080096">
   </a>
   Page Compression Metadata
  </h4>
  <p>
   Page compression metadata is found in the Information Schema
   <a class="link" href="information-schema-innodb-tablespaces-table.html" title="28.4.24 The INFORMATION_SCHEMA INNODB_TABLESPACES Table">
    <code class="literal">
     INNODB_TABLESPACES
    </code>
   </a>
   table, in the
      following columns:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       FS_BLOCK_SIZE
      </code>
      : The file system block size,
          which is the unit size used for hole punching.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       FILE_SIZE
      </code>
      : The apparent size of the file,
          which represents the maximum size of the file, uncompressed.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ALLOCATED_SIZE
      </code>
      : The actual size of the
          file, which is the amount of space allocated on disk.
     </p>
    </li>
   </ul>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    On Unix-like systems,
    <code class="literal">
     ls -l
     <em class="replaceable">
      <code>
       tablespace_name
      </code>
     </em>
     .ibd
    </code>
    shows
        the apparent file size (equivalent to
    <code class="literal">
     FILE_SIZE
    </code>
    ) in bytes. To view the actual
        amount of space allocated on disk (equivalent to
    <code class="literal">
     ALLOCATED_SIZE
    </code>
    ), use
    <code class="literal">
     du
        --block-size=1
     <em class="replaceable">
      <code>
       tablespace_name
      </code>
     </em>
     .ibd
    </code>
    . The
    <code class="literal">
     --block-size=1
    </code>
    option prints the allocated
        space in bytes instead of blocks, so that it can be compared to
    <code class="literal">
     ls -l
    </code>
    output.
   </p>
   <p>
    Use
    <a class="link" href="show-create-table.html" title="15.7.7.11 SHOW CREATE TABLE Statement">
     <code class="literal">
      SHOW CREATE TABLE
     </code>
    </a>
    to view the
        current page compression setting (
    <code class="literal">
     Zlib
    </code>
    ,
    <code class="literal">
     Lz4
    </code>
    , or
    <code class="literal">
     None
    </code>
    ). A table may
        contain a mix of pages with different compression settings.
   </p>
  </div>
  <p>
   In the following example, page compression metadata for the
      employees table is retrieved from the Information Schema
   <a class="link" href="information-schema-innodb-tablespaces-table.html" title="28.4.24 The INFORMATION_SCHEMA INNODB_TABLESPACES Table">
    <code class="literal">
     INNODB_TABLESPACES
    </code>
   </a>
   table.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa45712629"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token comment" spellcheck="true"># Create the employees table with Zlib page compression</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> employees <span class="token punctuation">(</span>
    emp_no      <span class="token datatype">INT</span>             <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    birth_date  <span class="token datatype">DATE</span>            <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    first_name  <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">14</span><span class="token punctuation">)</span>     <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    last_name   <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">16</span><span class="token punctuation">)</span>     <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    gender      <span class="token datatype">ENUM</span> <span class="token punctuation">(</span><span class="token string">'M'</span><span class="token punctuation">,</span><span class="token string">'F'</span><span class="token punctuation">)</span>  <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    hire_date   <span class="token datatype">DATE</span>            <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>emp_no<span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">COMPRESSION</span><span class="token operator">=</span><span class="token string">"zlib"</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true"># Insert data (not shown)</span>

<span class="token comment" spellcheck="true"># Query page compression metadata in INFORMATION_SCHEMA.INNODB_TABLESPACES</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> SPACE<span class="token punctuation">,</span> <span class="token keyword">NAME</span><span class="token punctuation">,</span> FS_BLOCK_SIZE<span class="token punctuation">,</span> FILE_SIZE<span class="token punctuation">,</span> ALLOCATED_SIZE <span class="token keyword">FROM</span>
       INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_TABLESPACES <span class="token keyword">WHERE</span> <span class="token keyword">NAME</span><span class="token operator">=</span><span class="token string">'employees/employees'</span>\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
SPACE<span class="token punctuation">:</span> 45
NAME<span class="token punctuation">:</span> employees/employees
FS_BLOCK_SIZE<span class="token punctuation">:</span> 4096
FILE_SIZE<span class="token punctuation">:</span> 23068672
ALLOCATED_SIZE<span class="token punctuation">:</span> 19415040</span></code></pre>
  </div>
  <p>
   Page compression metadata for the employees table shows that the
      apparent file size is 23068672 bytes while the actual file size
      (with page compression) is 19415040 bytes. The file system block
      size is 4096 bytes, which is the block size used for hole
      punching.
  </p>
  <h4>
   <a name="idm46045161056096">
   </a>
   Identifying Tables Using Page Compression
  </h4>
  <p>
   To identify tables for which page compression is enabled, you can
      check the Information Schema
   <a class="link" href="information-schema-tables-table.html" title="28.3.38 The INFORMATION_SCHEMA TABLES Table">
    <code class="literal">
     TABLES
    </code>
   </a>
   table's
   <code class="literal">
    CREATE_OPTIONS
   </code>
   column for tables
      defined with the
   <code class="literal">
    COMPRESSION
   </code>
   attribute:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa37729320"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token keyword">TABLE_NAME</span><span class="token punctuation">,</span> TABLE_SCHEMA<span class="token punctuation">,</span> CREATE_OPTIONS <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">TABLES</span> 
       <span class="token keyword">WHERE</span> CREATE_OPTIONS <span class="token operator">LIKE</span> <span class="token string">'%COMPRESSION=%'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> TABLE_NAME <span class="token punctuation">|</span> TABLE_SCHEMA <span class="token punctuation">|</span> CREATE_OPTIONS     <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> employees  <span class="token punctuation">|</span> test         <span class="token punctuation">|</span> COMPRESSION="zlib" <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   <a class="link" href="show-create-table.html" title="15.7.7.11 SHOW CREATE TABLE Statement">
    <code class="literal">
     SHOW CREATE TABLE
    </code>
   </a>
   also shows the
   <code class="literal">
    COMPRESSION
   </code>
   attribute, if used.
  </p>
  <h4>
   <a name="idm46045161047104">
   </a>
   Page Compression Limitations and Usage Notes
  </h4>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Page compression is disabled if the file system block size (or
          compression unit size on Windows) * 2 &gt;
      <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
       <code class="literal">
        innodb_page_size
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Page compression is not supported for tables that reside in
          shared tablespaces, which include the system tablespace,
          temporary tablespaces, and general tablespaces.
     </p>
    </li>
    <li class="listitem">
     <p>
      Page compression is not supported for undo log tablespaces.
     </p>
    </li>
    <li class="listitem">
     <p>
      Page compression is not supported for redo log pages.
     </p>
    </li>
    <li class="listitem">
     <p>
      R-tree pages, which are used for spatial indexes, are not
          compressed.
     </p>
    </li>
    <li class="listitem">
     <p>
      Pages that belong to compressed tables
          (
      <code class="literal">
       ROW_FORMAT=COMPRESSED
      </code>
      ) are left as-is.
     </p>
    </li>
    <li class="listitem">
     <p>
      During recovery, updated pages are written out in an
          uncompressed form.
     </p>
    </li>
    <li class="listitem">
     <p>
      Loading a page-compressed tablespace on a server that does not
          support the compression algorithm that was used causes an I/O
          error.
     </p>
    </li>
    <li class="listitem">
     <p>
      Before downgrading to an earlier version of MySQL that does
          not support page compression, uncompress the tables that use
          the page compression feature. To uncompress a table, run
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE ...
          COMPRESSION=None
       </code>
      </a>
      and
      <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
       <code class="literal">
        OPTIMIZE
          TABLE
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Page-compressed tablespaces can be copied between Linux and
          Windows servers if the compression algorithm that was used is
          available on both servers.
     </p>
    </li>
    <li class="listitem">
     <p>
      Preserving page compression when moving a page-compressed
          tablespace file from one host to another requires a utility
          that preserves sparse files.
     </p>
    </li>
    <li class="listitem">
     <p>
      Better page compression may be achieved on Fusion-io hardware
          with NVMFS than on other platforms, as NVMFS is designed to
          take advantage of punch hole functionality.
     </p>
    </li>
    <li class="listitem">
     <p>
      Using the page compression feature with a large
      <code class="literal">
       InnoDB
      </code>
      page size and relatively small file
          system block size could result in write amplification. For
          example, a maximum
      <code class="literal">
       InnoDB
      </code>
      page size of 64KB
          with a 4KB file system block size may improve compression but
          may also increase demand on the buffer pool, leading to
          increased I/O and potential write amplification.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
