1

We have a MySQL table where DATA_FREE has grown to TB in size. We are not in a position to run optimize on such larger table where we have Billions of Rows. We did POC in lower environment where we have 72.5 GB of DATA_FREE with 254M records. We disabled the indexes and ran the optimize command on the table, it took 65 mins overall for the optimize query to finish including index recreation time. And we suspect this would take lot of time in production and we can't have such a longer downtime. This table have frequent read, write and delete.

We also have an upcoming plan to upgrade MySQL server from 5.x to 8.x. And ops team does this process by creating a read replica of existing database. I would like to know during read replica creation, does it free the DATA_FREE?

1
  • What is your setting of innodb_file_per_table?
    – Rick James
    Mar 28, 2023 at 17:27

2 Answers 2

0

No.

An RDS read replica is created from a snapshot of the source. That is, a byte-for-byte physical backup of the tablespaces on the source, complete with any data fragmentation that exists in those tablespaces.

If you need to eliminate the data_free without downtime, I use pt-online-schema-change. It actually takes longer than OPTIMIZE TABLE, but that isn't a problem because you can continue to read and write the table while it's working.

0

How did you get into the situation of needing OPTIMIZE? If it was due to a "big delete", then were several better ways -- http://mysql.rjweb.org/doc.php/deletebig

If innodb_file_per_table was OFF when the table was last created or altered, then the metric is talking about the shared storage, not the specific table.

Your OPTIMIZE might have run faster if you had not turned off the indexes; it will rebuild the indexes and can probably do it faster than you can.

If the deletes are for purging "old" data, then PARTITION BY RANGE(TO_DAYS()) is probably the best option going forward. Partition . If the deletes are something else, please elaborate.

4
  • Hi Rich, We have a table where frequent insert and delete operations are performed. Looks like we haven't performed any optimize operation so far on this table. By querying the metadata, we realized that there is a DATA_FREE size in TB's. So I am looking for an option to clear this DATA_FREE storage and going forward to run the optimize on regular basis. We can't afford to have a downtime of more than 30 mins. We also have one upgrade planned later this year. During upgrade, if this can be resolved automatically then it would be good for us.
    – arun
    Apr 4, 2023 at 11:43
  • @arun - Is it "file_per_table"? That is, was innodb_file_per_table=ON when the table was created? If not, then Data_free is not relevant just to the table, but all tables combined. Also,... Having a high Data_free may not matter. Are you running out of disk space? Having performance problems? Etc.
    – Rick James
    Apr 4, 2023 at 14:48
  • @arun - And Caution! -- If there is not enough disk room to OPTIMIZE, then it will fail, and you will have wasted a lot of downtime. How big is the table, the indexes; how much free disk space; etc.?
    – Rick James
    Apr 4, 2023 at 14:49
  • Hi @rick, innodb_file_per_table was set to ON when table was created. We suspect that by clearing DATA_FREE from this large table will not only save some space but also improve the system performance. Yes I agree, we need enough disk space to run optimize. Table is having billions of records with data_free in TB's and actual data is less than TB because this table only contain 3 days of data.
    – arun
    Apr 5, 2023 at 6:39

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.