PostgreSQL分区表的性能损耗验证

概述分类：  Mysql/postgreSQL 今年的PG用户大会，华为的许中清分享了《PostgreSQL表分区实践》（当时我在另一个会场）。其中最后一页的测试结果表明，100分区的继承分区表的查询更新删除比不分区的普通表慢了2个数量级。虽然PG的手册中也说： 主表的所有分区的所有约束在约束排除中被审查，所以大量的分区将大大增加查询规划时间。 分区使用这些技术或许可以将分区提升到一百个且能很好的工作 分类： MysqL/postgresql

今年的PG用户大会，华为的许中清分享了《Postgresql表分区实践》（当时我在另一个会场）。其中最后一页的测试结果表明，100分区的继承分区表的查询更新删除比不分区的普通表慢了2个数量级。虽然PG的手册中也说：

主表的所有分区的所有约束在约束排除中被审查，所以大量的分区将大大增加查询规划时间。 分区使用这些技术或许可以将分区提升到一百个且能很好的工作； 不要试图使用成千上万的分区。
摘自:http://58.58.27.50:8079/doc/HTML/9.3.1_zh/ddl-partitioning.HTML#DDL-PARTITIONING-CONSTRAINT-EXCLUSION

但是否真慢到这种程度心中还是存了疑问，故测试验证之。

1.PG的分区表概述 先建立一个小的分区表，简单看看分区表相关的 *** 作。
PG的分区表是通过继承实现的，下面建一个有5个分区的分区表。

createtablemaintb(ID int,namechar(10);

tablechildtb_1(CHECK(IDBETWEEN1AND200)inherits(maintb;
tablechildtb_2BETWEEN201AND400tablechildtb_3BETWEEN401AND600tablechildtb_4BETWEEN601AND800tablechildtb_5BETWEEN801AND1000;

CREATEINDEXchildtb_IDx_1ONchildtb_1INDEXchildtb_IDx_2ONchildtb_2INDEXchildtb_IDx_3ONchildtb_3INDEXchildtb_IDx_4ONchildtb_4INDEXchildtb_IDx_5ONchildtb_5ORREPLACEFUNCTIONmaintb_insert_trigger)
RETURNSTRIGGERAS$$
BEGIN
IF(NEW.IDTHEN
INSERTINTOchildtb_1VALUES.*;
ELSIFINTOchildtb_2INTOchildtb_3INTOchildtb_4INTOchildtb_5ELSE
RAISE EXCEPTION'ID out of range!'ENDRETURNNulL;
$$
LANGUAGE plpgsqlTRIGGERinsert_maintb_trigger
BEFOREONmaintb
FOREACHROWEXECUTEPROCEDURE maintb_insert_trigger;
往父表插入的数据通过触发器被分发到了子表上

tmpdb=#insertintomaintbvalues(1'abcde12345';
INSERT0 0
tmpdbselectfromonlymaintb;
ID|name
----+------
(0rows)
tmpdbfromchildtb_1----+------------
1|abcde12345
row)
对父表的查询更新和删除 *** 作，PG会根据where条件和子表的CHECK约束条件分发到相应的子表上（这称为约束排除，详见PG手册）。

#explainfrommaintbwhereID=2;
query PLAN
----------------------------------------------------------------
Append(cost=0.00.4.50=2 wIDth=32)
->Seq Scanonmaintb.0=1 wIDth=48)
Filter:onchildtb_1=15(5)

tmpdbupdatemaintbsetname='xx';
query PLAN
Update=10delete---------------------------------------------------------------
Delete=6)
由于表中的数据量太小，所以上面的执行计划里没有用索引扫描。
如果试图更新分区键，把记录从一个分区更新到另一个分区。对不起，PG会报CHECK约束违法的错误。

setID=300=1;
错误:关系"childtb_1"的新列违反了检查约束"childtb_1_ID_check"
DETAIL:失败(300.
所以分区键是不能随便更新的，如果非要更新，只能先删后插。
PG如果无法确定要 *** 作哪个或哪些子表，就要在所有子表上都执行一遍。

+1-------------------------------------------------------------------
.229=56 wIDth=16=3.5onchildtb_2onchildtb_3onchildtb_4onchildtb_5.209=51 wIDth(13)
废话讲完，下面开始测试。

2.测试环境 测试环境为个人PC上的VMware虚拟机
PC
cpu:Intel Core i5-3470 3.2G(4核)
MEM:6GB
SSD:OCZ-VERTEX4 128GB(VMware虚拟机所在磁盘，非系统盘)
OS:Win7

VMware虚拟机
cpu:4核
MEM:1GB
OS:CentOS 6.5
PG:Postgresql 9.3.4(shared_buffers = 128MB,其他是默认值)

3.测试 3.1测试方法 1，仿照"PG的分区表概述"中的表定义分别建0,1,10,100和1000 5个不同分区数目的数据库。
2，使用批量insert对每个数据库都插入1000w条记录。
3，用pgbench分别以1，10和100并发执行单条记录的insert,select,update和delete，记录平均sql延迟。
对分区表的select,update和delete的测试分为覆盖所有分区和只 *** 作1个分区两种情况。

3.2建数据库 5个数据库分别命名如下:
db0: 0个分区(普通表)
db1: 1个分区
db10: 10个分区
db100: 100个分区
db1000: 1000个分区


为简化建表工作,创建一个用于生成分区表建表sql的sql脚本(有点绕口)
createsql.sql:

点击(此处)折叠或打开

\pset linestyle old-ascii
\ton

select'create sequence seq1;';
'create table maintb(ID int,name char(10));';

'create table childtb_'||ID|' (CHECK ( ID BETWEEN '-1:total/:pnum' AND '')) inherits(maintb);'fromselectgenerate_serIEs)IDs'CREATE INDEX childtb_IDx_'' ON childtb_'' (ID);''CREATE OR REPLACE FUNCTION maintb_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN'selectCASE WHEN ID>1THEN' ELS'ELSE' ''IF( NEW.ID BETWEEN '' ) THEN
INSERT INTO childtb_'' VALUES (NEW.*); '' ELSE
RAISE EXCEPTION ''ID out of range!'';
END IF;
RETURN NulL;
END;
$$
LANGUAGE plpgsql;''CREATE TRIGGER insert_maintb_trigger
BEFORE INSERT ON maintb
FOR EACH ROW EXECUTE PROCEDURE maintb_insert_trigger();';
创建db0

-bash-4.1$ createdb db0
.1$ psql db0
psql(9.3)
Type"help"forhelp.

db0sequenceseq1;
SEQUENCE
db0table
db0INDEXmaintb_IDxONmaintbINDEX
执行下面的命令,创建db1,db10,db100和db1000

createdb db1
psql-q-v total=10000000-v pnum-f createsql.sql|psql db1

createdb db10
psql|psql db10

createdb db100
psql=100|psql db100

createdb db1000
psql=1000|psql db1000
3.3批量插入数据 对每个db分别用下面的sql批量插入1000w条数据
insert into maintb select ID,'abcde12345' from (select generate_serIEs(1,10000000))IDs(ID);

点击(此处)折叠或打开

db0selectID;
INSERT0 10000000
Time:33521.119 ms
db0#analyze;
ANALYZE
Time:637.394 ms

db0\c db1
You are Now connectedtodatabase"db1"asuser"postgres".
db1;
INSERT0 0
Time:230640.841 ms
db1:3354.329 ms

db1\c db10
You are Now connected"db10".
db10:257349.510 ms
db10:3979.994 ms

db10\c db100
You are Now connected"db100".
db100:602719.217 ms
db100:6750.144 ms

db100\c db1000
You are Now connected"db1000".
db1000:7203528.417 ms
db1000:12512.537 ms
分区表的插入要执行触发器，所以比普通表要慢。从上面的数据可以看出：100以下分区时，执行时间随分区数的增长还比较缓慢；但当分区数为1000时，批量插入的时间已经变态到100分区时的12倍了。

3.4全表扫描 简单做个需要全表扫描的查询。下面的查询约束排除和索引都失效，走了全表扫描。因为大部分时间花在表扫描上，不关分区的事，所以执行时间相差不大。

点击(此处)折叠或打开

;
ID|name
----+------------
1|abcde12345
)

Time:2569.647 ms
db1000;
ID:2479.952 ms
db1:2384.198 ms
db10:2474.023 ms
db0:4065.615 ms
1000个分区时花的时间比较长，应该和文件系统缓存有关。连续执行2次，可以发现第二次执行时，1000个分区和不分区的执行时间差不多。

点击(此处)折叠或打开

b0:2461.604 ms
db0:1436.271 ms
db0:3977.888 ms
db1000:1400.637 ms
3.5 并发sql测试 准备以下pgbench的sql脚本文件
insert.sql:
\setrandom ID 1 10000000
:ID'abcd12345';
insert_smallrange.sql:
\setrandom ID 1 10000
;
select.sql:
;
select_smallrange.sql:
;
update.sql:
'aaaaa12345';
update_smallrange.sql:
;
delete.sql:
;
delete_smallrange.sql:
;
然后用pgbench对不同数据库进行不同并发数的 测试，比如对db0,测试insert的平均执行时间。

点击(此处)折叠或打开

.1$ pgbench-n-r-c 1-j 1-T 2-f.sql db0
transactiontype:Custom query
scaling factor:1
querymode:simple
numberofclIEnts:1
ofthreads:1
duration:2 s
oftransactions actually processed:7282
tps=3640.288324(includingconnections establishing)
tps=3647.901830(excluding connections establishing)
statement latencIEsinmilliseconds:
0.002014\setrandom ID 1 10000000
0.270468;

-c 10-j 10.sql db0
transaction:10
:10
duration:32075
tps=15772.769831=15932.463640.002050.621524-c 100-j 100:100
:100
duration:25692
tps=12085.876901=22583.213366.001861\setrandom ID 1 10000000
4.046697;
4.测试结果 1个并发（单位毫秒）

ID范围	sql	分区数
		0	1	10	100	1000
1~10000000	insert into maintb values(:ID,'abcd12345');	0.34568	0.809927	0.910337	0.967627	4.190777
	select * from maintb where ID=:ID;	0.14852	0.789401	0.90642	2.091848	38.66256
	update maintb set name = 'aaaaa12345' where ID=:ID;	0.48688	1.17653	1.493839	6.046276	598.335
	delete from maintb where ID=:ID;	0.43789	1.110788	1.235515	5.861994	589.4893
1~10000	insert into maintb values(:ID,'abcd12345');	0.26089	0.288702	0.297765	0.295104	0.313431
	select * from maintb where ID=:ID;	0.07621	0.109531	0.208925	1.237369	31.09006
	update maintb set name = 'aaaaa12345' where ID=:ID;	0.20382	0.378703	0.568731	5.161718	590.0673
	delete from maintb where ID=:ID;	0.13484	0.264828	0.464408	5.006479	586.6543

10个并发（单位毫秒）

ID范围	sql	分区数
		0	1	10	100	1000
1~10000000	insert into maintb values(:ID,'abcd12345');	1.14853	1.563754	1.661851	2.385968	10.7074
	select * from maintb where ID=:ID;	0.5432	1.274031	1.524999	5.950416	111.1581
	update maintb set name = 'aaaaa12345' where ID=:ID;	1.50849	2.740678	3.094661	23.89209	出错(*3)
	delete from maintb where ID=:ID;	1.59584	1.80079	2.727167	22.79166	出错(*3)
1~10000	insert into maintb values(:ID,'abcd12345');	0.6038	0.779655	0.673587	0.662618	0.789707
	select * from maintb where ID=:ID;	0.22318	0.316221	0.597139	4.822255	117.1621
	update maintb set name = 'aaaaa12345' where ID=:ID;	0.85966	0.959858	1.739188	20.3759	出错(*3)
	delete from maintb where ID=:ID;	0.29249	0.407228	1.158087	20.18293	出错(*3)

100个并发（单位毫秒）

ID范围	sql	分区数
		0	1	10	100	1000
1~10000000	insert into maintb values(:ID,'abcd12345');	6.77161	9.762775	11.93486	21.35377	1037.091
	select * from maintb where ID=:ID;	9.01432	10.91613	17.37906	87.52062	5919.649(*2)
	update maintb set name = 'aaaaa12345' where ID=:ID;	16.0372	21.10411	29.61658	380.3574	出错(*3)
	delete from maintb where ID=:ID;	11.3606	13.64317	28.92108	345.2502	出错(*3)
1~10000	insert into maintb values(:ID,'abcd12345');	5.12748	6.519101	6.270275	6.555714	8.49643
	select * from maintb where ID=:ID;	2.40127(*1)	3.226115	6.332551	71.98606	6258.338(*2)
	update maintb set name = 'aaaaa12345' where ID=:ID;	2.40123(*1)	8.497982	15.57208	368.961	(*3)
	delete from maintb where ID=:ID;	2.79931	3.985874	11.96576	289.3604	出错(*3)

*1)在db0上以 100个并发连起来执行小范围的select和update时，有时会发生死锁。
aa.sql:
export jobs=100
export db=db0
pgbench-c $jobs-j $jobs-f select_smallrange.sql $db|tail|awk'{print }'
pgbench-f update_smallrange'{print }'
-bash-4.1$ export jobs=100
-bash-4.1$ export db=db0
-bash-4.1$ sh aa.sql 2.368792
ClIEnt 57 aborted in state 1: 错误: 检测到死锁
DETAIL: 进程14436等待在事务 3145678上的ShareLock; 由进程14470阻塞.
进程14470等待在事务 3146294上的ShareLock; 由进程14436阻塞.
HINT: 详细信息请查看服务器日志.
7.002670 奇怪的是2个pgbench命令分开单独执行不发生过问题。有点怀疑执行update_smallrange.sql时，select_smallrange.sql并没有完全结束，但把-T改成-t依然可能发生，把update和insert放到同一个sql脚本中反而不发生。

*2) 在db1000上以 100个并发执行select会遭遇资源限制的错误。
出错消息1:
ClIEnt 77 abortedinstate 1:错误:共享内存用尽
HINT:您可能需要增加参数max_locks_per_transaction.
警告:共享内存用尽
出错消息2:
ClIEnt 31 aborted:无法打开文件"base/25376/30776":Too many open filesinsystem 根据消息提示修改postgresql.conf的配置后可以成功执行。
max_locks_per_transaction=64
max_files_per_process = 1000
==》
max_locks_per_transaction=1100
max_files_per_process = 500

*3) 在db1000上以10和 100个并发 执行update,delete老是报错，未能成功。
出错消息:
警告:中断联接 DETAIL:Postmaster 命令此服务器进程回滚当前事物并退出.
HINT:一会儿你将可以重联接数据库并且重复你的命令. 5.性能分析 插入慢是由于触发器，而且慢的也不算太多，顶多慢2到3倍。有异常的数据是在100并发时往有1000个分区的父表中插入数据，比不分区慢了100多倍。这应该是由于数据被随机写到1000个不同的子表文件中，导致IO的响应很慢。
查询更新和删除慢，是由于查询规划慢，大部分时间都耗在查询规划上，而且分区数越多，慢的越离谱。

点击(此处)折叠或打开

db1000=10000UPDATE9
Time:610.774 ms
db1000;
query PLAN
-----------------------------------------------------------------------------------
.44.73=12 wIDth)
)
Filter>Bitmap Heap Scan=4=11 wIDth)
Recheck Cond>BitmapIndexScanonchildtb_IDx_1IndexCond(7:661.814 ms
6.总结 1，正如PG手册上说的， 成千上万的分区是不太靠谱的。（不论是性能还是稳定性）
2，和许中清他们的测试结果一样，100个分区时，单条记录的select，update和delete *** 作的执行时间比不分区要慢2个数量级。
3，华为的原生分区方案确实不错，社区版也能提供这个功能就好了。
4，解决PG的继承表在分区较多时执行规划耗时的问题，除了在分区本身上下功夫，还可以通过缓存 执行计划达到目的。可惜目前PG的执行计划cache比较弱，很难满足分区表的场景（关于这一点准备之后再写一篇文章详细说明一下）。
5，不要被上面的测试结果吓到，对典型的olAP复杂查询来说，这点 执行规划耗时根本不值得注意，反而处理的总数据量少了，执行性能会大大提升。
6，对olTP应用则要仔细斟酌一下要不要分区，分多少个区了？显然分区数不宜超过100个，如果业务是按时间分区，历史数据分区尽量合并减少分区数。
7.参考 http://58.58.27.50:8079/doc/HTML/9.3.1_zh/ddl-partitioning.HTML http://wenku.it168.com/d_001578048.sHTML http://beigang.iteye.com/blog/1884415 总结

以上是内存溢出为你收集整理的PostgreSQL分区表的性能损耗验证全部内容，希望文章能够帮你解决PostgreSQL分区表的性能损耗验证所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。