Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table with 1.6 million rows.

Then I add a field on that table.

I am using hostgator as hosting. You know, they have like 16 core CPU.

Result: 1. phpmyadmin doesn't work for 10 minutes 2. After database back online the field I want to add is NOT added. 3. Fortunately I do not get a call from hostgator complaining.

I don't care it's slow. I know it's slow. It's 1.6 million row. I just want to do this once.

Is this because I use innodb engine?

The command is simple:

Alter table tablebusiness add column `Prominent` int(10) NOT NULL

I got this error:

#2013 - Lost connection to MySQL server during query

I do not need this operation to be run fast

I need it to run once.

I suppose I can copy the table somewhere else and then switch the old table into new table but innodb has cascade and I do not know how to do so.

Other update: Hostgator is pissed. Can't blame them. I must have used huge CPU power.

Looks to me there is a proper way to modify a table. Maybe shutting down the database first in maintenance mode or something. Maybe creating a copy of table first.

share|improve this question
    
Do you get an error? Can you post the actual ALTER TABLE statement you executed? –  ypercube Jun 14 '12 at 7:32

1 Answer 1

up vote 1 down vote accepted

ALTER on such a huge table can be slow irrespective of Engine_type

Follow this method to avoid downtime and quicker operation:

  1. Setup slave

  2. Stop replication.

  3. Make ALTER on slave

  4. Let slave catch up the master

  5. swap master and slave, so slave becomes production server with changed structure and minimum downtime

You can also refer this post from MySQL Performance blog:

http://www.mysqlperformanceblog.com/2008/03/27/using-mmm-to-alter-huge-tables/

share|improve this answer
    
What the hell is mmm? Can't I just use something from phpmyadmin? –  Jim Thio Jun 14 '12 at 9:22
    
MySQL Master-Master replication manager (MMM) kovyrin.net/2007/04/23/… –  Mahesh Patil Jun 14 '12 at 9:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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