iostat -d -k 2  # BusyBox可用基礎(chǔ)命令
iostat -dx 2    # 完整版功能，BusyBox不支持

docker exec -it test-postgresql bash -c "iostat -d -k 2"

Device             tps    kB_read/s    kB_wrtn/s    kB_dscd/s    kB_read    kB_wrtn    kB_dscd
vda               5.67        32.45       128.76         0.00    1048576    4194304          0

# Debian/Ubuntu系容器
docker exec -it test-postgresql bash -c "apt update && apt install -y sysstat"

# Alpine系容器
docker exec -it test-postgresql bash -c "apk add sysstat"

iostat -dx 2  # 顯示擴(kuò)展統(tǒng)計(jì)
iostat -c 2   # 查看CPU與I/O等待關(guān)系

# 查找容器使用的實(shí)際設(shè)備
device=$(docker inspect test-postgresql | grep -A5 "DeviceName" | grep "Source" | cut -d'"' -f4)

# 監(jiān)控該設(shè)備
iostat -dx $device 2

kB_wrtn/s呈現(xiàn)規(guī)律性波動(dòng) → 檢查點(diǎn)活動(dòng)
持續(xù)高kB_wrtn/s → 事務(wù)量過(guò)大

ALTER SYSTEM SET wal_buffers = '16MB';  -- 默認(rèn)4MB，大事務(wù)可增加
ALTER SYSTEM SET checkpoint_completion_target = 0.9;  -- 平滑檢查點(diǎn)
ALTER SYSTEM SET max_wal_size = '2GB';  -- 根據(jù)負(fù)載調(diào)整

ALTER SYSTEM SET shared_buffers = '4GB';  -- 通常設(shè)為內(nèi)存25%
ALTER SYSTEM SET effective_cache_size = '12GB';  -- 可用內(nèi)存的50-75%

-- 對(duì)于SSD存儲(chǔ)
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET seq_page_cost = 1.0;

SELECT query, calls, total_time, shared_blks_read
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

/var/lib/postgresql/data → 主數(shù)據(jù)（高性能設(shè)備）
/pg_wal → 單獨(dú)高速設(shè)備（可選）
/pg_log → 可放在普通設(shè)備

ALTER SYSTEM SET checkpoint_timeout = '30min';
SELECT pg_reload_conf();

ALTER SYSTEM SET max_connections = 100;

ALTER SYSTEM SET work_mem = '8MB';

# docker-compose-monitoring.yml
version: "3"
services:
  prometheus:
    image: prom/prometheus
    ports: ["9090:9090"]

  grafana:
    image: grafana/grafana
    ports: ["3000:3000"]

  node-exporter:
    image: prom/node-exporter
    volumes: ["/proc:/host/proc", "/sys:/host/sys"]

{
  "storage-driver": "overlay2",
  "storage-opts": ["overlay2.override_kernel_check=true"]
}

docker run -d \
  --mount type=volume,dst=/var/lib/postgresql/data,volume-opt=type=xfs \
  postgres:14

docker run -d \
  --memory="8g" \
  --memory-swap="12g" \
  --cpu-shares=1024 \
  --blkio-weight=500 \
  postgres:14