Not getting query analytics with 1.0.4

Percona Monitoring and Management (PMM) PMM 1.x
1

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.”

[root@mysql-cluster2 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)

[root@mysql-cluster3 ~]# 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)

[root@mysql-cluster1 ~]# 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)

[root@pmm-dev-noc01 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.

2

I see you have “slowlog” as a query source for mysql:queries.

[url]Percona Monitoring and Management
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.

3

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:
[root@mysql-cluster2 ~]# 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…

4

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.

5

I’ve removed the service and then added it back. Still getting that error. Here is the /var/log/pmm-mysql-queries-42001.log…
[root@mysql-cluster2 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?

6

I finally got this working. I ended up removing all percona apps from the box and re-installing pmm-client. Thanks for your help!