<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-information-schema-examples-compression-sect">
      </a>
      17.15.1.3 Using the Compression Information Schema Tables
     </h4>
    </div>
   </div>
  </div>
  <div class="example">
   <a name="innodb-information-schema-examples-compression">
   </a>
   <p class="title">
    <b>
     Example 17.1 Using the Compression Information Schema Tables
    </b>
   </p>
   <div class="example-contents">
    <p>
     The following is sample output from a database that contains
          compressed tables (see
     <a class="xref" href="innodb-compression.html" title="17.9 InnoDB Table and Page Compression">
      Section 17.9, “InnoDB Table and Page Compression”
     </a>
     ,
     <a class="link" href="information-schema-innodb-cmp-table.html" title="28.4.6 The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET Tables">
      <code class="literal">
       INNODB_CMP
      </code>
     </a>
     ,
     <a class="link" href="information-schema-innodb-cmp-per-index-table.html" title="28.4.8 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables">
      <code class="literal">
       INNODB_CMP_PER_INDEX
      </code>
     </a>
     , and
     <a class="link" href="information-schema-innodb-cmpmem-table.html" title="28.4.7 The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables">
      <code class="literal">
       INNODB_CMPMEM
      </code>
     </a>
     ).
    </p>
    <p>
     The following table shows the contents of
     <a class="link" href="information-schema-innodb-cmp-table.html" title="28.4.6 The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET Tables">
      <code class="literal">
       INFORMATION_SCHEMA.INNODB_CMP
      </code>
     </a>
     under a light
     <a class="link" href="glossary.html#glos_workload" title="workload">
      workload
     </a>
     .
          The only compressed page size that the buffer pool contains is
          8K. Compressing or uncompressing pages has consumed less than
          a second since the time the statistics were reset, because the
          columns
     <code class="literal">
      COMPRESS_TIME
     </code>
     and
     <code class="literal">
      UNCOMPRESS_TIME
     </code>
     are zero.
    </p>
    <div class="informaltable">
     <a name="innodb-information-schema-sample-innodb_cmp">
     </a>
     <table summary="Sample data from the INFORMATION_SCHEMA.INNODB_CMP table, showing the internal workings of InnoDB table compression under a light workload.">
      <colgroup>
       <col style="width: 15%"/>
       <col style="width: 15%"/>
       <col style="width: 15%"/>
       <col style="width: 15%"/>
       <col style="width: 20%"/>
       <col style="width: 20%"/>
      </colgroup>
      <thead>
       <tr>
        <th scope="col">
         page size
        </th>
        <th scope="col">
         compress ops
        </th>
        <th scope="col">
         compress ops ok
        </th>
        <th scope="col">
         compress time
        </th>
        <th scope="col">
         uncompress ops
        </th>
        <th scope="col">
         uncompress time
        </th>
       </tr>
      </thead>
      <tbody>
       <tr>
        <th scope="row">
         1024
        </th>
        <td>
         0
        </td>
        <td>
         0
        </td>
        <td>
         0
        </td>
        <td>
         0
        </td>
        <td>
         0
        </td>
       </tr>
       <tr>
        <th scope="row">
         2048
        </th>
        <td>
         0
        </td>
        <td>
         0
        </td>
        <td>
         0
        </td>
        <td>
         0
        </td>
        <td>
         0
        </td>
       </tr>
       <tr>
        <th scope="row">
         4096
        </th>
        <td>
         0
        </td>
        <td>
         0
        </td>
        <td>
         0
        </td>
        <td>
         0
        </td>
        <td>
         0
        </td>
       </tr>
       <tr>
        <th scope="row">
         8192
        </th>
        <td>
         1048
        </td>
        <td>
         921
        </td>
        <td>
         0
        </td>
        <td>
         61
        </td>
        <td>
         0
        </td>
       </tr>
       <tr>
        <th scope="row">
         16384
        </th>
        <td>
         0
        </td>
        <td>
         0
        </td>
        <td>
         0
        </td>
        <td>
         0
        </td>
        <td>
         0
        </td>
       </tr>
      </tbody>
     </table>
    </div>
    <p>
     According to
     <a class="link" href="information-schema-innodb-cmpmem-table.html" title="28.4.7 The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables">
      <code class="literal">
       INNODB_CMPMEM
      </code>
     </a>
     , there
          are 6169 compressed 8KB pages in the
     <a class="link" href="glossary.html#glos_buffer_pool" title="buffer pool">
      buffer pool
     </a>
     . The only
          other allocated block size is 64 bytes. The smallest
     <code class="literal">
      PAGE_SIZE
     </code>
     in
     <a class="link" href="information-schema-innodb-cmpmem-table.html" title="28.4.7 The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables">
      <code class="literal">
       INNODB_CMPMEM
      </code>
     </a>
     is used for block
          descriptors of those compressed pages for which no
          uncompressed page exists in the buffer pool. We see that there
          are 5910 such pages. Indirectly, we see that 259 (6169-5910)
          compressed pages also exist in the buffer pool in uncompressed
          form.
    </p>
    <p>
     The following table shows the contents of
     <a class="link" href="information-schema-innodb-cmpmem-table.html" title="28.4.7 The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables">
      <code class="literal">
       INFORMATION_SCHEMA.INNODB_CMPMEM
      </code>
     </a>
     under a light
     <a class="link" href="glossary.html#glos_workload" title="workload">
      workload
     </a>
     .
          Some memory is unusable due to fragmentation of the memory
          allocator for compressed pages:
     <code class="literal">
      SUM(PAGE_SIZE*PAGES_FREE)=6784
     </code>
     . This is
          because small memory allocation requests are fulfilled by
          splitting bigger blocks, starting from the 16K blocks that are
          allocated from the main buffer pool, using the buddy
          allocation system. The fragmentation is this low because some
          allocated blocks have been relocated (copied) to form bigger
          adjacent free blocks. This copying of
     <code class="literal">
      SUM(PAGE_SIZE*RELOCATION_OPS)
     </code>
     bytes has
          consumed less than a second
     <code class="literal">
      (SUM(RELOCATION_TIME)=0)
     </code>
     .
    </p>
    <div class="informaltable">
     <a name="innodb-information-schema-sample-innodb_cmpmem">
     </a>
     <table summary="Sample data from the INFORMATION_SCHEMA.INNODB_CMPMEM table, showing buffer pool memory operations for InnoDB table compression under a light workload.">
      <colgroup>
       <col style="width: 15%"/>
       <col style="width: 15%"/>
       <col style="width: 20%"/>
       <col style="width: 20%"/>
       <col style="width: 20%"/>
      </colgroup>
      <thead>
       <tr>
        <th scope="col">
         page size
        </th>
        <th scope="col">
         pages used
        </th>
        <th scope="col">
         pages free
        </th>
        <th scope="col">
         relocation ops
        </th>
        <th scope="col">
         relocation time
        </th>
       </tr>
      </thead>
      <tbody>
       <tr>
        <th scope="row">
         64
        </th>
        <td>
         5910
        </td>
        <td>
         0
        </td>
        <td>
         2436
        </td>
        <td>
         0
        </td>
       </tr>
       <tr>
        <th scope="row">
         128
        </th>
        <td>
         0
        </td>
        <td>
         1
        </td>
        <td>
         0
        </td>
        <td>
         0
        </td>
       </tr>
       <tr>
        <th scope="row">
         256
        </th>
        <td>
         0
        </td>
        <td>
         0
        </td>
        <td>
         0
        </td>
        <td>
         0
        </td>
       </tr>
       <tr>
        <th scope="row">
         512
        </th>
        <td>
         0
        </td>
        <td>
         1
        </td>
        <td>
         0
        </td>
        <td>
         0
        </td>
       </tr>
       <tr>
        <th scope="row">
         1024
        </th>
        <td>
         0
        </td>
        <td>
         0
        </td>
        <td>
         0
        </td>
        <td>
         0
        </td>
       </tr>
       <tr>
        <th scope="row">
         2048
        </th>
        <td>
         0
        </td>
        <td>
         1
        </td>
        <td>
         0
        </td>
        <td>
         0
        </td>
       </tr>
       <tr>
        <th scope="row">
         4096
        </th>
        <td>
         0
        </td>
        <td>
         1
        </td>
        <td>
         0
        </td>
        <td>
         0
        </td>
       </tr>
       <tr>
        <th scope="row">
         8192
        </th>
        <td>
         6169
        </td>
        <td>
         0
        </td>
        <td>
         5
        </td>
        <td>
         0
        </td>
       </tr>
       <tr>
        <th scope="row">
         16384
        </th>
        <td>
         0
        </td>
        <td>
         0
        </td>
        <td>
         0
        </td>
        <td>
         0
        </td>
       </tr>
      </tbody>
     </table>
    </div>
   </div>
  </div>
  <br class="example-break"/>
 </div>
 <br/>
</div>
