mysql:queries and mysql:metrics not work ..

Hello,

we test the functionality pmm.
We have 2 server : - PMM Server (Centos 7) : 10.130.3.53

  • PMM Client (Centos 6 with Percona Server 5.5) : perco - 10.130.3.62

For the Slow Query check for QAN PMM on the PMM Client :
slow_query_log_file=/var/log/mysql-slow.log
slow_query_log=ON
long_query_time=0.01
log_slow_admin_statements=ON
log_slow_slave_statements=ON

On the Client :
[root@perco ~]# pmm-admin list
pmm-admin 1.0.5

PMM Server | 10.130.3.53
Client Name | perco
Client Address | 10.130.3.62
Service manager | unix-systemv


SERVICE TYPE NAME CLIENT PORT RUNNING DATA SOURCE OPTIONS


mysql:queries perco 42001 YES root:@unix(/var/lib/mysql/mysql.sock) query_source=slowlog
mysql:metrics perco 42002 YES root:
@unix(/var/lib/mysql/mysql.sock)

[root@perco ~]# pmm-admin ping
OK, PMM server is alive.

PMM Server | 10.130.3.53
Client Name | perco
Client Address | 10.130.3.62

[root@perco ~]# pmm-admin check-network --no-emoji
PMM Network Status

Server | 10.130.3.53
Client | 10.130.3.62

  • Client --> Server

SERVER SERVICE STATUS


Consul API OK
QAN API OK
Prometheus API OK

Connection duration | 373.944µs
Request duration | 442.435µs
Full round trip | 816.379µs

  • Client <-- Server

SERVICE TYPE NAME REMOTE ENDPOINT STATUS


mysql:metrics perco 10.130.3.62:42002 DOWN

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.

When we launch pmm-admin check-network --no-emoji command, we can see the STATUS is DOWN …

But we check on the log file :
[root@perco log]# cat pmm-mysql-metrics-42002.log
time=“2016-10-25T19:57:14+02:00” level=info msg=“Starting mysqld_exporter (version=1.0.5, branch=master, revision=3c95bb2c647443430db2337627d5f677665f41c7)” source=“mysqld_exporter.go:631”
time=“2016-10-25T19:57:14+02:00” level=info msg=“Build context (go=go1.7.1, user=, date=)” source=“mysqld_exporter.go:632”
time=“2016-10-25T19:57:14+02:00” level=info msg=“Listening on 10.130.3.62:42002” source=“mysqld_exporter.go:666”

[root@perco log]# cat pmm-mysql-queries-42001.log

Version: percona-qan-agent 1.0.5

Basedir: /usr/local/percona/qan-agent

Listen: 127.0.0.1:42001

PID: 2686

API: 10.130.3.53/qan-api

UUID: 5c323db2bbd94aae4b1a05571b37387c

2016/10/25 19:51:51.182411 main.go:165: Starting agent…
2016/10/25 19:51:51.183653 main.go:343: Agent is ready
2016/10/25 19:51:51.196672 main.go:207: API is ready

And on the mysql-slow.log we can see entry :
[root@perco log]# tail -n 20 mysql-slow.log
FROM information_schema.tables t
JOIN information_schema.columns c USING (table_schema,table_name)
WHERE c.extra = ‘auto_increment’ AND t.auto_increment IS NOT NULL;

Time: 161025 20:09:35

User@Host: root[root] @ localhost []

Thread_id: 960 Schema: Last_errno: 0 Killed: 0

Query_time: 0.020572 Lock_time: 0.000375 Rows_sent: 1 Rows_examined: 106 Rows_affected: 0 Rows_read: 106

Bytes_sent: 401

SET timestamp=1477418975;
SELECT table_schema, table_name, column_name, auto_increment,
pow(2, case data_type
when ‘tinyint’ then 7
when ‘smallint’ then 15
when ‘mediumint’ then 23
when ‘int’ then 31
when ‘bigint’ then 63
end+(column_type like ‘% unsigned’))-1 as max_int
FROM information_schema.tables t
JOIN information_schema.columns c USING (table_schema,table_name)
WHERE c.extra = ‘auto_increment’ AND t.auto_increment IS NOT NULL;

But on the web interface, not found entry on the qan and metrics panels (we can see picture on the attachement) …

Why ?

We disable SELINUX and iptables for PMM Server and PMM client…

Do you have the idea for this problem ?

Thank you for tyour help,
Regards,
CM

Hello,

  • Please check the status of endpoint at http://server/prometheus/targets
  • Enter container docker exec -ti pmm-server bash and run curl http://10.130.3.62:42002/metrics. It should work, otherwise - it’s firewall problem.
  • Ensure server time (where container runs) is in sync (timezone does not matter)

Hello,

  • Please check the status of endpoint at http://server/prometheus/targets :
    We can see an attachement the output

  • Enter container docker exec -ti pmm-server bash and run curl http://10.130.3.62:42002/metrics. It should work, otherwise - it’s firewall problem :
    root@3a5cea16da90:/opt# curl http://10.130.3.62:42002/metrics

MySQL 3-in-1 exporter

MySQL 3-in-1 exporter

  • high-res metrics
  • medium-res metrics
  • low-res metrics
  • Ensure server time (where container runs) is in sync (timezone does not matter) :
    The Server PMM :
    [root@localhost ~]# date
    mer. oct. 26 01:53:19 CEST 2016

    The Container docker :
    [root@localhost ~]# docker exec -ti pmm-server bash
    root@3a5cea16da90:/opt# date
    Tue Oct 25 23:54:18 UTC 2016

    And the Percona Server PMM Client :
    root@978b04984347:/# date
    Tue Oct 25 22:14:48 CEST 2016

    For you the problem is the server time ?
    How is it possible that the container does not have the same time zone as the server?

    Do you have recommendation for configuration docker timezone ?

    Thank you for your help,
    Regards,
    CM

    It does not matter what timezone is, the time should be in sync. Container may have another timezone as configured on the image.

    So the problem like I said is with server time: mer. oct. 26 01:53:19 CEST 2016
    It can’t be in the future.
    Please sync server time and you will see the graphs.

    Hello,

    It works perfect thanks

    We have 2 another question :

    1/We have servers who are always on Percona Server 5.5, but we have this error because on PS 5.5 the EXPLAIN command is not supported for INSERT or DELETE querys.
    Do you have recommendation for by pass with error or work this query ?

    2/Next question, for SELECT query, the command EXPLAIN not work.We can see an attachement the output panel and output log root@4f4cee6f4972:/var/log# tail -f qan-api.log.
    It’s possible to explain me why the agent ws error lost connection to API but on the panel QAN is ok only explain not work?
    2016/10/26 10:25:53.396863 WARNING agent Lost connection to API
    2016/10/26 10:27:13.405483 WARNING agent ws error: EOF
    2016/10/26 10:27:13.405838 WARNING agent Lost connection to API

    Thank you for your help,
    regards,
    CM

    Unfortunately, the screenshots appear too small and I can’t see the issue. Is it possible to resend them?

    Could you provide the complete MySQL version?
    Perhaps you could provide us the ouput of:

    show variables like ‘%version%’;

    The problem is solved.

    Thank you for your help,
    The problem is to lost the connect between PMM Client and PMM Server.

    Thank you,
    Regards,