PG 15 beta1 安装与性能测试

原创大表哥 2022-06-02

2563

大家好，应小墨的邀请，这期来一篇PG最新版本15 beta1 的安装和性能测试。

我们先从官网下载一下，本次我们选择的是源代码自己编译的方式安装。

https://www.postgresql.org/ftp/source/

我们鼠标右键获取到下载地址，并服务器上进行下载：

INFRA [postgres@wqdcsrv3352 pg15]# wget https://ftp.postgresql.org/pub/source/v15beta1/postgresql-15beta1.tar.bz2 --no-check-certificate
--2022-06-02 10:21:23--  https://ftp.postgresql.org/pub/source/v15beta1/postgresql-15beta1.tar.bz2
Resolving ftp.postgresql.org (ftp.postgresql.org)... 87.238.57.227, 217.196.149.55, 72.32.157.246, ...
Connecting to ftp.postgresql.org (ftp.postgresql.org)|87.238.57.227|:443... connected.
WARNING: cannot verify ftp.postgresql.org's certificate, issued by ‘/C=US/O=Let's Encrypt/CN=R3’:
  Issued certificate has expired.
HTTP request sent, awaiting response... 200 OK
Length: 23518203 (22M) [application/octet-stream]
Saving to: ‘postgresql-15beta1.tar.bz2’


100%[===================================================================================================================================================>] 23,518,203  5.40MB/s   in 4.2s   


2022-06-02 10:21:29 (5.40 MB/s) - ‘postgresql-15beta1.tar.bz2’ saved [23518203/23518203]

解压安装包：

INFRA [postgres@wqdcsrv3352 pg15]# tar -xvf postgresql-15beta1.tar.bz2

安装RPM依赖包：

yum groupinstall "Development tools"
yum install -y bison flex readline-devel zlib-devel

我们进行源码的编译和安装:

比起来yum 的一站式的安装，编译源代码的方式更加灵活，我们在源代码编译可以指定一些参数，

例如，众所周知PG是OLTP和OLAP都都支持的混动数据库。不同类型的数据库的block 的大小也是不同的，

OLTP的系统默认是8K，对于OLAP的系统，你可以指定参数–with-blocksize = 32 表示数据块是32KB

下面我们来编译一个OLTP的数据库

INFRA [postgres@wqdcsrv3352 postgresql-15beta1]# ./configure --prefix=/opt/postgreSQL/pg15 --with-pgport=1992 –with-blocksize=8INFRA [postgres@wqdcsrv3352 postgresql-15beta1]# gmake && gmake install

编译安装完成后验证一下版本：

INFRA [postgres@wqdcsrv3352 postgresql-15beta1]# /opt/postgreSQL/pg15/bin/postgres --version
postgres (PostgreSQL) 15beta1

安装完数据库的软件之后，我们来初始化一下数据库：

创建相关的文件夹

INFRA [postgres@wqdcsrv3352 postgreSQL]# mkdir -p /data/postgreSQL/1992/{data,backups,scripts,archive_wals}

初始化数据库：

INFRA [postgres@wqdcsrv3352 postgreSQL]# /opt/postgreSQL/pg15/bin/initdb -D /data/postgreSQL/1992/data/ -W
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.


The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".


Data page checksums are disabled.


Enter new superuser password:
Enter it again:


fixing permissions on existing directory /data/postgreSQL/1992/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok


initdb: warning: enabling "trust" authentication for local connections
hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.


Success. You can now start the database server using:


    /opt/postgreSQL/pg15/bin/pg_ctl -D /data/postgreSQL/1992/data/ -l logfile start

我们启动数据库：

INFRA [postgres@wqdcsrv3352 postgreSQL]# /opt/postgreSQL/pg15/bin/pg_ctl -D /data/postgreSQL/1992/data/ -l logfile start
waiting for server to start.... done
server started

我们尝试登陆之前数据库，至此PG15 安装完成: psql -h 127.0.0.1 -p 1992

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

Okay. 至此我们完成了PG 15 beta1的安装。

在接下来的性能之前，我们启动一下pg_exporter 的客户端，以便在grafana 上进行性能测试的监控。

从目前官网上看，还没有出现支持PG14,15的exporter, https://github.com/prometheus-community/postgres_exporter

但是个人认为，应该也能兼容新的版本，问题应该不大。我们启动一下postgre exporter:

PG_EXPORTER_WEB_LISTEN_ADDRESS 这个是 postgres exporter的端口的地址 9188

#!/bin/bash

set -x 
export PG_EXPORTER_WEB_LISTEN_ADDRESS=":9188"
export DATA_SOURCE_NAME="postgresql://postgres:*****@127.0.0.1:1992/postgres?sslmode=disable" 

./postgres_exporter >> exporter_1992.log 2>&1 &

我们查看一下web 服务：

我们再从grafana 的dashboard 上看一下 PG15：

All right! 完事具备，只差运行性能测试的命令：我们采用的是 pgbench.

我们先初始化数据：-i 表示数据的初始化

INFRA [postgres@wqdcsrv3352 postgreSQL]# pgbench -i -s 16 -U postgres -p 1992 -d pgbench
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...
100000 of 1600000 tuples (6%) done (elapsed 0.06 s, remaining 0.93 s)
200000 of 1600000 tuples (12%) done (elapsed 0.17 s, remaining 1.16 s)
300000 of 1600000 tuples (18%) done (elapsed 0.28 s, remaining 1.21 s)
400000 of 1600000 tuples (25%) done (elapsed 0.35 s, remaining 1.06 s)
500000 of 1600000 tuples (31%) done (elapsed 0.46 s, remaining 1.01 s)
600000 of 1600000 tuples (37%) done (elapsed 0.57 s, remaining 0.96 s)
700000 of 1600000 tuples (43%) done (elapsed 0.65 s, remaining 0.83 s)
800000 of 1600000 tuples (50%) done (elapsed 0.75 s, remaining 0.75 s)
900000 of 1600000 tuples (56%) done (elapsed 0.86 s, remaining 0.67 s)
1000000 of 1600000 tuples (62%) done (elapsed 0.93 s, remaining 0.56 s)
1100000 of 1600000 tuples (68%) done (elapsed 1.03 s, remaining 0.47 s)
1200000 of 1600000 tuples (75%) done (elapsed 1.12 s, remaining 0.37 s)
1300000 of 1600000 tuples (81%) done (elapsed 1.23 s, remaining 0.28 s)
1400000 of 1600000 tuples (87%) done (elapsed 1.37 s, remaining 0.20 s)
1500000 of 1600000 tuples (93%) done (elapsed 1.52 s, remaining 0.10 s)
1600000 of 1600000 tuples (100%) done (elapsed 1.64 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.

下面我们来模拟 16个客户端线程，进行并发压测10分钟

（虚拟机的配置，cpu 8 core, memory 4GB, 测试机的配置，不要报太大的期望！）

INFRA [postgres@wqdcsrv3352 ~]# pgbench -M prepared -r -c 16 -j 8 -T 300 -U postgres -p 1992 -d pgbench -l

-M prepared表示绑定变量形式的调用SQL，
-r表示报告测试文件中每条SQL的平均执行延迟，
-c 16表示模拟16个客户端，
-j 8表示pgbench的工作线程是8个，
-T 表示压力测试的时间是300秒，

运行了10分钟测试结束后，会自动生成一个统计的report:

transaction type: <builtin: TPC-B (sort of)>
scaling factor: 16
query mode: prepared
number of clients: 16
number of threads: 8
duration: 300 s
number of transactions actually processed: 203377
latency average = 23.603 ms
tps = 677.865777 (including connections establishing)
tps = 677.879690 (excluding connections establishing)
statement latencies in milliseconds:
         0.433  \set aid random(1, 100000 * :scale)
         0.438  \set bid random(1, 1 * :scale)
         0.335  \set tid random(1, 10 * :scale)
         0.389  \set delta random(-5000, 5000)
         1.255  BEGIN;
         2.061  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         1.755  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         2.442  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         4.843  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         1.748  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         6.805  END;