Category Archives: database

MySQL Monitoring

MySQL is one of the most popular database engines.

Here are some handy sql statements to use to help with your development.

View Processes Connected

To pin point who is connected, list all processes.

SHOW PROCESSLIST;
#idUserHostdbCommandTimeStateInfo
1114testlocalhostDB_ASleep1546
2125testlocalhostQuery0initshow processlist
3126testlocalhostDB_BSleep16
Example list of connected client processes

To filter on fields use the information_schema.

select * from information_schema.processlist where db = 'DB_A'

Monitor Connections During Test

While testing your client software, monitor the connections and see if you are leaking them.

SHOW GLOBAL STATUS WHERE Variable_name RLIKE '^(.connect.)$'

The following variables will give you a quick overview of what is going on.

ConnectionsThe number of connection attempts (successful or not) to the MySQL server.

Max_used_connections
The maximum number of connections that have been in use simultaneously since the server started.
Threads_connectedThe number of currently open connections.

Connections will keep growing as it is all connection that have been made, and will not decrease.

Threads_connected you need to ensure goes back to a low number like one or two (for the connection you are monitoring on). If this stays high once your application ends, you are not closing connections.

Max_used_connections is interesting to see at any one time how many connections are active. This way you can increase the max_connections variable.

Acknowledgements

The following knowledge and help sites provided much of this content.

https://dev.mysql.com/doc/refman/8.0/en/show-status.html