概述

postgresql自帶提供了一款輕量級的壓力測試工具：pgbench ，可自行編寫腳本，按自己的需求對數據庫進行性能壓力測試。

這個工具是作者Greg Smith在使用原生pgbench過程中，發現每次運行產生的結果都不一致，而這個工具就正好解決了問題，解決方案：每個測試用例都運行N次，并且這些用例比通常用要更多的事務，最后將這些測試結果計算出一個平均值。

參考：
https://www.postgresql.org/docs/current/pgbench.html

一、概念(在PostgreSQL中執行基準線測試)

pgbench is a simple program for running benchmark tests on PostgreSQL. It runs the same sequence of SQL commands over and over, possibly in multiple concurrent database sessions, and then calculates the average transaction rate (transactions per second). By default, pgbench tests a scenario that is loosely based on TPC-B, involving five SELECT, UPDATE, and INSERT commands per transaction. However, it is easy to test other cases by writing your own transaction script files.

pgbench是一個用于在PostgreSQL數據庫中運行基準測試的簡單程序。pgbench在多個并發的數據庫會話中反復運行一系列相同的SQL命令，并計算事務執行的平均速率（每秒執行的事務個數）。 pgbench默認測試的是一種基于TPC-B的松散的測試，即一個事務中包括5個SELECT,UPDATE和INSERT語句。同時允許基于開發者自己書寫的事務腳本文件進行其他場景的測試。

二、語法

pgbench -i [option...] [dbname]

pgbench [OPTIONS]... [DBNAME]

說明：

Initialization options: # 初始化選項
  -i, --initialize         invokes initialization mode # 初始化數據庫
  -F, --fillfactor=NUM     set fill factor  # 設定填充因子(填充因子-對表的填充因子，類似oracle的pctfree作用)
  -n, --no-vacuum          do not run VACUUM after initialization # 完成后不收縮
  -q, --quiet              quiet logging (one message each 5 seconds) # 靜默模式
  -s, --scale=NUM          scaling factor # 規模因子（比例因子-控制表的數據大?。?  --foreign-keys           create foreign key constraints between tables # 在表間創建外鍵
  --index-tablespace=TABLESPACE
                           create indexes in the specified tablespace # 在指定表空間創建索引
  --tablespace=TABLESPACE  create tables in the specified tablespace # 在指定表空間創建表
  --unlogged-tables        create tables as unlogged tables # 創建unlogged類型的表

Options to select what to run:
  -b, --builtin=NAME[@W]   add builtin script NAME weighted at W (default: 1)
                           (use "-b list" to list available scripts)
  -f, --file=FILENAME[@W]  add script FILENAME weighted at W (default: 1)
  -N, --skip-some-updates  skip updates of pgbench_tellers and pgbench_branches
                           (same as "-b simple-update")
  -S, --select-only        perform SELECT-only transactions
                           (same as "-b select-only")

Benchmarking options:
  -c, --client=NUM         number of concurrent database clients (default: 1) # 模擬客戶端數
  -C, --connect            establish new connection for each transaction # 為每個事務啟用新鏈接
  -D, --define=VARNAME=VALUE
                           define variable for use by custom script # 用戶腳本定義的自定義變量
  -j, --jobs=NUM           number of threads (default: 1) # 工作線程數
  -l, --log                write transaction times to log file # 記錄每個事務的時間
  -L, --latency-limit=NUM  count transactions lasting more than NUM ms as late # 標記超時
  -M, --protocol=simple|extended|prepared
                           protocol for submitting queries (default: simple) # 使用的查詢協議，默認simple，老高建議使用prepared比較接近實際需求
  -n, --no-vacuum          do not run VACUUM before tests # 測試前不收縮表
  -P, --progress=NUM       show thread progress report every NUM seconds #  # 每隔$$秒輸出一次線程進度報告
  -r, --report-latencies   report average latency per command # 報告每個sql的平均執行延遲
  -R, --rate=NUM           target rate in transactions per second # 目標TPS
  -s, --scale=NUM          report this scale factor in output # 在輸出中報告規模因子
  -t, --transactions=NUM   number of transactions each client runs (default: 10) # 每個客戶端執行的事務數
  -T, --time=NUM           duration of benchmark test in seconds # 測試執行時間
  -v, --vacuum-all         vacuum all four standard tables before tests # 測試前收縮表
  --aggregate-interval=NUM aggregate data over NUM seconds # 每隔N秒聚合一次數據
  --log-prefix=PREFIX      prefix for transaction time log file
                           (default: "pgbench_log")
  --progress-timestamp     use Unix epoch timestamps for progress
  --sampling-rate=NUM      fraction of transactions to log (e.g., 0.01 for 1%)

Common options:
  -d, --debug              print debugging output
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -U, --username=USERNAME  connect as specified database user
  -V, --version            output version information, then exit
  -?, --help               show this help, then exit

三、pgbench壓測實驗

1、環境準備

1.1、創建測試用戶

psql -U postgres -p 5432
》CREATE ROLE pgbench LOGIN
  ENCRYPTED PASSword 'pgbench'
  SUPERUSER INHERIT CREATEDB NOCREATEROLE REPLICATION;

1.2、創建測試表空間

CREATE TABLESPACE pgbench_tbs
  OWNER pgbench
  LOCATION '/data/pgdata/pgbench';

1.3、創建測試庫pg_bench

CREATE DATABASE pgbench
  WITH OWNER = pgbench
       ENCODING = 'UTF8'
       TABLESPACE = pgbench_tbs;

2、初始化環境

使用命令：pgbench -i [ other-options ] dbname

創建測試表，并填充數據5000w筆記錄

  pgbench -p 5432 -U postgres -i -F 100 -s 500 pgbench 

3、默認腳本壓力測試

pg自帶壓測腳本分別執行了insert,update,select,可有效測試數據庫吞吐能力，和并發效率

3.1、單個session壓測20s

 pgbench -p 5432 -U pgbench -c 1 -T 20 -r pgbench

3.2、30個session壓測20s

 pgbench -p 5432 -U pgbench -c 30 -T 20 -r pgbench