SkyWalking
PostgreSQL monitoring
SkyWalking leverages postgres-exporter for collecting metrics data from PostgreSQL. It leverages OpenTelemetry Collector to transfer the metrics to OpenTelemetry receiver and into the Meter System.
Data flow
- postgres-exporter collect metrics data from PostgreSQL.
- OpenTelemetry Collector fetches metrics from postgres-exporter via Prometheus Receiver and pushes metrics to SkyWalking OAP Server via the OpenCensus gRPC Exporter or OpenTelemetry gRPC exporter.
- The SkyWalking OAP Server parses the expression with MAL to filter/calculate/aggregate and store the results.
Set up
- Set up postgres-exporter.
- Set up OpenTelemetry Collector . For details on Prometheus Receiver in OpenTelemetry Collector, refer to here.
- Config SkyWalking OpenTelemetry receiver.
PostgreSQL Monitoring
PostgreSQL monitoring provides monitoring of the status and resources of the PostgreSQL server.PostgreSQL server as a Service in OAP, and land on the Layer: POSTGRESQL.
PostgreSQL Supported Metrics
| Monitoring Panel | Unit | Metric Name | Description | Data Source |
|---|---|---|---|---|
| Shared Buffers | MB | meter_pg_shared_buffers | The number of shared memory buffers used by the server | postgres-exporter |
| Effective Cache | GB | meter_pg_effective_cache | The planner’s assumption about the total size of the data caches | postgres-exporter |
| Maintenance Work Mem | MB | meter_pg_maintenance_work_mem | The maximum memory to be used for maintenance operations | postgres-exporter |
| Seq Page Cost | meter_pg_seq_page_cost | The planner’s estimate of the cost of a sequentially fetched disk page. | postgres-exporter | |
| Random Page Cost | meter_pg_random_page_cost | The planner’s estimate of the cost of a nonsequentially fetched disk page. | postgres-exporter | |
| Max Worker Processes | meter_pg_max_worker_processes | Maximum number of concurrent worker processes | postgres-exporter | |
| Max WAL Size | GB | meter_max_wal_size | The WAL size that triggers a checkpoint | postgres-exporter |
| Max Parallel Workers | meter_pg_max_parallel_workers | The maximum number of parallel processes per executor node | postgres-exporter | |
| Work Mem | MB | meter_pg_max_work_mem | The maximum memory to be used for query workspaces. | postgres-exporter |
| Fetched Row Trend | meter_pg_fetched_rows_rate | The trend of the number of rows fetched by queries in this database. | postgres-exporter | |
| Inserted Row Trend | meter_pg_inserted_rows_rate | The trend of the number of rows inserted by queries in this database. | postgres-exporter | |
| Updated Row Trend | meter_pg_updated_rows_rate | The trend of the number of rows updated by queries in this database. | postgres-exporter | |
| Deleted Row Trend | meter_pg_deleted_rows_rate | The trend of the number of rows deleted by queries in this database. | postgres-exporter | |
| Returned Row Trend | meter_pg_returned_rows_rate | The trend of the number of rows returned by queries in this database. | postgres-exporter | |
| Committed Transactions Trend | meter_pg_committed_transactions_rate | The trend of the number of transactions in this database that have been committed | postgres-exporter | |
| Rolled Back Transactions Trend | meter_pg_rolled_back_transactions_rate | The trend of the number of transactions in this database that have been rolled back | postgres-exporter | |
| Buffers Trend | meter_pg_buffers_alloc meter_pg_buffers_checkpoint meter_pg_buffers_clean meter_pg_buffers_backend_fsync meter_pg_buffers_backend | The trend of the number of buffers | postgres-exporter | |
| Conflicts Trend | meter_pg_conflicts_rate | The trend of the number of queries canceled due to conflicts with recovery in this database | postgres-exporter | |
| Deadlock Trend | meter_pg_deadlocks_rate | The trend of the number of deadlocks detected in this database | postgres-exporter | |
| Cache Hit Rate | % | meter_pg_cache_hit_rate | The rate of cache hit | postgres-exporter |
| Temporary Files Trend | meter_pg_temporary_files_rate | The rate of total amount of data written to temporary files by queries in this database. All temporary files are counted, regardless of why the temporary file was created, and regardless of the log_temp_files setting | postgres-exporter | |
| Checkpoint Stat Trend | meter_pg_checkpoint_write_time_rate meter_pg_checkpoint_sync_time_rate meter_pg_checkpoint_req_rate meter_pg_checkpoint_timed_rate | The trend of checkpoint stat | postgres-exporter | |
| Active Sessions | meter_pg_active_sessions | The number of connections which state is active | postgres-exporter | |
| Idle Sessions | meter_pg_idle_sessions | The number of connections which state is idle,idle in transaction or idle in transaction (aborted) | postgres-exporter | |
| Locks Count | meter_pg_locks_count | Number of locks | postgres-exporter |
Customizations
You can customize your own metrics/expression/dashboard panel.
The metrics definition and expression rules are found in /config/otel-rules/postgresql.yaml.
The PostgreSQL dashboard panel configurations are found in /config/ui-initialized-templates/postgresql.