The Problem
MySQL is generating a lot of sleeping queries. At times so many that it was causing denial of service of sorts and really slowing (or shutting) down our site. We created a temporary solution by limiting the amount of time an idle process is open before it is killed (and has additional negative side affects for legitimate connections), but this is not the root of the problem, which I have been tasked to discover.
What Changed
This was not happening until recently, when we upgraded our server. We have (and have always had) a LAMP setup. Additionally, we updated the version of MySQL we're using, going from 5.6.19 to 5.6.26. We are also now using the latest stable version of PHP.
Research I've Done
My primary problem is that DBA is a weak part of my knowledge base. But the following articles have helped (1) improve my understanding of what may be going on and (2) show me that there are a lot of reasons this could be happening.
https://www.percona.com/blog/2007/02/08/debugging-sleeping-connections-with-mysql/ http://board.phpbuilder.com/showthread.php?10375101-How-to-Stop-MySQL-Sleep%28%29-Processes
wpapi.com/mysql-sleep-processes-issue-solved/
serverfault.com/questions/24191/mysql-sleeper-queries
major.io/2007/05/20/mysql-connections-in-sleep-state/
My Question
Essentially, because of my weak DBA skills, I'm having a hard time diagnosing what is our specific problem. Is it MySQL variables/settings, apache settings, a PDO problem? If so I could really use some how-to direction as I'm really just learning at the moment. It seems sleeping queries are being generated both in the form of duplicate queries (example: if I do show process list, my command to run the process list will show up as one of the current processes, as well as an additional sleeping query will be spawned), as well as just randomly we'll have tons of sleeping queries not related to duplicate processes.
As a side note, we don't use persistent connections. We did recently split out our database and all our clients have their own database now, but this all seemed much more aligned (time-frame wise) with the new server.
Any thoughts or insights in helping me diagnose and fix our issue would be very helpful. Thanks!