Comments on: Online DDL Tools and Metadata Locks https://www.percona.com/blog/online-ddl-tools-and-metadata-locks/ Tue, 13 Feb 2024 17:29:24 +0000 hourly 1 https://wordpress.org/?v=6.5.2 By: Federico Razzoli https://www.percona.com/blog/online-ddl-tools-and-metadata-locks/#comment-10973841 Mon, 28 Nov 2022 00:08:50 +0000 https://www.percona.com/blog/?p=83736#comment-10973841 Note that with MariaDB you can add NOWAIT to the ALTER TABLE. If the algorithm is INSTANT, this avoids any long wait.

]]> By: Morgan Tocker https://www.percona.com/blog/online-ddl-tools-and-metadata-locks/#comment-10973837 Wed, 23 Nov 2022 19:53:27 +0000 https://www.percona.com/blog/?p=83736#comment-10973837 I recently contributed a feature to gh-ost which attempts to us ALGORITHM=INSTANT before proceeding with the gh-ost OSC algorithm: https://github.com/github/gh-ost/blob/master/doc/command-line-flags.md#attempt-instant-ddl

It’s currently disabled by default, but that might change in future. I tried to make it clear in the docs that this requires a MDL upfront, which I agree with you is something users might not understand or be expecting 🙂

]]> By: Bill Karwin https://www.percona.com/blog/online-ddl-tools-and-metadata-locks/#comment-10973836 Wed, 23 Nov 2022 18:56:55 +0000 https://www.percona.com/blog/?p=83736#comment-10973836 At my last job, we invoke pt-osc with a short timeout (1 second) for the metadata lock. If it can’t get the lock, then retry. This helps because other queries won’t be blocked by pt-osc for longer than the timeout duration, if pt-osc is blocked by a long-running query.

We still had cases where pt-osc fails after 200 retries, because the database was too busy. In those cases, we must run the DDL at some other time with less load.

If the load is truly 24/7 and there is no time in the week with less traffic, then sorry, there is no way to run the DDL without downtime.

]]>