标签：pg

PG索引对导入性能影响

1 测试环境

1.1 硬件环境

CPU内存数据盘
2路Intel(R) Xeon(R) CPU E5-2620 v3 @ 2.40GHz 共24个逻辑核64G 2T sata*8

1.2 软件环境

操作系统：Red Hat Enterprise Linux Server release 7.4 (Maipo) 内核：3.10.0-693.el7.x86_64

PG：PostgreSQL 10.6

1.3 测试表

基础表结构：

CREATE TABLE test (

 a varchar(64),

 b varchar(64),

 c varchar(64),

 d varchar(64),

 e varchar(64),

 f varchar(64),

 g varchar(64),

 h varchar(64),

 i varchar(64),

 j varchar(64),

 k varchar(64),

 l TIMESTAMP

) ;

索引：

create index idx_1 on test(a);

create index idx_2 on test(b);

create index idx_3 on test(f);

create index idx_4 on test(i);

create index idx_5 on test(l);

数据样例：

9dfdd3a8-b65a-4705-a0ff-daf1819861d2 1111111111111111111 阿大 13888888888 陕H11111 546687257794821 460073102018579 a7:8e:c5:d3:2d:6c 4212922510 T1n5C9011216 2018-10-08 10:55:47

2 入库性能

2.1 总体结果

使用copy命令入库，每个文件1000W条，行长约180B。分别测试无索引、1个索引、3个索引、5个索引的导入性能。

2.1.1 测试结果

表中存量数据（千万）012345678910111213141516171819
no index2222222322232522252223222222222223232323
1 index（raid0*8）61797172741247678156781301297920280221209113247259
3 index（raid0*8）153214182275192324192362390419562121419142539328341694548500054638309
5 index（raid0*8）22622532725538743046798926002670295827863452426464821123619992   
3 index（单盘）136176185223177363202321207341309563329504       
5 index（单盘）185264282335242376419456355211663          

续：

表中存量数据（千万）2021222324252627282930313233343536
no index2222222222222322222223232322232223
1 index（raid0*8）271389396509567719875103697410161435158419731860207921187809

2.1.2 总结

  1. 索引对PG导入性能影响非常大。
  2. 在表数据量到达拐点之后（根据索引数及磁盘IO能力决定，即使只有1个索引），导入时间呈指数级增长。
  3. 更好的磁盘IO只能延缓拐点的到来，无法避免性能急剧降低。
  4. 根据上述实验结果，单表的数据量在小于7000W时，导入性能略有下降，但基本还算稳定。所以建议：
    • 实际使用时如果索引小于等于5个，单表数据量大于5000W条就必须采取如分区、分库、分表等措施，同时每个分区也不得超过5000W。
    • 如果索引大于5个，优先考虑降低索引个数，如过实在不能降低，则单个分区数据量不得大于2000W条。

MapD、PG-Strom、Vertica横向对比

1 简介

选取；MapD、PG-Strom、Vertica进行对比测试。其中MapD为列式GPU数据库；PG-Strom是在PostgreSQL（行式）上增加了GPU插件，使用GPU运算；Vertica为一般列式数据库，作为对比。

另外还有Kinetica、Brytlyt、BlazingDB等均为商用GPU数据库，流行度、文档、官方支持都弱于MapD，且无开源版本，未纳入本次测试。

2 系统配置

CPUIntel(R) Xeon(R) CPU E5-2650 v4 @ 2.20GHz*4
内存8G
磁盘260G ssd
GPUNVIDIA Tesla P40（24G显存）*1

3 性能测试

原始数据：美国2008年飞行数据（MapD官方提供）行长约400B，共2.1亿行，84G

3.1 入库性能

数据库导入性能表大小数据压缩比
mapd27.5w/s37G2.27
mapd 边查边入21.7w/s37G2.27
vertica14.2w/s6.4G13.12
pg-strom9.3w/s66G1.27
pg-strom多进程导入23.9w/s66G1.27

MapD与vertica导入时CPU使用率接近100%，均为瓶颈。边查边入的性能降低也是由于查询占用了部分CPU。PG-Strom一个导入进程只能用满1核，故需要多进程导入才能达到性能上限。

3.2 查询性能

测试语句：

Sql1: select count(*) from flights;
Sql2: select count(*) from flights where origin_country=’USA’;
Sql3: select count(*) as cnt,avg(distance) as dis from flights where flight_month=10;
Sql4: select origin_city,dest_city,count(*) as cnt,avg(airtime) as atime from flights group by origin_city,dest_city order by cnt desc,atime;
Sql5: select origin_state,dest_state,count(*) as cnt,avg(airtime) as atime from flights where distance<175 group by origin_state,dest_state ;

性能对比:

无缓存：

Sql 查询耗时(ms)mapd gpu+cpumapd cpu onlyverticapg with pg-strompg
sql1364130951548619670833
sql245714319583890782143490
sql3442839425118869975490
sql4591456998698942191286666
sql566665903328991956212259

有缓存：

Sql 查询耗时(ms)mapd gpu+cpumapd cpu onlyverticapg with pg-strompg
sql152721698694671456
sql258106578791004142894
sql3571491538887474287
sql42524968567927681270628
sql5162212263788107211838

3.3 并发测试

使用3.2中SQL5进行并发测试，其中distance随机生成：

SQL5：

并发QPS显存GPU
17.94.8G90%
108.54.8G90%
208.54.8G90%
508.44.8G90%

MapD引擎内部并无并发处理机制，实际多个会话执行的查询都是串行执行，系统资源也并未随并发数升高而增加。

4 测试结论

  1. 性能对比：
    • 入库性能：MapD与PG-Strom由于压缩比较低所以入库性能均高于Vertica。
    • 无论有无缓存，PG以及PG-Strom性能均远远低于MapD与Vertica。数据有缓存场景下，MapD的大部分统计查询得益于GPU的高吞吐量性能远高于vertica，同样PG-Strom也高于原生PG。
  2. MapD引擎内部并无并发处理机制，所有查询都是串行执行。已向官方证实。
  3. MapD在SQL方面只支持INSERT、SELECT，不支持UPDATE、DELETE、事务、索引等。PG-Strom由于是插件形式，所以SQL支持度是与PG相同，兼容性非常高。
  4. 本轮测试过程中MapD服务端同样发生过崩溃。

5 后续测试规划

基于公司的某一业务场景，和vertica、ydb进行对比测试。

PG数据库插件性能对比测试

1 简介

PG-Strom是在PostgreSQL上的GPU插件，可以使用GPU进行运算。

cstore_fdw是PostgreSQL上的列式插件，使用该插件可以对表进行列式存储。

对比原生PG与使用上述插件的导入及查询性能。

2 系统配置

CPUIntel(R) Xeon(R) CPU E5-2650 v4 @ 2.20GHz*4
内存8G
磁盘260G ssd
GPUNVIDIA Tesla P40（24G显存）*1

3 性能测试

原始数据：美国2008年飞行数据（MapD官方提供）行长约400B，共2.1亿行，84G

3.1 入库性能

数据库导入性能表大小数据压缩比
pg-strom9.3w/s66G1.27
pg-strom多进程导入23.9w/s66G1.27
cstore_fdw7w/s3.8G22.1

3.2 查询性能

测试语句：

Sql1: select count(*) from flights;
Sql2: select count(*) from flights where origin_country=’USA’;
Sql3: select count(*) as cnt,avg(distance) as dis from flights where flight_month=10;
Sql4: select origin_city,dest_city,count(*) as cnt,avg(airtime) as atime from flights group by origin_city,dest_city order by cnt desc,atime;
Sql5: select origin_state,dest_state,count(*) as cnt,avg(airtime) as atime from flights where distance<175 group by origin_state,dest_state ;

性能对比:

无缓存：

Sql 查询耗时(ms)pgpg with pg-stromcstore_fdwpg-strom & cstore_fdw
sql170833861962447835965
sql2143490907824931647441
sql3754908869967667902
sql412866669421914162485152
sql5212259919564123039182

有缓存：

Sql 查询耗时(ms)pgpg-stromcstore_fdwpg-strom & cstore_fdw
sql171456869462250335686
sql2142894910043974338782
sql3742878887445954686
sql412706289276811936264952
sql5211838881074195138719

4 测试结论

  1. 入库性能：PG一个导入进程只能用满1核，故需要多进程导入才能达到性能上限。但是cstore_fdw不支持多进程并发导入，只支持单进程。
  2. cstore_fdw插件数据压缩比很高，甚至高于vertica的13.12。
  3. 两个插件较原生PG均能大幅提高性能，且可以共同生效。
  4. 有无缓存对PG性能基本没有影响，只有cstore_fdw插件在有缓存场景下性能有所提升。
  5. 分析型业务可以使用cstore_fdw插件提速，但是该插件使用外部表，功能上限制较大，不支持删除与修改。