CODEWITHSUNDEEP
javamysqlservletsjdbc
Frank
Frank

Reputation: 31090

How to count open db connections?

I'm developing a web app using Java servlet to access Mysql db, how can I get the number of connections to my DB that is currently open ?

Edit :

I tried "show processlist", it showed me : 2695159, but that's not right, I'm just developing this new project, I'm the only user, couldn't have that many processes running, what I want is the number of users accessing my project's DB, not the number of all db users, but just the ones logged in to my database which has only one table.

Upvotes: 15

Views: 54926

Answers (8)

Idham Perdameian
Idham Perdameian

Reputation: 2277

You also can count open connection by show the status from Threads_connected variable name like this:

SHOW STATUS WHERE variable_name = 'Threads_connected';

Or you can also count the process list directly from information_schema.PROCESSLIST like below:

SELECT COUNT(*) FROM information_schema.PROCESSLIST;

Upvotes: 3

Gerryjun
Gerryjun

Reputation: 171

Depending on your MySQL version, you can perform a select on

SELECT COUNT(*) FROM information_schema.PROCESSLIST;

and you can do a where between the user, database, and host IP.

For example:

USE information_schema;
SELECT COUNT(*) FROM PROCESSLIST WHERE db ="mycase" AND HOST LIKE "192.168.11.174%"

Upvotes: 17

subhash lamba
subhash lamba

Reputation: 216

You may use this

SHOW GLOBAL STATUS; or show global status like "Threads_connected";

from Connections status you can findout total number of connections.

Upvotes: 0

DmitrySemenov
DmitrySemenov

Reputation: 10365

You can only select from Information_Schema.Processlist the data that belongs to you. It means you can use it for monitoring ONLY if you're logged in as root, otherwise you will be seeing the connections coming from your user you got logged in with.

If you want proper monitoring SQL, it will be:

SELECT variable_value
FROM INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE variable_name='threads_connected'

Upvotes: 2

Vallabha Vamaravelli
Vallabha Vamaravelli

Reputation: 1293

Run the following query, it lists out host name and no. of connections from each host:

SELECT host,count(host) FROM information_schema.processlist GROUP BY host;

Upvotes: 3

jishi
jishi

Reputation: 24634

show status like 'Threads_connected' or show global status like 'Threads_connected'

Not sure about the difference between those two in a user-context, and you might still suffer from the problem that you would see all connections, not only those from your app.

you can even check Threads_running to only see running threads (e.g not sleeping).

Upvotes: 3

Alnitak
Alnitak

Reputation: 340045

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

However that'll also show you any connections made with the same userID to the database which may not be coming from your servlet.

In general I would suggest that you're probably better off using a Connection Pool object (see http://java-source.net/open-source/connection-pools) to manage your connections to the MySQL server. This can increase performance by making DB connections persistent, so you don't always have the overhead of a new DB connection for each page load.

If your servlet needs to know the number of connections then your Connection Pool should come with a method that tells you how many connections are currently active.

Upvotes: 9

Dmitry Khalatov
Dmitry Khalatov

Reputation: 4379

show processlist

Upvotes: 2

Related Questions