Deadlock buildup causes cluster failure

[mrtsukim0t0]

Describe the bug

Under production load (~150 emails/minute), the Postal cluster experiences increasing deadlocks in the queued_messages table, eventually leading to complete service degradation where servers stop both sending and accepting new messages into the queue.

Expected behaviour

The cluster should:

• Sustain consistent throughput of 150 emails/minute indefinitely
• Maintain availability for both sending and queueing operations
• Handle concurrent database access
• Automatically recover from occasional deadlocks without service degradation

Screenshots

Postal error:

Deadlock:

Environment details

• OS: Debian 12
• Nodes: 5
• Workers: 200
• Database: Mariadb

Additional information/context

Mariadb config:

[willpower232]

Definitely sounds like a lot of traffic for a single database server, might be worth investigating using a database cluster if you don't want to increase the innodb_lock_wait_timeout even higher

[mrtsukim0t0]

Initially configured a 3 node DB cluster was configured but this quickly lead to deadlocks. All postal nodes were configured to use a single instance to resolve this issue. This has extended the interval until deadlock occurs. DB server load is normal, it is able to handle connections and i was able to acheive 300 mails/second, which was amazing, until the cluster slow down to ~15 mails/sec or less, sometimes stop delivery.

[mrtsukim0t0]

Deadlocks were frequent in queued_messages and webhook_requests table and indexing have resolved the issue. We made the following changes in MariaDB

Added indexes on ip_address_id, locked_by, locked_at & retry_after columns in queued_messages table

Added indexes on locked_at and retry_after columns in webhook_requests table

We have not observed deadlocks after indexing mentioned columns.

[willpower232]

Interesting, thank you for your research. Are you able to PR the extra migrations perhaps? Or at the very least document the missing things as an issue?