在本文中，我们将讨论盘口如何远程搭建出对 HAProxy 后面的 postgresql 集群进行基准测试。

SegmentFault用户【TG电报duotebb】版权所有，禁止转载。

在我们开始之前，谈谈我在写这篇文章时设计的环境元素是很有用的。

1. 1 adet HAProxy
2. 2 adet Etcd
3. 2 adet Postgresql-14 节点（2GB RAM，32GB 磁盘空间）

首先，我们可以谈谈如果我们没有冗余结构，我们如何进行基准测试。我们对此只有一个要求，那就是postgresql-contrib包装。让我们在安装了 Postgresql 的基于 debian 的服务器上设置我们的依赖项，如下所示。

sudo apt install postgresql-contrib

有了这个包，pgbench命令就可以使用了。我们可以像下面这样运行帮助参数来查看它是否安装正确。

zeki@kubuntu21:~$ pgbench --help
pgbench is a benchmarking tool for PostgreSQL.

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

Initialization options:
  -i, --initialize         invokes initialization mode
  -I, --init-steps=[dtgGvpf]+ (default "dtgvp")
                           run selected initialization steps
  -F, --fillfactor=NUM     set fill factor
  -n, --no-vacuum          do not run VACUUM during initialization
  -q, --quiet              quiet logging (one message each 5 seconds)
  -s, --scale=NUM          scaling factor
  --foreign-keys           create foreign key constraints between table
.
..
....

在确保我们的命令正常工作后，我们就可以开始我们的操作了。

1.为pgbench初始化必要的表

在使用 pgbench 之前，让我们使用-i参数对其进行初始化，如下所示。

sudo -u $DATABASE_USER pgbench -i -p $DATABASE_PORT -d $DATABASE_NAME

sudo -u postgres pgbench -i -p 5432 -d postgres
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.42 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 3.90 s (drop tables 0.01 s, create tables 0.69 s, client-side generate 0.72 s, vacuum 0.48 s, primary keys 2.01 s).

2.运行pgbench

我们可以如下开始我们的 pgbench 测试。

sudo -u $DATABASE_USER pgbench -c $CLIENT_NUM -j $JOB_NUM -t $TRANSACTION_NUM -d $DATABASE_NAME > benchmark.results

当我们按照上面的格式运行一个示例基准测试时，它将如下所示。

sudo -u postgres pgbench -c 2 -j 2 -t 100 -d postgres  > benchmark.results
Password: 
pgbench: pghost: 10.0.x.5 pgport: 5432 nclients: 3 nxacts: 100 dbName: postgres
starting vacuum...end.
pgbench: client 0 executing script "<builtin: TPC-B (sort of)>"
pgbench: client 0 executing \set aid
pgbench: client 5 executing script "<builtin: TPC-B (sort of)>"
pgbench: client 5 executing \set aid
pgbench: client 0 executing \set bid
pgbench: client 0 executing \set tid
pgbench: client 5 executing \set bid
.
..
...

该过程完成后，将生成如下所示的日志文件。此文件中包含的信息代表我们基准测试的结果。以下是我使用的基本系统的数据。

pgbench (14.2 (Debian 14.2-1.pgdg110+1))
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 2
number of threads: 2
number of transactions per client: 100
number of transactions actually processed: 200/200
latency average = 71.099 ms
initial connection time = 9.067 ms
tps = 28.129593 (without initial connection time)

1. 远程运行 pgbench

除了上述之外，只在 pgbench 参数中添加 HAProxy ip 地址和 postgresql 端口就足够了。它可以按如下方式运行。

sudo -u postgres pgbench -c 2 -j 2 -t 100 -d postgres -h 10.0.x.5 -p 5000 > benchmark.results
Password: 

pgbench: client 0 receiving
pgbench: client 2 executing \set aid
pgbench: client 2 executing \set bid
pgbench: client 2 executing \set tid
pgbench: client 1 receiving
pgbench: client 2 executing \set delta
pgbench: client 2 sending BEGIN;
pgbench: client 1 sending INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (6, 1, 73134, 4493, CURRENT_TIMESTAMP);
pgbench: client 1 receiving
pgbench: client 2 receiving
pgbench:pgbench: client 1 receiving
.
..
...

作为输出，我们可以看到如下结果。

pgbench (14.4 (Ubuntu 14.4-0ubuntu0.22.04.1), server 14.2 (Debian 14.2-1.pgdg110+1))
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 2
number of threads: 2
number of transactions per client: 100
number of transactions actually processed: 200/200
latency average = 48.882 ms
initial connection time = 15.425 ms
tps = 40.914923 (without initial connection time)