MySQL/MariaDB showing long-running "Sleep" queries

Symptoms

The server is slow, and many MySQL/MariaDB processes show Sleep as the command being run.

+------------+---------------------+----------------------+---------------------+---------------+--------+-------+---------+
|   Id          |   User                |  Host                  |  db                     | Command | Time | State | Info     |
+------------+---------------------+----------------------+----------------------+--------------+--------+-------+---------+
| 1037061 |  cpuser_dbuser | localhost:33274 | cpuser_dbname | Sleep       | 4747  |          | NULL |

Description

When a database connection is created, a session is simultaneously created on the server. If that connection and session are not closed properly, the query goes into sleep mode after the wait time is over. MySQL/MariaDB has two settings that will terminate inactive processes: wait_timeout and interactive_timeout. However, their default values are 28800 seconds (8 hours). It is recommended that the timeout values be low enough to kill abandoned connections quickly but long enough to prevent active connections waiting on another process from being disconnected.

Workaround

1. Access the server's command line as the 'root' user via SSH or "Terminal" in WHM.
2. Open /etc/my.cnf in your preferred text editor.
3. Add the following lines to the [mysqld] section.

   wait_timeout=$timeout
   interactive_timeout=$timeout

   Please note that "$timeout" must be replaced with the desired timeout length in seconds.
4. Save the changes and exit the text editor.
5. Restart the database server.

   /usr/local/cpanel/scripts/restartsrv_mysql

Additional resources

Server System Variables: wait_timeout

Server System Variables: interactive_timeout