100% open-source IoT Platform - Integrate your devices, add intelligence, build custom apps
Java asset-managementbuildingdashboarddataplatformdevice-managementedge-computingenergyenergy-managementenergy-monitorfleet-managementfleet-telematicsiotiot-applicationiot-platformmodbusmqttmqtt-brokerota-updaterules-enginesmartcity
# 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;