MySQL Table Details Not showing

[LTjikana]

Description:

My PMM MySQL Table Details not showing

Steps to Reproduce:

Ive setup a number of ppm clients connecting to my docker deployed PMM server, sees below:

1. yum install -y pmm2-client
2. pmm-admin config --server-insecure-tls --server-url=https://admin:admin@172.x.x.x

Version:

2.41.1

Logs:

3. Admin Status:

Agent ID : /agent_id/c32f7d5a-cb2c-4401-bfe3-62072d1968ff
Node ID  : /node_id/e125deaa-04a5-4eb1-a3d4-1c98cf8ed4d5
Node name: ppm_host

PMM Server:
	URL    : https://172.x.x.x:443/
	Version: 2.41.0

PMM Client:
	Connected        : true
	Time drift       : 561.658µs
	Latency          : 763.362µs
	Connection uptime: 100
	pmm-admin version: 2.41.0
	pmm-agent version: 2.41.0
Agents:
	/agent_id/3c49d6d1-af87-45ce-914d-fb2f29ed6e86 vmagent Running 42000
	/agent_id/9113b2ba-f081-408b-bb95-72e7dbb1dedf node_exporter Running 42001

4. Create DB User on MySQL Instance (Percona 8.0.34)

CREATE USER IF NOT EXISTS 'pmm'@'127.0.0.1' IDENTIFIED BY 'admin' WITH MAX_USER_CONNECTIONS 10;
GRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD, BACKUP_ADMIN ON *.* TO 'pmm'@'127.0.0.1';
SET PERSIST innodb_monitor_enable = ALL;

5. pmm-admin add mysql --username=pmm --password=admin name 127.0.0.1:3311 --query-source=perfschema --disable-tablestats-limit=0

pmm-admin list
Service type        Service name                         Address and port        Service ID
MySQL                name        127.0.0.1:3313          /service_id/54bf3655-f6bc-4611-8583-7e37a9ec1e59

Agent type                    Status           Metrics Mode        Agent ID                                              Service ID                                              Port
pmm_agent                     Connected                            /agent_id/54bf3655-f6bc-4611-8583-7e37a9ec1e59                                                                0
node_exporter                 Running          push                /agent_id/745537ed-3b88-4502-b2be-71510de6dd85                                                                42001
mysqld_exporter               Running          push                /agent_id/d141b953-1e02-4c71-8c99-b3687429f550        /service_id/54bf3655-f6bc-4611-8583-7e37a9ec1e59        42002
mysql_perfschema_agent        Running                              /agent_id/279dd54f-7339-4f4e-bcf5-10c405404a70        /service_id/54bf3655-f6bc-4611-8583-7e37a9ec1e59        0
vmagent                       Running          push                /agent_id/35a1975e-77de-4250-a6b5-116deb6af324                                                                42000

Expected Result:

for me to see MySQL Table data on PMM Dash

Actual Result:

The data existed before, we had to re-install the agents and the data disappeared on my dashboards.

Additional Information:

None

[matthewb]

@LTjikana I looked through the mysqld_exporter code as best I could. I don’t think ‘0’ is a proper value and is disabling collection of stats. Please remove and re-add the agent and specify a large number, 10,000 or so and see if that works.

[LTjikana]

Hi @matthewb , no luck…

pmm-admin add mysql --username=pmm --password=P*** hostname 127.0.0.1:3315 --query-source=perfschema --disable-tablestats-limit=10000
MySQL Service added.
Service ID  : /service_id/f54c4330-7191-4a75-a6a8-ff5da4c9945a
Service name: hostname

Table statistics collection enabled (the limit is 10000, the actual table count is 821).

Table stats still not showing…

[matthewb]

I see you have Service Name: $_all, have you changed this to a specific server?

[LTjikana]

Hi Mathew,

Nope, same results… server stats do not come through even after changing the limit on 3 of my servers to test.

[matthewb]

In that screenshot, you have “Service Name: --” which means you are not looking at any data. Please select a specific host.

[LTjikana]

There are no hosts that come through to that page… All my DB Nodes come through on all other pages/views with exception to the one we are troubleshooting.

Is there remedial action for this or does this page not work at all?

[Yunus]

If you have more than 1,000 tables then Table Details is disabled.

Percona Monitoring and Management - Optimize

Here in this document you can extend a limit if your tables are more and you need table statistics while adding a mysql service.

[LTjikana]

Hi Yunus,

Its worth noting that > 90% of my db estate has less than 1000 tables per instance.

I did that already on 3/165 nodes to test that theory and still nothing.

[matthewb]

Hello @LTjikana
For the record, this feature absolutely does work, and this feature has been around in PMM for a long time.

PMM Demo - MySQL Table Details

It is hard to determine if the table stats queries are even running. What I would do is find a mysql server with very low load, and enable the general query log. Watch this log for for this query. If you never see this query within 1 hour, then there is a misconfiguration somewhere.

Feel free to run that query manually yourself and see if anything is generated. If nothing is returned when you execute it, then you have a misconfiguration with your MySQL server.

[LTjikana]

Hi, What’s confusing to me is that this information/dash was there a couple of weeks ago when I first installed PMM, So Im hesitant that there is a local DB config issue on all my instances since nothing has gone into any of my prod instances since then from a MySQL config pov…

What did change is that I had to re-install PMM on a separate, bigger mount point and as such lost all my agents and as such had to re-enroll them and ever since this page and the QAN page are wonky ever since.

Quick Question Re the perfschema_exporter, should the metrics_mode be empty?

pmm-admin list
Service type        Service name           Address and port        Service ID
MySQL               srv-r1-l-ig-03        127.0.0.1:5511          /service_id/571e4f51-2583-4ce7-9525-ef435b9e3f9c

Agent type                    Status           Metrics Mode        Agent ID                                              Service ID                                              Port
pmm_agent                     Connected                            /agent_id/87b23baf-e704-42ec-a49f-e072cddcdb20                                                                0
node_exporter                 Running          push                /agent_id/7e8234bb-e2e0-4bba-82f6-dc740aac5d2f                                                                42000
mysqld_exporter               Running          push                /agent_id/cb47a7de-be84-4474-9900-239fbbfe23c0        /service_id/571e4f51-2583-4ce7-9525-ef435b9e3f9c        42002
mysql_perfschema_agent        Running                              /agent_id/4b5c9e6c-4b90-48e9-842e-661fffe13790        /service_id/571e4f51-2583-4ce7-9525-ef435b9e3f9c        0
vmagent                       Running          push                /agent_id/d4ab7394-f3ed-4ec6-8936-3981ff94ecb8                                                                42001