HybridDB for PostgreSQL , Greenplum 写入性能优化实践

标签

PostgreSQL , Greenplum , HybridDB for PostgreSQL , insert , 性能

背景

Greenplum写入数据的性能优化实践。

1 链路

尽量缩短客户端和数据库的链路，不要太多的跳数，比如NAT，PROXY，等越多，性能越差。

2 连接方式

尽量使用长连接，不要使用短连接，短连接的打开开销非常大。

3 存储属性

where storage_parameter is:  
   APPENDONLY={TRUE|FALSE}  
   BLOCKSIZE={8192-2097152}  
   ORIENTATION={COLUMN|ROW}  
   COMPRESSTYPE={ZLIB|QUICKLZ|RLE_TYPE|NONE}  
   COMPRESSLEVEL={0-9}  
   CHECKSUM={TRUE|FALSE}  
   FILLFACTOR={10-100}  
   OIDS[=TRUE|FALSE]  

行存与列存的选择方面，需要权衡插入与查询的风格与需求的性能，同时它们支持相互转换。

列存在单条INSERT时，性能较差，原因和列存的可靠性机制有关。

4 插入方法

1、单条INSERT，单步提交，性能最差

2、单条INSERT，批量提交

3、批量INSERT

4、COPY

5、segment并行，性能最好

5 索引

索引越多，写入性能越差。

6 绑定变量

如果是使用INSERT的单条写入方法，可以考虑使用绑定变量。减少CPU硬解析。

7 并发数

INSERT的写入，也可以使用并行（开多个连接）的方式，但是并不是开越多越好，通常不建议超过CPU核数。

8 倾斜

倾斜，数据分布一定不要出现倾斜，否则就会导致木桶效应，导致某些SEGMENT繁忙，某些空闲，不利于整体性能。

例子

1 性能较差的例子

使用列存，压缩，大BLOCK，一条一条INSERT，单步提交，使用短连接。

每秒插入19行。

create table t_bad(  
id int,   
c1 int default (random()*100)::int,   
c2 int default (random()*100)::int,   
c3 int default (random()*100)::int,   
c4 int default (random()*100)::int,   
c5 int default (random()*100)::int,   
c6 int default (random()*100)::int,   
c7 int default (random()*100)::int,   
c8 int default (random()*100)::int,   
c9 int default (random()*100)::int,   
c10 int default (random()*100)::int,   
c11 int default (random()*100)::int,   
c12 int default (random()*100)::int,   
c13 int default (random()*100)::int,   
c14 int default (random()*100)::int,   
c15 int default (random()*100)::int,   
c16 int default (random()*100)::int,   
c17 int default (random()*100)::int,   
c18 int default (random()*100)::int,   
c19 int default (random()*100)::int,   
c20 int default (random()*100)::int,   
c21 int default (random()*100)::int,   
c22 int default (random()*100)::int,   
c23 int default (random()*100)::int,   
c24 int default (random()*100)::int,   
c25 int default (random()*100)::int,   
c26 int default (random()*100)::int,   
c27 int default (random()*100)::int,   
c28 int default (random()*100)::int,   
c29 int default (random()*100)::int,   
c30 int default (random()*100)::int,   
c31 int default (random()*100)::int,   
c32 int default (random()*100)::int,   
crt_time timestamp  
)   
with (APPENDONLY=true, BLOCKSIZE=2097152, ORIENTATION=column, COMPRESSTYPE=zlib, CHECKSUM=true);  

vi test.sql  
\set id random(1,10000000)  
insert into t_bad (id, crt_time) values (:id, now());  

/home/digoal/pgsql10.4/bin/pgbench -M simple -n -r -P 1 -f ./test.sql -C -c 1 -j 1 -T 120  
  
transaction type: ./test.sql  
scaling factor: 1  
query mode: simple  
number of clients: 1  
number of threads: 1  
duration: 120 s  
number of transactions actually processed: 2190  
latency average = 50.341 ms  
latency stddev = 1.752 ms  
tps = 18.243126 (including connections establishing)  
tps = 19.855318 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         0.003  \set id random(1,10000000)  
        50.338  insert into t_bad (id, crt_time) values (:id, now());  

2 性能较好的例子

使用行存，批量INSERT，使用长连接，使用并发INSERT。

每秒插入3.41万行。

create table t_good(  
id int,   
c1 int default (random()*100)::int,   
c2 int default (random()*100)::int,   
c3 int default (random()*100)::int,   
c4 int default (random()*100)::int,   
c5 int default (random()*100)::int,   
c6 int default (random()*100)::int,   
c7 int default (random()*100)::int,   
c8 int default (random()*100)::int,   
c9 int default (random()*100)::int,   
c10 int default (random()*100)::int,   
c11 int default (random()*100)::int,   
c12 int default (random()*100)::int,   
c13 int default (random()*100)::int,   
c14 int default (random()*100)::int,   
c15 int default (random()*100)::int,   
c16 int default (random()*100)::int,   
c17 int default (random()*100)::int,   
c18 int default (random()*100)::int,   
c19 int default (random()*100)::int,   
c20 int default (random()*100)::int,   
c21 int default (random()*100)::int,   
c22 int default (random()*100)::int,   
c23 int default (random()*100)::int,   
c24 int default (random()*100)::int,   
c25 int default (random()*100)::int,   
c26 int default (random()*100)::int,   
c27 int default (random()*100)::int,   
c28 int default (random()*100)::int,   
c29 int default (random()*100)::int,   
c30 int default (random()*100)::int,   
c31 int default (random()*100)::int,   
c32 int default (random()*100)::int,   
crt_time timestamp  
) ;  

vi test.sql  
  
\set id random(1,10000000)  
insert into t_good (id, crt_time) select random()*100000000,now() from generate_series(1,100) t(id);  

/home/digoal/pgsql10.4/bin/pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 120  
  
transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 32  
number of threads: 32  
duration: 120 s  
number of transactions actually processed: 41006  
latency average = 93.658 ms  
latency stddev = 22.133 ms  
tps = 341.492522 (including connections establishing)  
tps = 341.562788 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         0.005  \set id random(1,10000000)  
        93.713  insert into t_good (id, crt_time) select random()*100000000,now() from generate_series(1,100) t(id);  

小结

1、缩短链路，不要太多的跳数，比如NAT，PROXY，等越多，性能越差。

2、使用长连接。不要使用短连接。

3、尽量使用COPY或批量INSERT，不要单条INSERT。

4、行存与列存的选择方面，需要权衡插入与查询的风格与需求的性能，同时它们支持相互转换。

5、一次性或间歇性导入，建议使用并行导入方式。通过segment并行导入的方式。

6、INSERT的写入，也可以使用并行（开多个连接）的方式，但是并不是开越多越好，通常不建议超过CPU核数。

7、索引越多，写入越慢。如果是批量一次性导入，可以先导入后并行建索引（同时创建多个索引）。

8、绑定变量，如果是使用INSERT的单条写入方法，可以考虑使用绑定变量。减少CPU硬解析。

9、倾斜，数据分布一定不要出现倾斜，否则就会导致木桶效应，导致某些SEGMENT繁忙，某些空闲，不利于整体性能。

10、其他方面，比如资源队列限制，硬件能力（CPU，IO，网络等）都可能成为瓶颈，建议遇到性能问题时观察。

参考

《分布式DB(Greenplum)中数据倾斜的原因和解法 - 阿里云HybridDB for PostgreSQL最佳实践》

《Greenplum insert的性能(单步\批量\copy) - 暨推荐使用gpfdist、阿里云oss外部表并行导入》

《Greenplum & PostgreSQL UPSERT udf 实现 - 2 batch批量模式》

《Greenplum & PostgreSQL UPSERT udf 实现 - 1 单行模式》

《Greenplum 海量数据，大宽表 行存 VS 列存》

《Greenplum 行存、列存，堆表、AO表性能对比 - 阿里云HDB for PostgreSQL最佳实践》

《Greenplum 行存、列存，堆表、AO表的原理和选择》

《Greenplum 最佳实践 - 行存与列存的选择以及转换方法》