ALTER innoDB table from row to page compression

All we need is an easy explanation of the problem, so here it is.

My tables have been created with InnoDB row compression (ENGINE=InnoDB ROW_FORMAT=COMPRESSED). Not I am changing them to page compression. According to the official documentation of MariaDB, enabling page compression affect the newly created tables only.

Thus, I create a replica table and use INSERT INTO SELECT.

I wonder if it is possible to ALTER a table to change the compression type (from row to page)?

We can ALTER an ordinary table to use page compression as

ALTER TABLE t1 PAGE_COMPRESSED=1;

My question is about a safe way to remove ROW_FORMAT=COMPRESSED and add PAGE_COMPRESSED=1.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I tried different methods and read many articles. The standard way to do so is

ALTER TABLE t1 KEY_BLOCK_SIZE=0 `PAGE_COMPRESSED`='ON';

However, this will leave KEY_BLOCK_SIZE on all keys.

Some say this is just the SHOW CREATE TABLE display, as KEY_BLOCK_SIZE=8 standards for the largest block size for the keys, but the value is exactly that of the COMPRESSED table. If the table was created with KEY_BLOCK_SIZE=1, keys will have KEY_BLOCK_SIZE=1 after uncompression.

Some, on the other hand, say this bug has been fixed in MySQL 8, but I confirm it exists in MariaDB 10.5.9.

VERDICT

ALTER is a clean way, but I favour INSERT INTO SELECT for two reasons:

  1. ALTER writes the uncompressed table on disk while INSERT writes the compressed pages on the disk.
  2. We can create new table on a separate disk to reduce I/O.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply