在本文中，咱们将探讨盘口如何近程搭建出对 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 --helppgbench 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 postgresdropping old tables...NOTICE:  table "pgbench_accounts" does not exist, skippingNOTICE:  table "pgbench_branches" does not exist, skippingNOTICE:  table "pgbench_history" does not exist, skippingNOTICE:  table "pgbench_tellers" does not exist, skippingcreating 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.resultsPassword: pgbench: pghost: 10.0.x.5 pgport: 5432 nclients: 3 nxacts: 100 dbName: postgresstarting vacuum...end.pgbench: client 0 executing script "<builtin: TPC-B (sort of)>"pgbench: client 0 executing \set aidpgbench: client 5 executing script "<builtin: TPC-B (sort of)>"pgbench: client 5 executing \set aidpgbench: client 0 executing \set bidpgbench: client 0 executing \set tidpgbench: client 5 executing \set bid......

该过程实现后，将生成如下所示的日志文件。此文件中蕴含的信息代表咱们基准测试的后果。以下是我应用的根本零碎的数据。

pgbench (14.2 (Debian 14.2-1.pgdg110+1))transaction type: <builtin: TPC-B (sort of)>scaling factor: 1query mode: simplenumber of clients: 2number of threads: 2number of transactions per client: 100number of transactions actually processed: 200/200latency average = 71.099 msinitial connection time = 9.067 mstps = 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.resultsPassword: pgbench: client 0 receivingpgbench: client 2 executing \set aidpgbench: client 2 executing \set bidpgbench: client 2 executing \set tidpgbench: client 1 receivingpgbench: client 2 executing \set deltapgbench: 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 receivingpgbench: client 2 receivingpgbench: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: 1query mode: simplenumber of clients: 2number of threads: 2number of transactions per client: 100number of transactions actually processed: 200/200latency average = 48.882 msinitial connection time = 15.425 mstps = 40.914923 (without initial connection time)