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 测试结果

表中存量数据（千万）	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19
no index	22	22	22	23	22	23	25	22	25	22	23	22	22	22	22	22	23	23	23	23
1 index（raid0*8）	61	79	71	72	74	124	76	78	156	78	130	129	79	202	80	221	209	113	247	259
3 index（raid0*8）	153	214	182	275	192	324	192	362	390	419	562	1214	1914	2539	3283	4169	4548	5000	5463	8309
5 index（raid0*8）	226	225	327	255	387	430	467	989	2600	2670	2958	2786	3452	4264	6482	11236	19992
3 index（单盘）	136	176	185	223	177	363	202	321	207	341	3095	6332	9504
5 index（单盘）	185	264	282	335	242	376	419	456	3552	11663

续：

表中存量数据（千万）	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36
no index	22	22	22	22	22	22	23	22	22	22	23	23	23	22	23	22	23
1 index（raid0*8）	271	389	396	509	567	719	875	1036	974	1016	1435	1584	1973	1860	2079	2118	7809

2.1.2 总结

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

PyGreSQL入门

——简单整理翻译自官方文档：http://www.pygresql.org/contents/tutorial.html

创建数据库连接

只需import DB类并创建一个实例，填入相应的连接信息，例：

>>> from pg import DB
>>> db = DB(dbname='testdb', host='pgserver', port=5432, user='scott', passwd='tiger')

1 2	>>> from pg import DB >>> db = DB(dbname='testdb', host='pgserver', port=5432, user='scott', passwd='tiger')

如果省略参数，则会使用默认值：

dbname,user默认为当前系统用户，host为localhost，port为5432。

执行SQL语句

DB.query()

>>> db.query("create table fruits(id serial primary key, name varchar)")

1	>>> db.query("create table fruits(id serial primary key, name varchar)")

获取所有表名

DB.get_tables()，类似psql中\d:

>>> db.get_tables()
['public.fruits']

1 2	>>> db.get_tables() ['public.fruits']

获取表属性

DB.get_attnames()，类似psql中\d table：

>>> db.get_attnames('fruits')
{'id': 'int', 'name': 'text'}

1 2	>>> db.get_attnames('fruits') {'id': 'int', 'name': 'text'}

检查权限

DB.has_table_privilege()

>>> db.has_table_privilege('fruits', 'insert')
True

1 2	>>> db.has_table_privilege('fruits', 'insert') True

插入数据

DB.insert() –注：GP不支持

>>> db.insert('fruits', name='apple')
{'name': 'apple', 'id': 1}

1 2	>>> db.insert('fruits', name='apple') {'name': 'apple', 'id': 1}

该方法将完整的行作为字典返回，包括自增列。可以将字典赋值给变量：

>>> banana = db.insert('fruits', name='banana')

1	>>> banana = db.insert('fruits', name='banana')

批量插入数据

Connection.inserttable()

在插入大量数据时，批量插入性能比单条插入快很多

>>> more_fruits = 'cherimaya durian eggfruit fig grapefruit'.split()
>>> data = list(enumerate(more_fruits, start=3))
>>> db.inserttable('fruits', data)

>>> more_fruits = 'cherimaya durian eggfruit fig grapefruit'.split()

>>> data = list(enumerate(more_fruits, start=3))

>>> db.inserttable('fruits', data)

查询数据

DB.query()

>>> print(db.query('select * from fruits'))
id|   name
--+----------
1|apple
2|banana
3|cherimaya
4|durian
5|eggfruit
6|fig
7|grapefruit
(7 rows)

>>> print(db.query('select * from fruits'))

id| name

--+----------

1|apple

2|banana

3|cherimaya

4|durian

5|eggfruit

6|fig

7|grapefruit

(7 rows)

将查询结果放入元组：

>>> q = db.query('select * from fruits')
>>> q.getresult()
... [(1, 'apple'), ..., (7, 'grapefruit')]

>>> q = db.query('select * from fruits')

>>> q.getresult()

... [(1, 'apple'), ..., (7, 'grapefruit')]

或字典：

>>> q.dictresult()
[{'id': 1, 'name': 'apple'}, ..., {'id': 7, 'name': 'grapefruit'}]

1 2	>>> q.dictresult() [{'id': 1, 'name': 'apple'}, ..., {'id': 7, 'name': 'grapefruit'}]

或named tuple：

>>> rows = q.namedresult()
>>> rows[3].name
'durian'

>>> rows = q.namedresult()

>>> rows[3].name

'durian'

使用DB.get_as_dict()可以轻松的将整张表数据加载到Python 字典中：

>>> db.get_as_dict('fruits', scalar=True)
OrderedDict([(1, 'apple'),
(2, 'banana'),
(3, 'cherimaya'),
(4, 'durian'),
(5, 'eggfruit'),
(6, 'fig'),
(7, 'grapefruit')])

>>> db.get_as_dict('fruits', scalar=True)

OrderedDict([(1, 'apple'),

(2, 'banana'),

(3, 'cherimaya'),

(4, 'durian'),

(5, 'eggfruit'),

(6, 'fig'),

(7, 'grapefruit')])

修改数据

DB.update()

>>> db.update('fruits', banana, name=banana['name'].capitalize())
{'id': 2, 'name': 'Banana'}
>>> print(db.query('select * from fruits where id between 1 and 3'))
id|  name
--+---------
1|apple
2|Banana
3|cherimaya
(3 rows)

>>> db.update('fruits', banana, name=banana['name'].capitalize())

{'id': 2, 'name': 'Banana'}

>>> print(db.query('select * from fruits where id between 1 and 3'))

id| name

--+---------

1|apple

2|Banana

3|cherimaya

(3 rows)

也可使用DB.query()

>>> db.query('update fruits set name=initcap(name)')
'7'

1 2	>>> db.query('update fruits set name=initcap(name)') '7'

返回值：‘7’表示更新的行数。

删除数据

DB.delete()

>>> db.delete('fruits', banana)
1

1 2	>>> db.delete('fruits', banana) 1

1表示删除的行数，再次执行就会显示0行被删除：

>>> db.delete('fruits', banana)
0

1 2	>>> db.delete('fruits', banana) 0

删除表

>>> db.query("drop table fruits")

1	>>> db.query("drop table fruits")

关闭连接

>>> db.close()

1	>>> db.close()

更高级的特性和详细信息，参阅：http://www.pygresql.org/contents/pg/index.html

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

1 简介

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

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

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

2 系统配置

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

3 性能测试

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

3.1 入库性能

数据库	导入性能	表大小	数据压缩比
pg-strom	9.3w/s	66G	1.27
pg-strom多进程导入	23.9w/s	66G	1.27
cstore_fdw	7w/s	3.8G	22.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)	pg	pg with pg-strom	cstore_fdw	pg-strom & cstore_fdw
sql1	70833	86196	24478	35965
sql2	143490	90782	49316	47441
sql3	75490	88699	6766	7902
sql4	1286666	94219	141624	85152
sql5	212259	91956	41230	39182

有缓存：

Sql 查询耗时(ms)	pg	pg-strom	cstore_fdw	pg-strom & cstore_fdw
sql1	71456	86946	22503	35686
sql2	142894	91004	39743	38782
sql3	74287	88874	4595	4686
sql4	1270628	92768	119362	64952
sql5	211838	88107	41951	38719

4 测试结论

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