We present a way to use an atomic, blocking table swap in the Facebook Online-Schema-Change solution, as well as in a rumored, other Online-Schema-rumored-Change solution. Update: also a caveat.
Quick recap (really quick)
pt-online-schema-change and facebook-osc are two popular online-schema-change solutions for MySQL. They both use triggers, but in different ways. While the Percona tool uses synchronous table updates, such that any INSERT|UPDATE|DELETE on the modified table causes an INSERT|UPDATE|DELETE on a ghost table, in the Facebook tool all cause an INSERT on a changelog table, which is then iterated, read, having entries applied on the ghost table.
The TL;DR is that DMLs on the table propagate synchronously, within same transaction in the Percona tool, and asynchronously, with lag, in the Facebook tool.
What’s the problem with the table swap?
In the Percona tool, once the logic is satisfied the copy is complete, we issue this query:
RENAME TABLE tbl TO tbl_old, tbl_new TO tbl;
This is an atomic, two table RENAME operation.
However with the asynchronous nature of the Facebook tool, such a RENAME would be a mistake. We must first block writes to the modified table, then make sure we have iterated the changelog table to the point of lock, apply those changes onto the ghost table, and only then do the swap.
The problem is: you cannot RENAME TABLES while one of them is LOCKed.
This is silly, and inconsistent, because:
> LOCK TABLES tbl WRITE; Query OK, 0 rows affected (0.00 sec) > RENAME TABLE tbl TO tbl_old, tbl_new TO tbl; ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction > ALTER TABLE tbl RENAME TO tbl_old; Query OK, 0 rows affected (0.00 sec)
Why would the RENAME fail where the ALTER works?
Small thing, but critical to the operation of the online-schema-change. From the Facebook OSC documentation:
Since alter table causes an implicit commit in innodb, innodb locks get released after the first alter table. So any transaction that sneaks in after the first alter table and before the second alter table gets a ‘table not found’ error. The second alter table is expected to be very fast though because copytable is not visible to other transactions and so there is no need to wait.
What the FB solution means
It means for a very brief duration, the table is not there. Your app will get errors.
Of course, we should be able to handle errors anytime, aywhere. But the honest truth is: we (as in the world) do not. Many apps will fail ungracefully should they get a table not found error.
An atomic swap, as compared, would make for briefly blocking operations, making the app ignorant of the swap.
Rumor
Rumor has it that we at GitHub are developing a new, triggerless, Online Schema Change tool. It is rumored to be based off binary logs and is rumored to have lots of interesting rumored implications.
Such rumored implementation would have to be asynchronous by nature, or so rumors say. And as such, it would fall for the same non-atomic table swap problem.
Solution
Once we heard it was rumored we were working on a triggerless online schema change tool, we realized we would have to solve the non-atomic swap problem. What we did was to gossip about it in between ourselves, which led to three different rumors of a solution, eventually manifested as three different working solutions. All three solutions make for blocking queries on the app’s side. I will present one of these solution here, based on voluntary locks. Continue reading » “Solving the Facebook-OSC non-atomic table swap problem”