I have been using pt-online-schema-change to reduce the file size on disk for a table that has lot of deletes. It has worked as expected for a long time. I have not updated anything. For my last 4 runs, the file size seems to be growing everytime I run. The number of rows is about the same. I started with 141G. Ran the pt-online-schema-change about 4 times. When I just ran it now, the file size on disk went from 191G to 200G. All the runs are completing successfully (triggers dopped, the _<table_name> dropped). Why would this be happening?
pt-online-schema-change --version
pt-online-schema-change 3.0.12
1 Like
Can you give us more information? How are you running pt-osc? Have you confirmed free space before running and after running? Are you watching the size of the shadow table as it is being populated?
1 Like
Here’s how I am running the command.
File size before I ran the command
cd /export/apps/moogdata/var; find ./ -type f -exec du -Sh {} + | sort -rh | head -n 20
160G ./lib/mysql/historic_moogdb/snapshots.ibd
pt-online-schema-change --chunk-size 20000 --user=root --ask-pass --progress time,10 --socket=/export/apps/moogdata/var/lib/mysql/mysql.sock --print --nocheck-unique-key-change --no-check-alter --execute --critical-load Threads_running=100 --alter "ENGINE=InnoDB" h=localhost,D=historic_moogdb,t=snapshots
It completes successfully
Copying `historic_moogdb`.`snapshots`: 99% 00:13 remain
Copying `historic_moogdb`.`snapshots`: 99% 00:05 remain
2022-02-25T09:12:20 Copied rows OK.
2022-02-25T09:12:20 Analyzing new table...
2022-02-25T09:12:20 Swapping tables...
RENAME TABLE `historic_moogdb`.`snapshots` TO `historic_moogdb`.`_snapshots_old`, `historic_moogdb`.`_snapshots_new` TO `historic_moogdb`.`snapshots`
2022-02-25T09:12:20 Swapped original and new tables OK.
2022-02-25T09:12:20 Dropping old table...
DROP TABLE IF EXISTS `historic_moogdb`.`_snapshots_old`
2022-02-25T09:13:00 Dropped old table `historic_moogdb`.`_snapshots_old` OK.
2022-02-25T09:13:00 Dropping triggers...
DROP TRIGGER IF EXISTS `historic_moogdb`.`pt_osc_historic_moogdb_snapshots_del`
DROP TRIGGER IF EXISTS `historic_moogdb`.`pt_osc_historic_moogdb_snapshots_upd`
DROP TRIGGER IF EXISTS `historic_moogdb`.`pt_osc_historic_moogdb_snapshots_ins`
2022-02-25T09:13:00 Dropped triggers OK.
Successfully altered `historic_moogdb`.`snapshots`.
File size went to 240G
cd /export/apps/moogdata/var; find ./ -type f -exec du -Sh {} + | sort -rh | head -n 20
240G ./lib/mysql/historic_moogdb/snapshots.ibd
I would use the innodb_ruby tools to count the number of data, index, and free pages before and after. Because this is row-by-row copying, the new table may be attempting to balance the pages to favor more intra-page free space for new INSERTs rather than make pages as compact as can be. Generally InnoDB will fill data pages only to 50 percent initially. This results in a naturally larger disk footprint. Maybe check out this parameter MySQL :: MySQL 5.7 Reference Manual :: 14.15 InnoDB Startup Options and System Variables