0

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:

enter image description here

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:

enter image description here

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.

Improve this question
2
  • What ROW_FORMAT was the table (before and after)?
    – Rick James
    Oct 12, 2019 at 0:39
  • Is each VARCHAR and TEXT usually the same length from row to row? Or maybe just some are 'constant' length? SELECT AVG(LENGTH(x)), STD(LENGTH(x)) FROM t for each of the 5 text columns.
    – Rick James
    Oct 12, 2019 at 0:49

1 Answer 1

Reset to default
0

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.

Improve this answer
10
  • Thanks for responding @ichalov! I suspected encoding at first as well, but it seems like that was unchanged. I will edit the post to add that information in there though. The lower row count doesn't really make much sense to me because the select count(*) queries were the same.
    – Xopherus
    Oct 4, 2019 at 12:53
  • Though from this article (dba.stackexchange.com/questions/151769/…), it seems like the row count metric is already inaccurate, so it shouldn't be trusted. The big outlier still seems to be the data length.
    – Xopherus
    Oct 4, 2019 at 13:26
  • Another idea is to try ANALYZE TABLE. It may change values in question to the correct ones. I guess AVG row length is a calculated value, so it should decrease if Table rows is recalculated to be bigger.
    – ichalov
    Oct 4, 2019 at 16:07
  • I've done that as well. The numbers change, but not significantly so. The diffs between data length are still over 100GiB apart.
    – Xopherus
    Oct 4, 2019 at 17:56
  • Another option is to copy both tables in the same database and compare them by LEFT JOIN. But that's probably not easy with these table sizes (maybe better done by cold file copy and not mysqldump). I can also propose to perform a statistical research of the data, e.g. take and compare gistograms of some text field length from both tables, with by query like select floor(pow(length(<text_field>), 0.1)), count(*) from <table> group by 1 order by 1; 30% difference in overall file size doesn't look too big (compared to other discrepancies). Maybe it somehow got fragmented during migration?
    – ichalov
    Oct 4, 2019 at 19:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.