Table optimisation using pt-online-schema-change

MySQL & MariaDB
1

Hi Experts,
I am trying to optimize a table on my mysql DB to reclaim disk space from DB to OS. Below is table details:-

+-----------------+----------------------------------------------+------------+-----------+------------+----------------+------------+
| table_schema    | table_name                                   | table_rows | Data (GB) | Index (GB) | Data Free (GB) | frag_ratio |
+-----------------+----------------------------------------------+------------+-----------+------------+----------------+------------+
| current         | snapshots                                    |   35507457 |     36.96 |       0.61 |         214.84 |     5.8130 |

Looking at the below output i can reclaim 214 GB space. But my MySql data directory has just around 52 GB space left.

So now if i run the pt-online-schema-change tool on the table would it create a temp historic.snapshots table of 36.96 GB, copying 35507457 rows into it? or would the temp table consume Data (GB) + Index (GB) + Data Free (GB) of space on the disk? I dont wont to run out of disk space while optimising and crash my MySql DB.

-Abhi

2

Hello, the info above is based on statistics so it is not 100% accurate. I suggest looking at the .ibd file to see the real current disk usage then re-assess.

2 Likes
3

Yes, this is how pt-osc would work. After the rows have been copied into this new table, it will be renamed as the current table, and the old table will be dropped.

2 Likes
4

Thanks @matthewb. I was able to successfully optimise the table.

-Abhi

1 Like