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: https://pastebin.com/gyZNhEsP
SHOW VARIABLES: https://pastebin.com/YAXFcz2G
UPDATE, action plan based on Ricks analyze:
- No good reason for MyISAM => all tables will be converted to InnoDB.
- Settings update (server has 32 GB RAM and SSD drives):
table_open_cache = 2K
innodb_log_file_size = 200M
innodb_flush_method = O_DIRECT
query_cache_type = OFF
query_cache_size = 0
table_definition_cache = 1K
innodb_buffer_pool_instances = 8
innodb_lru_scan_depth = 256
innodb_io_capacity = 1000
innodb_flush_neighbors = 0
- Added logging of SHOW ENGINE INNODB STATUS in monitoring script if we detect a freeze.
- In the long run: Replace all REPLACE-queries.
Looks like the problem occurs more frequently when a lot of writing is happening. Maybe the freezes occurs when data is flushed to disk or something similar. Hopefully the changes of I/O-settings will solve it.
UPDATE 2: We still have the same problem after the settings update. Managed to log SHOW ENGINE INNODB STATUS when a freeze occured: https://pastebin.com/EPg9RXgS
The queries are stuck in "flushing log". Can it be a problem with the SSD? Or writing to binlog? Anything else?