Hi!
I’m running PMM 1.5.3 in Docker.
Just after installation everything worked fine. But after a couple of hours, Query Analytics shows “There is no query data because the MySQL Server is not been configured for monitoring”.
On the client side I see the following errors:
2018/01/12 13:25:10.291158 WARNING data-sender Waiting for API to ack qan_1515691320066033553: read tcp 192.168.0.200:11118->192.168.0.211:80: i/o timeout
2018/01/12 13:25:18.295135 WARNING data-sender Waiting for API to ack qan_1515691320066033553: read tcp 192.168.0.200:11146->192.168.0.211:80: i/o timeout
2018/01/12 13:25:26.298139 WARNING data-sender Waiting for API to ack qan_1515691320066033553: read tcp 192.168.0.200:11154->192.168.0.211:80: i/o timeout
Network betwen server and client is fine (1G LAN).
Mysql and Linux metrics work withoun any problem.
Mysql (PS 5.7) is configured according to instructions (with slow-query-log).
Log from settings tab in web UI is attached.
Server Address | 192.168.0.211
Client Address | 192.168.0.200
System Time
NTP Server (0.pool.ntp.org) | 2018-01-12 17:25:10 +0300 MSK
PMM Server | 2018-01-12 14:25:10 +0000 GMT
PMM Client | 2018-01-12 17:25:10 +0300 MSK
PMM Server Time Drift | OK
PMM Client Time Drift | OK
PMM Client to PMM Server Time Drift | OK
Connection: Client → Server
SERVER SERVICE STATUS
Consul API OK
Prometheus API OK
Query Analytics API OK
This surely does not look normal. It states normal but it might be breaking something… Did you say if you restart the docker you get new data to appear but when… no more ?
You say
"
I’m running PMM 1.5.3 in Docker.
Just after installation everything worked fine. But after a couple of hours, Query Analytics shows “There is no query data because the MySQL Server is not been configured for monitoring”.
On the client side I see the following errors:"
Do I understand correctly in couple of hours you do not have any data at all, even data which was there - ie if you go from 1 hour to 24h or so ?
Currenly I can see Query data only from starting period, a few hours just after pmm-server installation. Later I see no data. Restarting pmm-server or pmm-client doesn’t work.
I tried to reinstall pmm-client - still no luck.
It’s the same with 2 clients - one local (that I showed in logs), other is located in remote DC.
PMM-server is running on pretty ancient hardware (Athlon X2-6000 - 2 cores, 3Ghz, 6Gb RAM, 1 SATA HDD). Could be that a problem? This issue is the same for 1 or 2 active pmm-clients.
I see you mentioned “Query Analytics” in the title, and I can see from the link you sent that both linux and mysql metrics are working. If you need to see data from queries, you need to have the mysql:queries exporter running, which you are not at the moment:
Connection: Client ← Server
SERVICE TYPE NAME REMOTE ENDPOINT STATUS HTTPS/TLS PASSWORD
linux:metrics block2 192.168.0.200:42000 OK YES -
mysql:metrics block2 192.168.0.200:42002 OK YES -
You need to issue:
shell> pmm-admin add mysql:queries
Make sure that long_query_time is set to something appropriate, too, because it may be set to 10 seconds (default), and only queries that take more than that will be logged.
I should also mention that it is probably a good idea for you to setup a user and password if it’s a public-facing deployment For this, you will need to stop the current pmm-server container, remove it, and start a new one with additional options enabled. Check [url]Percona Monitoring and Management (SERVER_PASSWORD (Option) section).
It seems that pmm-server just can’t cope with load. At night I started to see some queries from low-loaded server. Production server (with 2-3 kQPS) has no queries. Changing metrics resolution to 3s doesn’t help.
Metrics Resolution only impacts Metrics not queries. What data source are you using for Queries - is it using Slow Query Log or Performance Schema. Does switching query source works ?
What MySQL version do you have and how is it configured ?
PMM Server is the weak spot: Athlon X2 6000 (2 cores 3GHz), 6 Gb RAM, 1 SATA HDD. How to find bottleneck (can be CPU or HDD). As far as I see, PMM server generates about 80 IOPS on HDD, maybe that is the problem?
Is there some recommendations about PMM server hardware config?
Wow your server is indeed pretty slow one, especially HDD might be the problem. I have very low powered Intel Atom which is still able to handle few servers
You should go to System Overview and pick “pmm-server” as a host. This way you will be able to view resource usage.
Low traffic MySQL server works OK (mysql, linux and query metrics). With high-traffic server there’s no query data (no problems with mysql and linux metrics).
I attached load metrics from PMM server. At 19.10 I enabled stats from high traffic (in addition to low-traffc).
As far as I understand the bottleneck is HDD iops. Is threre some way to reduce iops by tuning PMM?
The best bet for you would be to change MySQL Server settings, which is likely the main cause of your disk IO
To do it:
Enter Docker Container:
docker exec -it pmm-server bash
vi /etc/my.cnf.d/00-pmm.cnf
Change innodb_buffer_pool_size=128M to amount of memory you would like to allocate to MySQL. Note it is not the only memory user is PMM. For 6GB you should set it to “1G” or so
Run “mysqladmin shutdown”
SupervisorD will automatically restart MySQL for you
We should make MySQL option settings better in the future
First, I tried your advice - changed innodb_buffer_pool_size to 1G. But that didn’t work.
Then I noticed innodb_flush_log_at_trx_commit = 1. It was a root cause of 80 write IOPS. I changed it to innodb_flush_log_at_trx_commit = 2 and now everything works like a charm.
I now that it breaks durability, but what data is collected in MySQL?
For this, you will need to stop the current pmm-server container, remove it, and start a new one with additional options enabled. Check [url]
