如何為PostgreSQL進行基準測試以達最佳性能

隨著 PostgreSQL 的應用日益增長，資料庫管理員（DBAs）和開發人員經常需要評估其性能，以確保其應用在不同工作負載下運行效率。性能基準測試是一個關鍵過程，用於衡量 PostgreSQL 處理不同負載的能力，有助於識別瓶頸和優化的領域。本文探討了工具、指標和測試方案，幫助您像專家一樣進行 PostgreSQL 的基準測試。

為什麼要進行 PostgreSQL 基準測試？

基準測試讓您可以：

1. 測量資料庫在特定工作負載下的吞吐量和延遲。
2. 識別硬體或配置瓶頸。
3. 比較優化的影響，如索引更改或查詢重寫。
4. 模擬高並發用戶活動或批量數據寫入等真實場景。

要追蹤的關鍵指標

在基準測試 PostgreSQL 時，著重關注這些指標：

• TPS（每秒交易數）：衡量資料庫在一秒內完成多少交易。
• IOPS（每秒輸入/輸出操作數）：跟蹤磁碟活動。
• 延遲：衡量執行查詢所需的時間，影響用戶體驗。
• 資源利用率：在基準測試期間跟蹤 CPU、記憶體和磁碟使用情況。

用於 PostgreSQL 基準測試的工具

1. pgbench

pgbench是什麼？

pgbench是PostgreSQL內建的基準測試工具。它模擬並行客戶端執行交易並測量數據庫的性能。

安裝

它與PostgreSQL安裝捆綁在一起。要進行驗證，運行：

bash
pgbench --version

入門

1. 初始化基準測試數據庫：

bash
pgbench -i -s 50 mydb

這裡，-s設置比例因子，用於確定數據集的大小。

2. 運行簡單基準測試：

bash
pgbench -c 10 -j 2 -T 60 mydb

• -c 10：客戶端連接數。
• -j 2：執行緒數。
• -T 60：基準測試持續時間（秒）。

範例輸出：

transaction type: TPC-B (sort of)
scaling factor: 50
number of clients: 10
number of threads: 2
duration: 60 s
tps = 1420.123 (excluding connections establishing)

2. Sysbench

為什麼使用Sysbench？

Sysbench是一個多功能的數據庫和系統基準測試工具。它比pgbench提供更多自定義工作負載的彈性。

安裝

使用以下命令安裝Sysbench：

bash
sudo apt-get install sysbench

入門

1. 準備基準測試：

bash
sysbench --db-driver=pgsql --pgsql-db=mydb \
  --pgsql-user=postgres --tables=10 --table-size=1000000 \
  oltp_read_write prepare

2. 運行基準測試：

bash
sysbench --db-driver=pgsql --pgsql-db=mydb \
  --pgsql-user=postgres --threads=4 \
  --time=60 oltp_read_write run

3. pg_stat_statements

pg_stat_statements是什麼？

一個追蹤查詢性能和執行統計的 PostgreSQL 擴展功能。雖然它不模擬工作負載，但有助於分析基準測試期間的慢查詢。

設置

1. 在 postgresql.conf 中啟用擴展：

shared_preload_libraries = 'pg_stat_statements'

2. 重新載入配置並創建擴展：

CREATE EXTENSION pg_stat_statements;

使用方法

運行以下查詢來識別運行時間長的語句：

SELECT query, total_exec_time, calls
FROM pg_stat_statements
ORDER BY total_exec_time DESC;

基準測試情景

以下是三種情景（讀取密集、寫入密集和混合工作負載）的基準結果的視覺表示。每秒事務數（TPS）圖表展示了 PostgreSQL 處理並發事務的效率，而延遲圖表說明了查詢執行所需的時間（以毫秒為單位）。

PostgreSQL 每秒事務數（TPS）

PostgreSQL 查詢延遲

工作負載類型

1. 讀取密集型工作負載

目標：測試在高讀取活動下的數據庫性能。

設置：使用默認的只讀事務 pgbench：

bash
pgbench -c 50 -T 120 -S mydb

• -S：僅執行 SELECT 查詢。
• -c 50：模擬 50 個並發客戶端。

2. 寫入密集型工作負載

目標：衡量具有頻繁插入或更新的數據庫性能。

設置： 修改基準測試以包括寫入操作：

bash
pgbench -c 20 -j 4 -T 120 -N mydb

• -N: 執行非SELECT查詢。

3. 混合讀寫工作負載

目標: 模擬一個現實世界中混合讀取和寫入的工作負載。

設置: 使用平衡的配置:

bash
pgbench -c 30 -j 4 -T 180 mydb

為了更好的基準結果優化PostgreSQL

調整內存設置

在postgresql.conf中調整這些參數:

shared_buffers = 25% of system memory
work_mem = 4MB
maintenance_work_mem = 64MB

啟用並行查詢執行

在postgresql.conf中調整這些參數:

max_parallel_workers_per_gather = 4

優化磁碟I/O

對WAL文件使用SSD並調整這些設置:

wal_buffers = 16MB
synchronous_commit = off

示例結果和解釋

場景: 50個同時客戶端在60秒內運行重讀工作負載。

輸出:

tps = 2500.456 (excluding connections establishing)

解釋: 在此工作負載下，數據庫能夠處理每秒2500筆交易。

如果TPS低於預期，使用EXPLAIN ANALYZE分析查詢計劃以識別性能瓶頸。

結論

基準測試PostgreSQL是識別性能限制並針對各種工作負載優化您的數據庫的有效方法。像pgbench和sysbench這樣的工具，結合pg_stat_statements提供的見解，使您能夠模擬真實場景並微調 PostgreSQL 配置。

通過掌握這些工具和技巧，您可以確保您的 PostgreSQL 實例為讀取密集型和寫入密集型應用提供高性能。