How to effectively change the structure of a MySQL table in a table with millions of records?

Question

I have a MySQL database , the size of which is up to 17 pieces and has 38 million records. At the moment, I need to increase the size of one column (varchar 40 to varchar 80) and add more columns.

Many of the fields are indexed, including the ones I need to change. This is part of a unique pair that is necessary for applications to work. Trying to just make changes yesterday, the request lasted almost four hours without completion, when I decided to reduce our downtime and just return the service back.

What is the most effective way to make changes to something like that?

Many of these records are also old, and if there is a good way to sort the fragments from the records, but they are still available, which can help with this problem, making the table more manageable in size.

Answer 1

With MySQL 5.1 and again with 5.5, some alter instructions have been improved to simply change the structure without overwriting the entire table ( http://dev.mysql.com/doc/refman/5.5/en/alter-table.html - search in place) . The availability of this varies depending on the type of change you are making, and the engine you are using, the InnoDB plugin is most important. In the case of your specific changes, although the entire table will be rewritten.

When we encounter these problems, we usually try to use replica databases. While you are adding and not deleting, you can first run DDL against the replica, and then plan a brief breakthrough to move the replica to the main role. If you ended up in RDS, this is even one of his suggested uses for replica instances http://aws.amazon.com/about-aws/whats-new/2012/10/11/amazon-rds-mysql-rr-promotion/ .

Some other options include:

• Selecting a subset of records in a new table with the required structure (use INTO OUTFILE to avoid table locking). Upon completion, you can schedule a maintenance window and REPLACE INTO or UPDATE any records that have been changed in the start table since the initial copy of the data. When the update is complete, RENAME TABLE... both tables wraps the changes.
• Using the Percona tool pt-online-schema-change: http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html . This tool works with triggers, so if you already have triggers in the tables that you want to change, this might not suit your needs.

Answer 2

You have a choice.

In any case, you must make a backup before doing this.

One possibility is to disable your service offline and do it locally as you tried. If you do, you must disable key checks and restrictions.

 ALTER TABLE bigtable DISABLE KEYS; SET FOREIGN_KEY_CHECKS=0; ALTER TABLE (whatever); ALTER TABLE (whatever else); ... SET FOREIGN_KEY_CHECKS=1; ALTER TABLE bigtable ENABLE KEYS; 

This will speed up ALTER TABLE. It will restore indexes immediately when you do ENABLE KEYS.

Another possibility is to create a new table with the new schema that you want, then turn off the keys in the new table, and then do as @Bader suggested and insert the contents of the old table.

After creating a new table, you turn on the keys again, and then rename the old table to some name, for example, "old_bigtable", and then rename the new table to "bigtable".

You may be able to save your service online while you fill out a new table. But this may not work well.

The third possibility is to dump a giant table (into a flat file) and then load it into a new table with a new layout. This is much like the second possibility, except that you get a free backup copy of the table. You can do this pretty quickly with SELECT DATA INTO OUTFILE and LOAD DATA INFILE . To do this, you will need access to the server file system.

In all cases, disable and then re-enable restrictions and keys to speed things up.

Answer 3

Create a new table with the new structure you want with a different name, such as NewTable.

Then paste the data into this new table from the old table using the following query:

 INSERT INTO NewTable (field1, field2, etc...) SELECT field1, field2, ... FROM OldTable 

After that, you can delete the old table and rename the new table to the original name

 DROP TABLE `OldTable`; RENAME TABLE `NewTable` TO `OldTable` ; 

I tried this approach on a very large table and it is much faster than changing the table.