[MDEV-23283] User Statistics does not correctly reflect concurrent_connections Created: 2020-07-24  Updated: 2023-12-16

Status: Confirmed
Project: MariaDB Server
Component/s: Plugin - userstat
Affects Version/s: 10.1, 10.4.13, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Minor
Reporter: Oli Sennhauser Assignee: Dave Gosselin
Resolution: Unresolved Votes: 1
Labels: upstream
Environment:

Linux, Ubuntu 18.04, n.a.



 Description   

User Statistics does not correctly reflect concurrent_connections:

SQL> SET GLOBAL userstat=1;
SQL> SELECT @@userstat, @@performance_schema;
+------------+----------------------+
| @@userstat | @@performance_schema |
+------------+----------------------+
|          1 |                    1 |
+------------+----------------------+
 
SQL> SHOW PROCESSLIST;
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
| Id | User        | Host      | db   | Command | Time | State                    | Info             | Progress |
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
| 10 | root        | localhost | NULL | Query   |    0 | Init                     | SHOW PROCESSLIST |    0.000 |
| 16 | app         | localhost | test | Sleep   |   38 |                          | NULL             |    0.000 |
| 17 | app         | localhost | test | Sleep   |   36 |                          | NULL             |    0.000 |
| 18 | root        | localhost | NULL | Sleep   |   34 |                          | NULL             |    0.000 |
| 19 | root        | localhost | NULL | Sleep   |   31 |                          | NULL             |    0.000 |
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
 
SQL> SELECT user, total_connections, concurrent_connections FROM information_schema.USER_STATISTICS;
+-------------+-------------------+------------------------+
| user        | total_connections | concurrent_connections |
+-------------+-------------------+------------------------+
| app         |                 2 |                      0 |
| root        |                 3 |                      0 |
+-------------+-------------------+------------------------+



 Comments   
Comment by Oli Sennhauser [ 2020-07-24 ]

Same on 10.5.3

Comment by Oli Sennhauser [ 2020-07-24 ]

I have not found anything in documentation that indicates some measures to take: https://mariadb.com/kb/en/user-statistics/

Comment by Elena Stepanova [ 2020-08-05 ]

It doesn't seem to work in Percona Server 5.7 either (the patch initially came from Percona).
The docs say that the value is unused for THREAD_STATISTICS, but doesn't say anything about USER_STATISTICS or CLIENT_STATISTICS.

Comment by Laurent Indermühle [ 2023-03-08 ]

I tested 10.5, 10.6 and 10.11. All always display 0 concurrent_connections.
It a shame, I have issues with a user that I wanted to monitor with Prometheus.

Comment by Dave Gosselin [ 2023-12-14 ]

I tested and inspected latest (at this moment) 11.4 git sha 875377ad824473774c

The concurrent_connections column in the information_schema.USER_STATISTICS was never hooked up to anything in our code. It is initialized to zero during init_user_stats and never updated nor read again. We have a couple of different paths forward to consider for fixing this bug, each described below:

1. The THD keeps a USER_CONN instance that will be created for each user only if the server is run with the max-user-connections=X parameter. The USER_CONN object maintains a count of concurrent connections. During update_global_user_stats_with_user we can update user_stats->concurrent_connections with thd->user_connect->connections (provided that thd->user_connect exists). This is sufficient to keep the value up-to-date both for newly created and deleted connections for the user.

2. Totally independent of and separate from max-user-connections=X, we can update user_stats->concurrent_connections on client connect or disconnect. These could accounted for during update_global_user_stats with the addition of a new parameter to the function to disambiguate a disconnect from a connect. For example, during do_handle_one_connection, the callsite update_global_user_stats just after close_connection would include a parameter indicating a disconnect, therefore we decrement the number of concurrent connections. On a new connection, increment_connection_count could increment the number of concurrent connections. There may be other callsites that need some adjustment as well.

serg pinging you for input because git indicates you've made some changes in this area, so I don't want to choose one solution or another without consulting you. Thanks.

Comment by Sergei Golubchik [ 2023-12-16 ]

The second looks simpler to me. Doesn't depend on user limis being enabled, increment_connection_count and update_global_user_stats are called anyway, so incrementing/decrementing concurrent_connections seems to be a non-issue there.

Generated at Thu Feb 08 09:21:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.