Waiting for table level lock

[hqarrse]

I'm hoping someone can give me some advice to solve an issue that is proving extremely frustrating!

We've got two XF forums installed on a DigitalOcean droplet - one of them is largish (12M posts, 110k members) and the other is smaller (1M posts, 55k members)

Both have run well for quite a long time but we recently moved from a CentOS7 to a CentOS 9 droplet. Since then the largish one has been problematic with some or all of the following symptoms approximately 5-6 times a day although becoming more frequent

• 500 error, Cloudflare decides that the server is down and/or the site reports "Oops! We ran into some problems."
• Higher server load & memory spike
• Exceeding max_user_connections_error reported in server error log in admin area
• "show full processlist" reveals lots of processes on largish forum waiting for table level lock on xf_session_activity
• Restarting nginx/mariadb solves the problem temporarily

Throughout the smallish forum works very happily!

The two servers are the same spec and, as far as I can tell, we're using the same settings for mariadb, php-fpm & nginx. I can't find anything useful in the logs other than the things I've already mentioned

 

[rdn]

waiting for table level lock on xf_session_activity

Do you have data and session caching configured?
Like Redis.

 

[hqarrse]

Do you have data and session caching configured?
Like Redis.

No - $config['cache']['enabled'] = false;

 

[rdn]

Then maybe try with it enabled?

 

[hqarrse]

Thanks for the suggestion and interest.

I've added redis (and Xon's extension) which works beautifully on the smallish forum (see below) but instantly kills the largish one (white screen).

 

[MentaL]

MySQL Performance Tuning Tool | Releem

Tune MySQL Performance automatically, accurately and effectively.

releem.com

A sponsor but recommended, it can optimize your dB. I'm using it and see if it helps. Let it run 24 hours though to aggregate data.