pt-online-schema-change getting hanged

09-17-2012, 05:19 AM

Hi All,

I'm using mysql 5.5.24 and percona-toolkit-2.1.2 version on linux platform.

Please correct me, if i'm wrong - As far as i know, we can make online schema change while the app is running using pt-online-schema-change tool.
However, i just execute one test case manually from command prompt and it got hanged.

Comment

I have installed mysql 5.5.24 and percona-toolkit-2.1.2 versions locally and tried to test. It works perfectly for me. I'm confused that why you are getting above error. As per status "Waiting for table metadata lock" , some process/transaction definitely running on that table from other session and that's why its getting metadata lock. metadata locks are kept for the duration of a transaction. Can you check, if someother process is not running on the same table while you are executing pt-osc?

Comment

So the problem is that you start transaction in the 1st session (begin before select) right?
Until the transaction is not committed MySQL won't let you do any DDL change on this table, and it's not only pt-osc problem, try any alter instead. What if your transaction operates on a column you want to change, but you already inserted or updated values to this column before the commit? If you would be able to alter table in another session before committing the first one, than you would end up with inconsistent one.
Note that DDL operations do immediately implicit commit, and you cannot rollback them:http://dev.mysql.com/doc/refman/5.5/...it-commit.html

In order to let pt-osc finish it's job you need to commit the transaction, and usually in OLTP operations the transactions are very short, and immediately committed, so not a problem.
pt-online-schema-change purpose is to let you do changes to a table without blocking it for updates for a long time, which is the case for normal alter. But final rename table, where pt-osc has to switch both new and old table needs the table's metadata not locked by any transaction, so it just waits for that lock release, not hangs.

Comment

Yes, you got it right that i'm holding the transaction from one session and performing DDL on 2nd session.
I got the concept and usage of pt-online-schema-change, but still 1 doubt:

How can we perform DDL over DB with ZERO downtime - Don't want to stop my app/JBOSS?

What we are doing right now is on any env including production:
1> STOP JBOSS services - so as to release app user.
2> kill all sessions at mysql other than root.
3> apply DB patch using root user - DDL operation.

Now, we are looking for this tool so as to make ZERO downtime while applying DB patches.

Please guide.

Thanks.

Comment

If the DDL change you perform does not affect the application job, like if you don't remove a column the app uses, pt-osc should not introduce any downtime. If your server is very busy though you may want to tune --max-load setting to minimize additional load impact.

The problem with normal ALTER is that it usually takes a long time when the table is big and during that time the table is blocked for writes.
pt-online-schema-change purpose is to eliminate that time by allowing the table to be editable during the alter process.

If you have long transactions opened before you started pt-online-schema-change, the tool will be able to finish it's job after those transactions will be commited, but it won't block them. Also no new transactions using the table in the mean time will be broken, as final rename done by pt-osc is just yet another transaction, very short one.