In MySQL 5.6, Oracle introduced a superb feature of online ddl which means that you can change schema of your tables without blocking any concurrent DML or other queries. Looks good right? Though, It's not applicable on every DDL you execute. In some cases, DDL happens the old way (blocks other DML operations) and you'll see waiting for metadata lock in the process list.
List of all such Online DDL operations can be viewed here : https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
Question : What to do in those cases where ddl operation is suppose to lock DML operations?
Ans : In production environment, This can be a serious problem and large tables can be huge amount of time and you cannot hold other update/delete/insert operations to wait that long. Solution is pt-online-schema-change i.e. part of percona toolkit. This is an awesome tool and can help you with running your blocking ddl as online.
How to get it?
Get it from here : https://www.percona.com/downloads/percona-toolkit/
You might face errors because of its dependencies, so install below mentioned perl modules in advance, if possible :
apt-get install libdbd-mysql-perl
Ok, After installing pt-online-schema-change, Question arises that how to use it and how does it work?
Ans : It's quite simple to use, most of the parameters are as common as other pt-tookit's tools though, I am mentioning some major onces which i used here to overcome my problem with its functioning.
What does it do to avoid metadata lock :
1. Create and alter temporary table
2. Capture changes from the table to the temporary table
3. Copy rows from the table to the temporary table
4. Synchronize the table and the temporary table
5. Swap/rename the table and the temporary table
How did I use it in my case :
pt-online-schema-change h=localhost,P=3306,u=root,D=database_name,t=table1 --ask-pass --execute --chunk-time=1 --chunk-size=5000 --nodrop-old-table --alter "modify column1 int(12) unsigned DEFAULT 0"
In the above command, DSN = h=localhost,P=3306,u=root,D=database_name,t=table1. This was most confusing for me and wasted almost an hour on that to understand DNS format where all related documents proven confusing, I just figured it out with pt-online-schema-change's help.
pt-online-schema-change --help DSN syntax is key=value[,key=value...] Allowable DSN keys: KEY COPY MEANING === ==== ============================================= A yes Default character set D yes Database for the old and new table F yes Only read default options from the given file P yes Port number to use for connection S yes Socket file to use for connection h yes Connect to host p yes Password to use when connecting t no Table to alter u yes User for login if not current user If the DSN is a bareword, the word is treated as the 'h' key.
If you understand this, Life will be quite fun with percona tools.
Other used parameters and their meanings :
1. --execute = Execute given alter.
2. --chunk-time=1 = Adjust chunk-size dynamically so that every chunk takes this much of time to execute. 0.5 secs is a default value.
3. --chunk-size=5000 = Copy 5000 records in every bunch. 1000 is a default value.
4. --nodrop-old-table = This option leaves old copy of table in your database just in case you want to have it old backup. Old table will be named as it is though with an underscore "" and ending with keyword "old".
5. --alter "modify column1 int(12) unsigned DEFAULT 0" = This part contains your alter statement without statement of "alter table tablename".
Note: There are some experiences which I wanted to share here.
1. This tool is great and works as expected though its Documentation says that it's not tested in replication environment. I used it on master with one slave and every operation replicated on slave including triggers creation and table copy.
2. You might also wants to delete old triggers and copy table just in case you killed it between for any reason.
I got help from following reference links :
https://www.percona.com/doc/percona-toolkit/2.0/dsndatasourcenamespecifications.html#bareword https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html http://www.gmarik.info/blog/2012/large-mysql-migrations/