Hi all,
I’m running PMM 2.32.0 in a docker container. I’m currently monitoring two Percona 5.7 DBs. I perform the following navigation in PMM:
On the left, I hover over MySQL and click on “Table details”
All of the graphs say “No data”. I’ve followed the documentation for setup, and I see tons of data on other screens. Is there something specific to be done to enable data to populate this screen? Perhaps that I overlooked? Any help would be greatly appreciated.
Please note, per the documentation, I am using the Slow Query Log configuration, as opposed to using the Performance Schema (MySQL - Percona Monitoring and Management).
Thanks!
Hi @ultrapain welcome back to the Percona forums!
I suspect you do not have performance_schema set up in order to expose this information. Can you refer to the setup_consumers table and verify you have the following consumers enabled:
mysql> select * from setup_consumers where ENABLED = 'YES';
+---------------------------+---------+
| NAME | ENABLED |
+---------------------------+---------+
| events_statements_current | YES |
| events_statements_history | YES |
| global_instrumentation | YES |
| thread_instrumentation | YES |
| statements_digest | YES |
+---------------------------+---------+
5 rows in set (0.00 sec)
Thanks for the quick reply!
Per the documentation, I am using the Slow Query Logs as my source, versus using the Performance Schema. Does that mean that the data I’m looking for won’t be available with this setup?
Hello @ultrapain,
The information on table size, free space, etc, comes from the information-schema. Does the MySQL PMM user have access to this schema?
That user has the following perms:
select, reload, process, replication client on .
Regarding the setup_consumers table, the results of that select statement against my PMM client DB matches that of yours.
This is my first time setting up PMM, so I admit that I know next to nothing about this, but I wanted to restate that I followed the guidelines for setting up the client to use the Slow Query Log, since that is what is recommended for my version of Percona. Specifically, I performed the following steps:
Enable the following in my.cnf:
slow_query_log=ON
log_output=FILE
long_query_time=0
log_slow_admin_statements=ON
log_slow_slave_statements=ON
log_slow_rate_limit=100
log_slow_rate_type=‘query’
slow_query_log_always_write_time=1
log_slow_verbosity=‘full’
slow_query_log_use_global_control=‘all’
I restarted the DB after adding the above parms to my.cnf. Anything you see that I may have missed?
I just updated the configuration to use the Performance Schema by performing the following actions:
I commented out the following items in /etc/my.cnf:
slow_query_log=ON
log_output=FILE
long_query_time=0
log_slow_admin_statements=ON
log_slow_slave_statements=ON
log_slow_rate_limit=100
log_slow_rate_type=‘query’
slow_query_log_always_write_time=1
log_slow_verbosity=‘full’
slow_query_log_use_global_control=‘all’
Added the following to /etc/my.cnf:
performance_schema=ON
performance-schema-instrument=‘statement/%=ON’
performance-schema-consumer-statements-digest=ON
innodb_monitor_enable=all
query_response_time_stats=ON
userstat=ON
Ran the following against the DB:
INSTALL PLUGIN QUERY_RESPONSE_TIME_AUDIT SONAME ‘query_response_time.so’;
INSTALL PLUGIN QUERY_RESPONSE_TIME SONAME ‘query_response_time.so’;
INSTALL PLUGIN QUERY_RESPONSE_TIME_READ SONAME ‘query_response_time.so’;
INSTALL PLUGIN QUERY_RESPONSE_TIME_WRITE SONAME ‘query_response_time.so’;
Restarted the DB, and I still do not see any table data.
Can you provide the exact command you used to add this MySQL server to PMM?
I added the node via the GUI. However, I recalled seeing a “use performance schema” checkbox, which I hadn’t previously checked (because I wasn’t using that). I couldn’t figure out a way to update the existing node to use the performance schema, so I removed and re-added it. It’s our testing region/DB, so I currently am using slow query log and performance schema because I am not concerned with performance hits in our test system. However, I’m still not seeing any table metrics.
You should have added the MySQL servers via command-line. That would have enabled tablestats by default. IIRC, in the GUI, you have to enable table stats. It should have been an option near the very end. Also, you should use slow query log for analytics as the information is better than what is available in perf schema. Remove both nodes, ssh to both nodes, add them via pmm-admin (see docs) and you’ll be fine.
@matthewb thanks for your help/guidance. Indeed, adding via the command line was the way to go. The documentation states to choose one or the other (perf schema or slow query), and that using both may incur a performance hit. Should I disregard that/not worry about it?
I wouldn’t worry about it. Perf schema is enabled by default and slow log can be changed if you ever reach 10,000+ connections.
Again, much thanks for the guidance and information - much appreciated!!!
Hi @mathewb apologies to just jump on this thread like this but im also experiencing the same however I used the --disable-tablestats-limit argument when adding the nodes with the following results:
pmm-admin add mysql --username=pmm --password=PXXX__ dbhostname_3321 127.0.0.1:3321 --query-source=perfschema --disable-tablestats-limit=3000
MySQL Service added.
Service ID : /service_id/982b6b7a-7f01-45b8-b4bc-ff4fb6fd7936
Service name: dbhostname_3321
Table statistics collection enabled (the limit is 3000, the actual table count is 2892).
I still however do not see any data on the table details tab/page
@LTjikana Check the error logs on that server. Make sure there are no permissions issues.
Hi Mathew,
luckily don’t rotate error logs so No Errors in my DB error log…
unless if there is another error log associated to the pmm-client I can check?
Thanks in advance!
There are PMM-specific error logs. I think you can get them from journalctl
Hi Mathew,
thanks for the information, do you mind sharing the exact command to get the logs for the pam-specific error logs?
sudo journalctl -xe and look for PMM messages. Use grep to filter.
Hi Mathew,
See attached logs from my Pam server, I think that’s the best place to get the logs since none of my 168 monitored hosts shows table details.
please let me know if you see anything amiss there.
pmm-server-logs.txt (101.8 KB)
Hello @LTjikana,
Unfortunately, no, the PMM server is not the best place for this. You need to look at the agent logs on one of the servers since it is the agents themselves that collect the data.