MySQL
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?
Is your harddrive full ?
The repair procedure will need at least twice as much space as the original table, if there's not enough room it'll just sit waiting for enough space. Repairing a 1.2Gb table should be very fast.
The following exerpt came out of the book "High Performance MySQL, Second Edition".
This is an excellent book and I would recommend it to anyone.
The short answer is:
With your table size and conditions, no matter what method you choose, I think you're potentially in for a long wait.
Table Conversions
There are several ways to convert a table from one storage engine to another, each with advantages and disadvantages.
ALTER TABLE
mysql> ALTER TABLE mytable ENGINE = Falcon;
This syntax works for all storage engines, but there’s a catch: it can take a lot of time. MySQL will perform a row-by-row copy of your old table into a new table. During that time, you’ll probably be using all of the server’s disk I/O capacity, and the original table will be read-locked while the conversion runs.
Dump and import
To gain more control over the conversion process, you might choose to first dump the table to a text file using the mysqldump utility. Once you’ve dumped the table, you can simply edit the dump file to adjust the CREATE TABLE statement it contains. Be sure to change the table name as well as its type, because you can’t have two tables with the same name in the same database even if they are of different types—and mysqldump defaults to writing a DROP TABLE command before the CREATE TABLE, so you might lose your data if you are not careful!
CREATE and SELECT
The third conversion technique is a compromise between the first mechanism’s speed and the safety of the second. Rather than dumping the entire table or converting it all at once, create the new table and use MySQL’s INSERT ... SELECT syntax to populate it, as follows:
mysql> CREATE TABLE innodb_table LIKE myisam_table;
mysql> ALTER TABLE innodb_table ENGINE=InnoDB;
mysql> INSERT INTO innodb_table SELECT * FROM myisam_table;
That works well if you don’t have much data, but if you do, it’s often more efficient to populate the table incrementally, committing the transaction between each chunk so the undo logs don’t grow huge. Assuming that id is the primary key, run this query repeatedly (using larger values of x and y each time) until you’ve copied all the data to the new table:
mysql> START TRANSACTION;
mysql> INSERT INTO innodb_table SELECT * FROM myisam_table
-> WHERE id BETWEEN x AND y;
mysql> COMMIT;
After doing so, you’ll be left with the original table, which you can drop when you’re done with it, and the new table, which is now fully populated. Be careful to lock the original table if needed to prevent getting an inconsistent copy of the data!
Best Answer
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)