In a MariaDB Galera cluster composed of 3 nodes when running schema changes, for example, an ALTER and being in TOI(Total Order Isolation) mode it will write lock the full cluster until the schema upgrade finishes.

One way to prevent the global write lock is to run the upgrade in each node by doing:

SET wsrep_OSU_method='RSU';
# run the alter 
SET wsrep_OSU_method='TOI';

To find how long it has been running the query I use this:

SELECT * FROM information_schema.processlist WHERE command != 'Sleep' AND info LIKE 'alter%' AND time >= 2 ORDER BY time DESC, id LIMIT 1;

By using the file system and checking the size of the databases I can roughly get an idea in size but not time.

But in any case, how to get the progress, estimate or calculate the remaining time that the lock will last?

  • I suspect that it is not possible. The reason... Certain operations get slower and slower as they progress. This is because of sorts (reindexing) and/or filling up cache (ram). – Rick James Jun 6 '20 at 22:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.