Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

userstat / I_S.{client,user}_statistics missing most of data

Description

Userstat feature, or tables INFORMATION_SCHEMA.CLIENT_STATISTICS and INFORMATION_SCHEMA.USER_STATISTICS are missing most of the data.

 

Server version:

Percona-XtraDB-Cluster-server-56-5.6.42-28.30.1.el6.x86_64

 

The configuration "userstat" is enabled:

# grep userstat /etc/my.cnf
userstat=1

 

But still the tables show almost nothing (note that I have redacted the IP address, the field "CLIENT" does show a correct IP address but ~all other fields are zero):

mysql> select * from client_statistics;
+-----------------+------------------++----------------------------------------++---------------------++----------------------------++------------------------------------++-------------------------------++----------------------------------++-------------------------------------++-------------------------------------------++---------------------------------++--------------------------------------+
| CLIENT | TOTAL_CONNECTIONS | CONCURRENT_CONNECTIONS | CONNECTED_TIME | BUSY_TIME | CPU_TIME | BYTES_RECEIVED | BYTES_SENT | BINLOG_BYTES_WRITTEN | ROWS_FETCHED | ROWS_UPDATED | TABLE_ROWS_READ | SELECT_COMMANDS | UPDATE_COMMANDS | OTHER_COMMANDS | COMMIT_TRANSACTIONS | ROLLBACK_TRANSACTIONS | DENIED_CONNECTIONS | LOST_CONNECTIONS | ACCESS_DENIED | EMPTY_QUERIES | TOTAL_SSL_CONNECTIONS |
+-----------------+------------------++----------------------------------------++---------------------++----------------------------++------------------------------------++-------------------------------++----------------------------------++-------------------------------------++-------------------------------------------++---------------------------------++--------------------------------------+
| 00.00.0.0 | 6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| | 1 | 1 | 29940137 | 484 | 548 | 0 | 0 | 0 | 0 | 29149934 | 28300922 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 00.00.0.1 | 21328 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 00.00.0.2 | 1595541 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 000.000.000.1 | 26186 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 000.000.000.2 | 167 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 00.00.0.3 | 51582 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

Is it a bug? If it is a limitation in PXC, would be nice to have it documented somewhere.

Environment

EL7

AFFECTED CS IDs

258136

Attachments

2
  • 05 Mar 2019, 08:04 AM
  • 05 Mar 2019, 08:03 AM

Smart Checklist

Activity

Show:

KennT July 23, 2019 at 8:04 AM

commit 5561e20676e93dfe49dcd179bc60c3f99827c7dd (origin/5.6-pxc-2432)
Author: Kenn Takara <kenn.takara@percona.com>
Date: Sat Jul 20 15:14:00 2019 -0700

https://perconadev.atlassian.net/browse/PXC-2432#icft=PXC-2432 : userstat / I_S.{client,user}_statistics missing most of data

Issue
PXC is not updating the user stats.

Solution
The WSREP code added an if/else block, and the stat updates were being
done only in the else block. Replicated the stat updates to the
if block.

KennT July 2, 2019 at 7:44 AM

This has been fixed in PXC 8.0.  The issue is that the update_stats() is not called at the end of dispatch_command().

KennT July 2, 2019 at 1:10 AM
Edited

Also, if the commands are coming from the binary interface, then some of the statistics show up (table_rows_read is updated but select_commands is not).  In the code, I don't think any code coming through COM_QUERY will update the stats,it has a param update_userstat which is always false

^^ Nevermind, the stats are getting updated outside of the statement.

Is upstream also updating the commands (select,update,other)?

KennT June 25, 2019 at 1:54 AM

Confirmed on 5.7

Francesco Nassano June 11, 2019 at 1:48 PM

I think we suffer for the same, after upgrade from 5.6.28 to 5.6.43 we cannot see anymore "Rows updated" data in userstats. We are investigating other metrics to be sure everything else is really ok.

 

 

+--------------------+ | @@Version | +--------------------+ | 5.6.43-84.3-56-log | +--------------------+

 

 

 

mysql> SELECT @@Version_comment ; +---------------------------------------------------------------------------------------------------+ | @@Version_comment | +---------------------------------------------------------------------------------------------------+ | Percona XtraDB Cluster (GPL), Release rel84.3, Revision e2908fe, WSREP version 28.32, wsrep_28.32 | +---------------------------------------------------------------------------------------------------+

 

 

 

mysql> SELECT @@userstat ; +------------+ | @@userstat | +------------+ | 1 | +------------+

 

 

 

mysql> SELECT * FROM INFORMATION_SCHEMA.CLIENT_STATISTICS WHERE CLIENT != "localhost" ; +---------------+-------------------+------------------------+----------------+-----------+----------+----------------+------------+----------------------+--------------+--------------+-----------------+-----------------+-----------------+----------------+---------------------+-----------------------+--------------------+------------------+---------------+---------------+-----------------------+ | CLIENT | TOTAL_CONNECTIONS | CONCURRENT_CONNECTIONS | CONNECTED_TIME | BUSY_TIME | CPU_TIME | BYTES_RECEIVED | BYTES_SENT | BINLOG_BYTES_WRITTEN | ROWS_FETCHED | ROWS_UPDATED | TABLE_ROWS_READ | SELECT_COMMANDS | UPDATE_COMMANDS | OTHER_COMMANDS | COMMIT_TRANSACTIONS | ROLLBACK_TRANSACTIONS | DENIED_CONNECTIONS | LOST_CONNECTIONS | ACCESS_DENIED | EMPTY_QUERIES | TOTAL_SSL_CONNECTIONS | +---------------+-------------------+------------------------+----------------+-----------+----------+----------------+------------+----------------------+--------------+--------------+-----------------+-----------------+-----------------+----------------+---------------------+-----------------------+--------------------+------------------+---------------+---------------+-----------------------+ | 192.168.1.70 | 19674 | 0 | 86476 | 0 | 0 | 99190 | 877704 | 0 | 423069 | 0 | 4979427144 | 0 | 0 | 0 | 0 | 2 | 1 | 22 | 0 | 0 | 0 | | | 1 | 0 | 688875 | 173 | 175 | 0 | 0 | 29592 | 0 | 9233382 | 4663997 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 10.5.1.250 | 44859186 | 0 | 73356685 | 0 | 0 | 225170443 | 262656 | 0 | 660300325 | 46 | 89661405339 | 0 | 0 | 1 | 0 | 15199 | 2918 | 5416 | 0 | 0 | 0 | | 127.0.0.1 | 4 | 0 | 66 | 0 | 0 | 20 | 0 | 0 | 1633 | 0 | 3543398 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 192.168.1.68 | 31 | 0 | 570103 | 0 | 0 | 159 | 0 | 0 | 3988150 | 0 | 949944 | 0 | 0 | 0 | 0 | 0 | 0 | 13 | 0 | 0 | 0 | | 10.5.1.249 | 74302955 | 0 | 102311135 | 0 | 0 | 383463545 | 657186 | 0 | 1565745874 | 0 | 188600663107 | 0 | 0 | 0 | 0 | 28779 | 6207 | 10494 | 0 | 0 | 0 | | 10.5.1.192 | 28803 | 0 | 156 | 0 | 0 | 144015 | 0 | 0 | 13709278 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 192.168.1.117 | 32 | 0 | 18912 | 0 | 0 | 294 | 0 | 0 | 71894 | 0 | 12289 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | 0 | 0 | 0 | | 10.5.3.250 | 489 | 1 | 2528675 | 11816 | 11769 | 332909084 | 447496179 | 0 | 6551689 | 0 | 2183886337 | 448885 | 0 | 3020332 | 0 | 0 | 0 | 1 | 0 | 7173 | 0 | +---------------+-------------------+------------------------+----------------+-----------+----------+----------------+------------+----------------------+--------------+--------------+-----------------+-----------------+-----------------+----------------+---------------------+-----------------------+--------------------+------------------+---------------+---------------+-----------------------+

 

 

Done

Details

Assignee

Reporter

Time tracking

1d 7h 10m logged

Fix versions

Affects versions

Priority

Smart Checklist

Created February 22, 2019 at 4:07 AM
Updated March 6, 2024 at 10:16 PM
Resolved July 23, 2019 at 8:02 AM
Configure

Flag notifications