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

Not getting query analytics with 1.0.4

rossiwarossiwa ContributorCurrent User Role Novice
I've added 4 client - 3 modes of PXC and one node of mariadb. OS and mysql metrics show up for all the hosts but no query analytics. On the qan page I get this message for mariadb "There is no data for the selected MySQL instance, time range or search query". For the PXC nodes I get this message "QAN API error: "MySQL Query Analytics configuration not found".
Check the /var/log/qan-api.log file in docker container for more information."

[[email protected] data]# pmm-admin list
pmm-admin 1.0.4

PMM Server | 192.168.12.92
Client Name | mysql-cluster2
Client Address | 192.168.12.66
Service manager | unix-systemv






SERVICE TYPE NAME CLIENT PORT RUNNING DATA SOURCE OPTIONS





linux:metrics mysql-cluster2 42000 YES -
mysql:queries mysql-cluster2 42001 YES root:***@unix(/var/lib/mysql/mysql.sock) query_source=slowlog
mysql:metrics mysql-cluster2 42002 YES root:***@unix(/var/lib/mysql/mysql.sock)

[[email protected] ~]# pmm-admin list
pmm-admin 1.0.4

PMM Server | 192.168.12.92
Client Name | mysql-cluster3
Client Address | 192.168.12.67
Service manager | unix-systemv






SERVICE TYPE NAME CLIENT PORT RUNNING DATA SOURCE OPTIONS





linux:metrics mysql-cluster3 42000 YES -
mysql:queries mysql-cluster3 42001 YES root:***@unix(/var/lib/mysql/mysql.sock) query_source=slowlog
mysql:metrics mysql-cluster3 42002 YES root:***@unix(/var/lib/mysql/mysql.sock)


[[email protected] ~]# pmm-admin list
pmm-admin 1.0.4

PMM Server | 192.168.12.92
Client Name | mysql-cluster1
Client Address | 192.168.12.65
Service manager | unix-systemv






SERVICE TYPE NAME CLIENT PORT RUNNING DATA SOURCE OPTIONS





linux:metrics mysql-cluster1 42000 YES -
mysql:queries mysql-cluster1 42001 YES root:***@unix(/var/lib/mysql/mysql.sock) query_source=slowlog
mysql:metrics mysql-cluster1 42002 YES root:***@unix(/var/lib/mysql/mysql.sock)


[[email protected] vmware-tools-distrib]# pmm-admin list
pmm-admin 1.0.4

PMM Server | 192.168.12.92
Client Name | pmm-dev-noc01
Client Address | 192.168.12.92
Service manager | linux-systemd






SERVICE TYPE NAME CLIENT PORT RUNNING DATA SOURCE OPTIONS





linux:metrics pmm-dev-noc01 42000 YES -
mysql:queries pmm-dev-noc01 42001 YES root:***@tcp(localhost:3306) query_source=slowlog
mysql:metrics pmm-dev-noc01 42002 YES root:***@tcp(localhost:3306)


I've checked that the slowlog is collecting queries, but nothing shows up on the qan page.

Thanks.

Comments

  • weberweber Advisor Inactive User Role Beginner
    I see you have "slowlog" as a query source for mysql:queries.

    https://www.percona.com/doc/percona-monitoring-and-management/conf-mysql.html#settings-for-the-slow-query-log
    So basically, you have to enable those options for MariaDB:
    log_output='file'
    long_query_time=0
    slow_query_log=ON

    However, we recommend enabling performance_schema for MariaDB.
    This requires:
    - set performance_schema=ON
    - run:
    UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE 'statement/sql/%’;
    UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'statements_digest’;
    - pmm-admin remove mysql:queries
    - pmm-admin add mysql:queries --query-source perfschema

    But you can still try slowlog for MariaDB.

    For PXC we recommend slowlog and here is version specific MySQL options:
    **Percona 5.6+ ~ 5.6.12**
    ```
    log_output='file'
    log_slow_rate_type='query'
    log_slow_rate_limit=100
    long_query_time=0
    log_slow_verbosity='full'
    log_slow_admin_statements=ON
    log_slow_slave_statements=ON
    slow_query_log_use_global_control='all'
    #slow_query_log_always_write_time=1 // not until 5.6.13
    slow_query_log=ON
    ```

    **Percona 5.6.13+**
    ```
    log_output='file'
    log_slow_rate_type='query'
    log_slow_rate_limit=100
    long_query_time=0
    log_slow_verbosity='full'
    log_slow_admin_statements=ON
    log_slow_slave_statements=ON
    slow_query_log_use_global_control='all'
    slow_query_log_always_write_time=1
    slow_query_log=ON
    ```

    If you can try this on your non-production database it would be better to test.
  • rossiwarossiwa Contributor Current User Role Novice
    Thanks for the reply. I've re-set all the slow log settings to the values you suggested and restarted the pmm client. I'm getting this error for the PXC node on qan page ... "QAN API error: "MySQL Query Analytics configuration not found. Check the /var/log/qan-api.log file in docker container for more information." The /var/log/qan-api.log does not seem to have anything helpful.
    The pmm-admin check-network command returns:
    [[email protected] ~]# pmm-admin check-network --no-emoji
    PMM Network Status

    Server | 192.168.12.92
    Client | 192.168.12.66

    * Client --> Server

    SERVER SERVICE CONNECTIVITY

    Consul API OK
    QAN API OK
    Prometheus API OK

    Connection duration | 295.273µs
    Request duration | 612.556µs
    Full round trip | 907.829µs

    * Client <-- Server



    METRIC NAME PROMETHEUS ENDPOINT REMOTE STATE



    linux mysql-cluster2 192.168.12.66:42000 OK
    mysql mysql-cluster2 192.168.12.66:42002 OK



    On a more positive note, I am now getting query analytics for the mariadb!

    Thanks..
  • weberweber Advisor Inactive User Role Beginner
    Regarding pxc node, simply remove mysql:queries and add them back using pmm-admin. You may check for errors under /var/log/pmm-mysql-queries*.log once the service is added back.
  • rossiwarossiwa Contributor Current User Role Novice
    I've removed the service and then added it back. Still getting that error. Here is the /var/log/pmm-mysql-queries-42001.log...
    [[email protected] log]# cat /var/log/pmm-mysql-queries-42001.log
    # Version: percona-qan-agent 1.0.4
    # Basedir: /usr/local/percona/qan-agent
    # Listen: 127.0.0.1:42001
    # PID: 21963
    # API: 192.168.12.92/qan-api
    # UUID: 38a1005624114e366430ac703493423f
    2016/09/16 20:11:29.672457 main.go:160: Starting agent...
    2016/09/16 20:11:29.673466 main.go:338: Agent is ready
    2016/09/16 20:11:29.679116 main.go:202: API is ready


    Is it possible that this is caused by a previous version of pmm client that is not completely removed? I had the 1.0.0 client on here previously. How do I make sure I've removed all traces of the old client?
  • rossiwarossiwa Contributor Current User Role Novice
    I finally got this working. I ended up removing all percona apps from the box and re-installing pmm-client. Thanks for your help!
This discussion has been closed.

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