MySQL User Statistics is enabled but graphs are not displayed in Pmm MySQL User Statistics dashboard

Enabled the userstat in mysql and also updated in the my.cnf

SET GLOBAL userstat=1;


Here are the outputs for the pmm-admin info/list/check-network.

[root@e-mysql-01.lga1 ~]# pmm-admin info
pmm-admin 1.17.3

PMM Server | prod-perconamm-01.lga1.admarketplace.net (password-protected)
Client Name | e-mysql-01.lga1.admarketplace.net
Client Address | 10.2.0.135
Service Manager | linux-upstart

Go Version | 1.10.1
Runtime Info | linux/amd64

[root@e-mysql-01.lga1 ~]# pmm-admin list
pmm-admin 1.17.3

PMM Server | prod-perconamm-01.lga1.admarketplace.net (password-protected)
Client Name | e-mysql-01.lga1.admarketplace.net
Client Address | 10.2.0.135
Service Manager | linux-upstart

-------------- ---------------------------------- ----------- -------- -------------------------------------------- ---------------
SERVICE TYPE NAME LOCAL PORT RUNNING DATA SOURCE OPTIONS
-------------- ---------------------------------- ----------- -------- -------------------------------------------- ---------------
linux:metrics e-mysql-01.lga1.admarketplace.net 42000 NO -
mysql:metrics e-mysql-01.lga1.admarketplace.net 42002 NO pmm_app:***@unix(/var/lib/mysql/mysql.sock) tablestats=OFF
[root@e-mysql-01.lga1 ~]# pmm-admin check-network
PMM Network Status

Server Address | prod-perconamm-01.lga1.admarketplace.net
Client Address | 10.2.0.135

* System Time
NTP Server (0.pool.ntp.org) | 2020-04-09 13:43:42 +0000 UTC
PMM Server | 2020-04-09 13:43:42 +0000 GMT
PMM Client | 2020-04-09 09:43:42 -0400 EDT
PMM Server Time Drift | OK
PMM Client Time Drift | OK
PMM Client to PMM Server Time Drift | OK

* Connection: Client --> Server
-------------------- -------
SERVER SERVICE STATUS
-------------------- -------
Consul API OK
Prometheus API OK
Query Analytics API DOWN

Connection duration | 1.656123ms
Request duration | -1.315941ms
Full round trip | 340.182µs


* Connection: Client <-- Server
-------------- ---------------------------------- ----------------- ------- ---------- ---------
SERVICE TYPE NAME REMOTE ENDPOINT STATUS HTTPS/TLS PASSWORD
-------------- ---------------------------------- ----------------- ------- ---------- ---------
linux:metrics e-mysql-01.lga1.admarketplace.net 10.2.0.135:42000 OK - -
mysql:metrics e-mysql-01.lga1.admarketplace.net 10.2.0.135:42002 OK - -

Hi,
In this case I suggest you to check

  • If MySQL is reporting some data for USER statistics  through  SHOW USER_STATISTICS
  • If this information is shown in MySQL Exporter status output 
  • If this information can be seen by querying Prometheus directly
This can help to point out where specifically things go wrong
For more information see 
https://www.percona.com/blog/2018/01/17/troubleshooting-percona-monitoring-and-management-pmm-metrics/

Hi Peter,
> I have checked the SHOW USER_STATISTICS for all the hosts and its reporting data for 5 hosts out of 6.
> Not sure how to check the MySQL exporter status output.
> I have seen the Prometheus directly, and here’s the output. 

for e-mysql-01.lga1.admarketplace.net SHOW USER_STATISTICS data is not displayed. 



For w-mysql-01.sfo1.admarketplace.net, w-mysql-02.sfo1.admarketplace.net and w-mysql-03.sfo1.admarketplace.net, SHOW USER_STATISTICS data is displayed but graphs are not displayed. 


Once in a while we can see few host names in the drop down list and later they disappear.

Please let me know if any more information needed. 


These Graphs show the  Prometheus Scrape statistics where you can see it fails on many hosts for “low resolution data” probably due to timeout. 

To see what MySQL exporter returns you should go to the URL which you see being scraped such as https://10.4.0.118:42002/metrics-lr
See if you get output and it it has  anything matching user_stat out there

I have opened the and searched for user_stat and I found the following 

# HELP mysql_info_schema_user_statistics_access_denied_total The number of times this user’s connections issued commands that were denied.
# TYPE mysql_info_schema_user_statistics_access_denied_total counter
mysql_info_schema_user_statistics_access_denied_total{user="adengine_app"} 0

Is this the only match you found or is it one of many lines ? 

It was around 336 lines.
Similar to below line, but the only difference was the user name. 

mysql_info_schema_user_statistics_access_denied_total{user="adengine_app"} 0

OK. So were any other values reported

mysql_info_schema_user_statistics_access_denied_total

This corresponds to access denied cases while there should be also other data about rows read written etc which are actually plotted on dashboard

No other values where reported. 

If this is the case you have problem on the exporter side - might be it is timing out or something - check error logs,
You need to have other values reported such as


``` # HELP mysql_info_schema_user_statistics_bytes_sent_total The number of bytes sent to this user’s connections. # TYPE mysql_info_schema_user_statistics_bytes_sent_total counter mysql_info_schema_user_statistics_bytes_sent_total{user="mysql.session"} 0 mysql_info_schema_user_statistics_bytes_sent_total{user="pmm"} 1.5773185981e+10 mysql_info_schema_user_statistics_bytes_sent_total{user="root"} 328 mysql_info_schema_user_statistics_bytes_sent_total{user="sbtest"} 79362 ```

Heres the over all search list.

OK. This means your exporter returns data. Now go to prometheus query interface ( https://PMMSERVERIP/prometheus)  and enter “mysql_info_schema_user_statistics_update_commands_total”  to see if you’re having data for servers in questions actually making it into Prometheus.

In the prometheus query interface I have enter the “mysql_info_schema_user_statistics_update_commands_total” and did not found the following servers. 
e-mysql-01.lga1.admarketplace.net

w-mysql-01.sfo1.admarketplace.net
w-mysql-02.sfo1.admarketplace.net
w-mysql-03.sfo1.admarketplace.net

If you see the data in the exporter output but not  in the prometheus when most likely you have timeouts getting this data for some reason.

Is there a way to find out the root cause ?
The configuration is similar for all the servers.
FYI, the following hosts names are in the list but graphs are not avaiable
> w-mysql-02.sfo1.admarketplace.net
> w-mysql-03.sfo1.admarketplace.net