CODEWITHSUNDEEP
mysqldatabasevb.net
Baseult
Baseult

Reputation: 41

Show active MySql connections and active user of your program

I've coded a tool that connects to my MySql database. This tool is used by several users and I would like to see 2 options.

  1. I want to see how many users have connected to my MySql database with 'connection.open' in the last 10 minutes.

  2. How many are currently actively connected with 'connection.open'.

Is it possible to display the number of active connections as text in my program as "active connections" and "connections in the last 10 minutes"?

I know that keeping connections open is bad since it needs a lot of server traffic which slows down the server ig.. so is there possibly a simpler way to show me how many users are currently actively using my program?

Thanks for any help, I don't need spoonfed, just a hint in which direction to search. Maybe there is already a helpful contribution which I have not found yet.

Im using VB.NET btw so if someone has actual code for that I would be very thankful.

Upvotes: 2

Views: 8429

Answers (2)

lewis4u
lewis4u

Reputation: 15047

I think there is no way to check who had an active connections X minutes ago. Maybe I'm wrong about that, please do correct me anyone who knows better.

BUT There are these 2 commands:

check how many active connections are there;

SHOW PROCESSLIST;

that will give something like this:

mysql> SHOW PROCESSLIST;
+-----+-----------------+-----------+------+---------+-------+------------------------+-----------------------+
| Id  | User            | Host      | db   | Command | Time  | State                  | Info                  |
+-----+-----------------+-----------+------+---------+-------+------------------------+-----------------------+
|   5 | event_scheduler | localhost | NULL | Daemon  | 23977 | Waiting on empty queue | NULL                  |
| 139 | root            | localhost | NULL | Query   |     0 | starting               | SHOW FULL PROCESSLIST |
+-----+-----------------+-----------+------+---------+-------+------------------------+-----------------------+
2 rows in set (0.00 sec)

Have in mind that event_scheduler process will always be there, it's part of mysql.

and this command will give you the integer number of how many connections are there currently using the DB

SHOW STATUS WHERE`variable_name` = 'Threads_connected';

mysql> SHOW STATUS WHERE`variable_name` = 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 1     |
+-------------------+-------+
1 row in set (0.00 sec)

And with a little bit tuning you can get count currently active connections:

SELECT COUNT(*)
FROM `PROCESSLIST`
WHERE `INFO` IS NOT NULL;

and mysql user names which are using the DB at that moment:

SELECT `USER`
FROM `PROCESSLIST`
WHERE `INFO` IS NOT NULL;

Upvotes: 2

nbk
nbk

Reputation: 49375

For your second informatio Shows you number of connected

show status where `variable_name` = 'Threads_connected';

this will show you what those process are doing

show processlist;

As you can not get information about the last 10 minutes

you can show

SHOW GLOBAL STATUS LIKE '%Connections%'

Connection_errors_max_connections   0
Connections                         9
Max_used_connections                2
Max_used_connections_time           2020-08-05 18:16:05
Mysqlx_connections_accepted         0
Mysqlx_connections_closed           0
Mysqlx_connections_rejected         0

But this will only show you information for the hole server

Upvotes: 4

Related Questions