1

My tables have been created with InnoDB row compression (ENGINE=InnoDB ROW_FORMAT=COMPRESSED). Now I am changing them to page compression. According to the official documentation of MariaDB, enabling page compression affects 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 using

ALTER TABLE t1 PAGE_COMPRESSED=1;

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

Improve this question
4
  • 1
    Try it with an explicit ALGORITHM=COPY. Also please report back with disk size comparisons between Row and Page. And any other metrics you have. Do you have Snappy? Do you have "punch hole"?
    – Rick James
    Apr 14, 2021 at 19:39
  • @RickJames nice point, I will try it. What does ALGORITHM=COPY exactly do? I intend to use the default zlib as it is used for my current row compression. Since it is SSD, it naturally benefits from sparse files. Of course, I am practising on HDD too (though it is not very favourable, at least theoretically).
    – Googlebot
    Apr 14, 2021 at 20:48
  • 1
    That option to ALTER says to: Create new (empty) table; Copy data into it; Rename to swap. Logically it involves a "newly create table", but I don't have proof that it achieves your goal. It should un-row-compress, then page-compress. Suggest you do OS commands to measure the actual size of the table before and after. Row compression gives about 2x compression. Page compression may give you closer to 3x.
    – Rick James
    Apr 14, 2021 at 21:34
  • @RickJames I tried your suggestion. ALGORITHM=COPY does not help here since it does copy KEY_BLOCK_SIZE too. Then, there is a conflict between page and row compressions.
    – Googlebot
    Apr 23, 2021 at 16:33

1 Answer 1

Reset to default
0

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.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.