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

mysql:queries and mysql:metrics not work ..

cmonscmons EntrantInactive User Role Beginner
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 :
[[email protected] ~]# 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)


[[email protected] ~]# pmm-admin ping
OK, PMM server is alive.

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


[[email protected] ~]# 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 :
[[email protected] 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"


[[email protected] 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 :
[[email protected] 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
# [email protected]: 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

Comments

  • weberweber Advisor Inactive User Role Beginner
    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)
  • cmonscmons Entrant Inactive User Role Beginner
    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 :
    [email protected]:/opt# curl http://10.130.3.62:42002/metrics
    <html>
    <head><title>MySQL 3-in-1 exporter</title></head>
    <body>
    <h1>MySQL 3-in-1 exporter</h1>
    <li><a href="/metrics-hr">high-res metrics</a></li>
    <li><a href="/metrics-mr">medium-res metrics</a></li>
    <li><a href="/metrics-lr">low-res metrics</a></li>
    </body>
    </html>

    Ensure server time (where container runs) is in sync (timezone does not matter) :
    The Server PMM :
    [[email protected] ~]# date
    mer. oct. 26 01:53:19 CEST 2016

    The Container docker :
    [[email protected] ~]# docker exec -ti pmm-server bash
    [email protected]:/opt# date
    Tue Oct 25 23:54:18 UTC 2016


    And the Percona Server PMM Client :
    [email protected]:/# 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
  • weberweber Advisor Inactive User Role Beginner
    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.
  • cmonscmons Entrant Inactive User Role Beginner
    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 [email protected]:/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
  • weberweber Advisor Inactive User Role Beginner
    Unfortunately, the screenshots appear too small and I can't see the issue. Is it possible to resend them?
  • carlos.salguerocarlos.salguero Percona Toolkit Developer Percona Staff Role
    Could you provide the complete MySQL version?
    Perhaps you could provide us the ouput of:

    show variables like '%version%';
  • cmonscmons Entrant Inactive User Role Beginner
    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,
This discussion has been closed.

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