乐趣区

关于postgresql:盘口环境下搭建使用飞鸟Postgresql-远程源码基准信用网测试

jiezi

在本文中，咱们将探讨盘口如何近程搭建出对 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)
退出移动版