1
2
3
4
5
6
7
8
9

# 安裝 PostgreSQL 14
sudo apt update
sudo apt install -y postgresql-14 postgresql-contrib-14

# 確認服務狀態
sudo systemctl status postgresql

# 確認版本
psql --version

1
2
3
4
5
6
7
8

# 切換到 postgres 使用者
sudo -u postgres psql

# 在 psql 中執行
CREATE USER benchmark_user WITH PASSWORD 'benchmark_password';
CREATE DATABASE benchmark_db OWNER benchmark_user;
GRANT ALL PRIVILEGES ON DATABASE benchmark_db TO benchmark_user;
\q

1
2
3
4
5
6

# 確認 pgbench 已安裝
which pgbench
pgbench --version

# 如果未安裝，可透過以下命令安裝
sudo apt install -y postgresql-contrib-14

1
2
3
4
5
6
7
8

# 初始化 pgbench 測試資料（scale factor = 50，約 750MB 資料）
pgbench -i -s 50 -h localhost -U benchmark_user benchmark_db

# 參數說明：
# -i: 初始化模式
# -s 50: scale factor，決定資料量大小（每個 scale 約 15MB）
# -h: 資料庫主機
# -U: 使用者名稱

1
2
3
4
5
6
7

# 基本測試：10 個客戶端，執行 60 秒
pgbench -c 10 -j 2 -T 60 -h localhost -U benchmark_user benchmark_db

# 參數說明：
# -c 10: 模擬 10 個並行客戶端
# -j 2: 使用 2 個工作執行緒
# -T 60: 測試持續 60 秒

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

pgbench (14.10 (Ubuntu 14.10-0ubuntu0.22.04.1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: simple
number of clients: 10
number of threads: 2
duration: 60 s
number of transactions actually processed: 45678
latency average = 13.142 ms
latency stddev = 8.234 ms
initial connection time = 45.678 ms
tps = 761.234567 (without initial connection time)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# 使用預備語句模式（提高效能）
pgbench -c 20 -j 4 -T 120 -M prepared -h localhost -U benchmark_user benchmark_db

# 顯示進度報告（每 5 秒）
pgbench -c 20 -j 4 -T 120 -P 5 -h localhost -U benchmark_user benchmark_db

# 輸出詳細延遲統計
pgbench -c 20 -j 4 -T 120 --aggregate-interval=10 -h localhost -U benchmark_user benchmark_db

# 記錄每個交易的延遲到檔案
pgbench -c 20 -j 4 -T 60 -l --log-prefix=pgbench_log -h localhost -U benchmark_user benchmark_db

1
2
3
4
5
6
7
8

# 建立讀取測試腳本
cat > /tmp/select_only.sql << 'EOF'
\set aid random(1, 100000 * :scale)
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
EOF

# 執行純讀取測試
pgbench -c 50 -j 8 -T 120 -f /tmp/select_only.sql -h localhost -U benchmark_user benchmark_db

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# 建立寫入測試腳本
cat > /tmp/insert_only.sql << 'EOF'
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (:tid, :bid, :tid, :delta, CURRENT_TIMESTAMP);
EOF

# 執行純寫入測試
pgbench -c 20 -j 4 -T 120 -f /tmp/insert_only.sql -h localhost -U benchmark_user benchmark_db

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

# 建立複雜查詢測試腳本
cat > /tmp/complex_query.sql << 'EOF'
\set aid1 random(1, 100000 * :scale)
\set aid2 random(1, 100000 * :scale)

-- 聯結查詢
SELECT a.aid, a.abalance, b.bbalance, t.tbalance
FROM pgbench_accounts a
JOIN pgbench_branches b ON a.bid = b.bid
JOIN pgbench_tellers t ON a.bid = t.bid
WHERE a.aid BETWEEN :aid1 AND :aid1 + 100
LIMIT 10;

-- 聚合查詢
SELECT bid, COUNT(*), AVG(abalance), MAX(abalance), MIN(abalance)
FROM pgbench_accounts
WHERE aid BETWEEN :aid2 AND :aid2 + 1000
GROUP BY bid;
EOF

# 執行複雜查詢測試
pgbench -c 10 -j 4 -T 120 -f /tmp/complex_query.sql -h localhost -U benchmark_user benchmark_db

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

# 建立讀取腳本 (read.sql)
cat > /tmp/read.sql << 'EOF'
\set aid random(1, 100000 * :scale)
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
EOF

# 建立寫入腳本 (write.sql)
cat > /tmp/write.sql << 'EOF'
\set aid random(1, 100000 * :scale)
\set delta random(-5000, 5000)
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
EOF

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

# 80% 讀取，20% 寫入
pgbench -c 30 -j 6 -T 120 \
    -f /tmp/read.sql@4 \
    -f /tmp/write.sql@1 \
    -h localhost -U benchmark_user benchmark_db

# 50% 讀取，50% 寫入
pgbench -c 30 -j 6 -T 120 \
    -f /tmp/read.sql@1 \
    -f /tmp/write.sql@1 \
    -h localhost -U benchmark_user benchmark_db

# 90% 讀取，10% 寫入（典型 OLTP 場景）
pgbench -c 30 -j 6 -T 120 \
    -f /tmp/read.sql@9 \
    -f /tmp/write.sql@1 \
    -h localhost -U benchmark_user benchmark_db

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

# 建立完整 OLTP 測試腳本
cat > /tmp/oltp_mixed.sql << 'EOF'
\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)

BEGIN;

-- 更新帳戶餘額
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

-- 查詢更新後的餘額
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

-- 更新櫃員餘額
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

-- 更新分行餘額
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

-- 插入交易歷史
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

COMMIT;
EOF

# 執行測試
pgbench -c 20 -j 4 -T 300 -f /tmp/oltp_mixed.sql -P 10 \
    -h localhost -U benchmark_user benchmark_db

1
2
3
4
5

# 安裝 PgBouncer
sudo apt install -y pgbouncer

# 查看版本
pgbouncer --version

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

# 編輯設定檔
sudo tee /etc/pgbouncer/pgbouncer.ini << 'EOF'
[databases]
benchmark_db = host=127.0.0.1 port=5432 dbname=benchmark_db

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 10
reserve_pool_timeout = 3
server_idle_timeout = 60
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1
admin_users = postgres
stats_users = postgres
EOF

# 設定使用者認證
sudo tee /etc/pgbouncer/userlist.txt << 'EOF'
"benchmark_user" "md5$(echo -n 'benchmark_passwordbenchmark_user' | md5sum | cut -d' ' -f1)"
EOF

# 重新啟動 PgBouncer
sudo systemctl restart pgbouncer
sudo systemctl status pgbouncer

1
2
3
4
5
6
7
8
9

# 測試直接連線（無連線池）
echo "=== 直接連線測試 ==="
pgbench -c 100 -j 10 -T 60 -C \
    -h localhost -p 5432 -U benchmark_user benchmark_db

# 測試透過 PgBouncer 連線
echo "=== PgBouncer 連線池測試 ==="
pgbench -c 100 -j 10 -T 60 -C \
    -h localhost -p 6432 -U benchmark_user benchmark_db

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# 測試不同模式的效能差異
for mode in session transaction statement; do
    echo "=== 測試 $mode 模式 ==="
    # 修改設定
    sudo sed -i "s/pool_mode = .*/pool_mode = $mode/" /etc/pgbouncer/pgbouncer.ini
    sudo systemctl restart pgbouncer
    sleep 2

    # 執行測試
    pgbench -c 50 -j 8 -T 60 -h localhost -p 6432 -U benchmark_user benchmark_db
done

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# 開啟新終端機，在測試期間執行監控
vmstat 5 | tee /tmp/vmstat_output.txt

# 欄位說明：
# procs: r(執行中) b(等待I/O)
# memory: swpd, free, buff, cache
# swap: si(換入) so(換出)
# io: bi(區塊讀) bo(區塊寫)
# system: in(中斷) cs(context switch)
# cpu: us(使用者) sy(系統) id(閒置) wa(等待I/O)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# 安裝 sysstat
sudo apt install -y sysstat

# 監控磁碟 I/O（每 5 秒更新）
iostat -x 5 | tee /tmp/iostat_output.txt

# 重要指標：
# %util: 磁碟使用率
# await: 平均 I/O 等待時間
# r/s, w/s: 每秒讀寫次數

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

# 建立監控腳本
cat > /tmp/pg_monitor.sql << 'EOF'
-- 查看目前連線狀態
SELECT state, count(*)
FROM pg_stat_activity
WHERE datname = 'benchmark_db'
GROUP BY state;

-- 查看資料庫統計
SELECT
    numbackends as "連線數",
    xact_commit as "提交交易",
    xact_rollback as "回滾交易",
    blks_read as "區塊讀取",
    blks_hit as "快取命中",
    ROUND(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) as "快取命中率%"
FROM pg_stat_database
WHERE datname = 'benchmark_db';

-- 查看表格統計
SELECT
    relname as "表格",
    seq_scan as "循序掃描",
    idx_scan as "索引掃描",
    n_tup_ins as "插入",
    n_tup_upd as "更新",
    n_tup_del as "刪除"
FROM pg_stat_user_tables;

-- 查看鎖等待
SELECT
    blocked_locks.pid AS blocked_pid,
    blocking_locks.pid AS blocking_pid,
    blocked_activity.query AS blocked_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
WHERE NOT blocked_locks.granted;
EOF

# 執行監控（在測試期間持續執行）
watch -n 5 "psql -h localhost -U benchmark_user -d benchmark_db -f /tmp/pg_monitor.sql"

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

#!/bin/bash
# 建立完整的監控腳本
cat > /tmp/benchmark_monitor.sh << 'SCRIPT'
#!/bin/bash

OUTPUT_DIR="/tmp/benchmark_results_$(date +%Y%m%d_%H%M%S)"
mkdir -p $OUTPUT_DIR

echo "開始監控，輸出目錄: $OUTPUT_DIR"

# 背景執行各種監控
vmstat 5 > $OUTPUT_DIR/vmstat.log &
VMSTAT_PID=$!

iostat -x 5 > $OUTPUT_DIR/iostat.log &
IOSTAT_PID=$!

# 監控 PostgreSQL
while true; do
    psql -h localhost -U benchmark_user -d benchmark_db -t -c \
        "SELECT now(), numbackends, xact_commit, xact_rollback, blks_read, blks_hit FROM pg_stat_database WHERE datname = 'benchmark_db';" \
        >> $OUTPUT_DIR/pg_stats.log
    sleep 5
done &
PGSTAT_PID=$!

echo "監控 PID: vmstat=$VMSTAT_PID, iostat=$IOSTAT_PID, pgstat=$PGSTAT_PID"
echo "使用 'kill $VMSTAT_PID $IOSTAT_PID $PGSTAT_PID' 停止監控"

# 等待使用者中斷
wait
SCRIPT

chmod +x /tmp/benchmark_monitor.sh

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

pgbench (14.10)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: simple
number of clients: 20
number of threads: 4
duration: 120 s
number of transactions actually processed: 89234
latency average = 26.876 ms
latency stddev = 15.432 ms
initial connection time = 123.456 ms
tps = 743.617892 (without initial connection time)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

# 執行測試並記錄延遲
pgbench -c 20 -j 4 -T 60 -l --log-prefix=/tmp/latency \
    -h localhost -U benchmark_user benchmark_db

# 分析延遲分佈
cat /tmp/latency.* | awk '{print $3}' | sort -n | \
    awk 'BEGIN {count=0; sum=0}
         {latency[NR]=$1; sum+=$1; count++}
         END {
             print "總交易數:", count;
             print "平均延遲:", sum/count, "ms";
             print "最小延遲:", latency[1], "ms";
             print "最大延遲:", latency[count], "ms";
             print "P50 延遲:", latency[int(count*0.5)], "ms";
             print "P95 延遲:", latency[int(count*0.95)], "ms";
             print "P99 延遲:", latency[int(count*0.99)], "ms";
         }'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

#!/bin/bash
# 效能比較測試腳本
cat > /tmp/benchmark_comparison.sh << 'SCRIPT'
#!/bin/bash

RESULT_FILE="/tmp/benchmark_results.csv"
echo "clients,threads,duration,tps,latency_avg,latency_stddev" > $RESULT_FILE

# 測試不同的客戶端數量
for clients in 10 20 50 100 200; do
    threads=$((clients / 5))
    [ $threads -lt 1 ] && threads=1
    [ $threads -gt 16 ] && threads=16

    echo "測試: clients=$clients, threads=$threads"

    result=$(pgbench -c $clients -j $threads -T 60 \
        -h localhost -U benchmark_user benchmark_db 2>&1)

    tps=$(echo "$result" | grep "tps = " | tail -1 | awk '{print $3}')
    latency_avg=$(echo "$result" | grep "latency average" | awk '{print $4}')
    latency_std=$(echo "$result" | grep "latency stddev" | awk '{print $4}')

    echo "$clients,$threads,60,$tps,$latency_avg,$latency_std" >> $RESULT_FILE

    # 等待系統穩定
    sleep 10
done

echo "結果已儲存至 $RESULT_FILE"
cat $RESULT_FILE
SCRIPT

chmod +x /tmp/benchmark_comparison.sh

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

# 使用 gnuplot 繪製效能圖表
cat > /tmp/plot_results.gnuplot << 'EOF'
set terminal png size 1200,600
set output '/tmp/benchmark_chart.png'
set datafile separator ","
set title "PostgreSQL 效能基準測試結果"
set xlabel "並行客戶端數"
set ylabel "TPS (每秒交易數)"
set y2label "延遲 (ms)"
set y2tics
set grid
set key top left

plot '/tmp/benchmark_results.csv' using 1:4 with linespoints title 'TPS' axis x1y1, \
     '' using 1:5 with linespoints title '平均延遲' axis x1y2
EOF

# 執行繪圖（需安裝 gnuplot）
# sudo apt install -y gnuplot
# gnuplot /tmp/plot_results.gnuplot

1
2
3
4
5
6
7
8
9

# 編輯 PostgreSQL 設定
sudo nano /etc/postgresql/14/main/postgresql.conf

# 重要記憶體參數
shared_buffers = 4GB                    # 建議設為總記憶體的 25%
effective_cache_size = 12GB             # 建議設為總記憶體的 75%
work_mem = 256MB                        # 每個排序/雜湊操作的記憶體
maintenance_work_mem = 1GB              # 維護操作（如 VACUUM）的記憶體
wal_buffers = 64MB                      # WAL 緩衝區大小

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# WAL 相關設定
wal_level = replica
max_wal_size = 4GB                      # 檢查點間隔的最大 WAL 大小
min_wal_size = 1GB                      # 保留的最小 WAL 大小
checkpoint_completion_target = 0.9       # 檢查點完成時間佔比
checkpoint_timeout = 15min              # 檢查點超時時間

# 效能優化
synchronous_commit = off                # 非同步提交（犧牲少量持久性換取效能）
full_page_writes = on                   # 保持開啟以確保資料完整性

1
2
3
4
5
6
7
8

# 連線設定
max_connections = 200                   # 最大連線數
superuser_reserved_connections = 5      # 保留給超級使用者的連線

# 並行設定
max_parallel_workers_per_gather = 4     # 每個查詢的最大平行工作程序
max_parallel_workers = 8                # 系統最大平行工作程序
max_worker_processes = 12               # 最大背景工作程序

1
2
3
4
5
6
7

# 成本估算參數
random_page_cost = 1.1                  # SSD 建議設為 1.1
effective_io_concurrency = 200          # SSD 建議設為 200
seq_page_cost = 1.0                     # 循序讀取成本

# 統計資訊
default_statistics_target = 200         # 統計樣本大小（預設 100）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# 重新載入設定
sudo systemctl reload postgresql

# 驗證設定已生效
psql -h localhost -U postgres -c "SHOW shared_buffers;"
psql -h localhost -U postgres -c "SHOW effective_cache_size;"
psql -h localhost -U postgres -c "SHOW work_mem;"

# 執行調校後的效能測試
pgbench -c 50 -j 8 -T 120 -P 10 -h localhost -U benchmark_user benchmark_db

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

# 調整 Linux 核心參數
sudo tee /etc/sysctl.d/99-postgresql.conf << 'EOF'
# 增加共享記憶體限制
vm.overcommit_memory = 2
vm.overcommit_ratio = 80

# 減少 swap 使用
vm.swappiness = 10

# 增加檔案描述符限制
fs.file-max = 65536

# 網路優化
net.core.somaxconn = 65535
net.ipv4.tcp_max_syn_backlog = 65535
EOF

# 套用設定
sudo sysctl -p /etc/sysctl.d/99-postgresql.conf

# 調整使用者限制
sudo tee /etc/security/limits.d/postgresql.conf << 'EOF'
postgres soft nofile 65536
postgres hard nofile 65536
postgres soft nproc 65536
postgres hard nproc 65536
EOF