PMM 3.1 MySQL custom query not working

Percona Monitoring and Management (PMM) PMM 3.x
,
1

Hi

I have been trying to get custom queries to work by pushing a custom metric via the file in:

/usr/local/percona/pmm/collectors/custom-queries/mysql/high-resolution/queries-mysqld.yml

mysql_info_schema_custom:
  query: "SELECT t.table_schema AS 'schema', t.table_name as 'table', t.data_length + t.index_length AS total_size FROM information_schema.TABLES t WHERE table_type = 'BASE_TABLE' AND t.table_schema NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys');"
  metrics:
    - schema:
        usage: "LABEL"
        description: "Schema Name"
    - table:
        usage: "LABEL"
        description: "Table Name"
    - total_size:
        usage: "GAUGE"
        description: "Total Size of table"

In PMM, under Advanced Data Exploration dashboard the custom metric does not get picked up.

In the screenshot it shows that the perfschema collector is pushing every minute (not sure if the same should apply to the mysqld_exporter - but do not see that pushed every minute) in the /var/log/messages logs.

image (2)
image (2)1520×892 127 KB

At the moment, I am stuck as I have seen others posted that their custom queries or custom metrics are not being picked up - but with no solution (or follow up).

Any guidance would be appreciated (unless this is a bug that has yet to be addressed).

Thank you

3

@wazza

The query itself doesn’t looks correct. It returns empty.

mysql> SELECT t.table_schema AS 'schema', t.table_name as 'table', t.data_length + t.index_length AS total_size FROM information_schema.TABLES t WHERE table_type = 'BASE_TABLE' AND t.table_schema NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys');
Empty set (0.00 sec)

Instead BASE_TABLE it should be BASE TABLE

WHERE table_type = 'BASE TABLE'

With this change query it returns the rows. Please verify if this fix the issues.

mysql> SELECT t.table_schema AS 'schema', t.table_name as 'table', t.data_length + t.index_length AS total_size FROM information_schema.TABLES t WHERE table_type = 'BASE TABLE' AND t.table_schema NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys');
+--------+-------+------------+
| schema | table | total_size |
+--------+-------+------------+
| test   | t1    |      16384 |
+--------+-------+------------+
1 row in set (0.00 sec)

Let me also share a BP- Running Custom MySQL Queries in Percona Monitoring and Management in reference to custom query implementation.

Was this query running fine with the older version ? Are there any other custom queries which works fine in your environment ? Can you share the screenshot of the dashboard where you checking or observed missing ?

4

Hi @anil.joshi

I have updated the query (realised it afterwards) but the outcome is still the same.

This is a fresh new installation of 3.1 - no previous version of PMM - as we are testing PMM and doing comparisons. The custom query was just to test it as we would like to detect GTID errant transactions via PMM.

5

Is anyone experiencing issues with custom queries on a new installation of PMM?