Size Requirements for pt-online-schema-change

MySQL & MariaDB
1

So I have a table whose .ibd file is 3.1T. However, selecting the size of the table from info schema says its 140GB. I want to perform a pt-online-schema-change to essentially “optimize” it. I have enough disk space for another 140GB, but don’t have 3.1T extra. Thoughts on if I will have enough extra space to perform this? Other thoughts on why such a huge discrepancy?

2

HI @James_Janovich1
Run this SQL for your table:

SELECT table_schema, table_name, CONCAT(ROUND((data_length / 1024 / 1024),2),'MB') dataMB, CONCAT(ROUND((index_length / 1024 / 1024),2),'MB') indexMB, CONCAT(ROUND((data_free / 1024 / 1024),2),'MB') dataFreeMB    FROM information_schema.tables  WHERE table_name = 'XXXXX';

You can see the actual amount taken up by data and indexes, and how much is free. The “discrepancy” is due to InnoDB being a greedy disk monster. InnoDB never releases free’d disk space back to the filesystem. It only grows. At some point, you had 3.1T of data in this table, then you deleted it. Now there’s 2.9T of free, empty pages in that table. optimize the table to recreate the table and reclaim the space.

If you have at least 320GB of free space, I’d say you are good to go.

2 Likes
3

Thank you! So this is interesting. I have a master and 2 slaves. The master shows the dataMB as 775419.00MB and the slaves show 139741.00MB. But the file on disk on both is 3.1TB. if I ran the pt-online-schema-change I would need to run on the master and based on those numbers likely would not have enough disk space I believe. Thoughts?

1 Like
4

You should need as much space as data+index to rebuild the table. Strange that your dataMB is different between source/replica. When was the last time you ran pt-table-checksum to verify data consistency between s/r?

1 Like
5

Also keep in mind that you not only need extra space for the copy table, but a lot of binary log files will also be generated due to the table rebuild. Make sure expire_log_days is set appropriately and that there is enough disk space on all nodes so that the binary logs can be fetched by the replica before they are purged on the primary

1 Like