Table size is bigger than before after InnoDB Page Compression

Question

I'm doing a test on mysql compression

run in mysql 5.7
test table like this

    mysql> show table status like 'test'\G;
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 583678
 Avg_row_length: 585
    Data_length: 341835776
Max_data_length: 0
   Index_length: 12075008
      Data_free: 7340032
 Auto_increment: 604856
    Create_time: 2022-07-12 16:31:02
    Update_time: 2022-07-12 16:35:57
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

before the compression test like this

ibd file
[root@cqh test]# ll -h test*
-rw-r----- 1 mysql mysql  16K Jul 12 16:31 test.frm
-rw-r----- 1 mysql mysql 352M Jul 12 16:36 test.ibd

mysql> SELECT TABLE_NAME, TABLE_SCHEMA, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'test';
+------------+--------------+----------------+
| TABLE_NAME | TABLE_SCHEMA | CREATE_OPTIONS |
+------------+--------------+----------------+
| test       | test         |                |
+------------+--------------+----------------+
1 row in set (0.00 sec)

mysql> SELECT SPACE, NAME, FS_BLOCK_SIZE, FILE_SIZE, ALLOCATED_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE NAME='test/test'\G
*************************** 1. row ***************************
         SPACE: 8578384
          NAME: test/test
 FS_BLOCK_SIZE: 4096
     FILE_SIZE: 369098752
ALLOCATED_SIZE: 369102848
1 row in set (1.72 sec)

after

mysql> ALTER TABLE test COMPRESSION="zlib";
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT TABLE_NAME, TABLE_SCHEMA, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'test';
+------------+--------------+--------------------+
| TABLE_NAME | TABLE_SCHEMA | CREATE_OPTIONS     |
+------------+--------------+--------------------+
| test       | test         | COMPRESSION="zlib" |
+------------+--------------+--------------------+
1 row in set (0.00 sec)

mysql> OPTIMIZE TABLE test;
+-----------+----------+----------+-------------------------------------------------------------------+
| Table     | Op       | Msg_type | Msg_text                                                          |
+-----------+----------+----------+-------------------------------------------------------------------+
| test.test | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.test | optimize | status   | OK                                                                |
+-----------+----------+----------+-------------------------------------------------------------------+
2 rows in set (15.65 sec)

mysql> SELECT SPACE, NAME, FS_BLOCK_SIZE, FILE_SIZE, ALLOCATED_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE NAME='test/test'\G
*************************** 1. row ***************************
         SPACE: 8578385
          NAME: test/test
 FS_BLOCK_SIZE: 4096
     FILE_SIZE: 415236096
ALLOCATED_SIZE: 155222016
1 row in set (0.12 sec)

after:ibd file
[root@cqh test]# ll -h test*
-rw-r----- 1 mysql mysql  16K Jul 12 16:42 test.frm
-rw-r----- 1 mysql mysql 396M Jul 12 16:42 test.ibd

I did this by reading the documentationMySQL InnoDB Page Compression; and i have the same question like thisTable size increased after running "optimize table tablename";, why does the compression prompt succeed, but the idb file grows instead of becoming smaller

[**Please Never** post images of code, data or error messages](https://meta.stackoverflow.com/a/285557/2310830). Please edit your question and include copy/paste the text into the question, formatted. This is so that we can try to reproduce the problem without having to re-type everything, and your question can be properly indexed or read by screen readers. — RiggsFolly, Jul 12 '22 at 09:38
Thank you for your advice. I will deal with the problem again and then release it — Lulu_Chan, Jul 12 '22 at 09:42

score 0 · Answer 1 · answered Jul 12 '22 at 16:25

0

If you are compressing JPGs or other already-compressed data, your second compression is futile
InnoDB's compression option has issues. The benchmarks don't show better than 2x compression. So I am not surprised by a use case that shows less than 1x.
415MB vs 155MB should like close to 3x??
.ibd files never shrink, they only grow. There may be a lot of "free" space in the "396M" that can be used for future Inserts.
ALTER TABLE test ENGINE=InnoDB will rebuild the table by copying it over -- thereby (I think) shrinking the "396M".
You are trying 4K block size? Did you also try 8K?

answered Jul 12 '22 at 16:25

Rick James

135,179
13
127
222

1、This is my first commpression. 2、I am new to mysql;I'm not sure what the second point means. 3、About the fourth;I ran another test like this: before: [root@cqh test]# ll -h test1* -rw-r----- 1 mysql mysql 8.7K Jul 13 09:39 test1.frm -rw-r----- 1 mysql mysql 72M Jul 13 09:48 test1.ibd after: [root@cqh test]# ll -h test1* -rw-r----- 1 mysql mysql 8.7K Jul 13 09:54 test1.frm -rw-r----- 1 mysql mysql 64M Jul 13 09:54 test1.ibd `.ibd` file smaller than befor 4、I am trying 4K block size;And I haven't tried @Rick James – Lulu_Chan Jul 13 '22 at 02:20
@Lulu_Chan - What I meant by (1) was that JPG files is already compressed. If you are not expecting to have so much data that you will have trouble with disk size, I recommend abandoning compression. – Rick James Jul 13 '22 at 03:56

Table size is bigger than before after InnoDB Page Compression

1 Answers1