データベース監視

1
-- スロークエリログ設定（postgresql.conf）
2
log_min_duration_statement = 1000  -- 1秒以上のクエリをログ
3
log_statement = 'none'  -- 通常のステートメントはログしない
4
5
-- pg_stat_statements 拡張（推奨）
6
CREATE EXTENSION pg_stat_statements;
7
8
-- 遅いクエリTop10
9
SELECT
10
  query,
11
  calls,
12
  mean_exec_time::numeric(10,2) AS avg_ms,
13
  total_exec_time::numeric(10,2) AS total_ms,
14
  rows
15
FROM pg_stat_statements
16
ORDER BY mean_exec_time DESC
17
LIMIT 10;
18
19
-- 現在実行中のクエリ
20
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
21
FROM pg_stat_activity
22
WHERE state = 'active' AND query NOT LIKE '%pg_stat_activity%'
23
ORDER BY duration DESC;

1
-- スロークエリログ設定（my.cnf）
2
slow_query_log = 1
3
slow_query_log_file = /var/log/mysql/slow.log
4
long_query_time = 1
5
6
-- performance_schema から遅いクエリ
7
SELECT
8
  DIGEST_TEXT,
9
  COUNT_STAR,
10
  AVG_TIMER_WAIT/1000000000 AS avg_ms,
11
  SUM_TIMER_WAIT/1000000000 AS total_ms
12
FROM performance_schema.events_statements_summary_by_digest
13
ORDER BY AVG_TIMER_WAIT DESC
14
LIMIT 10;
15
16
-- 現在実行中のクエリ
17
SHOW PROCESSLIST;
18
-- または
19
SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND != 'Sleep';

1
-- データベースサイズ
2
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname))
3
FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;
4
5
-- テーブルサイズ
6
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
7
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
8
9
-- インデックス使用状況
10
SELECT relname, idx_scan, seq_scan,
11
  CASE WHEN seq_scan > 0 THEN idx_scan::float / seq_scan ELSE 0 END AS ratio
12
FROM pg_stat_user_tables ORDER BY seq_scan DESC;
13
14
-- 接続数
15
SELECT count(*), state FROM pg_stat_activity GROUP BY state;
16
17
-- キャッシュヒット率（目標: 99%以上）
18
SELECT
19
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio
20
FROM pg_statio_user_tables;
21
22
-- レプリケーションラグ
23
SELECT client_addr, state,
24
  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
25
FROM pg_stat_replication;

1
# docker-compose.yml
2
services:
3
  postgres-exporter:
4
    image: prometheuscommunity/postgres-exporter
5
    environment:
6
      DATA_SOURCE_NAME: "postgresql://user:pass@postgres:5432/mydb?sslmode=disable"
7
    ports:
8
      - "9187:9187"
9
10
# prometheus.yml
11
scrape_configs:
12
  - job_name: 'postgres'
13
    static_configs:
14
      - targets: ['postgres-exporter:9187']