Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
I have the following tables:
Table1:
row_format=DYNAMIC
Size = 5.87 GB
Index = 3.88 GB
Free = 4 MB
Avg Row Length = 197 B
Rows = 38686411
(Actual file size: 15359541248 bytes)
.
Table2:
row_format=COMPRESSED key_block_size=8
Size = 4.26 GB
Index = 2.72 GB
Free = 3.5 MB
Avg Row Length = 86 B
Rows = 83063985
(Actual file size: 12448694272 bytes)
.
Both tables have some meta data (ints/enums) and a main text field (100% variable length, with user messages/comments).
.
As experiment, for Table1, I made the text column COMPRESSED (using pt-online-schema-change), and converted to:
row_format=DYNAMIC `PAGE_COMPRESSED`=1 `PAGE_COMPRESSION_LEVEL`=6
It became like this:
Size = 6.23 GB
Index = 6.61 GB
Free = 5 MB
Avg Row Length = 173 B
(Actual file size: 13988003840 bytes)
.
For Table2, using normal ALTER TABLE, I converted to:
row_format=DYNAMIC `PAGE_COMPRESSED`=1 `PAGE_COMPRESSION_LEVEL`=9
It became like this:
Size = 12.59 GB
Index = 7.06 GB
Free = 4 MB
Avg Row Length = 159 B
(Actual file size: 21399339008 bytes)
.
These are values from information_schema.
I understand that the actual file sizes don't matter so much (they can be sparse files),
but looking at the Size/Index, the PAGE_COMPRESSED seems to be causing the tables to be bigger, rather than smaller.
Table1 – From no compression at all, to be compressed, the data increased 1GB, and indexes doubled.
(I wanted to compress the text field only, but the innodb default configs made pt-online-schema-change also put PAGE_COMPRESSED)
Table2 – From ROW_FORMAT=COMPRESSED to maximum level of PAGE_COMPRESSION, both data and index increased considerably.
Does it make sense that PAGE_COMPRESSION, even with level 9, is compressing less than ROW_FORMAT=COMPRESSED?
Attachments
Issue Links
- relates to
-
MDEV-11068 Review which innodb_compression_algorithm to support in binary packages
- Closed
-
MDEV-15528 Avoid writing freed InnoDB pages
- Closed
-
MDEV-16129 InnoDB page compression not working as documented
- Open
-
MDEV-21727 Optimize redo logging for ROW_FORMAT=COMPRESSED
- Open
-
MDEV-19780 Remove the TokuDB storage engine
- Closed
-
MDEV-24797 Column Compression - ERROR 1265 (01000): Data truncated for column
- Closed
-
MDEV-25734 mbstream breaks page compression on XFS
- Closed