I have a standard LAMP setup with PHP FPM connecting to the Mariadb server on the local host via a linux file socket.
I've been getting an issue that has been getting successively worse for the last few weeks, for some reason the mariadb server just stops responding to the PHP processes. I can log onto the mariadb console and run "SHOW PROCESSLIST" and I can see that all the PHP process are connected to the database, but are all in the sleeping state.
All the PHP process are in a spin lock waiting for the results from the database, but the database is not responding. The PHP processes run at 100% CPU and while the mariadb is about 5% CPU. If I stop the database, the PHP stop spinning. If I use php5 or php7 processes, the same problem exists.
This state continues until I log into the console and type a command like "SHOW PROCESSLIST;" and "set global general_log_file = OFF;" and for some reason this then sparks the database into action. Restarting the database does not fix the problem, and after a few transactions, it again locks up.
The database seems to lock and sometimes unlock itself, but blocks all PHP processes in the action.
There is nothing in any of the log files, and I've tried changing as many of the setting that I thought would help. Nothing has fixed the issue, or I don't know what resource limit to increase.
The problem is getting worse as the size of the database has gotten larger and the amount of queries has become more.