Custom Query : not showing up?

Percona Monitoring and Management (PMM) PMM 2.x
1

I’ve tried creating a custom query to check for partitions older than a certain date to assist in data retention management.
I’ve added a query to :
/usr/local/percona/pmm2/collectors/custom-queries/mysql/low-resolution/queries-mysqld.yml


custom_mysql_partition_check: ## The namespace (prefix of the metric name) for the custom query.
    query: "select count(1) as CNT,TABLE_SCHEMA, TABLE_NAME from information_schema.partitions where table_name = '[tablename]' and str_to_date( replace(PARTITION_DESCRIPTION,"'","") , '%Y-%m-%d' ) <= date_add(date_add(LAST_DAY(NOW()),interval 1 DAY),interval -4 MONTH) GROUP BY TABLE_SCHEMA,TABLE_NAME;"
    metrics:
      - COUNT:
         usage: "COUNTER"
         description: "Number of old partitions"
      - TABLE_NAME:
         usage: "LABEL"
         description: "Table name"
      - PARTITION_NAME:
         usage: "LABEL"
         description: "Partition Name"
      - TABLE_SCHEMA:
         usage: "LABEL"
         description: "Schema Name"

howewever, I’m not seeing the custom query in the dashboard dropdown under advanced data exploration, but nor am I seeing any errors in my pmm-agent.log

Have I missed something in how to enable the custom queries or the parsing of the path and yml file of the custom queries?

1 Like
2

Hi @Andrew2,
Have you restarted pmm-agent or mysqld_exporter?

1 Like
3

I have the same problem. Queries are working and metrics give information if you look at the client.

pg_buffercache_buffered{collector=“custom_query.lr”,datname=“1”,relname=“1”,server=“10.1.1.2:5432”} 2
HELP pg_buffercache_buffers_percent
TYPE pg_buffercache_buffers_percent gauge
pg_buffercache_buffers_percent{collector=“custom_query.lr”,datname=“1”,relname=“1”,server=“10.1.1.2:5432”} 3
HELP pg_buffercache_percent_of_relation
TYPE pg_buffercache_percent_of_relation gauge
pg_buffercache_percent_of_relation{collector=“custom_query.lr”,datname=“1”,relname=“1”,server=“10.1.1.2:5432”} 4

But then comes the error message. And I don’t get these metrics data on the server.

HELP pg_exporter_user_queries_load_error Whether the user queries file was loaded and parsed successfully (1 for error, 0 for success).
TYPE pg_exporter_user_queries_load_error gauge
pg_exporter_user_queries_load_error{collector=“custom_query.lr”,filename=“/usr/local/percona/pmm2/collectors/custom-queries/postgresql/low-resolution/pg_buffercashe.yml”,hashsum=“69483915140e073ccf88d6de9b0856477060d3c540609ce86d7d5b8ef00450a3”} 0

1 Like
4

Hi @andrrreasss, by saying “Queries are working and metrics give information” do you mean that you checked exporter ran by PMM or standalone? Could you check file permissions?

1 Like
5

@nurlan, exporter who launched PMM

1 Like