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!!!