[GRAFANA] PostgreSQL Slow Query Monitoring dengan Grafana - fourslickz/notes GitHub Wiki

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:[email protected]: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:

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             │
└────────────────────────────────────────────┘

[GRAFANA] PostgreSQL Slow Query Monitoring dengan Grafana - fourslickz/notes GitHub Wiki

PostgreSQL Slow Query Monitoring dengan Grafana

1. Arsitektur

2. Environment PostgreSQL

3. Aktifkan pg_stat_statements

4. Aktifkan Extension

5. Buat User PostgreSQL untuk Exporter

6. Test User Exporter

7. Install postgres_exporter

8. Buat Linux User

9. Konfigurasi Exporter

10. Buat Systemd Service

11. Cek Exporter

12. Test Metrics

13. Test stat_statements

14. Test dari Monitoring Server

15. Firewall

16. Konfigurasi Prometheus

17. Validate Prometheus

18. Test Prometheus Target

19. Test Metric Slow Query

20. PostgreSQL Datasource Grafana

21. Dashboard Grafana

Top 20 Slow Queries

Top berdasarkan Total Execution Time

Top Most Executed Query

22. Prometheus Dashboard Queries

Query Calls/sec

Execution Time/sec

Rows/sec

Block Read

Block Write

PostgreSQL Status

23. Dashboard Layout

24. Slow Query yang Ditemukan

25. Maintenance pg_stat_statements

26. Troubleshooting

Exporter tidak running

PostgreSQL tidak terdeteksi

stat_statements gagal

Prometheus target DOWN

Cek port exporter

27. Checklist

Final Architecture

3. Aktifkan `pg_stat_statements`

7. Install `postgres_exporter`

13. Test `stat_statements`

25. Maintenance `pg_stat_statements`

`stat_statements` gagal