0

Mysql 5.05 that is hosting an older application that still gets lots of love from the users. Unfortunately, I'm not anything other than a hack dba at best, and am very hesitant in my skills to safely migrate to a new version of the database unless absolutely necessary. We are in the process of procuring a new application to take over responsibilities for the old application, but are probably a year out or so.

Anyway, I was patching the application the other day and added a column to a table, the command took a while to complete and in the meantime nearly filled up my drive hosting the datafiles. (table is roughly 25G) I believe this was a function of the creation of a temporary table. For reasons I'm not clear on, the space did not become free again after the column was added; i.e., I lost roughly 25G of disk space. I believe (?) this was due to the fact that the database was created with a single datafile; I'm not really sure on the whys, but I do know that I had to free up some space elsewhere to get the drive to an operable state.

That all being said, I've got the column added, but it is worthless to the application without an index. I held off adding the index trying to figure out if it is going to create another massive, persistent 'temporary' table at index creation time. Can anyone out there give me insight into:

  1. Will a create index and or alter table create index statement result in the creation of a temporary table the same size as the existing table?

  2. How can I recover the space that got added to ibdata1 when I added the column?

Any and all advice is greatly appreciated.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user1911071
  • 41
  • 1
  • 5

1 Answers1

1

  1. MySQL prior to version 5.1 adds/removes indices on InnoDB tables by building temporary tables. It's very slow and expensive. The only way around this is to either upgrade MySQL to 5.1, or to dump the table with e.g. mysqldump, drop it, recreate it with the new indices, and then restore it from the dump.

  2. You can't shrink ibdata1 at all. Your only solution is to rebuild from scratch. It is possible to configure MySQL so it doesn't use one giant ibdata1 file for all the databases - read that answer and it will explain how to configure MySQL/InnoDB so this doesn't happen again, and also how to safely dump and recreate all your databases.

Ultimately, you probably want to

  • Make a complete dump of your database
  • Upgrade to MySQL 5.1 or newer
  • Turn on InnoDB one-file-per-table mode
  • Restore the dump.
Community
  • 1
  • 1
Stuart Caie
  • 2,803
  • 14
  • 15
  • Hi Stuart - Thanks for the complete, though discouraging response. Considering I *just* added 25G of temp (?) space to the ibdata1 file, would creating the index now give me *another* 25G of space allocated, or would the space I just created get used for another temporary adjustment? – user1911071 Mar 24 '14 at 16:13
  • You now have 25GB of "free" space inside the `ibdata1` file, it's just that InnoDB won't give it back to the filesystem. So repeating the indexing process will use up that 25GB free space inside the file for its temporary table, and then release it after the index is added. – Stuart Caie Mar 24 '14 at 16:41