My operating system version is Ubuntu 18.04, and the database is running on Percona Server for MySQL 5.7.34. The MySQL service crashed unexpectedly twice, on April 21 and May 6 respectively. Before the service outage, the database logs showed obvious InnoDB latch wait errors: Thread 139909617137408 had waited at row0sel.cc line 3611 for 851 seconds for the semaphore, specifically an S-lock on the RW-latch at 0x7f4400fc8570 (created in file buf0buf.cc line 1438). A writer thread (thread id 139948401846016) had reserved this RW-latch in wait exclusive mode, with the current status showing 1 reader, 1 waiter flag, and the lock_word being ffffffffffffffff.
This appears to be a known bug. Could you help check what causes it? Looking forward to your response.
MySQL will self kill if the main thread is stuck for a long enough period. Even though there are some bug reports about this, is an expected behaviour
On the provided logs there are multiple messages about IO saturation:
InnoDB: page_cleaner: 1000ms intended loop took 4320ms. The settings might not be optimal. (flushed=96, during the time.)
And then multiple latch waits. Like you mentioned
Reasons could be many. Memory swapping, low BP size or inadequate workload/untuned queries, DDL executions or server saturation etc…
I would suggest you take a look at the overall memory, disk, CPU usage (I suggest you install PMM from Percona toolkit) and check the server overall usage. If the hardware capacity is at max usage then you should work on tuning queries and the server . Else consider increasing server resources
Last but not least, 5.7 went EOL a few years back, you should strongly upgrade to at least the next major release (8.0 which also went EOL last month). Or if possible, consider upgrading to 8.4
Thanks for sparing your valuable time to reply! I have a question. Regarding the outage on April 21st, I only saw one active thread running a simple UPDATE statement with a good execution plan. I also noticed some DBAs mentioned it might be caused by the Adaptive Hash Index. Would it be more appropriate to disable it?
Hello @ocean buf0buf.cc is the file that implements the buffer pool, thus nothing to do with ADI. Also, this is an S-Lock. Shared locks only appear when FOREIGN KEYS are involved so I would go through your schema and reduce any FKs, and ensure there are no circular FKs. You can also check sys.innodb_lock_waits to see which query is causing the lock.
Ubuntu 18 is no longer supported
MySQL 5.7 is no longer supported
MySQL 8.0 EOL in April 2026, and is no longer supported
Please upgrade to a supported OS and MySQL version for the latest bug fixes, and performance improvements.