Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

with a table of about 2 million rows,

alter table xxx add yyy smallint default 0
is taking forever, its current state is copying data to tmp table. the table is using innodb, has a few indices. tmp_table_size=4G and max_heap_table_size = 4g, why would it take so long? how to speed it up?

share|improve this question
    
How big is the table file-size wise? As for improving the speed - get a faster disk, it's going to be disk-IO bound. Saving it onto a different drive won't hurt either, though even with table_per_file set, it's not trivial to set that up. Without it, it's almost impossible. –  Alister Bulman Mar 3 '11 at 23:24
    
ibdata1 is 219G. I guess maybe that's why it's slow.. –  user12145 Mar 4 '11 at 14:22
add comment

1 Answer 1

up vote 2 down vote accepted

In the book "High Performance MySQL" Second Edition by Jeremy Zawodny & Peter Ziestev there is a high-speed, high-risk method of adding a column to table, regardless of table size. It goes something like this:

METHOD 1

Assuming db is the database where xxx resides

Step 1. CREATE TABLE zzz LIKE xxx;

Step 2. ALTER TABLE zzz add yyy smallint default NULL; -- Not using 0 for now

Step 3. Perform the following in the OS

service mysql stop
cd /var/lib/mysql/db
mv xxx.frm xxxold.frm
cp zzz.frm xxx.frm
service mysql start

Step 4. Try and see if xxx is accesible

If this does not work try this:

METHOD 2

Step 1. CREATE TABLE zzz LIKE xxx;

Step 2. ALTER TABLE zzz add yyy smallint default 0;

Step 3. INSERT INTO zzz (col1,col2,...colN) SELECT col1,col2,...colN FROM xxx;

Step 4. ALTER TABLE xxx RENAME jjj;

Step 5. ALTER TABLE zzz RENAME xxx;

The second method has to work. The first is recommended from that book I mentioned.

Give them a Try !!!

P.S. before trying the first method, backup everything (LVM snapshot)

share|improve this answer
    
is this myisam only since you mentioned .frm files –  user12145 Apr 29 '11 at 18:06
    
yes My first answer is MyISAM only. InnoDB is too convoluted to do since metadata aside from the .frm is sitting inside ibdata1. The second answer works regardless of storage engine. –  RolandoMySQLDBA Apr 29 '11 at 18:20
add comment

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.