Hi,

I had seen some discussions about this and tried setting

innodb_adaptive_hash_index = OFF

But it didn't resolve the problem.

/Conor

From: Gordan Bobic <gordan.bobic@gmail.com>
Sent: Tuesday 16 March 2021 14:06
To: Conor Murphy <conor_mark_murphy@hotmail.com>
Cc: maria-discuss@lists.launchpad.net <maria-discuss@lists.launchpad.net>
Subject: Re: [Maria-discuss] Problem with InnoDB: Semaphore wait after upgrading to 10.3.27
 
Just a hunch, but have you tried disabling AHI?
innodb_adaptive_hash_index = 0

I vaguely remember having a similar problem a while back and it was AHI related.

I hit a number of spurious locking issues in 10.4.17 that seem to have been resolved in 10.4.18. It is plausible that some of them also affected the 10.3 branch and were similarly resolved recently. Some of them manifested similarly until I mitigated them by setting the lock_wait_timeout to 30 on the basis that it was better for a query to give up and be retried than to be blocking things for longer. I haven't seen it come up since the 10.4.18 upgrade. 10.4.18 change log has quite a few fixes for various locking issues.


On Tue, Mar 16, 2021 at 1:58 PM Conor Murphy <conor_mark_murphy@hotmail.com> wrote:
Hi,

We have a handful of servers running MariaDB 10.3. We noticed issues on two of them where we were getting issues like "InnoDB: A long semaphore wait:--Thread 140602802394880 has waited at dict0stats.cc line 1969 for 241.00 seconds the semaphore:" and crashes like "ERROR] [FATAL] InnoDB: Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.210215  7:14:24 [ERROR] mysqld got signal 6 ;"

Turned out that these two servers were running 10.3.27 whereas the others were running 10.3.17. After downgrading the two problem servers to 10.3.17, all the issues with InnoDB semaphores stopped.

Any ideas on how to track down what change between 10.3.17 to 10.3.27 might be causing this problem?

Thanks,
Conor
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to     : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp