InnoDB table size increased substantially after ALTER

Question

I'm trying to understand why the data size of my table increased so much after performing this ALTER TABLE statement on an InnoDB table:

ALTER TABLE alert ADD COLUMN receiver_id INT(10) UNSIGNED

Note that I used gh-ost to perform the ALTER because the size of the table was rather large. I know that this will create a net new table, copy the rows and rebuild the indexes as data is copied. Based on what I know, I would expect the outcome to be very similar to how OPTIMIZE TABLE works.

Here's some stats from the old table (I got these from SQL Workbench, but they should be calculated from information_schema):

• Table rows: 40037398
• AVG row length: 8800
• Data length: 328 GiB
• Index length: 12.9 GiB
• Table size (estimate): 341 GiB
• Autoincrement: 65447094

And here's a screenshot of the column data from information_schema.COLUMNS, including the data types and encodings:

And here are the same stats for the new table which has the receiver_id column applied to it:

• Table rows: 24763900
• AVG row length: 18735
• Data length: 432.1 GiB
• Index length: 12.2 GiB
• Table size (estimate): 444.3 GiB
• Autoincrement: 65472610

And here's a screenshot of the column data from information_schema.COLUMNS, including the data types and encodings. You should see the additional INT column at the bottom:

The difference in autoincrement makes sense because after the tables had been swapped, new writes were being applied to the latter. The decrease in index length also makes some sense to me, as rebuilding indexes can optimize the storage underneath. Can anyone provide any insight into why Table rows, Data length would change so dramatically?

EDIT 10/4 adds COLUMN data screenshots.

Answer 1

It's difficult to answer without knowing the nature of the data being copied and maybe of the integration software that is used.

But I decided to share my guesses hoping it may lead to better diagnostic:

1. Row length increase may be because texts were converted into Unicode during migration.

2. Lower row count maybe due to some kind of deduplication that also removed records with less text data thus increasing average row length. Or it might be just that the migration process was aborted in the middle.

It might help to make a more educated guess if you supplement the question with SHOW CREATE TABLE output for both source and target tables.