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
.
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"?ALGORITHM=COPY
exactly do? I intend to use the defaultzlib
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).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.ALGORITHM=COPY
does not help here since it does copyKEY_BLOCK_SIZE
too. Then, there is a conflict between page and row compressions.