I have a problem where queries freezes sporadically during the day. I made a script monitoring the processlist and have detected a pattern where a slow query is running (different slow queries) and then (fast) queries are stuck in different states, ie. opening tables, query end, update, etc, like this:
time (seconds) state query 7 Opening tables SELECT id FROM... 8 Opening tables SELECT type FROM... 8 query end UPDATE cache... 8 Opening tables SELECT language FROM... 9 query end INSERT INTO cache... 9 query end INSERT INTO cache... 29 Sending data SELECT product_id FROM...
Usually it’s write operations getting stuck, but also simple selects in state “Opening tables”. Then something happens and the queries disappears from the list. Not sure what this “something” is though. It feels like something is locking the queries but I’m not able to pinpoint what it is.
Any ideas what can be causing this problem or how I can debug it further?
MariaDB version: 10.1.41-MariaDB-0+deb9u1
SHOW GLOBAL STATUS: