Not the answer you need?
Register and ask your own question!

PMM MySql 8.0.11 Instances Not Showing Graphs

baaspbaasp ContributorCurrent User Role Novice
I have a mixed environment of MySql 5.x and 8.0.11 Instances.

The 5.x Instances are Enterprise and the 8.0.11 are Community.

I have no issues in monitoring the MySql 5.x with PMM.

The 8.0.11 instances do not gather all the data and the MySql Overview graphs are not populated (see attachment)

How do I investigate further and correct.


Diagnostics
========================
[[email protected] ~]$ sudo pmm-admin check-network –-no-emoji
PMM Network Status

Server Address | SERVERIP
Client Address | CLIENTIP

* System Time
NTP Server (0.pool.ntp.org) | unable to get ntp time: %!s(<nil>)
PMM Server | 2018-12-18 14:38:14 +0000 GMT
PMM Client | 2018-12-18 09:43:08 -0500 EST
PMM Client to PMM Server Time Drift | 294s
Time is out of sync. Please make sure the server time is correct to see the metrics.

* Connection: Client --> Server

SERVER SERVICE STATUS

Consul API OK
Prometheus API OK
Query Analytics API OK

Connection duration | 1.350818ms
Request duration | -544.205µs
Full round trip | 806.613µs


* Connection: Client <-- Server





SERVICE TYPE NAME REMOTE ENDPOINT STATUS HTTPS/TLS PASSWORD





linux:metrics linux_lcormysqlp01 CLIENTIP:42000 OK YES YES
mysql:metrics mysql_lcormysqlp01 CLIENTIP:42002 DOWN YES YES

When an endpoint is down it may indicate that the corresponding service is stopped (run 'pmm-admin list' to verify).
If it's running, check out the logs /var/log/pmm-*.log

When all endpoints are down but 'pmm-admin list' shows they are up and no errors in the logs,
check the firewall settings whether this system allows incoming connections from server to address:port in question.

Also you can check the endpoint status by the URL: http://SERVERIP/prometheus/targets

ENDPOINT STATUS - metrics-hr is the issue
============================================
mysql (50/72 up)
Endpoint State Labels Last Scrape Error

https://CLIENTIP:42002/metrics-lr
UP instance="mysql_lcormysqlp01" 30.96s ago
https://CLIENTIP:42002/metrics-hr
DOWN instance="mysql_lcormysqlp01" 139ms ago no token found
https://CLIENTIP:42002/metrics-mr
UP instance="mysql_lcormysqlp01" 45ms ago

Comments

  • baaspbaasp Contributor Current User Role Novice
    Note that I have seen runs of sudo pmm-admin check-network –-no-emoji where the remote endpoint is OK for both linux:metrics and mysql:metrics.

    It makes no difference for the PMM graphs.
  • baaspbaasp Contributor Current User Role Novice
    Hi,

    I just setup two new MySql 5.6 servers

    Bot show

    * Connection: Client <-- Server





    SERVICE TYPE NAME REMOTE ENDPOINT STATUS HTTPS/TLS PASSWORD





    linux:metrics linux_NAME01 ###.###.###.###:42000 DOWN YES YES
    mysql:metrics mysql_NAME01 ###.###.###.###:42002 DOWN YES YES


    Yet both populate the graphs in PMM correctly.

    The issue appears to be MySql 8.0.11 specific....

    Could someone advise what to look into please?
  • roma.novikovroma.novikov Percona Percona Staff Role
    * System Time
    NTP Server (0.pool.ntp.org) | unable to get ntp time: %!s(<nil>)
    PMM Server | 2018-12-18 14:38:14 +0000 GMT
    PMM Client | 2018-12-18 09:43:08 -0500 EST
    PMM Client to PMM Server Time Drift | 294s
    Time is out of sync. Please make sure the server time is correct to see the metrics.


    This can be related to some problems. Pls check ntp on your Client and Server
  • baaspbaasp Contributor Current User Role Novice
    Roma,

    Thanks for the suggestion. It was badly out of sync and is now correct.

    Unfortunately this did not correct the problem.

    The issue is specific to MySql 8

    Only the MySql graphs do not populate. Specifically if you wish to focus on one "MySql Client Thread Activity" does not populate.

    However I see two the do. "Process States" and "Top Process States Hourly".

    What is different between MySql 5.6 and 8.x that would lead to this issue?

    Thanks,

    Peter
  • baaspbaasp Contributor Current User Role Novice
    This is current status

    [[email protected] ~]$ sudo pmm-admin check-network –-no-emoji
    PMM Network Status

    Server Address | XXX.XXX.XXX.XXX
    Client Address | XXX.XXX.XXX.XXX

    * System Time
    NTP Server (0.pool.ntp.org) | unable to get ntp time: %!s(<nil>)
    PMM Server | 2019-01-08 18:28:09 +0000 GMT
    PMM Client | 2019-01-08 13:28:21 -0500 EST
    PMM Client to PMM Server Time Drift | OK

    * Connection: Client --> Server

    SERVER SERVICE STATUS

    Consul API OK
    Prometheus API OK
    Query Analytics API OK

    Connection duration | 2.294508ms
    Request duration | -1.583215ms
    Full round trip | 711.293µs


    * Connection: Client <-- Server





    SERVICE TYPE NAME REMOTE ENDPOINT STATUS HTTPS/TLS PASSWORD





    linux:metrics mysql80_lcormysqlp01 XXX.XXX.XXX.XXX:42000 OK YES YES
    mysql:metrics mysql80_lcormysqlp01 XXX.XXX.XXX.XXX:42002 DOWN YES YES

    When an endpoint is down it may indicate that the corresponding service is stopped (run 'pmm-admin list' to verify).
    If it's running, check out the logs /var/log/pmm-*.log

    When all endpoints are down but 'pmm-admin list' shows they are up and no errors in the logs,
    check the firewall settings whether this system allows incoming connections from server to address:port in question.

    Also you can check the endpoint status by the URL: http://XXX.XXX.XXX.XXX/prometheus/targets



    I have attached the current status from prometheus as well.
  • baaspbaasp Contributor Current User Role Novice
    Does anyone have suggestions on how to diagnose and correct the issue please?
  • baaspbaasp Contributor Current User Role Novice
    Using http://www.dataarchitect.cloud/troubleshooting-percona-monitoring-and-management-pmm-metrics/

    I have been able to see that it is the High Resolution MySQL data that is not being gathered.

    I need to get a port opened before I can see the data stream directly in a browser now.

    In addition the client is on 1.16.0 and the server is running 1.17.0. This too is being addressed. But I don't believe it is a factor as the issue has persisted for months.
  • baaspbaasp Contributor Current User Role Novice
    Client now running 1.17.0 and issue persists

    On start of mysqld_exporter we see:

    time="2019-01-14T15:28:22-05:00" level=info msg="Starting mysqld_exporter (version=, branch=, revision=)" source="mysqld_exporter.go:331"
    time="2019-01-14T15:28:22-05:00" level=info msg="Build context (go=go1.10.1, user=, date=)" source="mysqld_exporter.go:332"
    time="2019-01-14T15:28:22-05:00" level=info msg="HTTP basic authentication is enabled" source="mysqld_exporter.go:386"
    time="2019-01-14T15:28:22-05:00" level=info msg="HTTPS/TLS is enabled" source="mysqld_exporter.go:401"
    time="2019-01-14T15:28:22-05:00" level=info msg="Enabled High Resolution scrapers:" source="mysqld_exporter.go:415"
    time="2019-01-14T15:28:22-05:00" level=info msg=" --collect.info_schema.innodb_metrics" source="mysqld_exporter.go:417"
    time="2019-01-14T15:28:22-05:00" level=info msg=" --collect.global_status" source="mysqld_exporter.go:417"
    time="2019-01-14T15:28:22-05:00" level=info msg="Enabled Medium Resolution scrapers:" source="mysqld_exporter.go:421"
    time="2019-01-14T15:28:22-05:00" level=info msg=" --collect.info_schema.processlist" source="mysqld_exporter.go:423"
    time="2019-01-14T15:28:22-05:00" level=info msg=" --collect.slave_status" source="mysqld_exporter.go:423"
    time="2019-01-14T15:28:22-05:00" level=info msg=" --collect.perf_schema.eventswaits" source="mysqld_exporter.go:423"
    time="2019-01-14T15:28:22-05:00" level=info msg=" --collect.perf_schema.file_events" source="mysqld_exporter.go:423"
    time="2019-01-14T15:28:22-05:00" level=info msg=" --collect.info_schema.query_response_time" source="mysqld_exporter.go:423"
    time="2019-01-14T15:28:22-05:00" level=info msg=" --collect.info_schema.innodb_cmp" source="mysqld_exporter.go:423"
    time="2019-01-14T15:28:22-05:00" level=info msg=" --collect.info_schema.innodb_cmpmem" source="mysqld_exporter.go:423"
    time="2019-01-14T15:28:22-05:00" level=info msg="Enabled Low Resolution scrapers:" source="mysqld_exporter.go:427"
    time="2019-01-14T15:28:22-05:00" level=info msg=" --collect.global_variables" source="mysqld_exporter.go:429"
    time="2019-01-14T15:28:22-05:00" level=info msg=" --collect.binlog_size" source="mysqld_exporter.go:429"
    time="2019-01-14T15:28:22-05:00" level=info msg=" --collect.info_schema.userstats" source="mysqld_exporter.go:429"
    time="2019-01-14T15:28:22-05:00" level=info msg=" --collect.custom_query" source="mysqld_exporter.go:429"
    time="2019-01-14T15:28:22-05:00" level=info msg="Listening on 192.168.72.192:42002" source="mysqld_exporter.go:438"
    time="2019-01-14T14:37:23-05:00" level=info msg="Starting mysqld_exporter (version=, branch=, revision=)" source="mysqld_exporter.go:331"
    time="2019-01-14T14:37:23-05:00" level=info msg="Build context (go=go1.10.1, user=, date=)" source="mysqld_exporter.go:332"
    time="2019-01-14T14:37:23-05:00" level=info msg="HTTP basic authentication is enabled" source="mysqld_exporter.go:386"
    time="2019-01-14T14:37:23-05:00" level=info msg="HTTPS/TLS is enabled" source="mysqld_exporter.go:401"
    time="2019-01-14T14:37:23-05:00" level=info msg="Enabled High Resolution scrapers:" source="mysqld_exporter.go:415"
    time="2019-01-14T14:37:23-05:00" level=info msg=" --collect.info_schema.innodb_metrics" source="mysqld_exporter.go:417"
    time="2019-01-14T14:37:23-05:00" level=info msg=" --collect.global_status" source="mysqld_exporter.go:417"
    time="2019-01-14T14:37:23-05:00" level=info msg="Enabled Medium Resolution scrapers:" source="mysqld_exporter.go:421"
    time="2019-01-14T14:37:23-05:00" level=info msg=" --collect.slave_status" source="mysqld_exporter.go:423"
    time="2019-01-14T14:37:23-05:00" level=info msg=" --collect.info_schema.processlist" source="mysqld_exporter.go:423"
    time="2019-01-14T14:37:23-05:00" level=info msg=" --collect.perf_schema.eventswaits" source="mysqld_exporter.go:423"
    time="2019-01-14T14:37:23-05:00" level=info msg=" --collect.perf_schema.file_events" source="mysqld_exporter.go:423"
    time="2019-01-14T14:37:23-05:00" level=info msg=" --collect.info_schema.query_response_time" source="mysqld_exporter.go:423"
    time="2019-01-14T14:37:23-05:00" level=info msg=" --collect.info_schema.innodb_cmp" source="mysqld_exporter.go:423"
    time="2019-01-14T14:37:23-05:00" level=info msg=" --collect.info_schema.innodb_cmpmem" source="mysqld_exporter.go:423"
    time="2019-01-14T14:37:23-05:00" level=info msg="Enabled Low Resolution scrapers:" source="mysqld_exporter.go:427"
    time="2019-01-14T14:37:23-05:00" level=info msg=" --collect.global_variables" source="mysqld_exporter.go:429"
    time="2019-01-14T14:37:23-05:00" level=info msg=" --collect.binlog_size" source="mysqld_exporter.go:429"
    time="2019-01-14T14:37:23-05:00" level=info msg=" --collect.info_schema.userstats" source="mysqld_exporter.go:429"
    time="2019-01-14T14:37:23-05:00" level=info msg=" --collect.custom_query" source="mysqld_exporter.go:429"
    time="2019-01-14T14:37:23-05:00" level=info msg="Listening on 192.168.72.192:42002" source="mysqld_exporter.go:438"
  • baaspbaasp Contributor Current User Role Novice
    Yet Prometheus shows "no token found" for metrics-hr
  • baaspbaasp Contributor Current User Role Novice
    The current results of :

    sudo pmm-admin check-network –-no-emoji


    Are all GREEN (see attached).

    Yet I still do not have any data for Prometheus it still shows "no token found" for metrics-hr

    And hence the detailed graphs for MySql are blank.
  • PeterPeter Percona CEO Percona Moderator Role
    Hi,

    If you go to https://192.168.72.192:42002/metrics-hr with browser are you getting page with metrics or are you seeing something else

    Per:

    https://github.com/prometheus/prometheus/issues/3154

    Such behavior is possible if there is some malformed metric name. Might be in your installation such metric is reported for some reason ?
  • baaspbaasp Contributor Current User Role Novice
    Peter, Thanks for the reply.

    I am just waiting on our internal team to open the ports so I may see https://192.168.72.192:42002/metrics-hr from my PC.

    May take a few days:( I will report the results once I have them.

    But we are advancing and that is all that matters.

    Peter
  • PeterPeter Percona CEO Percona Moderator Role
    Hi,


    There is an easier way :) You can log in to your server and use curl to get the output:

    curl -k https://192.168.72.192:42002/metrics-hr
  • baaspbaasp Contributor Current User Role Novice
    Attached is output of curl -k -u admin:PASSWORDSECRET https://192.168.72.192:42002/metrics-hr

    There is some data returned with dash - but would it matter ?

    # TYPE go_gc_duration_seconds summary
    go_gc_duration_seconds{quantile="0"} 2.6608e-05
    go_gc_duration_seconds{quantile="0.25"} 4.1159e-05
    go_gc_duration_seconds{quantile="0.5"} 5.0552e-05
    go_gc_duration_seconds{quantile="0.75"} 7.095e-05
    .
    .
    .
    # HELP mysql_info_schema_innodb_metrics_adaptive_hash_index_adaptive_hash_searches_btree_total Number of searches using B-tree on an index search
    .
    .
    .
    # HELP mysql_info_schema_innodb_metrics_buffer_buffer_pool_read_ahead_evicted_total Read-ahead pages evicted without being accessed (innodb_buffer_pool_read_ahead_evicted)


    It would appear to be gathering the data just not processing it:(

    Thanks in advance for reviewing and advising on this issue.

    Peter
  • baaspbaasp Contributor Current User Role Novice
    Have any ideas based on the output been sparked?
  • baaspbaasp Contributor Current User Role Novice
    Hi has the data provided sparked an insight into correcting the issue?
  • baaspbaasp Contributor Current User Role Novice
    Sorry for the spam, my bad I had not noted the thread had gon over to a second page.

    Attached is the logs.zip from the server.
  • baaspbaasp Contributor Current User Role Novice
    Output of check-network

    [[email protected] ~]$ sudo pmm-admin check-network –-no-emoji
    PMM Network Status

    Server Address | SERVER_IP
    Client Address | CLIENT_IP

    * System Time
    NTP Server (0.pool.ntp.org) | unable to get ntp time: %!s(<nil>)
    PMM Server | 2019-01-22 13:24:48 +0000 GMT
    PMM Client | 2019-01-22 08:26:25 -0500 EST
    PMM Client to PMM Server Time Drift | 97s
    Time is out of sync. Please make sure the server time is correct to see the metrics.

    * Connection: Client --> Server

    SERVER SERVICE STATUS

    Consul API OK
    Prometheus API OK
    Query Analytics API OK

    Connection duration | 1.771548ms
    Request duration | -615.793µs
    Full round trip | 1.155755ms


    * Connection: Client <-- Server





    SERVICE TYPE NAME REMOTE ENDPOINT STATUS HTTPS/TLS PASSWORD





    linux:metrics mysql80_lcormysqld01 CLIENT_IP:42000 OK YES YES
    mysql:metrics mysql80_lcormysqld01 CLIENT_IP:42002 DOWN YES YES

    When an endpoint is down it may indicate that the corresponding service is stopped (run 'pmm-admin list' to verify).
    If it's running, check out the logs /var/log/pmm-*.log

    When all endpoints are down but 'pmm-admin list' shows they are up and no errors in the logs,
    check the firewall settings whether this system allows incoming connections from server to address:port in question.

    Also you can check the endpoint status by the URL: http://SERVER_IP/prometheus/targets
  • baaspbaasp Contributor Current User Role Novice
    Output of pmm-admin summary from client attached.
  • roma.novikovroma.novikov Percona Percona Staff Role
    baasp Thanks for logs! and Thanks Peter for Idea.
    You right - https://github.com/prometheus/mysqld_exporter/issues/325#issuecomment-426798030 this is our case and
    mysql_global_status_validate_password.dictionary_file_words_count 0

    is wrong metrics name.

    We did some fixes in our version and upstream https://github.com/prometheus/mysqld_exporter/pull/307 but later it was discovered that the problem is not 100% solved (https://github.com/prometheus/mysqld_exporter/pull/337)
    The fix is not released in upstream.

    baasp can you try a solution from https://github.com/prometheus/mysqld_exporter/issues/325#issuecomment-426798030? Meanwhile, we'll prepare some code with patch from upstream fix
  • baaspbaasp Contributor Current User Role Novice
    Yay, Thanks Roma and Peter.

    The solution :

    This problem apparently caused to pluggin:
    mysql> SHOW VARIABLES LIKE '%dictionary%';
    +
    +
    +
    | Variable_name | Value |
    +
    +
    +
    | validate_password.dictionary_file | |
    +
    +
    +
    1 row in set (0.00 sec)

    To resolve this problem, I disabled in the mysql:

    mysql> SELECT @@plugin_dir;
    +
    +
    | @@plugin_dir |
    +
    +
    | /usr/lib64/mysql/plugin/ |
    +
    +
    1 row in set (0.00 sec)

    mysql> UNINSTALL COMPONENT 'file://component_validate_password';

    Restart mysql and the mysqld_export connect to Prometheus.


    Works.

    However I cannot deploy that into production as we require enforcement of the password complexity.

    When do you estimate a new release containing the patch will be published?

    :)
  • Michael CoburnMichael Coburn Principal Architect, Percona Percona Staff Role
    Hi baasp

    We will be doing a 1.17.1 release on or around February 13, and it will include a fix for the issue you're experiencing. If you'd like to track the issue, it is in our JIRA:
    https://jira.percona.com/browse/PMM-3471
    Thank you for your assistance while we identified and solved your request!
  • baaspbaasp Contributor Current User Role Novice
    Hi,

    I have deployed the 1.17.1 release but the issue is not release.

    :(

    Peter
  • baaspbaasp Contributor Current User Role Novice
    Resolved I meant to say.
  • baaspbaasp Contributor Current User Role Novice
    Hi,

    Can someone review this and let me know what to do to have the fix reviewed as it did not fix the issue.

    Peter
  • hpakniahpaknia Entrant Current User Role Novice
    I still have this problem. This is the server-client info (all centos 7):
    1. Server is docker "percona/pmm-server:2"
    2. Client 1 is mysql 5.7, everything works fine and I can see the graphs on the server.
    3. Client 2 is percona mysql 8.16 and none of the graphs shows up. Server reveals agents are connected, both 5.7 and 8. But for client 2 (mysql 8) I don't get any graph. Not even client server OS info. I checked pmm-admin status on both clients. Both are connected and working file. Node exporter is running.

    On both clients I have installed ppm2-client using yum. Interestingly I can not file any pmm log files on any of the client servers.
    Any idea?
  • hpakniahpaknia Entrant Current User Role Novice
    And I did create the pmm user with "mysql_native_password" option. An observation: on both clients I login using root account. Running "show full processlist" reveals 4 pmm users being connected on mysql 5.7, but on mysql 8.0 I see only one pmm connection.
    It shows 1. pmm user can connact to both servers 2. There are more connections on mysql 5.7 than mysql 8.0, why?
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.