I have 2 frontend nginx servers going to 4 backend php-fpm servers which all run Wordpress. The php-fpm servers all connect to one MySQL server and I was checking some stats earlier each php-fpm server has 15 php-fpm processes which is fine, however the MySQL server has between 60 - 150 processes which are shown when I run
show processlist; when the number reaches 160 processes on the MySQL server the php-fpm servers seem to crash and I get a nginx 504 error.
Is this due to the php process hanging while it is waiting for the MySQL query to finish, but because MySQL has too many processes it takes it's time and locks up. I am only guessing here but could this be the issue ?
Also could alot of open connections to MySQL from PHP-FPM be an issue and why I am getting a nginx 504 error, I am guessing that if I have 150 processes going to MySQL alot of them might still have open connections. I have checked and all the processes from running
show processlist; don't have a time value over 1.