Why Optimize Table does not shrink table size?

Question

I implemented this solution for big database, only keep data for 14 days (remove data daily based on date). When I run optimize TABLE table1; The size supposes to decrease but in my case, it increases.

/etc/my.cnf contains:

innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

ll --block-size=GB /var/lib/mysql/mydb/ print:

-rw-r-----. 1 mysql mysql  17GB Nov 20 15:45 table1.ibd
-rw-r-----. 1 mysql mysql   5GB Nov 20 15:30 table2.ibd
-rw-r-----. 1 mysql mysql 181GB Nov 20 15:29 table3.ibd

Answer 1

If you ran OPTIMIZE TABLE table1; multiple times and the the file table1.ibd is the same size, then it is already defragmented as much as possible.

It is possible that the rows you deleted had very little VARCHAR, TEXT, or BLOB data or even NULL values. Running OPTIMIZE TABLE after deleting such rows won't really make a significant dent in space.

If all your InnoDB tables reside in .ibd tables, then you should look at the file called ibdata1 and see how big it is. Back on Apr 23, 2013, I wrote How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?. This where I discuss how ibdata1 can hold transaction info for MVCC. That space is never reclaimed with OPTIMIZE TABLE regardless of innodb_file_per_table being on or off.

You may want to look into the InnoDB Infrastructure CleanUp section of my StackOverflow post you referred to.

UPDATE 2022-11-20 21:37 EST

Your Comment

First, when I implement the solution of mentioned link the size reduces from 1TB to 300GB. But Now the size reaches 500GB meaning 200GB increases for the same number of rows. Second, I run OPTIMIZE TABLE three times for the same table with the same result (no decrease). Third, my tables are (pId, fId,textField), and textField contains big JSON. Finally, ibdata1 is only 1557MB. What can I do now, please?

If you are storing JSON data uncompressed, each UPDATE of a JSON field could easily cause severe fragmentation if the JSON column value is increased in size. This may explain why the table the grows in size.

If even you run the following in week's time

SELECT AVG(LENGTH(textfield)) AverageLength FROM table1;
OPTIMIZE TABLE table1;
SELECT AVG(LENGTH(textfield)) AverageLength FROM table1;

The reduction of the average length would be miniscule at best. Just the slightest increase in the size of the JSON value would be enough to make that fragmentation. Just picture it : a 3KB (3072 bytes) JSON value increasing in size by just a few bytes (say 10 bytes). That would be enough to have this slightly larger JSON (now 3082 bytes) written across a different data pages (each InnoDB data page is 16K) while the old value (length 3072 bytes) is left wide open as some kind of pigeon hole in the old value's InnoDB page location.

SUGGESTION

You may need to consider using the COMPRESS() and UNCOMPRESS() functions when storing JSON values. It could greatly control the amount of residual fragmentation.

Please test it in a staging environment.

First, copy table1 in another database in staging (called table1_copy).

Then, run the following:

SELECT
    SUM(LENGTH(textfield)),SUM(LENGTH(COMPRESS(textfield)))
    INTO @bytes_uncompressed,@bytes_compressed
FROM table1_copy;
SET @bytes_diff = @bytes_uncompressed - @bytes_compressed,
SELECT @bytes_uncompressed,@bytes_compressed,@bytes_diff\G

If the compression is worth it, the

ALTER TABLE table1_copy SET textfield = COMPRESS(textfield);

Now the really bad news : Your developers must now run UNCOMPRESS(textfield) to get the JSON value out of the table and must use COMPRESS(textfield) upon every INSERT and UPDATE.

This is really all that I can suggest. The rest of this requires some elbow grease on the part of your developers. If the compression is worth it and you have the same INSERT and UPDATE performance

Answer 2

Is this a time-series? And you are purging data older than 14 days? If so, I strongly recommend that you switch to PARTITION BY RANGE(TO_DAYS(..)) and DROP PARTITION and REORGANIZE PARTITION daily. It will

• Be much faster than DELETE.
• Avoid the need for OPTIMIZE.
• The total size will expand and contract between 14 and 15 day's worth of data.

Details: Partition

Answer 3

In my case, optimize TABLE took a very long time, and did not reduce the size as expected.

I followed the @RickJames advice to switch to PARTITION BY RANGE(TO_DAYS(..)). After implementing and monitoring the database for two weeks, The advances are:

1. Reduce the database size from 600GB to 150GB without a daily increase.
2. Deleting the previous day's data take less than 30 seconds. (daily data is around 10GB)
3. Make the Select query faster because it uses a specific partition instead of the whole table.

This is my complete solution if someone needs it in the future:

1. Using a list instead of JSON for text columns. compressing the bigtext columns as suggested by @RolandoMySQLDBA

ALTER TABLE table1 SET textfield = COMPRESS(textfield);

2. Make the date column the primary key as a requirement for partitioning. (My partition is based on a date column)

ALTER TABLE  `table1` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`,`date`);

3. Making Partition for the first time.

ALTER TABLE `table1` PARTITION BY RANGE (TO_DAYS(`date`))( 
    PARTITION p20221203 VALUES LESS THAN  (TO_DAYS('2022-12-03')), 
    PARTITION p20221204 VALUES LESS THAN  (TO_DAYS('2022-12-04')), 
    PARTITION p20221205 VALUES LESS THAN  (TO_DAYS('2022-12-05')),  
    PARTITION p20221206 VALUES LESS THAN  (TO_DAYS('2022-12-06')),  
    PARTITION p20221207 VALUES LESS THAN  (TO_DAYS('2022-12-07')),  
    PARTITION p20221208 VALUES LESS THAN  (TO_DAYS('2022-12-08')),  
    PARTITION p20221209 VALUES LESS THAN  (TO_DAYS('2022-12-09')),  
    PARTITION p20221210 VALUES LESS THAN  (TO_DAYS('2022-12-10')),  
    PARTITION p20221211 VALUES LESS THAN  (TO_DAYS('2022-12-11')),  
    PARTITION p20221212 VALUES LESS THAN  (TO_DAYS('2022-12-12')),  
    PARTITION p20221213 VALUES LESS THAN  (TO_DAYS('2022-12-13')),  
    PARTITION p20221214 VALUES LESS THAN  (TO_DAYS('2022-12-14')),  
    PARTITION p20221215 VALUES LESS THAN  (TO_DAYS('2022-12-15')),  
    PARTITION p20221216 VALUES LESS THAN  (TO_DAYS('2022-12-16')),  
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

4. Adding a new partition before the new day start; deleting the partition of 14 days ago. Using bash script. I running it at 23:50 using Cronjob

#!/bin/bash
ADD_NAME="p$(date --date='2 days' +%Y%m%d)";
ADD_DATE=$(date --date='2 days' +%Y-%m-%d);
DELETE_DATE="p$(date --date='14 days ago' +%Y%m%d)";
mysql  -u USER -p'pass' db <<EOF
ALTER TABLE table1 DROP PARTITION $DELETE_DATE;
ALTER TABLE table1 REORGANIZE PARTITION pmax INTO (
  partition $ADD_NAME VALUES LESS THAN (TO_DAYS('$ADD_DATE')),
  partition pmax VALUES LESS THAN MAXVALUE
);
EOF