Add a column to a large MySql table while online

Question

I need to add a new column to a table in MySQL DB (MyISAM table) that contains more than 20 Million rows.

The process of adding the column must be in run-time, I mean that the app will still be running and rows will still be inserted and selected while altering the table.

• How will this affect the running app?
• How long can it take to perform this change?

How can I do this process safely and without harming the running app?

Shushan

Clone, benchmark, plan, deploy. – Chrysalis

What database engine are you using? – Henhouse

@Henhouse I think you mean MyISAM. DB is MySQL. – Shushan

He said he was using MyIsam – Vaisya

Answer 1

Table is locked while DDL queries are performed. This doesn't mean that the server doesn't accept queries on other sessions while locked but they are queued up and probably time out before your ALTER TABLE is done. Depending on factors like hardware, table structure and of course amount of rows (which you said is quite high) the alter will take a while.

On MySQL 5.5 (faster index creation, innodb), 8-core CPU, chip disks, altering a 5 mil row table with several indexes takes about 15-20 minutes in our case.

I suggest to create a copy, and alter the copy. You'll have to replay the data delta after you're done. Facebook had to deal with this on a way higher level, check this out

http://m.facebook.com/note.php?note_id=430801045932

Tho, I can't promise this would all work safely on the MyISAM Engine

EDIT:

Percona created a toolkit which apparently works on all storage engines:

http://www.mysqlperformanceblog.com/2012/04/05/percona-toolkit-2-1-with-new-online-schema-change-tool/

With this release we introduce a new version of pt-online-schema-change, a tool that enables you to ALTER large tables with no blocking or downtime. As you know, MySQL locks tables for most ALTER operations, but pt-online- schema-change performs the ALTER without any locking. Client applications can continue reading and writing the table with no interruption.

Vaisya

Answer 2

While it's adding the new column, it will lock the table. The implication of this is that any application that tries to use the table will be blocked until it's done. No harm will be done to the data, but any applications that try to use the table will hang.

It's hard to say how long it will take, it depends on how big the database is already, since it will have to do lots of data copying, and the speed of your server. As someone said above, the way to find this out is to make a copy of the table and perform the change on the copy first.

Henhouse

"any applications that try to use the table will hang" - at least, until the table is unlocked or they timeout (and assuming that they make a blocking call to the database driver). – Bouncer