Hacker News Reader

Comment by shlomi-noach | Hacker News Reader

I'm curious whether pretix attempted using MariaDB/Galera with single-write mode, such that the three servers still run synchronous replication, but only one of them gets the writes.

I suspect (disclaimer: not running Galera myself) "WSREP has not yet prepared node for application use" and deadlocks would be mitigated, and am curious to learn if that were indeed the case.

View on HN

No, we haven't. You are absolutely right that this would probably mitigate the deadlocks. However,

(1) the deadlocks/WSREP was a problem annoying us, while the query performance was a problem leading to real customer complaints, (2) as I see it right now, we would have needed to take complicated steps to ensure that all application nodes always connect to the same database node; just a hardcoded priority would probably not be enough, (3) a mechanism for (2) would likely make us loose more of the advantages of Galera, e.g. short failover downtimes

Since you already use HAProxy you should get that almost for free. Your app would talk to the databased by communicating through HAProxy, which would direct traffic to the single-writer; HAProxy's health tests would detect who the single-writer is, and would adapt in the event of failover.

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.

fwiw, 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.

The 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.

the "WSREP" error is something you never have to worry about with Galera assuming you are correctly deploying beneath a proxy server like HAProxy that handles this aspect for you. The proxy server is set up to only provide nodes that are available.

We also run some applications in "single-node" mode where HAProxy routes all connections to one node at a time - but if a node goes down it switches the app to another one. Hence the whole point of HA.

Reply on news.ycombinator.com