-- SSD 环境：随机读写代价接近顺序读写
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
ALTER SYSTEM SET seq_page_cost = 1.0;
SELECT pg_reload_conf();

# =============================================
# 共享内存
# =============================================
shared_buffers = 4GB              # 共享缓冲区（RAM 的 25%）
huge_pages = try                  # 大页支持（减少 TLB 缺失）
wal_buffers = 64MB                # WAL 缓冲区

# =============================================
# 每连接工作内存
# =============================================
work_mem = 32MB                   # 排序/哈希操作内存
hash_mem_multiplier = 2.0         # 哈希操作的额外倍数
maintenance_work_mem = 1GB        # VACUUM/CREATE INDEX 内存

# =============================================
# 优化器内存估算
# =============================================
effective_cache_size = 12GB       # OS 缓存估算（RAM 的 75%）

-- work_mem 影响的操作:
-- ORDER BY, DISTINCT, JOIN (Hash Join), GROUP BY, 聚合

-- 查看排序操作的内存使用
EXPLAIN (ANALYZE, BUFFERS)
MATCH (p:Person) RETURN p.name ORDER BY p.age;

-- 在 sort 节点查看:
--   Sort Method: quicksort  Memory: 25kB  ← 内存排序
--   Sort Method: external merge  Disk: 1024kB  ← 磁盘排序（work_mem 不足）

-- 按查询设置 work_mem
SET work_mem = '64MB';
-- 执行复杂排序查询
RESET work_mem;

shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 16MB
maintenance_work_mem = 512MB
max_connections = 100
max_parallel_workers_per_gather = 2

shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 32MB
maintenance_work_mem = 1GB
max_connections = 200
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

shared_buffers = 32GB
effective_cache_size = 96GB
work_mem = 64MB
maintenance_work_mem = 4GB
max_connections = 500
max_parallel_workers_per_gather = 8
max_parallel_workers = 16

; pgbouncer.ini
[databases]
agens_graph = host=127.0.0.1 port=5432 dbname=agens

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; 连接池模式
pool_mode = transaction          ; 事务级连接池（推荐）
; pool_mode = session            ; 会话级连接池

; 连接限制
default_pool_size = 50           ; 每用户/数据库对的连接池大小
max_client_conn = 1000           ; 最大客户端连接数
min_pool_size = 10               ; 最小保持连接数
reserve_pool_size = 5            ; 保留连接（突发流量）

; 超时设置
server_idle_timeout = 600        ; 空闲连接超时
client_idle_timeout = 0          ; 客户端空闲超时
query_timeout = 300              ; 查询超时（秒）

-- 通过 PgBouncer 管理接口查看状态
-- psql -h 127.0.0.1 -p 6432 pgbouncer

SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;
SHOW SERVERS;

-- 使用 pgbench 进行基准测试
-- 创建测试脚本 test_query.sql
-- \set person_id random(1, 100000)
-- SET graph_path = benchmark;
-- MATCH (p:Person)-[:KNOWS*1..3]->(fof:Person) WHERE id(p) = :person_id RETURN count(fof);

-- 运行基准测试
-- pgbench -c 10 -j 2 -T 60 -f test_query.sql agens

-- 记录结果
-- tps = 1500 (transactions per second)
-- latency avg = 6.5ms
-- latency stddev = 2.1ms

-- 记录执行超过 1 秒的查询
ALTER SYSTEM SET log_min_duration_statement = 1000;
-- 记录所有语句的执行时间
ALTER SYSTEM SET log_statement_stats = on;
SELECT pg_reload_conf();

-- 启用 pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 查看最慢的查询
SELECT
    query,
    calls,
    total_exec_time / 1000 AS total_seconds,
    mean_exec_time / 1000 AS avg_seconds,
    max_exec_time / 1000 AS max_seconds,
    rows,
    100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 重置统计
SELECT pg_stat_statements_reset();

-- 查看当前等待事件
SELECT
    pid,
    wait_event_type,
    wait_event,
    state,
    query
FROM pg_stat_activity
WHERE wait_event IS NOT NULL AND state = 'active';

-- 常见等待事件:
-- LWLock     → 轻量级锁（缓冲区、WAL）
-- Lock       → 重量级锁（行锁、表锁）
-- BufferPin  → 缓冲区固定
-- IO         → 磁盘 I/O
-- Client     → 等待客户端

-- 缓冲区命中率（应 > 99%）
SELECT
    sum(blks_hit) AS hits,
    sum(blks_read) AS reads,
    round(100.0 * sum(blks_hit) / (sum(blks_hit) + sum(blks_read)), 2) AS hit_ratio
FROM pg_stat_database
WHERE datname = current_database();

-- 事务统计
SELECT
    datname,
    xact_commit AS commits,
    xact_rollback AS rollbacks,
    deadlocks,
    temp_files,
    pg_size_pretty(temp_bytes) AS temp_bytes
FROM pg_stat_database
WHERE datname = current_database();

-- 表膨胀检测
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;