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 & Optimization Tool

Releem is an AI-powered database performance optimization tool that lets you keep your database servers fast, secure and reliable.Tune MySQL Performance automatically, accurately and effectively. Releem makes managing and tuning database servers easy.

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.