Comments on: Solving the non-atomic table swap, Take II https://shlomi-noach.github.io/blog/mysql/solving-the-non-atomic-table-swap-take-ii Blog by Shlomi Noach Tue, 21 Jun 2016 08:22:00 +0000 hourly 1 https://wordpress.org/?v=5.3.3 By: shlomi https://shlomi-noach.github.io/blog/mysql/solving-the-non-atomic-table-swap-take-ii/comment-page-1#comment-363978 Tue, 21 Jun 2016 08:22:00 +0000 https://shlomi-noach.github.io/blog/?p=7567#comment-363978 @Justin,

– The `select * for update` may take long minutes to potentially a couple hours to merely grab the locks; meanwhile it will pollute the master’s buffer pool (though I agree an ongoing migration also pollutes it, just not all at the same time)

– I thought about relying on events, but that means I must have `event_scheduler` enabled. Seems fine, but I want our tool to go public and be used by everyone, so maybe some people turn off their scheduler, and if the tool happens to turn it on, bad things happen.

– And still some `pt-kill` might kill the event_scheduler-exeuted connection. What happens if my `rename table a to b` executes 1ms _after_ said connection was killed? Events have been applied to the original table, and we just shoved them aside…

]]> By: Justin Swanhart https://shlomi-noach.github.io/blog/mysql/solving-the-non-atomic-table-swap-take-ii/comment-page-1#comment-363886 Mon, 20 Jun 2016 20:55:49 +0000 https://shlomi-noach.github.io/blog/?p=7567#comment-363886 You can of course do lock table write instead of select count(*) for update, but I am not sure if innodb always honors lock table write… I seem to recall an option that prevents that, but I can’t remember it off the top of my head.

]]> By: Justin Swanhart https://shlomi-noach.github.io/blog/mysql/solving-the-non-atomic-table-swap-take-ii/comment-page-1#comment-363883 Mon, 20 Jun 2016 20:46:54 +0000 https://shlomi-noach.github.io/blog/?p=7567#comment-363883 How about using an EVENT that does a ‘SELECT * FROM TABLE FOR UPDATE’ then sits waiting for a GET_LOCK lock to show up and releases the SELECT lock when it does?

This way you can:
event: select * from X for update
event: loop (with short sleep()) until IS_USED_LOCK(‘release_lock_X’);
rollback;

asynch process:
finish apply
rename table a to b; –blocks on SELECT FOR UPDATE
in second connection GET_LOCK(‘release_lock_X’)
rename completes

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/solving-the-non-atomic-table-swap-take-ii/comment-page-1#comment-363864 Mon, 20 Jun 2016 18:44:52 +0000 https://shlomi-noach.github.io/blog/?p=7567#comment-363864 It’s worth noting that on replicas this is a non atomic two-step swap.
On replica, there will be no writes on the table in between the two RENAMEs, but there may potentially be other writes from other tables in between those RENAMEs.

]]>