2

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
3
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. Nov 20, 2022 at 14:49
  • When I run optimize TABLE table1; (Also, for table2 and table3), The table size should shrink dramatically because in previous days I delete multi-million rows. My question is, why the size of the tables do not decrease?
    – Niyaz
    Nov 20, 2022 at 15:02
  • Does the size stay approximately the same for a full 14 days? Please provide the ll output for 14 days (if we can't provide an answer before then).
    – Rick James
    Nov 21, 2022 at 3:59

3 Answers 3

1

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

3
  • thanks for your answer. Implementing PARTITION is in my plan but, I should find downtime to prevent losing a huge amount of data. Also, I will monitor the size of the tables for 14 days. I will give you an update about both of them.
    – Niyaz
    Nov 21, 2022 at 8:10
  • 1
    @Niyaz - OPTIMIZE took downtime because copied the table. Adding Partitioning needs a similar amount of downtime. (I strongly suggest you test the ALTER separately before running it in Production.) Note two restrictions: No FKs, no UNIQUE indexes.
    – Rick James
    Nov 21, 2022 at 16:17
  • I implement PARTITION for tables, and it works perfectly. Thanks.
    – Niyaz
    Nov 30, 2022 at 21:06
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

4
  • 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?
    – Niyaz
    Nov 20, 2022 at 22:59
  • One week there might be relatively empty cells; the next week might have bulky ones. It all averages out. That is, I do not buy your suggestion that cell size can explain why Optimize never works.
    – Rick James
    Nov 21, 2022 at 3:59
  • @RolandoMySQLDBA Thanks for your answer. My plan is: First, monitor the size of all tables for 14 days, and check how the size change. (I will give you an update about that) Second, to reduce the size, I will implement the COMPRESS that you mentioned and convert JSON to LIST, which I think reduces size dramatically. But my concern is how COMPRESS affect insertion time because I have a very tight schedule. I should insert multi-millions of rows every hour. (no worries about UNCOMPRESS it is on the GUI side)
    – Niyaz
    Nov 21, 2022 at 8:03
  • Also, In my case, there is no UPDATE operation. ONLY INSERT, SELECT, and DELETE.
    – Niyaz
    Nov 21, 2022 at 8:19
1

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);
  1. 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`);
  1. 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
);
  1. 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
1
  • 1
    +1 for actually putting in the work for your solution Dec 16, 2022 at 21:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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