Is your MySQL software Cluster ready?

When we do Galera Cluster consulting we always discuss with the customer if his software is Galera Cluster ready. This basically means: Can the software cope with the Galera Cluster specifics?

If it is a software product developed outside of the company we recommend to ask the software vendor if the software supports Galera Cluster or not.

We typically see 3 different answers:

• We do not know. Then they are at least honest.
• Yes we do support Galera Cluster. Then they hopefully know what they are talking about but you cannot be sure and should test carefully.
• No we do not. Then they most probably know what they are talking about.

If the software is developed in-house it becomes a bit more tricky because the responsibility for this statement has to be taken by you or some of your colleagues.

Thus it is good to know what are the characteristics and the limitations of a Cluster like Galera Cluster for MySQL.

Most of the Galera restrictions an limitation you can find here.

DDL statements cause TOI operations

DDL and DCL statements (like CREATE, ALTER, TRUNCATE, OPTIMIZE, DROP, GRANT, REVOKE, etc.) are executed by default in Total Order Isolation (TOI) by the Online Schema Upgrade (OSU) method. To achieve this schema upgrade consistently Galera does a global Cluster lock.

It is obvious that those DDL operations should be short and not very frequent to not always block your Galera Cluster. So changing your table structure must be planned and done carefully to not impact your daily business operation.

But there are also some not so obvious DDL statements causing TOI operations (and Cluster locks).

• TRUNCATE TABLE ... This operation is NOT a DML statement (like DELETE) but a DDL statement and thus does a TOI operation with a Cluster lock.
• CREATE TABLE IF NOT EXISTS ... This operation is clearly a DDL statement but one might think that it does NOT a TOI operation if the table already exists. This is wrong. This statement causes always a TOI operation if the table is there or not does not matter. If you run this statement very frequent this potentially causes troubles to your Galera Cluster.
• CREATE TABLE younameit_tmp ... The intention is clear: The developer wants to create a temporary table. But this is NOT a temporary table but just a normal table called _tmp. So it causes as TOI operation as well. What you should do in this case is to create a real temporary table like this: CREATE TEMPORARY TABLE yournameit_tmp ... This DDL statement is only executed locally and will not cause a TOI operation.

How to check?

You can check the impact of this problem with the following sequence of statements:

mysql> SHOW GLOBAL STATUS LIKE 'Com_create_table%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Com_create_table | 4     |
+------------------+-------+

mysql> CREATE TABLE t1_tmp (id INT);

mysql> SHOW GLOBAL STATUS LIKE 'Com_create_table%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Com_create_table | 5     | --> Also changes on the Slave nodes!
+------------------+-------+

mysql> CREATE TEMPORARY TABLE t2_tmp (id INT);

mysql> SHOW GLOBAL STATUS LIKE 'Com_create_table%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Com_create_table | 6     | --> Does NOT change on the Slave nodes!
+------------------+-------+

mysql> CREATE TABLE IF NOT EXISTS t1_tmp (id INT);
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Com_create_table | 7     | --> Also changes on the Slave nodes!
+------------------+-------+

Find out in advance

If you want to find out before migrating to Galera Cluster if you are hit by this problem or not you can either run:

mysql> SHOW GLOBAL STATUS
WHERE variable_name LIKE 'Com_create%'
   OR variable_name LIKE 'Com_alter%'
   OR variable_name LIKE 'Com_drop%'
   OR variable_name LIKE 'Com_truncate%'
   OR variable_name LIKE 'Com_grant%'
   OR variable_name LIKE 'Com_revoke%'
   OR variable_name LIKE 'Com_optimize%'
   OR variable_name LIKE 'Com_rename%'
   OR variable_name LIKE 'Uptime'
;
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Com_create_db        | 2     |
| Com_create_table     | 6     |
| Com_optimize         | 1     |
| Uptime               | 6060  |
+----------------------+-------+

Or if you want to know exactly who was running the query from the PERFORMANCE_SCHEMA:

SELECT user, host, SUBSTR(event_name, 15) AS event_name, count_star
  FROM performance_schema.events_statements_summary_by_account_by_event_name
 WHERE count_star > 0 AND
     ( event_name LIKE 'statement/sql/create%'
    OR event_name LIKE 'statement/sql/alter%'
    OR event_name LIKE 'statement/sql/drop%'
    OR event_name LIKE 'statement/sql/rename%'
    OR event_name LIKE 'statement/sql/grant%'
    OR event_name LIKE 'statement/sql/revoke%'
    OR event_name LIKE 'statement/sql/optimize%'
    OR event_name LIKE 'statement/sql/truncate%'
    OR event_name LIKE 'statement/sql/repair%'
    OR event_name LIKE 'statement/sql/check%'
     )
;
+------+-----------+--------------+------------+
| user | host      | event_name   | count_star |
+------+-----------+--------------+------------+
| root | localhost | create_table |          4 |
| root | localhost | create_db    |          2 |
| root | localhost | optimize     |          1 |
+------+-----------+--------------+------------+

If you need help to make your application Galera Cluster ready we will be glad to assist you.