Geek Answers Handbook

Add column to large MySql table while online

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

The process of adding a column should be at run time, I mean that the application will still be running, and the rows will still be inserted and selected when the table changes.

  • How will this affect the running application?
  • How long will it take to complete this change?

How can I make this process safe and not damage the running application?

+6
source share
2 answers

The table is locked when executing DDL queries. This does not mean that the server does not accept requests for other sessions when it is blocked, but they are in the queue and there is probably a timeout before performing ALTER TABLE. Depending on factors such as hardware, table structure and, of course, the number of rows (which, as you said, are quite high), the change will take some time.

In MySQL 5.5 (faster index creation, innodb), an 8-core processor, chip disks that change the 5 mil row table with multiple indexes, take about 15-20 minutes in our case.

I suggest creating a copy and changing the copy. Once you are done, you will have to redo the data. Facebook had to handle it at a higher level, check it out

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

Tho, I can’t promise that all this will work safely in MyISAM Engine

EDIT:

Percona has created a toolkit that seems to work on all storage engines:

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

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

+4
source

While it adds a new column, it locks the table. The consequence of this is that any application that tries to use the table will be blocked until it is completed. The data will not be harmful, but any applications that try to use the table will hang.

It is hard to say how long it will take, it depends on how big the database is, since it will have to copy a lot of data and the speed of your server. As someone said above, the way to find out is to make a copy of the table and make the change in the first copy.

+2
source

More articles:


All Articles