Slow at Altering Large Tables

[davidb2002]

I have a table of around 7mil records (data length: 1GB, Index Length 1GB, 12 Indexes, InnoDB). I am needing to add a mediumint and a tinyint to the end of the table. Up until now, adding a row has been no problem, but for some reason this is taken much longer than other times we have altered this table. It has taken up to 30min ‘copy to tmp table’ and we still killed it because it was causing some problems with access to the table.

I am looking for suggestions/help on how to improve the speed of adding these fields to this table. I am using a slightly modified version of mysql_huge conf that works well.

[MarkRose]

I’ve read that many people use a master-master setup when dealing with databases this large. They’ll turn off replication, alter the table on the slave, restore replication, switch masters, then repeat for the new slave.

[constejo]

[B]davidb2002 wrote on Fri, 25 January 2008 05:21[/B]

data length: 1GB

That's not really much - MySQL can handle a LOT more rows. But - it depends on the hardware (especially RAM) of your server. Can you give us some information about this?

[B]Quote:[/B]

Up until now, adding a row has been no problem

I'm sure, you mean "adding a column" - don't you? ;)

[B]Quote:[/B]

has taken up to 30min 'copy to tmp table'

That's a result of too less RAM or no proper configuration.

[B]Quote:[/B]

I am using a slightly modified version of mysql_huge conf that works well.

"huge" in mysql did not work for me - so I think, it's "relative". I never understood, why MySQL Corp. didn't give us a configuration for really huge databases... ;)