3 ms·
In my experience it did indeed remove a lot of deadlocks when we went from multi master writing to electing a single "writable" node. So in the end it is pretty
by clon 9y ago
In my experience it did indeed remove a lot of deadlocks when we went from multi master writing to electing a single "writable" node. So in the end it is pretty much the same as deploying some read only slaves, plus automatic master promotion.
But not quite. Since even with this setup you might get a lot more weird deadlocks compared to a normal master slave setup, even running SELECT. Before we ditched Galera we were never really able to solve a couple classes of deadlocks, one involving CREATE TEMPORARY TABLE AS SELECT. Also, not all Galera deadlocks are really deadlocks, but actually Galera certification errors, reported as deadlocks to the client, so this distinction must be drawn as well.
Remember, a slave just stupidly replays stuff from the binlog onto a known state in a single thread, in the same serialised order that the master has already been able to commit. Galera on the other hand will attempt to certify and commit individual transactions. A certification does not really guarantee that the trx will be able to be committed when it comes to that. Not even in the case of single writer node, in many circumstances.
I'd say, after working with Galera/PXC for several years at considerable scale, that the premise of multi master writing only works in very narrow domains and even then you need to write a lot of application code to recover from Galera idiosyncrasies.
- evanelias 9y agofwiw, CREATE [TEMPORARY] TABLE AS SELECT can be problematic in MySQL even without Galera. It is outright disallowed if MySQL 5.6+ Global Transaction ID (GTID) is enabled, for example. The issue is that it's combining DDL and DML in a single statement/transaction, but DDL is still not yet inherently transactional in MySQL. The work-around is to just to use two statements, CREATE TABLE (or possibly CREATE TABLE LIKE) and then a separate INSERT...SELECT. That all said, yes, Galera should ideally handle this gracefully instead of ever deadlocking. But I don't have enough Galera experience to comment on that aspect.
- clon 9y agoThe docs state that creating tables is not supported, but I am unsure about the '[TEMPORARY]' part. In practice, it is not verboten, see below for 5.6: mysql> SHOW VARIABLES LIKE '%gtid%'; +---------------------------------+-----------+ | Variable_name | Value | +---------------------------------+-----------+ | binlog_gtid_simple_recovery | OFF | | enforce_gtid_consistency | ON | | gtid_deployment_step | OFF | | gtid_executed | | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | | | simplified_binlog_gtid_recovery | OFF | +---------------------------------+-----------+ 9 rows in set (0.00 sec) mysql> CREATE TABLE test AS SELECT 1; ERROR 1786 (HY000): CREATE TABLE ... SELECT is forbidden when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1. mysql> CREATE TEMPORARY TABLE test AS SELECT 1; Query OK, 1 row affected (0.03 sec) Records: 1 Duplicates: 0 Warnings: 0 So perhaps it is still 'not supported', as in our experience it does indeed cause some issues, probably avoidable if you are willing to do dirty reads (READ COMMITTED). I wonder if REPEATABLE READ is a good default anyway.