3

There's 2 parts to this question. I'm trying to "simply" add a column to a fairly large table. The current table has about 200 million rows and about 10 columns, is about 80 GB in size, has 2 keys (primary and one other), and is constantly being written into (it's live).

I'm attempting to add a column as follows:

ALTER TABLE my_large_busy_table ADD new_column BIGINT(20);

  1. The first issue I'm having is with the amount of time it's taking. On my last attempt, I got timed out after 100 minutes. I wanted to make sure this is reasonable because it seems like a long time. One option I'm considering is turning off all incoming queries to the table to prevent any sort of blocking issues and re-running without any timeout limits.

  2. The next issue is after I got timed out on my previous query, I checked AWS CloudWatch and noticed that my db instance ate through another ~160 GB of free space. Most of this would have been from the ALTER TABLE command. From my understanding, when adding a table, MySQL copies the data over to a new table. If that's the case shouldn't it take ~80 GB (the size of the table)? In addition, after timing out the space is not being released, so now I have 160 GB of data that I can't seem to access, find or remove.

I'm trying to release that 160 GB of space as well as actually add my column to the table.

UPDATE

So apparently if MySQL crashes during an alter, InnoDB can cause an orphaned table which was causing the space issue I had. See: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting-datadict.html

I eventually fixed it by using SHOW PROCESSLIST in MySQL and then KILL-ing the process.

Instead of altering the table, I've created a new table with the new columns and I'm copying data over in smaller chunks which seems to work much better.

stoneage
  • 510
  • 3
  • 14
  • What's the engine on the table? MyISAM or InnoDB or another? Depending on how many indexes and foreign keys, yes it could be a very costly operation. – stakolee Feb 12 '15 at 20:09
  • 1
    Wow, that's a huge table. Doing this is going to take a long time, no matter what. Doing it on prod while it's being written to is really dangerous. I recommend taking the system offline, perform the update, then bring it back online. You can probably reclaim the space with optimize. http://dev.mysql.com/doc/refman/5.5/en/optimize-table.html – Halfstop Feb 12 '15 at 20:10
  • 1
    An alternative is to make a new table, and join it with the original table when you need columns from both. That's what we've done rather than change the schema of our main table with all users, because it would be too painful to rebuild it. – Barmar Feb 12 '15 at 20:14
  • Engine is InnoDB. Yea, I'm leaning towards either taking the system offline or making a separate table. Before doing that I'm trying to figure out how to free up the space. I'll take a look at optimize. – stoneage Feb 12 '15 at 20:17
  • Pretty sure you can't recover space in InnoDB without reimporting everything unless you have `innodb_file_per_table`. See discussion here http://stackoverflow.com/questions/1270944/mysql-innodb-not-releasing-disk-space-after-deleting-data-rows-from-table – ianjs Feb 12 '15 at 22:54

0 Answers0