Skip to content

armgit / core

Public Imported from GitHub
0

100% open-source IoT Platform - Integrate your devices, add intelligence, build custom apps

16.8 KB
Raw Blame
# Query Exporter Configuration for OpenRemote PostgreSQL Monitoring
# Documentation: https://github.com/albertodonato/query-exporter/blob/main/docs/configuration.rst

databases:
  openremote:
    dsn:
      dialect: postgresql
      user: !env POSTGRES_USER
      password: !env POSTGRES_PASSWORD
      host: !env POSTGRES_HOST
      port: !env POSTGRES_PORT
      database: !env POSTGRES_DB
    labels:
      instance: openremote

metrics:
  pg_collation_mismatch_count:
    type: gauge
    description: Number of text indexes with collation version mismatches requiring a REINDEX

  pg_cache_hit_percentage:
    type: gauge
    description: What percentage of data is being served instantly from RAM versus being slowly read from disk. You want this as high as possible

  pg_connections_limit:
    type: gauge
    description: Count of connections max limit

  pg_connections_used:
    type: gauge
    description: Count of connections in use

  pg_connections_free:
    type: gauge
    description: Count of connections available

  pg_connections_stuck:
    type: gauge
    description: Count of connections with state of idle in transaction

  pg_long_running_queries_count:
    type: gauge
    description: Count of queries that have been running for longer than 30 seconds

  pg_longest_query_duration_seconds:
    type: gauge
    description: Duration of the longest running query in seconds

  pg_hot_update_percent:
    type: gauge
    labels: [table_name]
    description: Table percentage of updates that are HOT updates indicates good fillfactor

  pg_dead_tuple_percent:
    type: gauge
    labels: [table_name]
    description: Table ratio of dead tuples to live ones a ratio > 10-20% indicates not aggressive enough autovacuum

  pg_last_autovacuum_hours:
    type: gauge
    labels: [table_name]
    description: Table hours since last auto vacuum run successfully

  pg_last_autoanalyze_hours:
    type: gauge
    labels: [table_name]
    description: Asset table hours since last auto analyze run successfully

  pg_db_disk_size:
    type: gauge
    description: DB size in MB

  pg_datapoint_raw_data_size:
    type: gauge
    description: Asset datapoint table raw uncompressed size in MB

  pg_datapoint_indexes_size:
    type: gauge
    description: Asset datapoint table indexes size in MB

  pg_datapoint_toast_size:
    type: gauge
    description: Asset datapoint TOAST table size in MB

  pg_datapoint_disk_size:
    type: gauge
    description: Asset datapoint table size in MB

  pg_datapoint_data_index_ratio:
    type: gauge
    description: Asset datapoint table index size to data size ratio (over 1 indicates an possible issue)

  pg_datapoint_chunk_count:
    type: gauge
    description: Asset datapoint table hypertable chunk count

  pg_datapoint_uncompressed_chunk_count:
    type: gauge
    description: Asset datapoint table hypertable uncompressed chunk count

  pg_datapoint_chunks_needing_compression:
    type: gauge
    description: Asset datapoint table hypertable chunks needing compression count

  pg_datapoint_chunk_start_weeks:
    type: gauge
    description: Asset datapoint table oldest hypertable chunk in weeks

  pg_datapoint_chunk_end_weeks:
    type: gauge
    description: Asset datapoint table newest hypertable chunk in weeks

  pg_datapoint_chunks_not_analyzed:
    type: gauge
    description: Asset datapoint table hypertable chunks not yet analyzed

  pg_datapoint_largest_uncompressed_chunk:
    type: gauge
    description: Asset datapoint table largest uncompressed hypertable chunk in MB

  pg_datapoint_uncompressed_cache_hit_ratio:
    type: gauge
    description: Asset datapoint table cache hit ratio for uncompressed chunks (Aim for 99%+)

  pg_datapoint_uncompressed_blks_read_total:
    type: counter
    description: Asset datapoint total physical disk blocks read for uncompressed chunks (Monitor rate with spikes indicate RAM spillover)

  pg_datapoint_compression_ratio:
    type: gauge
    description: Asset datapoint table compression ratio

  pg_datapoint_query:
    type: gauge
    description: Dummy metric to get typical query time metric

  pg_background_errors:
    type: counter
    description: Count of errors in background worker processes

  pg_timescale_job_total_runs:
    type: counter
    labels: [job_id, proc_name]
    description: TimescaleDB job total runs by job

  pg_timescale_job_total_failures:
    type: counter
    labels: [job_id, proc_name]
    description: TimescaleDB job total failures by job

  pg_timescale_job_last_run_duration_seconds:
    type: gauge
    labels: [job_id, proc_name]
    description: TimescaleDB job last run duration in seconds

  pg_timescale_job_next_start_seconds:
    type: gauge
    labels: [job_id, proc_name]
    description: Seconds until next scheduled run for each TimescaleDB job

  pg_timescale_job_last_run_status:
    type: gauge
    labels: [job_id, proc_name, last_run_status]
    description: TimescaleDB job last run status marker (1)

  pg_wal_total:
    type: counter
    description: Total WAL written since statistics reset in MB

  pg_bgwriter_checkpoints_timed_total:
    type: counter
    description: Scheduled checkpoints executed

  pg_bgwriter_checkpoints_req_total:
    type: counter
    description: Requested checkpoints executed

  pg_bgwriter_checkpoint_write_time_seconds_total:
    type: counter
    description: Total time spent writing checkpoints in seconds

  pg_bgwriter_checkpoint_sync_time_seconds_total:
    type: counter
    description: Total time spent syncing checkpoints in seconds

  pg_table_bloat_count:
    type: gauge
    description: Number of tables where dead tuples > 30% of live rows

  pg_index_bloat_count:
    type: gauge
    description: Number of indexes that are larger than 150% of table size

queries:
  collation_mismatch:
    interval: 86400 # Daily check
    databases: [openremote]
    metrics: [pg_collation_mismatch_count]
    sql: |
      WITH mismatched_collations AS (
        SELECT oid 
        FROM pg_catalog.pg_collation 
        WHERE collversion IS NOT NULL 
          AND collversion <> pg_collation_actual_version(oid)
      ),
      mismatched_indexes AS (
        SELECT DISTINCT d.objid AS index_oid
        FROM pg_catalog.pg_depend d
        JOIN mismatched_collations mc ON d.refobjid = mc.oid
        WHERE d.classid = 'pg_class'::regclass
          AND d.refclassid = 'pg_collation'::regclass
      )
      SELECT count(i.oid) AS pg_collation_mismatch_count
      FROM pg_catalog.pg_class i
      JOIN pg_catalog.pg_index idx ON i.oid = idx.indexrelid
      JOIN mismatched_indexes mi ON i.oid = mi.index_oid
      WHERE i.relkind = 'i';

  cache_hit_ratio:
    interval: 3600 # Hourly
    databases: [openremote]
    metrics: [pg_cache_hit_percentage]
    sql: |
      SELECT 
        ROUND(sum(heap_blks_hit)::numeric / (sum(heap_blks_hit) + sum(heap_blks_read) + 0.001) * 100, 2) as pg_cache_hit_percentage
        FROM pg_statio_user_tables;

  connection_state:
    interval: 300 # 5 minutes
    databases: [openremote]
    metrics: [pg_connections_limit, pg_connections_used, pg_connections_free, pg_connections_stuck]
    sql: |
      SELECT
        max_conn.max_limit as pg_connections_limit,
        active.total_used as pg_connections_used,
        (max_conn.max_limit - active.total_used) AS pg_connections_free,
        idle_xact.idle_in_xact_count as pg_connections_stuck
        FROM
        (SELECT current_setting('max_connections')::int AS max_limit) max_conn,
        (SELECT count(*) AS total_used FROM pg_stat_activity) active,
        (SELECT count(*) AS idle_in_xact_count FROM pg_stat_activity WHERE state = 'idle in transaction') idle_xact;

  long_running_queries:
    interval: 60 # 1 minute
    databases: [openremote]
    metrics: [pg_long_running_queries_count, pg_longest_query_duration_seconds]
    sql: |
      SELECT 
        COUNT(*)::numeric AS pg_long_running_queries_count,
        COALESCE(EXTRACT(EPOCH FROM MAX(now() - query_start)), 0)::numeric AS pg_longest_query_duration_seconds
      FROM pg_stat_activity
      WHERE state = 'active' 
        AND now() - query_start > interval '30 seconds'
        AND pid <> pg_backend_pid();

  table_tuples:
    interval: 300 # 5 minutes
    databases: [openremote]
    metrics: [pg_hot_update_percent, pg_dead_tuple_percent, pg_last_autovacuum_hours, pg_last_autoanalyze_hours]
    sql: |
      SELECT
        relname as table_name,
        CASE
        WHEN n_tup_upd > 0
        THEN ROUND((n_tup_hot_upd::numeric / n_tup_upd) * 100, 2)
        ELSE 0.00
        END AS pg_hot_update_percent,
        -- Calculate the percentage of the table that is dead space
        ROUND((n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0)) * 100, 2) AS pg_dead_tuple_percent,
        -- Calculate hours since last autovacuum
        ROUND((EXTRACT(EPOCH FROM (now() - last_autovacuum)) / 3600.0)::numeric, 1) AS pg_last_autovacuum_hours,
        -- Calculate hours since last autoanalyze
        ROUND((EXTRACT(EPOCH FROM (now() - last_autoanalyze)) / 3600.0)::numeric, 1) AS pg_last_autoanalyze_hours
        FROM pg_stat_user_tables
        WHERE schemaname = 'openremote';

  db_size:
    interval: 3600 # Hourly
    databases: [openremote]
    metrics: [pg_db_disk_size]
    sql: |
      SELECT ROUND(pg_database_size(current_database()) / 1048576.0, 0) as pg_db_disk_size;

  datapoint_table_size:
    interval: 3600 # Hourly
    databases: [openremote]
    metrics:
      [
        pg_datapoint_raw_data_size,
        pg_datapoint_indexes_size,
        pg_datapoint_toast_size,
        pg_datapoint_disk_size,
        pg_datapoint_data_index_ratio,
      ]
    sql: |
      SELECT 
        ROUND(table_bytes / 1048576.0, 0) AS pg_datapoint_raw_data_size,
        ROUND(index_bytes / 1048576.0, 0) AS pg_datapoint_indexes_size,
        ROUND(toast_bytes / 1048576.0, 0) AS pg_datapoint_toast_size,
        ROUND(total_bytes / 1048576.0, 0) AS pg_datapoint_disk_size,
        ROUND(index_bytes::numeric / NULLIF(table_bytes + toast_bytes, 0), 2) AS pg_datapoint_data_index_ratio
        FROM hypertable_detailed_size('asset_datapoint');

  datapoint_chunks:
    interval: 86400 # Daily
    databases: [openremote]
    metrics:
      [
        pg_datapoint_chunk_count,
        pg_datapoint_uncompressed_chunk_count,
        pg_datapoint_chunks_needing_compression,
        pg_datapoint_chunk_start_weeks,
        pg_datapoint_chunk_end_weeks,
      ]
    sql: |
      SELECT
        count(*) AS pg_datapoint_chunk_count,
        -- Count chunks that are NOT compressed
        count(*) FILTER (WHERE is_compressed = false) AS pg_datapoint_uncompressed_chunk_count,
        -- Count chunks older than 7 days (604800 seconds) that are NOT compressed
        count(*) FILTER (
        WHERE is_compressed = false
        AND range_end < (now() - INTERVAL '7 days')
        ) AS pg_datapoint_chunks_needing_compression,
        ROUND((EXTRACT(EPOCH FROM (now() - min(range_start::timestamptz))) / 604800.0)::numeric, 1) AS pg_datapoint_chunk_start_weeks,
        ROUND((EXTRACT(EPOCH FROM (now() - max(range_end::timestamptz))) / 604800.0)::numeric, 1) AS pg_datapoint_chunk_end_weeks
      FROM timescaledb_information.chunks
      WHERE hypertable_name = 'asset_datapoint'
      GROUP BY hypertable_name;

  datapoint_largest_uncompressed_chunk:
    interval: 86400 # Daily
    databases: [openremote]
    metrics: [pg_datapoint_largest_uncompressed_chunk]
    sql: |
      SELECT 
        ROUND(s.total_bytes / 1048576.0, 0) AS pg_datapoint_largest_uncompressed_chunk
      FROM timescaledb_information.chunks c
      JOIN chunks_detailed_size('asset_datapoint') s
      ON c.chunk_schema = s.chunk_schema
      AND c.chunk_name = s.chunk_name
      WHERE c.is_compressed = false
      ORDER BY s.total_bytes DESC
      LIMIT 1;

  datapoint_autoanalyze:
    interval: 86400 # Daily
    databases: [openremote]
    metrics: [pg_datapoint_chunks_not_analyzed]
    sql: |
      SELECT 
        count(*) AS pg_datapoint_chunks_not_analyzed
        FROM pg_stat_user_tables
        WHERE relname LIKE '_hyper_%'
        AND last_autoanalyze IS NULL
        AND last_analyze IS NULL
        AND n_mod_since_analyze > current_setting('autovacuum_analyze_threshold')::numeric;

  datapoint_historical_aggregation:
    interval: 3600 # Hourly
    databases: [openremote]
    metrics: [pg_datapoint_query]
    sql: |
      SELECT datapoint_metric_query() as pg_datapoint_query;

  uncompressed_memory_pressure:
    interval: 3600 # Hourly
    databases: [openremote]
    metrics: [pg_datapoint_uncompressed_cache_hit_ratio, pg_datapoint_uncompressed_blks_read_total]
    sql: |
      WITH uncompressed_chunks AS (
        SELECT chunk_name
        FROM timescaledb_information.chunks
        WHERE hypertable_name = 'asset_datapoint'
          AND is_compressed = false
      )
      SELECT
        -- Calculate the percentage of data served directly from RAM
        ROUND(
          COALESCE(
            (sum(s.heap_blks_hit) + sum(s.idx_blks_hit) + sum(s.toast_blks_hit))::numeric / 
            NULLIF(
              sum(s.heap_blks_hit) + sum(s.idx_blks_hit) + sum(s.toast_blks_hit) + 
              sum(s.heap_blks_read) + sum(s.idx_blks_read) + sum(s.toast_blks_read), 
            0) * 100, 
          0), 
        2) AS pg_datapoint_uncompressed_cache_hit_ratio,

        -- Track the raw number of times Postgres had to go to the physical disk
        COALESCE(sum(s.heap_blks_read) + sum(s.idx_blks_read) + sum(s.toast_blks_read), 0) AS pg_datapoint_uncompressed_blks_read_total
      FROM pg_statio_user_tables s
      JOIN uncompressed_chunks c ON s.relname = c.chunk_name;

  compression_stats:
    interval: 86400 # Daily
    databases: [openremote]
    metrics: [pg_datapoint_compression_ratio]
    sql: |
      SELECT 
        COALESCE(
          (before_compression_total_bytes::numeric / NULLIF(after_compression_total_bytes, 0)), 
          1.0
        ) AS pg_datapoint_compression_ratio
      FROM hypertable_compression_stats('asset_datapoint');

  background_errors:
    interval: 3600 # Hourly
    databases: [openremote]
    metrics: [pg_background_errors]
    sql: |
      SELECT
        SUM(total_failures) AS pg_background_errors
        FROM timescaledb_information.job_stats;

  timescale_job_stats:
    interval: 3600 # Hourly
    databases: [openremote]
    metrics:
      [
        pg_timescale_job_total_runs,
        pg_timescale_job_total_failures,
        pg_timescale_job_last_run_duration_seconds,
        pg_timescale_job_next_start_seconds,
        pg_timescale_job_last_run_status,
      ]
    sql: |
      SELECT
        s.job_id::text AS job_id,
        CONCAT(j.proc_schema, '.', j.proc_name) AS proc_name,
        COALESCE(s.last_run_status, 'unknown') AS last_run_status,
        COALESCE(s.total_runs, 0) AS pg_timescale_job_total_runs,
        COALESCE(s.total_failures, 0) AS pg_timescale_job_total_failures,
        EXTRACT(EPOCH FROM COALESCE(s.last_run_duration, INTERVAL '0 seconds')) AS pg_timescale_job_last_run_duration_seconds,
        EXTRACT(EPOCH FROM (COALESCE(s.next_start, now()) - now())) AS pg_timescale_job_next_start_seconds,
        1 AS pg_timescale_job_last_run_status
      FROM timescaledb_information.job_stats s
      JOIN timescaledb_information.jobs j ON j.job_id = s.job_id
      WHERE j.hypertable_name = 'asset_datapoint';

  write_path_pressure:
    interval: 300 # 5 minutes
    databases: [openremote]
    metrics:
      [
        pg_wal_total,
        pg_bgwriter_checkpoints_timed_total,
        pg_bgwriter_checkpoints_req_total,
        pg_bgwriter_checkpoint_write_time_seconds_total,
        pg_bgwriter_checkpoint_sync_time_seconds_total,
      ]
    sql: |
      SELECT
        (w.wal_bytes / 1048576.0) AS pg_wal_total,
        c.num_timed AS pg_bgwriter_checkpoints_timed_total,
        c.num_requested AS pg_bgwriter_checkpoints_req_total,
        ROUND((c.write_time / 1000.0)::numeric, 0) AS pg_bgwriter_checkpoint_write_time_seconds_total,
        ROUND((c.sync_time / 1000.0)::numeric, 0) AS pg_bgwriter_checkpoint_sync_time_seconds_total
      FROM pg_stat_wal w
      CROSS JOIN pg_stat_checkpointer c;

  table_and_index_bloat:
    interval: 300 # 5 minutes
    databases: [openremote]
    metrics: [pg_table_bloat_count, pg_index_bloat_count]
    sql: |
      WITH bloated_tables AS (
        SELECT count(*) AS tbl_count
        FROM pg_stat_user_tables
        WHERE schemaname IN ('openremote', 'public')
        AND pg_relation_size(relid) > 5 * 1024 * 1024
        AND (n_dead_tup::numeric / NULLIF(n_live_tup, 0)) > 0.30
      ),
      bloated_indexes AS (
      SELECT count(*) AS idx_count
      FROM pg_stat_user_indexes
      WHERE schemaname IN ('openremote', 'public')
      -- 1. NOISE FILTER: Only look at indexes larger than 10MB.
      AND pg_relation_size(indexrelid) > 10 * 1024 * 1024
      AND (pg_relation_size(indexrelid)::numeric / GREATEST(pg_relation_size(relid), 1)) > 1.50
      )
      SELECT
      (SELECT tbl_count FROM bloated_tables) AS pg_table_bloat_count,
      (SELECT idx_count FROM bloated_indexes) AS pg_index_bloat_count;