pgbench和sysbench初次压测PG集群

pgbench和sysbench初次压测PG集群

pgbench和sysbench是两个不同的压测工具；前者只适用于pg数据库，后者可以适用于mysql，pg，sql-server等常见关系型数据库。若是关于tps的测试，建议采用sysbench，因为在事务测试模式下，其所采用的sql增删查改操作相对pgbench的默认脚本tpcb-更为具体丰富。

0.pgbench压测

常见的开源数据库的基准测试工具有 benchmarksql、 sysbench 等， PostgreSQL 自带运行基准测试的简单程序 pgbench。 pgbench 是一个类 TPC-B 的基准测试工具，其中在每个事务中涉及五个SELECT、UPDATE以及INSERT命令，可以执行内置的测试脚本(内置脚本主要包括select-only、simple-update、tpcb-like)，也可以自定义脚本文件。

$ /pgbench --help
pgbench is a benchmarking tool for PostgreSQL.

Usage:
  pgbench [OPTION]... [DBNAME]

Initialization options:
  -i, --initialize         invokes initialization mode              #初始化模式
  -I, --init-steps=[dtgvpf]+ (default "dtgvp")
                           run selected initialization steps
  -F, --fillfactor=NUM     set fill factor                          #填充因子       
  -n, --no-vacuum          do not run VACUUM during initialization  #不会运行VACUUM进程删除已有数据
  -q, --quiet              quiet logging (one message each 5 seconds)#每5s打印一条信息到日志
  -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      

Options to select what to run:
  -b, --builtin=NAME[@W]   add builtin script NAME weighted at W (default: 1) #3钟内置脚本
                           (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 #-b simple-update
                           (same as "-b simple-update")
  -S, --select-only        perform SELECT-only transactions #-b select-only（只读）
                           (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)
  -n, --no-vacuum          do not run VACUUM before tests                     #不能运行VACUUM进程
  -P, --progress=NUM       show thread progress report every NUM seconds      #进程数
  -r, --report-latencies   report average latency per command                 #每一个命令上报延迟
  -R, --rate=NUM           target rate in transactions per second             
  -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
  --log-prefix=PREFIX      prefix for transaction time log file
                           (default: "pgbench_log")
  --progress-timestamp     use Unix epoch timestamps for progress
  --random-seed=SEED       set random seed ("time", "rand", integer)
  --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,