9

I need a query that will give me the active or open number of connections to a certain database for a certain user. I have been searching for hours and have come up with nothing so far. I am close, so here is what I have tried.

  1. SHOW STATUS WHERE `variable_name` = 'Threads_connected';

  2. SHOW STATUS LIKE '%onn%';

  3. SELECT * FROM information_schema.processlist WHERE USER='database_user';

I work for a web hosting company, and one of our clients keeps hitting his max_user_connections limit, so in troubleshooting why I need to know how many connections his user is using right now since he is no a shared server. I am currently using MySQL (InnoDB) version 5.5.36. Any help would be greatly appreciated!

10

What you need is a breakdown by user and hostname along with a total

SELECT IFNULL(usr,'All Users') user,IFNULL(hst,'All Hosts') host,COUNT(1) Connections
FROM
(
    SELECT user usr,LEFT(host,LOCATE(':',host) - 1) hst
    FROM information_schema.processlist
    WHERE user NOT IN ('system user','root')
) A GROUP BY usr,hst WITH ROLLUP;

This will handle host address that have a colon separating hostname and port number

I hope everybody is not logging in as root

-1

There was a discussion in detail for this: https://dba.stackexchange.com/a/47160/385

In short, the queries you need are

SELECT max_user_connections FROM mysql.user WHERE user='db_user' AND host='localhost';

and

SHOW VARIABLES LIKE 'max_user_connections';

UPDATE

Maybe this is not direct answer to your question, but according to http://dev.mysql.com/doc/refman/5.5/en/user-resources.html MySQL allows you to limit the number of access per hour. So, maybe that answers why a certain user hits limit while the number of connections of them are small according to process list. Unfortunately, I cannot find reference on how to get the access counter.

  • I do not want to change the number of max_user_connections or view the max_user_connections, I know how to do that. I want to view how many of those connections a specific user has active. – Will Hughes Sep 17 '15 at 3:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.