مانیتورینگ و Performance Tuning

monitoring برای پروداکشن حیاتی است – بدون آن، مشکلات وقتی متوجه می‌شوید که کاربر شکایت می‌کند. در این فصل با view‌های آماری PostgreSQL، pgBadger، Prometheus + postgres_exporter و Grafana برای dashboard کامل آشنا می‌شویم.

View‌های آماری

PostgreSQL view‌های آماری زیادی دارد که به‌صورت real-time داده می‌دهند:

pg_stat_activity

-- اتصال‌های فعلی
SELECT 
    pid, datname, usename, application_name, client_addr,
    state, wait_event_type, wait_event,
    query_start, NOW() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

-- query‌های طولانی
SELECT pid, NOW() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND NOW() - query_start > INTERVAL '30 seconds';

-- idle in transaction (مشکل‌ساز)
SELECT pid, NOW() - state_change AS idle_for, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < NOW() - INTERVAL '5 minutes';

-- شمارش اتصال هر database
SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname;

pg_stat_database

SELECT 
    datname,
    numbackends AS connections,
    xact_commit, xact_rollback,
    blks_read, blks_hit,
    round(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS cache_hit_pct,
    tup_returned, tup_fetched,
    tup_inserted, tup_updated, tup_deleted,
    deadlocks,
    temp_files, pg_size_pretty(temp_bytes) AS temp_size
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1', 'postgres')
ORDER BY datname;

متریک‌های مهم

• cache_hit_pct: باید > 99% باشد – اگر کم است، shared_buffers کم است
• deadlocks: باید نزدیک صفر باشد
• temp_files: نشانه work_mem کم

pg_stat_user_tables

SELECT 
    schemaname, relname,
    seq_scan, seq_tup_read,
    idx_scan, idx_tup_fetch,
    n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd,
    n_live_tup, n_dead_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup, 0), 2) AS dead_pct,
    last_vacuum, last_autovacuum,
    last_analyze, last_autoanalyze,
    vacuum_count, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

نشانه‌های هشدار

• seq_scan زیاد روی جدول بزرگ: ایندکس کم
• n_dead_tup > 20% n_live_tup: نیاز به VACUUM
• last_autovacuum خیلی قدیمی: autovacuum مشکل دارد
• n_tup_hot_upd << n_tup_upd: HOT updates کم – ایندکس‌های زیاد

pg_stat_user_indexes

-- ایندکس‌های unused
SELECT 
    schemaname, relname AS table, indexrelname AS index,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;
-- این ایندکس‌ها overhead دارند بدون مزیت

-- بزرگ‌ترین ایندکس‌های پراستفاده
SELECT 
    relname, indexrelname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC
LIMIT 20;

pg_stat_statements – عمیق‌ترین

CREATE EXTENSION pg_stat_statements;

-- top 10 کندترین query (total_exec_time)
SELECT 
    substring(query, 1, 80) AS short_query,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(max_exec_time::numeric, 2) AS max_ms,
    round(stddev_exec_time::numeric, 2) AS stddev_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- query‌های پر مصرف I/O
SELECT 
    substring(query, 1, 80) AS short_query,
    calls,
    round(blk_read_time::numeric, 2) AS read_ms,
    round(blk_write_time::numeric, 2) AS write_ms,
    shared_blks_read, shared_blks_hit
FROM pg_stat_statements
ORDER BY (blk_read_time + blk_write_time) DESC
LIMIT 10;

-- query‌های با cache hit ratio پایین
SELECT 
    substring(query, 1, 80),
    calls,
    shared_blks_hit + shared_blks_read AS total_blks,
    round(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS hit_pct
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 0
ORDER BY total_blks DESC
LIMIT 10;

-- ریست
SELECT pg_stat_statements_reset();

Slow Query Log

# postgresql.conf
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_truncate_on_rotation = on

# log query‌های > 1 ثانیه
log_min_duration_statement = 1000

# log همه DDL
log_statement = 'ddl'                    # یا 'all' برای همه

# log connection
log_connections = on
log_disconnections = on

# log lock waits
log_lock_waits = on
deadlock_timeout = 1s

# log temp files (نشانه work_mem کم)
log_temp_files = 0

# log autovacuum وقتی > 1 ثانیه
log_autovacuum_min_duration = 1000

# format محتوا
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

pgBadger – تحلیل لاگ

pgBadger گزارش HTML زیبا از log می‌سازد:

sudo apt install pgbadger

# تولید گزارش
pgbadger /var/log/postgresql/postgresql-2026-04-30_*.log -o report.html

# با چند فایل
pgbadger /var/log/postgresql/*.log -o report.html

# گزارش روزانه (cron)
pgbadger -I /var/log/postgresql/postgresql-$(date -d 'yesterday' +%Y-%m-%d)*.log 
         -O /var/www/pgbadger/

# گزارش فقط برای یک query type
pgbadger --filter "user=app_user" /var/log/postgresql/*.log

گزارش شامل: top slow queries، top frequent queries، lock‌ها، connection‌ها، temp files، vacuum، نمودارهای زمانی.

Prometheus + postgres_exporter

برای monitoring پیوسته با metrics و alerting:

# نصب postgres_exporter
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz
tar xzf postgres_exporter-*.tar.gz
sudo mv postgres_exporter /usr/local/bin/

# user مخصوص monitoring
sudo -u postgres psql <<EOF
CREATE USER monitoring WITH PASSWORD 'monitor_pass';
GRANT pg_monitor TO monitoring;
EOF

# /etc/systemd/system/postgres_exporter.service
[Unit]
Description=Prometheus Postgres Exporter
After=network.target

[Service]
User=postgres
Environment="DATA_SOURCE_NAME=postgresql://monitoring:monitor_pass@localhost:5432/postgres?sslmode=disable"
ExecStart=/usr/local/bin/postgres_exporter --web.listen-address=:9187
Restart=always

[Install]
WantedBy=multi-user.target

sudo systemctl enable --now postgres_exporter

# تست
curl http://localhost:9187/metrics

config Prometheus

# prometheus.yml
scrape_configs:
  - job_name: 'postgres'
    static_configs:
      - targets: ['10.0.0.10:9187', '10.0.0.20:9187']
        labels:
          environment: 'production'

Grafana Dashboards

Grafana dashboards زیبا برای visualization. Dashboards آماده:

• 9628: PostgreSQL Database (popular)
• 455: PostgreSQL Stats
• 9594: PostgreSQL Replication
• 14114: PostgreSQL Detailed

# نصب Grafana
sudo apt install grafana
sudo systemctl enable --now grafana-server

# باز کنید http://localhost:3000
# admin/admin (تغییر دهید)
# Add data source: Prometheus → http://localhost:9090
# Import dashboard 9628

متریک‌های کلیدی برای dashboard

1. Connection: pg_stat_database_numbackends
2. Cache hit ratio: pg_stat_database_blks_hit / blks_read
3. Transactions/sec: rate(pg_stat_database_xact_commit)
4. Slow queries: pg_stat_statements_total_exec_time
5. Locks: pg_locks_count
6. Database size: pg_database_size_bytes
7. Replication lag: pg_replication_lag_seconds
8. Disk space: node_filesystem_avail_bytes

Alerting

# prometheus alert rules
groups:
- name: postgres
  rules:
  - alert: PostgresDown
    expr: pg_up == 0
    for: 1m
    annotations:
      summary: "PostgreSQL is down"

  - alert: PostgresHighConnections
    expr: pg_stat_database_numbackends > 150
    for: 5m
    annotations:
      summary: "High connection count: {{ $value }}"

  - alert: PostgresLowCacheHit
    expr: |
      rate(pg_stat_database_blks_hit[5m]) /
      (rate(pg_stat_database_blks_hit[5m]) + rate(pg_stat_database_blks_read[5m])) < 0.95
    for: 10m
    annotations:
      summary: "Low cache hit ratio"

  - alert: PostgresReplicationLag
    expr: pg_replication_lag_seconds > 60
    for: 2m
    annotations:
      summary: "Replication lag > 60s"

  - alert: PostgresDeadlocks
    expr: rate(pg_stat_database_deadlocks[5m]) > 0
    annotations:
      summary: "Deadlock detected"

script سلامت کلی

#!/bin/bash
# postgres_health.sh

psql -U postgres <<EOF
echo '=== Connections ==='
SELECT count(*), state FROM pg_stat_activity GROUP BY state;

echo
echo '=== Cache Hit Ratio ==='
SELECT round(100.0 * sum(blks_hit) / NULLIF(sum(blks_hit) + sum(blks_read), 0), 2) AS cache_hit_pct
FROM pg_stat_database;

echo
echo '=== Database Sizes ==='
SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database
WHERE datname NOT IN ('template0', 'template1') ORDER BY pg_database_size(datname) DESC;

echo
echo '=== Tables Needing VACUUM ==='
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables WHERE n_dead_tup > 1000
ORDER BY dead_pct DESC NULLS LAST LIMIT 5;

echo
echo '=== Top Slow Queries ==='
SELECT calls, round(mean_exec_time::numeric, 2) AS mean_ms,
       substring(query, 1, 60)
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 5;

echo
echo '=== Long Running Queries ==='
SELECT pid, NOW() - query_start AS duration, substring(query, 1, 60)
FROM pg_stat_activity
WHERE state = 'active' AND NOW() - query_start > INTERVAL '5 seconds';
EOF

auto_explain

extension که خودکار EXPLAIN را برای query‌های کند log می‌کند:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = 1000     # query‌های > 1 ثانیه
auto_explain.log_analyze = true
auto_explain.log_buffers = true
auto_explain.log_format = 'json'

بهترین شیوه‌ها

• monitoring از روز اول راه‌اندازی
• alerting برای متریک‌های critical
• retention مناسب (Prometheus 15 روز کافی است)
• dashboard اختصاصی هر سرویس
• log rotation برای جلوگیری از پر شدن دیسک
• pg_stat_statements همیشه فعال
• pgBadger گزارش هفتگی
• alert fatigue را اجتناب کنید (فقط critical‌ها)

جمع‌بندی

• view‌های آماری: pg_stat_activity، pg_stat_database، pg_stat_user_tables، pg_stat_statements
• cache hit ratio > 99%، deadlocks نزدیک صفر
• slow query log + auto_explain
• pgBadger برای گزارش HTML
• Prometheus + postgres_exporter + Grafana = stack مدرن
• alerting با Prometheus rules
• script‌های health check خودکار