Hi, I’m just testing a few of our percona replication servers version 5.7.
I registered them successfully and added them to the MySQL service. As far as I can tell they are all the same for how I set MySQL variables and created the mysql user. For some reason only the pmm server and the first one I added are listed under Node Name in PMM Query Analytics. There’s no expansion or show all option. They just aren’t there. I can see them under PMM Inventory/ Services and they look like the same setup as the one that is listed.
Is there something I’m potentially over looking? Or is there a bug? I tested a manual connection to 3306 on the 2 servers that are not listed under query analytics. Everything in terms of permissions and firewalls and networks looks good. I’ll be happy to provide more details and screenshots if that’s helpful.
Thank you.
1 Like
Hi Trevor,
What source of SQL queries is used? It can be slowlog or perfschema (default: slowlog).
1 Like
Oh sorry for leaving that out. I went with slowlog because a few of the variables for perfschema were missing from our installation (at least I didn’t see them with a show global variables), so I figured i’d get better data from slowlog.
1 Like
Could you make sure that services have got recommended settings?
1 Like
sorry for the slow replies. Been distracted at work lately.
Yes, i have carefully set the variables in mysql and it is accumulating slow queries in the log file in /var/lib/mysql.
I have no idea why when I add the mysql service the nodes don’t show up in the queries section. I’ve looked at logs and haven’t seen much in the way of clues.
1 Like
Do you think maybe I should try adding the mysql service from the command line on the client rather than from the front end on the server?
1 Like
one other thing. Percona documentation says to change the name of the slow log file to “slow_query_log_file” in order for the variables that control size and number of slow log files to actually work. If pmm is using mysql-slow.log by default, then that isn’t being updated after my changes which would explain why the servers aren’t showing up in PMM Query Analytics.
Thanks.
1 Like
So I switched to schema and the service is added to the Analytics. I don’t understand why slow query log won’t add the service. The documents recommend slow query vs schema for percona. I’m not sure how much more detail I’d get. It’s good to see that some queries are taking ages, like 40 seconds, but how much more useful would the detail be to solve why they are taking a long time? My frist guess is temp tables are being created on disk because there are 40K rows, but I’m not a DBA, so just an educated guess.
1 Like
Hi Trevor,
So I switched to schema and the service is added to the Analytics. I don’t understand why slow query log won’t add the service.
If a mysql service is added as a remote MySQL instance then only perfschema source can work.
It’s not possible to get access to files on a remote server.
It’s good to see that some queries are taking ages, like 40 seconds, but how much more useful would the detail be to solve why they are taking a long time? My frist guess is temp tables are being created on disk because there are 40K rows, but I’m not a DBA, so just an educated guess.
You can get more information about a query in Details tab. Maybe it’s required to create indexes for some tables.
e.g.
1 Like
Okay,
Thank you very much. I do recall reading that mysql slow query had to be on the same server, but I thought maybe that meant as the pmm-agent. Makes sense now. Thank you for clarifying. Thankfully I didn’t waste too much time banging my head trying to get it to work. Schema is at least a start 
1 Like
