MySQL show status - show open database connections

MySQL "show status" FAQ: Can you demonstrate how to use the MySQL show status command to show MySQL variables and status information, such as the number of open MySQL connections?

I don't have a whole lot of time today to give this a detailed discussion, but here is a quick look at some MySQL work that I did recently to show MySQL open database connections.

MySQL show status - Open database connections

You can show MySQL open database connections (and other MySQL database parameters) using the MySQL show status command, like this:

mysql> show status like 'Conn%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 8     | 
+---------------+-------+
1 row in set (0.00 sec)


mysql> show status like '%onn%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         | 0     | 
| Connections              | 8     | 
| Max_used_connections     | 4     | 
| Ssl_client_connects      | 0     | 
| Ssl_connect_renegotiates | 0     | 
| Ssl_finished_connects    | 0     | 
| Threads_connected        | 4     | 
+--------------------------+-------+
7 rows in set (0.00 sec)

All those rows and values that are printed out correspond to MySQL variables that you can look at. Notice that I use like 'Conn%'in the first example to show variables that look like "Connection", then got a little wiser in my second MySQL show status query.

MySQL show processlist

Here's what my MySQL processlist looks like when I had my Java application actively running under Tomcat:

mysql> show processlist;
+----+------+-----------------+--------+---------+------+-------+------------------+
| Id | User | Host            | db     | Command | Time | State | Info             |
+----+------+-----------------+--------+---------+------+-------+------------------+
|  3 | root | localhost       | webapp | Query   |    0 | NULL  | show processlist | 
|  5 | root | localhost:61704 | webapp | Sleep   |  208 |       | NULL             | 
|  6 | root | localhost:61705 | webapp | Sleep   |  208 |       | NULL             | 
|  7 | root | localhost:61706 | webapp | Sleep   |  208 |       | NULL             | 
+----+------+-----------------+--------+---------+------+-------+------------------+
4 rows in set (0.00 sec)

And here's what it looked like after I shut Tomcat down:

mysql> show processlist;
+----+------+-----------+--------+---------+------+-------+------------------+
| Id | User | Host      | db     | Command | Time | State | Info             |
+----+------+-----------+--------+---------+------+-------+------------------+
|  3 | root | localhost | webapp | Query   |    0 | NULL  | show processlist | 
+----+------+-----------+--------+---------+------+-------+------------------+
1 row in set (0.00 sec)

As a final note, you can also look at some MySQL variables using the mysqladmin command at the Unix/Linux command line, like this:

$ mysqladmin status

Uptime: 4661  Threads: 1  Questions: 200  Slow queries: 0  Opens: 16  Flush
tables: 1  Open tables: 6  Queries per second avg: 0.043

MySQL show status - Summary

Finally, here are two quick links to MySQL status pages:

I hope this brief look at the MySQL SHOW STATUS command has been helpful. As you can see, you can show information about a lot of MySQL status information, including the number of MySQL database connections, MySQL processes, and much more.

Thanks

Thanks for taking the time to post this. It was helpful already :)

Just a note: "Connections" variable only shows the connection attempts. If you want to see the open connections you need to look at "Threads_connected".

BR

Post new comment

The content of this field is kept private and will not be shown publicly.