Preventing deadlocks in Galera

[Wouter0100]

Last week I posted about having deadlocks in MySQL in Discord, but I'd love to discuss this a little bit more in-depth as I may be contributing a fix for this at some point (or I'll hire someone, as I do not have any experience with Ruby). And nothing is more sad then building something which doesn't get merged in the end. Let me explain the issue first.

Due to redundancy, we nearly always run MySQL in Galera (multi-master) setup. This has been working pretty well with Postal as well, but we've seen one (major) issue. When we ramp up the sending of e-mails things deadlock. This is because we have a loadbalancer in front of our SMTP and web servers of Postal, that - randomly - forwards SMTP connections to a set of SMTP servers. If these SMTP servers run specific queries at the exact same time, a deadlock occurs. Mainly due to statistics. We could set the loadbalancer to only use a single server, but this greatly reduces capacity.

A good example is here. At line 571 the following query is ran: UPDATE `statistics` SET `total_outgoing` = COALESCE(`total_outgoing`, 0) + 1 WHERE `statistics`.`id` = 1. This query locks this specific row (due how InnoDB works), and if this query is ran twice at the same time by different SMTP servers - it creates a deadlock and throws an error in Ruby. As a result, the add_to_message_queue is not called and I must call it manually. As a workaround I created a new Docker image where add_to_message_queue is called before the update of the stats, but this is of course a nasty workaround not feasible for real production use with Postal.

Another issue that I've seen is the update last_used_at timestamp for the credentials. Here the specific row is updated, but if 2 API calls arrive at the exact same time - this creates another deadlock. Luckily, none of our customers currently run using the API - so this is not that important for me.

Regardless, as you see, this is quite an issue. I've thought about potential proper fixes for it, and normally I would use some kind of temporary key value store for it - but this is not included in the current requirements of Postal and thus not ideal. In the end I'd love to suggest the following:

A new (optional?) queue for updating single rows. As far as I can see it should support incrementing a number and setting a value. This queue should be processed by a single, dedicated worker (something like cron and req-queuer, but not creating new queue items - but processing them). New queue items are then submitted in the code, instead of actually incrementing or setting the values.

Would this be a suitable solution or do you guys have any other suggestions?

[Skywrath-Mage]

I'm not so technical, but we "solve" the clustering issue with 2 installs of Postal and a Proxy in front.
Not the best and the optimal solution but the only one that we found, because we just have experience with pSQL and CockroachDB in cluster never used MySQL.

[Skywrath-Mage]

Maybe the support to CockroachDB could be a stable solution for SQL support + Scabalility.

[Wouter0100]

Personally I really don't prefer CockroachDB, we have it running as well - but it is more of a hassle to run stable.

Do you also have 2 different databases? So you configure everything twice in both installs? That is far from ideal, indeed.

[Skywrath-Mage]

We have almost any issue with CockroachDB because it almost has 100% compatibility with pSQL.
But anyway support for migrating to pSQL or CockroachDB, could be a big thing now but could help to scale in the future.

For the limitation, yes we install everything in September and just switch to rspamd + postal 2, but we have to do everything manually. w8ing for the new API, so that we can automate.

[willpower232]

This, somewhat old, blog post suggests that deadlocks are a limitation of Galera but I don't know enough about multi-write SQL clusters to know if there is an easy resolution.

https://severalnines.com/database-blog/avoiding-deadlocks-galera-setting-haproxy-single-node-writes-and-multi-node-reads

[Wouter0100]

Correct, a single write node or single master would fix it, but that wouldn't make sense in some cases due to the increased load on the single node.

We're deploying a multi-master cluster, meaning all nodes in the cluster should be able to accept writes.