Since a few weeks ago I am having problems with one of my databases, all the queries made are blocked and they take a long time to execute, regardless of which table they are made in (Even queries like SHOW PROCESSLIST are slow)
This always happens once an hour and in the same minute, although sometimes it also happens at some other time, the block usually lasts between a few seconds and several minutes.
I have also found that the process of mysql increases the use of one of the CPU cores to 100% (of 4 cores).
At the moment I tried the following to find the cause of the problem, but without any result.
I tried to deactivate all my cron jobs for at least a day, to see if this was the problem, but there was no difference.
I've tried SHOW ENGINE INNODB STATUS, but it also shows no Deadlock.
With PROCESSLIST SHOW, I only see the blocked queries, and I can not find any that might be causing the block.
My version of MariaDB es: 10.2.14 , the tables are InnoDB .
Update 13-05-2018
Today I tried to disable the mysql cache with query_cache_type=0 , but the problem persists.
As I have commented in the comments, I have tried to use innotop , I do not see that there is any deadlock happening, but it does show an increase in open tables and queries, I leave some captures.
RowOps, Open Tables and InnoDb Txns:
RowOps, Open Tables and InnoDb Txns 2:
Query List and InnoDb Txns:
Here you can see the rise of load avg in ttyload
I'll be doing more tests.
Update 17-05-2018
In the end I came to the conclusion that it was a server problem (VPS 4CPU 8G), I tried to analyze all the queries that were made during the block with a script and it was always a different query the first to crash, the scripts PHP that did not use Mysql were also hanging.
After a server change the problem is no longer there.
Now I could see that the old server is still hanging by itself, without any visit, I leave a capture of munin:
