Using percona with AWS RDS – Editing Big DB tables

I recently had a situation where i had to alter huge tables(having >3 Million records). Normally we go with standard process of altering the table, you go to SQL editor, type in your SQL command, execute it and table is altered. But when it comes to huge tables, you may want to alter your approach.

There are 2 ways to alter big table:

1. Create a new table(copy of original table), make necessary changes to schema, stop the original table to store new data, copy the original table data to new table, rename tables. And we are done.
   Note : when you have table constantly changing then going with this option is not ideal. It will result in data loss and inconvenience for the users.
2. Use Percona toolkit

Percona is a reliable tool to alter your tables without any data loss and minimum downtime(almost 0 minutes). Percona doesn’t stop alter, modify options while running, and takes into account the changes done while it is performing the operation.

Long story short, this clever tool automates the following process:

1. Create new, empty table with altered schema
2. Create insert, delete and update triggers on old table to make sure all changes are automatically replicated to the new table
3. Perform copy of old table data into new table in chunks
4. When in sync, replace the new table with the old one by renaming both one by one.

Because of the nature of this operation, there are some risks and limitations so it’s critical to familiarise with documentation.

Here are the steps to update/alter table:

1. First ssh to EC2 instance.
2. Install percona toolkit using following commands:

   $ sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
   
   Check that the packages are available:
   $ yum list | grep percona-toolkit
   
   Install the package:
   sudo yum install percona-toolkit
   
   

3. Start altering table with ‘pt-online-schema-change’
   pt-online-schema-change — execute — recursion-method none — progress percentage,1 — alter “ADD COLUMN [column] INT DEFAULT NULL” h=[db_host],D=[database],t=[table],u=[user],p=[password]
4. Alteration has started. At this point you should see new table in database called _[tablename]_new.
5. Once the process has finished, At this point you should see similar report:
   Copied rows OK.
   Analyzing new table…
   Swapping tables…
   Swapped original and new tables OK.
   Dropping old table…
   Dropped old table [old_table] OK.
   Dropping triggers…
   Dropped triggers OK.
   Successfully altered [table]
6. Done! At this point everything be ready and your table is now updated.

How easy was this. Percona is a great, try it for big tables.