Why does Purge cause Semaphore wait due to OPTIMIZE TABLE on Offloaded Audit DB

[Sebastian van Voorn]

Hello,

We have an offloaded DB instance fo our Audit DB. It is a single machine with 4 CPU and 8GB mem.
At night (1 AM) we start the purge job to remove older records than 14 days. It removes about 100k to 200k of Audit records each night.

The delete actions takes a few hours. After the delete the job starts the OPTIMIZE TABLE command. And during this Actionwe see:
mysqld[30368]: A long semaphore wait:
Depending the day tens or hundreds occur. And sometimes it causes a crash:
mysqld[30368]: [FATAL] Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.
Why is the long semaphore wait happening? I suspect this events to "hang" the connections and caused our production cluster to hang allso..

What can I do to prevent this semaphore wait?

Thanks in advance.

Sebastian van Voorn

------------------------------
Greetings,
Sebastian van Voorn.
[JobTitle]
[CompanyName]
------------------------------

[Ben Urbanski]

Hello Sebastian,

You may want to open a support case for this issue. They may have experience with and possibly a KB article for the problem.

Regards,

------------------------------
Ben Urbanski
Product Manager, API Gateway
Layer7 API Management
------------------------------

[Sebastian van Voorn]

This is case nr 33704650

------------------------------
Greetings,
Sebastian van Voorn.
[JobTitle]
[CompanyName]
------------------------------