PG 15 beta1 安裝與性能測試

原創大表哥 2022-06-02

3457

大家好，應小墨的邀請，這期來一篇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, 測試機的配置，不要報太大的期望?。?/p>

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;