|
|
 |
[03.30.2008] 02:52 - SLOW MySQL innodb alters
I like MySQL. I like InnoDB. But damn, when you have a big table with a lot of data in it and you want to alter a column, the change can take hours. And then time out. Apparently this is because when InnoDB is set up to use 1 file for the entire database (as it does by default), when you make changes to the structure of in the global table space, it has to copy the entire database with the new changes and then replace the existing copy. Very IE intensive.
It's really too bad that InnoDB doesn't have anything that allows it to reclaim free disk space when data is removed from a table, because that's what has caused this problem in the first place since the data is no longer needed in the table. The typical solution with InnoDB is to remove the data, export the tables and then re-import. But I can't even get to that part of the process because one of the columns is set to NOT NULL. So if I try to alter the table, it takes hours and then fails. If I try to drop the column it takes hours and then fails. If I try to create a new table based on the old table, but without those columns, it takes hours and then fails. Maybe it's time to look into having innoDB use a different file for each table via the innodb_file_per_table setting. All of those on the edge of your seat, I'll keep you updated. |
|
