PostgreSQL 14 preview - libpq驱动 支持pipeline 模式, SQL请求支持异步化通信, 大幅度提升性能, 降低RT

作者

digoal

日期

2021-03-16

标签

PostgreSQL , pipeline , 异步 , libpq

背景

https://www.postgresql.org/docs/devel/libpq-pipeline-mode.html

pipeline为异步请求模式, 如果client要与数据库进行一连串的SQL交互, 而且这些SQL的返回结果没有前后依赖, 那么可以使用pipeline模式, 发起SQL1后不需要等SQL1的结果即可发起SQL2的请求.

在网络延迟很高的场景使用pipeline模式性能提升非常明显, 而SQL有前后依赖的场景不适合pipeline模式.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=acb7e4eb6b1c614c68a62fb3a6a5bba1af0a2659

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9aa491abbf07ca8385a429385be8d68517384fdf

```
Implement pipeline mode in libpq
author Alvaro Herrera alvherre@alvh.no-ip.org
Mon, 15 Mar 2021 21:13:42 +0000 (18:13 -0300)
committer Alvaro Herrera alvherre@alvh.no-ip.org
Mon, 15 Mar 2021 21:13:42 +0000 (18:13 -0300)
commit acb7e4eb6b1c614c68a62fb3a6a5bba1af0a2659
tree ff5dccb6a8372d0373a442841d8df4333a234eaa tree
parent 146cb3889c3ccb3fce198fe7464a1296a9e107c3 commit | diff
Implement pipeline mode in libpq

Pipeline mode in libpq lets an application avoid the Sync messages in
the FE/BE protocol that are implicit in the old libpq API after each
query. The application can then insert Sync at its leisure with a new
libpq function PQpipelineSync. This can lead to substantial reductions
in query latency.

Co-authored-by: Craig Ringer craig.ringer@enterprisedb.com
Co-authored-by: Matthieu Garrigues matthieu.garrigues@gmail.com
Co-authored-by: Álvaro Herrera alvherre@alvh.no-ip.org
Reviewed-by: Andres Freund andres@anarazel.de
Reviewed-by: Aya Iwata iwata.aya@jp.fujitsu.com
Reviewed-by: Daniel Vérité daniel@manitou-mail.org
Reviewed-by: David G. Johnston david.g.johnston@gmail.com
Reviewed-by: Justin Pryzby pryzby@telsasoft.com
Reviewed-by: Kirk Jamison k.jamison@fujitsu.com
Reviewed-by: Michael Paquier michael.paquier@gmail.com
Reviewed-by: Nikhil Sontakke nikhils@2ndquadrant.com
Reviewed-by: Vaishnavi Prabakaran VaishnaviP@fast.au.fujitsu.com
Reviewed-by: Zhihong Yu zyu@yugabyte.com
Discussion: https://postgr.es/m/CAMsr+YFUjJytRyV4J-16bEoiZyH=4nj+sQ7JP9ajwz=B4dMMZw@mail.gmail.com
Discussion: https://postgr.es/m/CAJkzx4T5E-2cQe3dtv2R78dYFvz+in8PY7A8MArvLhs_pg75gg@mail.gmail.com
```

pgbench 率先支持pipeline使用,

```
Add libpq pipeline mode support to pgbench
author Alvaro Herrera alvherre@alvh.no-ip.org
Mon, 15 Mar 2021 21:33:03 +0000 (18:33 -0300)
committer Alvaro Herrera alvherre@alvh.no-ip.org
Mon, 15 Mar 2021 21:33:03 +0000 (18:33 -0300)
commit 9aa491abbf07ca8385a429385be8d68517384fdf
tree 4dbfcf06d0b125fdcb4f3568bdc840f3c2d4c20d tree
parent acb7e4eb6b1c614c68a62fb3a6a5bba1af0a2659 commit | diff
Add libpq pipeline mode support to pgbench

New metacommands \startpipeline and \endpipeline allow the user to run
queries in libpq pipeline mode.

Author: Daniel Vérité daniel@manitou-mail.org
Reviewed-by: Álvaro Herrera alvherre@alvh.no-ip.org
Discussion: https://postgr.es/m/b4e34135-2bd9-4b8a-94ca-27d760da26d7@manitou-mail.org
```

+ <para> + <literal>\gset</literal> and <literal>\aset</literal> cannot be used in + pipeline mode, since the query results are not yet available by the time + the commands would need them. + </para> + <para> The following example puts the final account balance from the first query into variable <replaceable>abalance</replaceable>, and fills variables @@ -1270,6 +1276,22 @@ SELECT 4 AS four \; SELECT 5 AS five \aset </programlisting></para> </listitem> </varlistentry> + + <varlistentry id='pgbench-metacommand-pipeline'> + <term><literal>\startpipeline</literal></term> + <term><literal>\endpipeline</literal></term> + + <listitem> + <para> + These commands delimit the start and end of a pipeline of SQL + statements. In pipeline mode, statements are sent to the server + without waiting for the results of previous statements. See + <xref linkend="libpq-pipeline-mode"/> for more details. + Pipeline mode requires the use of extended query protocol. + </para> + </listitem> + </varlistentry>

pgbench的pipeline使用方法;

```
\startpipeline
-- 把多条SQL放这, 不需要等SQL结果返回即可连续发射
-- sql1
-- sql2
-- sql3
...
\endpipeline

-- 外面的sql又可以继续使用非pipeline的模式
```

例子

pgbench -M prepared -n -r -P 1 -f ./t.sql -c 16 -j 16 -T 5 progress: 1.0 s, 2893.0 tps, lat 5.376 ms stddev 11.833 progress: 2.0 s, 3126.0 tps, lat 5.110 ms stddev 16.975 progress: 3.0 s, 3106.0 tps, lat 5.179 ms stddev 17.444 progress: 4.0 s, 3003.0 tps, lat 5.298 ms stddev 17.140 progress: 5.0 s, 2982.0 tps, lat 5.366 ms stddev 17.016 pgbench (PostgreSQL) 14.0 transaction type: ./t.sql scaling factor: 1 query mode: prepared number of clients: 16 number of threads: 16 duration: 5 s number of transactions actually processed: 15128 latency average = 5.278 ms latency stddev = 16.270 ms initial connection time = 14.798 ms tps = 3029.043417 (without initial connection time) statement latencies in milliseconds: 0.000 \set size 10000000 0.001 \set r random_zipfian(1, :size, 2) 0.001 \set k permute(:r, :size) 0.078 begin; 0.111 insert into tbl values (:r, :k); 0.093 insert into tbl values (:r, :k); 0.083 insert into tbl values (:r, :k); 0.071 insert into tbl values (:r, :k); 0.113 insert into tbl values (:r, :k); 0.129 insert into tbl values (:r, :k); 0.084 insert into tbl values (:r, :k); 0.079 insert into tbl values (:r, :k); 0.093 insert into tbl values (:r, :k); 0.107 insert into tbl values (:r, :k); 0.093 insert into tbl values (:r, :k); 0.151 insert into tbl values (:r, :k); 0.094 insert into tbl values (:r, :k); 0.089 insert into tbl values (:r, :k); 0.128 insert into tbl values (:r, :k); 0.079 insert into tbl values (:r, :k); 0.085 insert into tbl values (:r, :k); 0.101 insert into tbl values (:r, :k); 0.123 insert into tbl values (:r, :k); 0.097 insert into tbl values (:r, :k); 0.109 insert into tbl values (:r, :k); 0.093 insert into tbl values (:r, :k); 0.118 insert into tbl values (:r, :k); 0.118 insert into tbl values (:r, :k); 0.128 insert into tbl values (:r, :k); 0.112 insert into tbl values (:r, :k); 0.087 insert into tbl values (:r, :k); 0.103 insert into tbl values (:r, :k); 0.101 insert into tbl values (:r, :k); 0.097 insert into tbl values (:r, :k); 0.136 insert into tbl values (:r, :k); 0.103 insert into tbl values (:r, :k); 0.097 insert into tbl values (:r, :k); 0.142 insert into tbl values (:r, :k); 0.082 insert into tbl values (:r, :k); 0.127 insert into tbl values (:r, :k); 0.078 insert into tbl values (:r, :k); 0.105 insert into tbl values (:r, :k); 0.076 insert into tbl values (:r, :k); 0.116 insert into tbl values (:r, :k); 0.105 insert into tbl values (:r, :k); 0.113 insert into tbl values (:r, :k); 0.100 insert into tbl values (:r, :k); 0.107 insert into tbl values (:r, :k); 0.099 insert into tbl values (:r, :k); 0.106 insert into tbl values (:r, :k); 0.108 insert into tbl values (:r, :k); 0.103 insert into tbl values (:r, :k); 0.107 insert into tbl values (:r, :k); 0.120 end;

pgbench -M prepared -n -r -P 1 -f ./t.sql -c 16 -j 16 -T 5 progress: 1.0 s, 13276.8 tps, lat 1.170 ms stddev 4.170 progress: 2.0 s, 13738.1 tps, lat 1.155 ms stddev 5.102 progress: 3.0 s, 13290.7 tps, lat 1.224 ms stddev 5.711 progress: 4.0 s, 13264.4 tps, lat 1.188 ms stddev 5.630 progress: 5.0 s, 12139.2 tps, lat 1.310 ms stddev 6.359 pgbench (PostgreSQL) 14.0 transaction type: ./t.sql scaling factor: 1 query mode: prepared number of clients: 16 number of threads: 16 duration: 5 s number of transactions actually processed: 65765 latency average = 1.213 ms latency stddev = 5.459 ms initial connection time = 17.293 ms tps = 13174.110194 (without initial connection time) statement latencies in milliseconds: 0.000 \set size 10000000 0.001 \set r random_zipfian(1, :size, 2) 0.001 \set k permute(:r, :size) 0.000 \startpipeline 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 0.000 insert into tbl values (:r, :k); 1.214 \endpipeline

性能提升多少倍取决于每一次交互的延迟在整个请求中的时间占比.

例如以上例子, 本地环境, 网络延迟只有0.1毫秒左右. 50条SQL, 采用pipeline模式比普通事务性能提升4倍. 如果网络延迟达到1毫秒, 性能将提升50倍.

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.