Total Thread Connections
show status where variable_name = 'threads_connected';
Max Connections
The connected threads are limited by max_connections:
show variables like 'max_connections'; SET GLOBAL max_connections = 350;
Connection Timeout
The connected threads timeout and are closed if they are inactive for too long.
show variables like 'wait_timeout'; -- applies to new connection sessions only - 20 minutes (default 28800 is 8 hours) SET GLOBAL wait_timeout = 1200; -- seconds
Running Threads
show status where variable_name = 'threads_running';
Top Hosts
If you want to know which hosts and users are taking up the most open connections quickly and easily:
-- display the number of connections by host for all users (ignores client port)
select SUBSTRING_INDEX(`host`, ':', 1) jHost,
count(distinct `user`) 'user cnt' ,
count(SUBSTRING_INDEX(`host`, ':', 1)) 'host cnt',
SUM(`time`)
from information_schema.processlist
group by `jHost`
order by 3 DESC, 3 desc;
Top Hosts By User
-- display the number of connections by host and user (ignores client port)
select SUBSTRING_INDEX(`host`, ':', 1) jHost,
`user`,
count(distinct `user`),
count(SUBSTRING_INDEX(`host`, ':', 1)),
SUM(`time`)
from information_schema.processlist
group by `jHost`, `user`
order by 4 DESC, 5 desc;