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;
# | id | User | Host | db | Command | Time | State | Info |
---|---|---|---|---|---|---|---|---|
1 | 114 | test | localhost | DB_A | Sleep | 1546 | ||
2 | 125 | test | localhost | Query | 0 | init | show processlist | |
3 | 126 | test | localhost | DB_B | Sleep | 16 |
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.
Connections | The 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_connected | The 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.