content_image/665205.jpg
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 InnoDB  with an innodb_page_size value of 16K or greater. 

## Enabling Page Compression 

To enable page compression, specify the COMPRESSION  attribute in the CREATE TABLE  statement. For example: 

CREATE TABLE t1 (c1 INT) COMPRESSION="zlib"; 

You can also enable page compression in an ALTER TABLE  statement. However, ALTER TABLE ... COMPRESSION  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 OPTIMIZE TABLE . 

## Disabling Page Compression 

ALTER TABLE t1 COMPRESSION="zlib"; OPTIMIZE TABLE t1; 

To disable page compression, set COMPRESSION=None  using ALTER TABLE . Writes to the tablespace that occur after setting COMPRESSION=None  no longer use page compression. To uncompress existing pages, you must rebuild the table using OPTIMIZE TABLE  after setting COMPRESSION=None . 

## Page Compression Metadata 

ALTER TABLE t1 COMPRESSION="None"; OPTIMIZE TABLE t1; 

Page compression metadata is found in the INFORMATION_SCHEMA.INNODB_TABLESPACES  table, in the following columns: 

• FS_BLOCK_SIZE : The file system block size, which is the unit size used for hole punching. 

• FILE_SIZE : The apparent size of the file, which represents the maximum size of the file, uncompressed. 

• ALLOCATED_SIZE : The actual size of the file, which is the amount of space allocated on disk. 

<img src='content_image/665205.jpg'>

## Note 

On Unix-like systems, ls -l tablespace_name .ibd  shows the apparent file size (equivalent to FILE_SIZE ) in bytes. To view the actual amount of space allocated on disk (equivalent to ALLOCATED_SIZE ), use du --block-size=1 tablespace_name .ibd . The --block-size=1  option prints the allocated space in bytes instead of blocks, so that it can be compared to ls -l  output. 

Use SHOW CREATE TABLE  to view the current page compression setting ( Zlib , Lz4 , or None ). A table may contain a mix of pages with different compression settings. 

In the following example, page compression metadata for the employees table is retrieved from the INFORMATION_SCHEMA.INNODB_TABLESPACES  table. 

# Create the employees table with Zlib page compression CREATE TABLE employees ( 

