How can I see how many MySQL connections are open?

Solution 1:

I think there are a couple of ways:

SHOW STATUS WHERE `variable_name` = 'Threads_connected'

or you can do a SHOW PROCESSLIST and find out unique values in the Id column. In old PHP API mysql, there is mysql_list_processes function that does the same as SHOW PROCESSLIST, too.

But first one should work for you. And perhaps you might like to check on other STATUS variables

Solution 2:

There are other useful variables regarding connections and in your particular case variable Connections might help find out if your code is making too many connections. Just check it value before and after running code.

# mysql -e 'SHOW STATUS WHERE variable_name LIKE "Threads_%" OR variable_name = "Connections"'

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Connections       | 22742 |
| Threads_cached    | 1     |
| Threads_connected | 87    |
| Threads_created   | 549   |
| Threads_running   | 51    |
+-------------------+-------+

• Connections

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

• Threads_cached

  The number of threads in the thread cache.

• Threads_connected

  The number of currently open connections.

• Threads_created

  The number of threads created to handle connections. If Threads_created is big, you may want to increase the thread_cache_size value. The cache miss rate can be calculated as Threads_created/Connections.

• Threads_running

  The number of threads that are not sleeping.

Solution 3:

Current connections status:

mysqladmin status

Look at Threads: count. More detailed information about current connections can be obtained with the commands:

user@host:~$ mysqladmin -uroot -ppass extended-status | grep Threads
| Threads_cached                           | 0           |
| Threads_connected                        | 3           |
| Threads_created                          | 3           |
| Threads_running                          | 1           |

user@host:~$ mysqladmin -uroot -ppass processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 53 | root | localhost |    | Sleep   | 258  |       |                  |
| 54 | root | localhost |    | Sleep   | 253  |       |                  |
| 58 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

FYI mysqladmin -v -uroot -ppass processlist is analog of show full processlist.

Commands can be shortened to any unique prefix, and called simultaneously:

user@host:~$ mysqladmin -v -uroot -ppass proc stat
+----+------+-----------+----+---------+------+-------+-----------------------+
| Id | User | Host      | db | Command | Time | State | Info                  |
+----+------+-----------+----+---------+------+-------+-----------------------+
| 53 | root | localhost |    | Sleep   | 951  |       |                       |
| 54 | root | localhost |    | Sleep   | 946  |       |                       |
| 65 | root | localhost |    | Query   | 0    |       | show full processlist |
+----+------+-----------+----+---------+------+-------+-----------------------+
Uptime: 1675  Threads: 3  Questions: 171  Slow queries: 0  Opens: 235  
Flush tables: 1  Open tables: 57  Queries per second avg: 0.102