Skip to content

[GRAFANA] PostgreSQL Slow Query Monitoring dengan Grafana

Jump to bottom
fourslickz edited this page Sep 10, 2026 · 1 revision

PostgreSQL Slow Query Monitoring dengan Grafana

Panduan instalasi monitoring PostgreSQL Slow Query menggunakan:

  • PostgreSQL 15.17
  • pg_stat_statements
  • postgres_exporter v0.20.1
  • Prometheus
  • Grafana

Target database:

pramuka_gamification_db_prod

1. Arsitektur

┌─────────────────────────────────────┐
│ PostgreSQL Server                   │
│                                     │
│ PostgreSQL 15.17                    │
│ pramuka_gamification_db_prod        │
│ pg_stat_statements                  │
│                                     │
│ postgres_exporter :9187             │
└──────────────────┬──────────────────┘
                   │
                   │ HTTP :9187
                   │
                   ▼
┌─────────────────────────────────────┐
│ Monitoring Server                   │
│                                     │
│ Prometheus :9090                    │
│        │                            │
│        ▼                            │
│ Grafana :3000                       │
└─────────────────────────────────────┘

PostgreSQL Server:

10.130.249.232

2. Environment PostgreSQL

Versi:

PostgreSQL 15.17
Ubuntu 22.04

Config:

/etc/postgresql/15/main/postgresql.conf

Data directory:

/var/lib/postgresql/15/main

3. Aktifkan pg_stat_statements

Cek:

SHOW shared_preload_libraries;

Jika kosong, edit:

sudo nano /etc/postgresql/15/main/postgresql.conf

Set:

shared_preload_libraries = 'pg_stat_statements'

Jika sudah ada library lain:

shared_preload_libraries = 'library_lain,pg_stat_statements'

Restart:

sudo systemctl restart postgresql

Cek:

sudo systemctl status postgresql --no-pager

Verifikasi:

sudo -u postgres psql
SHOW shared_preload_libraries;

Harus terdapat:

pg_stat_statements

4. Aktifkan Extension

Masuk ke database:

\c pramuka_gamification_db_prod

Buat extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Verifikasi:

SELECT extname, extversion
FROM pg_extension
WHERE extname = 'pg_stat_statements';

Test:

SELECT count(*)
FROM pg_stat_statements;

5. Buat User PostgreSQL untuk Exporter

Masuk sebagai administrator:

sudo -u postgres psql

Buat user:

CREATE USER postgres_exporter
WITH PASSWORD 'PASSWORD_KUAT';

Berikan role monitoring:

GRANT pg_monitor TO postgres_exporter;

Beri akses database:

GRANT CONNECT
ON DATABASE pramuka_gamification_db_prod
TO postgres_exporter;

6. Test User Exporter

psql \
  -h 127.0.0.1 \
  -U postgres_exporter \
  -d pramuka_gamification_db_prod

Test:

SELECT current_user, current_database();

Test akses:

SELECT count(*)
FROM pg_stat_statements;

Jika berhasil, user exporter sudah bisa membaca statistik PostgreSQL.

7. Install postgres_exporter

Versi:

v0.20.1

Cek architecture:

uname -m

Untuk x86_64:

cd /tmp

wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.20.1/postgres_exporter-0.20.1.linux-amd64.tar.gz

tar -xzf postgres_exporter-0.20.1.linux-amd64.tar.gz

Copy binary:

sudo cp \
  postgres_exporter-0.20.1.linux-amd64/postgres_exporter \
  /usr/local/bin/postgres_exporter

Permission:

sudo chown root:root /usr/local/bin/postgres_exporter
sudo chmod 755 /usr/local/bin/postgres_exporter

Cek:

/usr/local/bin/postgres_exporter --version

Expected:

postgres_exporter, version 0.20.1

8. Buat Linux User

sudo useradd \
  --system \
  --no-create-home \
  --shell /usr/sbin/nologin \
  postgres_exporter

Cek:

id postgres_exporter

9. Konfigurasi Exporter

Buat directory:

sudo mkdir -p /etc/postgres_exporter

Buat file:

sudo nano /etc/postgres_exporter/postgres_exporter.env

Isi:

DATA_SOURCE_NAME=postgresql://postgres_exporter:PASSWORD_KAMU@127.0.0.1:5432/pramuka_gamification_db_prod?sslmode=disable

Jika password memiliki karakter khusus seperti:

@
#
/
:
%

password harus di-URL-encode karena digunakan dalam connection URL.

Amankan:

sudo chown root:postgres_exporter \
  /etc/postgres_exporter/postgres_exporter.env

sudo chmod 640 \
  /etc/postgres_exporter/postgres_exporter.env

10. Buat Systemd Service

Buat:

sudo nano /etc/systemd/system/postgres_exporter.service

Isi:

[Unit]
Description=Prometheus PostgreSQL Exporter
Wants=network-online.target
After=network-online.target postgresql.service

[Service]
Type=simple
User=postgres_exporter
Group=postgres_exporter

EnvironmentFile=/etc/postgres_exporter/postgres_exporter.env

ExecStart=/usr/local/bin/postgres_exporter \
  --web.listen-address=0.0.0.0:9187 \
  --collector.stat_statements

Restart=always
RestartSec=5

NoNewPrivileges=true
PrivateTmp=true
ProtectSystem=strict
ProtectHome=true

[Install]
WantedBy=multi-user.target

Reload:

sudo systemctl daemon-reload

Enable:

sudo systemctl enable postgres_exporter

Start:

sudo systemctl start postgres_exporter

11. Cek Exporter

sudo systemctl status postgres_exporter --no-pager

Jika gagal:

sudo journalctl \
  -u postgres_exporter \
  -n 100 \
  --no-pager

12. Test Metrics

curl -s http://127.0.0.1:9187/metrics

Test PostgreSQL:

curl -s http://10.130.249.232:9187/metrics \
  | grep '^pg_up'

Expected:

pg_up 1

13. Test stat_statements 

curl -s http://10.130.249.232:9187/metrics \
  | grep -i 'stat_statements' \
  | head -30

Cari:

pg_scrape_collector_success{collector="stat_statements"} 1

Metric yang tersedia pada setup ini:

pg_stat_statements_block_read_seconds_total
pg_stat_statements_block_write_seconds_total
pg_stat_statements_calls_total
pg_stat_statements_rows_total
pg_stat_statements_seconds_total

14. Test dari Monitoring Server

Dari server Prometheus:

curl -s http://10.130.249.232:9187/metrics \
  | grep '^pg_up'

Expected:

pg_up 1

15. Firewall

Port exporter:

9187/TCP

Jangan expose port 9187 ke public internet.

Idealnya:

Prometheus VM
     │
     │ TCP 9187
     ▼
PostgreSQL VM

Contoh UFW:

sudo ufw allow from IP_PROMETHEUS to any port 9187 proto tcp

Cek:

sudo ufw status

16. Konfigurasi Prometheus

Cari config:

sudo find /etc/prometheus \
  -type f \
  \( -name '*.yml' -o -name '*.yaml' \)

Biasanya:

/etc/prometheus/prometheus.yml

Backup:

sudo cp \
  /etc/prometheus/prometheus.yml \
  /etc/prometheus/prometheus.yml.bak

Edit:

sudo nano /etc/prometheus/prometheus.yml

Tambahkan:

  - job_name: 'postgres-gamification'
    static_configs:
      - targets:
          - '10.130.249.232:9187'
        labels:
          service: 'postgresql'
          database: 'pramuka_gamification_db_prod'
          environment: 'production'

Contoh:

scrape_configs:

  - job_name: 'prometheus'
    static_configs:
      - targets:
          - 'localhost:9090'

  - job_name: 'postgres-gamification'
    static_configs:
      - targets:
          - '10.130.249.232:9187'
        labels:
          service: 'postgresql'
          database: 'pramuka_gamification_db_prod'
          environment: 'production'

17. Validate Prometheus

promtool check config /etc/prometheus/prometheus.yml

Expected:

SUCCESS: /etc/prometheus/prometheus.yml is valid prometheus config file syntax

Reload:

sudo systemctl reload prometheus

Jika reload tidak tersedia:

sudo systemctl restart prometheus

18. Test Prometheus Target

Buka:

http://IP_PROMETHEUS:9090/targets

Cari:

postgres-gamification

Status harus:

UP

Test PromQL:

up{job="postgres-gamification"}

Expected:

1

19. Test Metric Slow Query

Query calls:

pg_stat_statements_calls_total{
  job="postgres-gamification"
}

Execution time:

pg_stat_statements_seconds_total{
  job="postgres-gamification"
}

Rows:

pg_stat_statements_rows_total{
  job="postgres-gamification"
}

Block read:

pg_stat_statements_block_read_seconds_total{
  job="postgres-gamification"
}

Block write:

pg_stat_statements_block_write_seconds_total{
  job="postgres-gamification"
}

20. PostgreSQL Datasource Grafana

Datasource:

grafana-postgresql-datasource-gamification

UID:

afxsibohenv9ce

Database:

pramuka_gamification_db_prod

Host:

10.130.249.232:5432

User:

postgres_exporter

21. Dashboard Grafana

Dashboard menggunakan dua datasource:

Prometheus
    │
    ├── Query Calls/sec
    ├── Execution Time/sec
    ├── Rows/sec
    ├── Block Read
    ├── Block Write
    └── PostgreSQL UP

PostgreSQL
    │
    ├── Top Slow Queries
    ├── Top Total Execution Time
    └── Most Executed Queries

Top 20 Slow Queries

Datasource:

PostgreSQL

Query:

SELECT
    queryid,
    calls,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    ROUND(max_exec_time::numeric, 2) AS max_ms,
    ROUND(total_exec_time::numeric, 2) AS total_ms,
    rows,
    query
FROM pg_stat_statements
WHERE dbid = (
    SELECT oid
    FROM pg_database
    WHERE datname = 'pramuka_gamification_db_prod'
)
AND calls > 0
ORDER BY mean_exec_time DESC
LIMIT 20;

Top berdasarkan Total Execution Time

SELECT
    queryid,
    calls,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    ROUND(max_exec_time::numeric, 2) AS max_ms,
    ROUND(total_exec_time::numeric, 2) AS total_ms,
    rows,
    query
FROM pg_stat_statements
WHERE dbid = (
    SELECT oid
    FROM pg_database
    WHERE datname = 'pramuka_gamification_db_prod'
)
AND calls > 0
ORDER BY total_exec_time DESC
LIMIT 20;

Top Most Executed Query

SELECT
    queryid,
    calls,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    ROUND(total_exec_time::numeric, 2) AS total_ms,
    rows,
    query
FROM pg_stat_statements
WHERE dbid = (
    SELECT oid
    FROM pg_database
    WHERE datname = 'pramuka_gamification_db_prod'
)
ORDER BY calls DESC
LIMIT 20;

22. Prometheus Dashboard Queries

Query Calls/sec

sum(
  rate(
    pg_stat_statements_calls_total{
      job="postgres-gamification",
      datname="pramuka_gamification_db_prod"
    }[5m]
  )
)

Execution Time/sec

sum(
  rate(
    pg_stat_statements_seconds_total{
      job="postgres-gamification",
      datname="pramuka_gamification_db_prod"
    }[5m]
  )
)

Rows/sec

sum(
  rate(
    pg_stat_statements_rows_total{
      job="postgres-gamification",
      datname="pramuka_gamification_db_prod"
    }[5m]
  )
)

Block Read

sum(
  rate(
    pg_stat_statements_block_read_seconds_total{
      job="postgres-gamification",
      datname="pramuka_gamification_db_prod"
    }[5m]
  )
)

Block Write

sum(
  rate(
    pg_stat_statements_block_write_seconds_total{
      job="postgres-gamification",
      datname="pramuka_gamification_db_prod"
    }[5m]
  )
)

PostgreSQL Status

up{
  job="postgres-gamification"
}

23. Dashboard Layout

PostgreSQL - Gamification Slow Query
│
├── PostgreSQL Status
├── Query Calls / sec
├── Execution Time / sec
├── Rows / sec
│
├── Query Calls / Second
├── Query Execution Time / Second
│
├── Block Read Time / Second
├── Block Write Time / Second
│
├── Top 20 Slow Queries
│
├── Top 20 Queries
│   └── Total Execution Time
│
└── Top 20 Most Executed Queries

24. Slow Query yang Ditemukan

Saat analisis awal, query berikut menjadi query paling berat:

Query ID:
-9113787273790285623

Statistik:

Calls      : 358
Average    : 1903.90 ms
Total      : 681597.59 ms
Percentage : 66.32%

Query melakukan aggregation:

WITH aggregated AS (
    SELECT
        anggota_id,
        SUM(total_point) AS total_point,
        MAX(updated_at) AS updated_at
    FROM gamification_leaderboard
    WHERE deleted_at IS NULL
    GROUP BY anggota_id
)

Tabel:

gamification_leaderboard

Statistik tabel saat analisis:

Live rows : 953,802
Dead rows : 99,441
Total     : 601 MB
Table     : 108 MB
Indexes   : 493 MB

Query ini menjadi kandidat utama untuk optimasi selanjutnya.

25. Maintenance pg_stat_statements

Lihat statistik:

SELECT
    queryid,
    calls,
    mean_exec_time,
    total_exec_time,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Reset statistik:

SELECT pg_stat_statements_reset();

Hati-hati: reset akan menghapus statistik yang sedang dikumpulkan. Sebaiknya reset hanya ketika diperlukan, misalnya setelah optimasi query dan ingin membuat baseline baru.

26. Troubleshooting

Exporter tidak running

sudo systemctl status postgres_exporter

Log:

sudo journalctl \
  -u postgres_exporter \
  -n 100 \
  --no-pager

PostgreSQL tidak terdeteksi

curl -s http://127.0.0.1:9187/metrics \
  | grep '^pg_up'

Expected:

pg_up 1

stat_statements gagal

curl -s http://127.0.0.1:9187/metrics \
  | grep 'stat_statements'

Cari:

pg_scrape_collector_success{collector="stat_statements"} 1

Prometheus target DOWN

Dari Prometheus server:

curl -s http://10.130.249.232:9187/metrics \
  | grep '^pg_up'

Expected:

pg_up 1

Cek port exporter

sudo ss -lntp | grep 9187

Expected:

0.0.0.0:9187

27. Checklist

[✓] PostgreSQL 15.17
[✓] pg_stat_statements enabled
[✓] Extension pg_stat_statements
[✓] User postgres_exporter
[✓] GRANT pg_monitor
[✓] postgres_exporter v0.20.1
[✓] Exporter :9187
[✓] pg_up = 1
[✓] stat_statements collector = 1
[✓] Monitoring VM bisa akses :9187
[✓] Prometheus scrape target
[✓] Prometheus target UP
[✓] pg_stat_statements metrics masuk Prometheus
[✓] PostgreSQL datasource Grafana
[✓] Grafana dashboard

Final Architecture

                    PRODUCTION
┌────────────────────────────────────────────┐
│ PostgreSQL Server                          │
│ 10.130.249.232                             │
│                                            │
│ PostgreSQL 15.17                           │
│       │                                    │
│       ├── pramuka_gamification_db_prod     │
│       │                                    │
│       └── pg_stat_statements               │
│                    │                       │
│                    ▼                       │
│           postgres_exporter                │
│                    │                       │
│                  :9187                     │
└────────────────────┼───────────────────────┘
                     │
                     │ metrics
                     ▼
┌────────────────────────────────────────────┐
│ MONITORING VM                              │
│                                            │
│ Prometheus                                 │
│      │                                     │
│      ▼                                     │
│ Grafana                                    │
│                                            │
│ PostgreSQL Slow Query Dashboard             │
└────────────────────────────────────────────┘

Clone this wiki locally