pg 15 beta1 安装与性能测试 -m6米乐安卓版下载

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

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

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 上进行性能测试的监控。

从目前m6米乐安卓版下载官网上看，还没有出现支持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: 
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;

我们可以看到tps在 677.865777 左右。

我们通过grafana的dash board 也可以监控到tps的指标：

最后想说的是： 2020-2030年是数据库国产化的10年。 pg会成为这个10年数据库国产化的主力军。