XF 2.3 Galera cluster xf_user deadlock on post alerts deletion

[shockblazy]

We've been stress testing a new xenforo installation on a galera cluster (preparing for a forum migration) and we've encountered a deadlock error, which we can't figure out:

​

We already tried different galera settings for the nodes, none of the variables helps this issue (some other deadlocks that we had were fixed tho, this is the only one that we can't)

I would guess that commenting out the alerts deletion on post deletion would probably fix it, but has someone managed to fix this without altering the code?

Note: the cluster has 1 write node, all others are load balanced read nodes.

Cheers.

 

[Xon]

Stock Xenforo is quite bad at maintaining a consistent locking order for alerts & user updates, there are still some outstanding bug reports around this (ref #1 #2, #3 ).

Try my Alerts Improvements add-on, it actually rewrites a lot of the core Alert expiring/handling code to explicitly avoid deadlocks and too-large transaction sets for alerts for a galera cluster.

X

Alert Improvements by Xon

Apr 30, 2018

A collection of improvements to the XenForo Alerts system.

• Xon
• alerts
• Add-ons [2.x]

This add-on is used heavily on https://forums.spacebattles.com/ and handles 500k-600k alerts per day

 

[Xon]

Speaking of alerts foot-guns with galera and a high enough alert generation rate; the stock 32bit unsigned int autoincrement id isn't enough.

Thread 'xf_thread_read auto_increment out of range'

Feb 16, 2022

If you set up a MySQL cluster for replication, the auto_increment values generated by MySQL will go up by more than 1 at a time (see auto_increment_increment) - the default (we've been told) is to increment them by 10. This means you use up IDs 10x quicker (so you'd run out of IDs at 420 million rows, not 4.2 billion).

If you have a particularly a) large, b) active, and c) long running forum, combined with the auto_increment values going up by 10 instead of 1, you will eventually (after many, many years) exhaust the IDs available for this table. Unlike the xf_thread_view...

• mattrogowski
• Replies: 20
• Forum: Bug reports

• 

Alert Improvements add-on (should) support 64bit xf_user_alert.alert_id with it's feature but 3rd party code which binds to alert_id will probably break.

A 64bit xf_user_alert.alert_id is very nearly required on spacebattles:

MariaDB [sbforums]>select round(100*max(alert_id)/pow(2,32),0) as "%" from xf_user_alert;
+------+
| %    |
+------+
|   74 |
+------+

 

[shockblazy]

Stock Xenforo is quite bad at maintaining a consistent locking order for alerts & user updates, there are still some outstanding bug reports around this (ref #1 #2, #3 ).

Try my Alerts Improvements add-on, it actually rewrites a lot of the core Alert expiring/handling code to explicitly avoid deadlocks and too-large transaction sets for alerts for a galera cluster.

X

Alert Improvements by Xon

Apr 30, 2018

A collection of improvements to the XenForo Alerts system.

• Xon
• alerts
• Add-ons [2.x]

This add-on is used heavily on https://forums.spacebattles.com/ and handles 500k-600k alerts per day

Will try it out!

Speaking of alerts foot-guns with galera and a high enough alert generation rate; the stock 32bit unsigned int autoincrement id isn't enough.

Thread 'xf_thread_read auto_increment out of range'

Feb 16, 2022

If you set up a MySQL cluster for replication, the auto_increment values generated by MySQL will go up by more than 1 at a time (see auto_increment_increment) - the default (we've been told) is to increment them by 10. This means you use up IDs 10x quicker (so you'd run out of IDs at 420 million rows, not 4.2 billion).

If you have a particularly a) large, b) active, and c) long running forum, combined with the auto_increment values going up by 10 instead of 1, you will eventually (after many, many years) exhaust the IDs available for this table. Unlike the xf_thread_view...

• mattrogowski
• Replies: 20
• Forum: Bug reports

• 

Alert Improvements add-on (should) support 64bit xf_user_alert.alert_id with it's feature but 3rd party code which binds to alert_id will probably break.

A 64bit xf_user_alert.alert_id is very nearly required on spacebattles:

MariaDB [sbforums]>select round(100*max(alert_id)/pow(2,32),0) as "%" from xf_user_alert;
+------+
| %    |
+------+
|   74 |
+------+

Yes, we actually noticed this too (also applies to some other very active tables as well) and pushed a patch for it.

I'll update the thread, if we fix the alerts issue.