CODEWITHSUNDEEP
phpmysqlapache
Sahal
Sahal

Reputation: 4136

How can I see how many MySQL connections are open?

How can I see how many connections have been opened during the current request via mysql_connect in PHP running on Apache?

I know that if I call mysql_connect function 100 times with the same parameters, it will always return the same connection link. It will not start new connection once the connection already exists.

But I just want to make sure mysql_connect is not starting a new one.

I am working with a legacy system which contains many mysql_connect function calls.

Is there any setting in Apache or is there any way I can log this number of connections in Apache or MySQL log file?

Upvotes: 48

Views: 106469

Answers (5)

Kamil Dziedzic
Kamil Dziedzic

Reputation: 5012

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 the 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.

Upvotes: 43

Alexander Yancharuk
Alexander Yancharuk

Reputation: 14501

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

Upvotes: 12

Abhay
Abhay

Reputation: 6645

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

Upvotes: 67

X10nD
X10nD

Reputation: 22030

I dont think you can see the number of connection, but can limit the connections to the mysql server.

Upvotes: -1

gmhk
gmhk

Reputation: 15940

You could use the MySQL command show processlist to get the number of connections.

Upvotes: 6

Related Questions