Don’t Alter Table. Do Copy and Rename

In some cases a MySQL MyISAM table structure needs to be alter. This includes adding, removing and changing table columns (or indexes) and even re-ordering the MySQL table.
In these cases, for performance and safety reasons, it is wise to avoid altering the current working MySQL table and adopt the Copy and Rename approach.

The Copy and Rename approach consist from the following steps:

  • Create similar temporary table but with the requested change
  • Disable the temporary table keys,
  • Copy the rows from the original table to the temporary table
  • Enable the temporary table keys,
  • Backup the original table and rename the temporary table to have the original table name

For example, in a case where an existing MySQL table needed new column,
instead of:

Alter tbl add column colx int not null;

do the following:

Create table tbl_tmp like tbl;
Alter table tbl_tmp add column colx int not null;
Alter table tbl_tmp disable keys;
Insert into tble_tmp select *,0 from tbl;
Alter table tbl_tmp enable keys;
Rename table tbl to tbl_old;
Rename table tbl_tmp to tbl;

ALTER TABLE tbl_tmp DISABLE KEYS tells MySQL (for a MyISAM table) to stop updating non-unique indexes. ALTER TABLE tbl_tmp ENABLE KEYS then re-create the missing indexes. MySQL re-create indexes with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup.

MySQL Quiz
17 Comments
  1. Hi,

    Of course, the above method assumes:
    - the table will not be required to change during the copy time
    - it is a MyISAM table: DISABLE/ENABLE KEYS have nothing to do with InnoDB.
    - it is a small/MyISAM table: a large InnoDB table will make for an extremely large transaction, hence very slow transaction, with MVCC going wild during that time.

    For small tables, this may be just OK, but then, for small tables the problem is not all that interesting.

    For larger tables you may:
    - Try oak-online-alter-table (part of my openark-kit toolkit)
    - Try Facebook Online Schema Change
    - Do it your way, but break the INSERT.. SELECT into smaller chunks using oak-chunk-update (again, part of openark-kit)
    - Likewise, using mk-archiver (part of Maatkit)
    Reorder the above list as you see fit.

    I’ve seen INSERT INTO… SELECT single-transaction-queries which took 3 days to complete, but broken into smaller chunks went through in less than 30 minutes. The difference in time is drastic.

    Regards

    • admin says:

      Hi Shlomi,
      You are right that the Copy and Rename approach is good only for MyISAM tables.
      In huge MyISAM tables the Copy and Rename approach is performing much better than the ALTER TABLE.
      I cant see any improvement by breaking the INSERT.. SELECT into smaller chunks.

      • Well, there’s less locks on the table. It would be possible to write to the table during that time. Although, this also means you may have inconsistent copy, in case someone was DELETEing or UPDATEing rows already accessed.
        By the way, oak-online-alter-table solves that as well.

  2. Lachlan Mulcahy says:

    I’m not sure how great this approach is.

    The ALTER TABLE statement in MySQL actually creates a new temporary copy of the table with the new structure and then copies the rows into the new table. At the end, it finally swaps the newly built table in place. For this reason it is safe, because any failure during the process will not affect the original copy of the table.

    Also, in this approach the table may receive new rows INSERTed at any time during the steps above, meaning that some new rows may not end up copied correctly into the new table.

    If this table was read-only and and loss of new INSERTs was not a concern, then the READ lock by the ALTER TABLE statement would not cause an availability problem.

    I guess the only real benefit we are left with here is of having the old backed up copy.

    By the way – I’m not trying to be a troll here so much as give some constructive feedback.

    • admin says:

      I assume that in this maintenance period, the site will be in “Read-Only” mode. That is mean no write request to the table.
      In the “Copy and Rename” approach, the site will still serve read request while in the “ALTER TABLE” approach the read requests will be blocked until MySQL will done altering the table.

  3. Cameron says:

    As a note, rather than doing 2 renames, they can be done atomically via the one command:
    Rename table tbl to tbl_old, table tbl_tmp to tbl;

  4. Cameron says:

    Oops, should read Rename table tbl to tbl_old, tbl_tmp to tbl;

  5. Ken Swift says:

    I might be wrong but this solution is flawed. You say for performance and safty it it the best option. Consider big table 10milon rows with hihgh transaction rate. Doing as You say I would have to
    - create whole new table
    - add,column disaable keys,
    - copy 10milion rows,
    - rename tables

    What about ongoing transations while coping 10milon rows ? I don’t know how exactly myql handles multi concurency problem but it seems with your solution i would miss some/many rows. So it realy is tu much hassle.

    What not just alter table tbl add column colx int not null default 0 ?
    In postgres You can do this without locking writes to table = save and fastest way.

  6. danielj says:

    so where is the benefit to do manually the same MySQL will do anyway when calling ALTER TABLE?

    • admin says:

      The benefits are:
      1. In the time the table is copied, the MySQL can still serve read requests. If you are using ALTER TABLE, MySQL will not be able to serve read request (they will be blocked until the ALTER command will be finished)
      2. I have done some performance test on MyIsam tables that show that the Copy and Rename approach perform much better.

  7. Hi,

    You should first approve my comment for it to appear on your post, before responding to it. No one can see it but myself.

    The copy+rename may perform much better than ALTER, but I suggest they perform much worse than if you did the copy in smaller chunks.
    But the truth is I never benchmarked on huge MyISAM tables.

  8. xyz says:

    What is the benefit if we change the table name using alter command

    • Ilan Hazan says:

      In most cases, ALTER TABLE makes a temporary copy of the original table. MySQL waits for other operations that are modifying the table, then proceeds. It incorporates the alteration into the copy, deletes the original table, and renames the new one. While ALTER TABLE is executing, the original table is readable by other sessions.

      HOWEVER, If you use ALTER TABLE tbl_name RENAME TO new_tbl_name without any other options, MySQL simply renames any files that correspond to the table tbl_name without making a copy.

      That is mean that both the “ALTER … RENAME” and the “RENAME TABLE” statement are equivalent except one thing:
      You cannot use RENAME to rename a TEMPORARY table. However, you can use ALTER TABLE instead:
      mysql> ALTER TABLE orig_name RENAME new_name;

Leave a Reply

*