Tuesday, June 29, 2010

mysql hack - altering huge tables

You have a huge mysql table - maybe 100 GB. And you need to run alter on it - to either add an index, drop an index, add a column or drop a column. If you run the simple mysql "alter table" command, you will end up spending ages to bring the table back into production.

Here is a simple hack to get the thing done. The benefit of the hack is that the alter runs quite fast. But since this is a hack, you will need to take care of the backups - in case anything goes wrong. I have seen this hack work effectively with both MyISAM and InnoDB tables.

Here i have created a simple table to show this hack process. You can assume that this table has billions of rows and is more than 100GB in size.

CREATE TABLE `testhack` (
`id` int(11) NOT NULL DEFAULT '0',
`unq` varchar(100) DEFAULT NULL,
`keyword` varchar(250) DEFAULT NULL,
UNIQUE KEY `unq` (`unq`)

I need to drop the unique key. So, i create a new table testhack_new with the following schema

CREATE TABLE `testhack_new` (
`id` int(11) NOT NULL DEFAULT '0',
`unq` varchar(100) DEFAULT NULL,
`keyword` varchar(250) DEFAULT NULL,

Flush both tables with read lock

mysql> Flush tables with read lock;

Open another terminal. And go to the mysql/data/<database> directory. Do the following:

mysql/data/test $ mv testhack.frm testhack_old.frm; mv testhack_new.frm testhack.frm; mv testhack_old.frm testhack_new.frm; mv testhack.MYI testhack_old.MYI; mv testhack_new.MYI testhack.MYI; mv testhack_old.MYI testhack_new.MYI;

So, what is happening here is that the index, table definitions are being switched. After this process, the table definition of testhack will not contain the unique key. Now unlock the tables in the main window. And run repair tables to remove any issues.

mysql> unlock tables;
mysql> repair tables testhack;
| Table         | Op     | Msg_type | Msg_text                                  |
| test.testhack | repair | warning  | Number of rows changed from 0 to 20000000 | 
| test.testhack | repair | status   | OK                                        | 

The repair table rebuilds the indexes. It is faster since it skips the use of key_cache for rebuilding the index (which is used in a normal alter table scenario).


Venu Anuganti said...

It works only for MyISAM related; but not for InnoDB.. becareful with InnoDB as you could endup corrupting your tablespace and only option will be to reload later on due to ids.

gamegeek said...

@ Venu : my mistake, i checked it out for MyISAM. And this technique is meant for MyISAM only...

Aman Sharma said...

It's really a gr8 hack man!!

Anonymous said...

MySQL 5.1 supports fast alter table that does many operations without the need for a full table rebuild. That's not supported for the built-in InnoDB but the plugin version does support it. You should use 5.1.48 or later with its bundled plugin if you want to do this.

ALTER TABLE, OPTIMIZE TABLE and REPAIR TABLE use the same method for MyISAM index building. "Repair with keycache" is used if myisam_max_sort_file_size isn't big enough. Best to set it to all available disk space. "Repair by sorting" is used if it's big enough for the server to be sure that it won't run out of disk space when sorting in a temporary file during the repair operation.

James Day, MySQL Senior Support Engineer, Oracle

Unknown said...

It is a great hack .let me introduce myself, I am khalil i am in sybsc(computer science)i my college they use linux 6.0. Each student is alloted with a computer (90 in total)which is connected to a main computer it can access to any of us data ,but we cannot access it.so could you tell me a way to hack it or simply see the data records so as to modify them ....ratbat21@gmail.com do reply me