Galera Cluster Enterprise Edition (EE) has had support for Non-Blocking Operations (NBO) schema upgradesever since the first release of Galera Cluster Enterprise Edition (EE). It is reasonably well documented, and we think more people will benefit from using it, of course. Schema changes tend to always remain a daunting task when you have large databases to manage.

To compare between Total Order Isolation (TOI), the default, with NBO that is available in Galera Cluster Enterprise Edition (EE), we create a test table:

CREATE TABLE large;
USE large;

CREATE TABLE test_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    data VARCHAR(255),
    num1 INT,
    num2 INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Then we create some random data:

DELIMITER //
CREATE PROCEDURE populate_test_table(IN total_rows INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < total_rows DO
        INSERT INTO test_table (data, num1, num2)
        SELECT 
            CONCAT('Data ', LPAD(FLOOR(1 + (RAND() * 1000000)), 7, '0')),
            FLOOR(1 + (RAND() * 1000000)),
            FLOOR(1 + (RAND() * 1000000))
        FROM 
            (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) a,
            (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) b
        LIMIT 25;
        
        SET i = i + 25;
        
        IF i % 1000 = 0 THEN
            DO SLEEP(1); -- Add a small delay every 1000 rows to prevent overwhelming the system
        END IF;
    END WHILE;
END //
DELIMITER ;

On a VM with 1GB RAM and 1 vCPU (basically the cheapest DigitalOcean box you can find), and a 3-node Galera Cluster, the general times it takes to populate test_table varies:

mysql> call populate_test_table(1000);
Query OK, 0 rows affected (1.02 sec)
mysql> call populate_test_table(10000);
Query OK, 0 rows affected (10.24 sec)
mysql> call populate_test_table(100000);
Query OK, 0 rows affected (1 min 41.87 sec)

Try to have 500,000 rows at minimum, and execute:

SET SESSION wsrep_OSU_method='TOI';
SET autocommit=0;
START TRANSACTION;
SET @start_time = NOW();
ALTER TABLE test_table 
    ADD COLUMN new_column1 VARCHAR(100), 
    ADD INDEX idx_complex (num1, num2);
SET @end_time = NOW();
SELECT TIMEDIFF(@end_time, @start_time) AS toi_duration;
COMMIT;

On my test data set, this took approximately 7 seconds.

mysql> SELECT TIMEDIFF(@end_time, @start_time) AS toi_duration;
+-----------------+
| toi_duration    |
+-----------------+
| 00:00:07.000000 |
+-----------------+
1 row in set (0.00 sec)

TOI applies changes to all nodes simultaneously, ensuring consistency, so during those 7 seconds, we definitely faced brief interruptions in write operations.

Then we try the NBO method which aims to minimse disruptions during schema changes.

SET SESSION wsrep_OSU_method=NBO;
SET autocommit=0;
START TRANSACTION;
SET @start_time = NOW();
ALTER TABLE test_table 
    LOCK = SHARED,
    ADD COLUMN new_column2 INT;
SET @end_time = NOW();
SELECT TIMEDIFF(@end_time, @start_time) AS nbo_duration;
COMMIT;
SET SESSION wsrep_OSU_method=TOI;

This was complete FAST! The LOCK = SHARED clause allows read operations on the table during the schema change, while write operations are blocked.

mysql> SELECT TIMEDIFF(@end_time, @start_time) AS nbo_duration;
+-----------------+
| nbo_duration    |
+-----------------+
| 00:00:00.000000 |
+-----------------+
1 row in set (0.00 sec)

Well, the ALTER statement completed very quickly, but there may be a background operation for the schema change. NBO allows for more efficient schema changes while maintaining cluster functionality. Don’t forget you can verify what the wsrep_OSU_method is:

mysql> SELECT @@wsrep_osu_method;
+--------------------+
| @@wsrep_osu_method |
+--------------------+
| NBO                |
+--------------------+
1 row in set (0.00 sec)

Both TOI and NBO have their place in database management. TOI offers simplicity and predictability, while NBO provides efficiency and reduced impact on cluster operations. Don’t forget the limitations of the statements you can execute with NBO, as well – it is not a panacea. Also, while ALTER is fast, adding indexes might of course be a little slower.

If you would like to evaluate NBO for schema changes in your Galera Cluster, and try out all the offerings of Galera Cluster Enterprise Edition (EE), please contact your friendly sales representative via email at sales@galeracluster.com or via the contact form.