伍佰目录 短网址
  当前位置:海洋目录网 » 站长资讯 » 站长资讯 » 文章详细 订阅RssFeed

PostgreSQL10分区表性能研究报告

来源:本站原创 浏览:107次 时间:2022-11-06

背景


哈啰出行旗下包括哈啰单车、助力车、顺风车、打车、电动车、换电等几乎所有业务都基于PostgreSQL数据库构建,PostgreSQL的安全、稳定、高效为哈啰出行的上亿用户提供了强大的基础。随着时间的推移,表里的数据量越来越大,有些需要分库分表处理,有些可以通过简单的分区处理即可,表分区有很多好处:

1):只vacuum 最近分区子表而不是vacuum大表,降低io消耗,减少表膨胀。

2):方便维护表,如创建索引耗时更短,通过清理历史分区释放磁盘空间。

3):减少数据扫描等


01

测试目的

1)测试pg_pathman、native、inherit分区表和不分区表的QPS/TPS性能;2)对比:压测索引键+分区键(有索引)查询/插入方式的QPS/TPS、CPU利用率;


02

测试说明

PG实例信息:实例ID:i-bp15xu7930bhkq3urjwm, PG10, 4C8GB。主表下有20个分区表,按月分区,总数据量均为5612.5504万,均匀分布在各分区。查询的对应时间段月分区的的数据量为280万。4个和10个并发压测,压测脚本:


/usr/pgsql-10/bin/pgbench -f select/insert.sql -c 4/10 -j 4/10 -n -P 10 -r -T 300/180 -R 10000 -p 7474 -d postgres -U postgres -h locahost


03

测试CASE



04

测试结论

1,在5612.5504万数据量,20个分区情况下select的结论:

1)不分区表相比分区表性能更高,占用cpu更低,qps更高。

2)分区表之间对比,qps相差不多的情况下,pathman分区方式占用cpu更低,10并发下native和inherits占用cpu超过90%以上。


2,在5612.5504万数据量,20个分区情况下insert的结论:

1)不分区表相比分区表性能更高,占用cpu更低,tps较高。

2)分区表之间对比,tps性能相差不多的情况下,pathman分区方式占用cpu更低,native和inherits占用cpu都大于pathman方式。


select记录(取三次执行记录的平均数,每次执行时间5分钟):


insert记录(取二次执行记录的平均数,每次执行时间3分钟):


05

测试环境硬件配置信息

CPU:

postgres@VECS04164:~$ cat /proc/cpuinfo | grep "model name"model name : Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHzmodel name : Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHzmodel name : Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHzmodel name : Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHz


内存:

postgres@VECS04164:~$ cat /proc/meminfo | grep -i totalMemTotal: 8193156 kBSwapTotal: 0 kBVmallocTotal: 34359738367 kBHugePages_Total: 0


操作系统版本:

postgres@VECS04164:~$ cat /etc/redhat-releaseCentOS release 6.9 (Final)


需要提前安装好pg_pathman插件:

下载地址:https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-6.9-x86_64/

需要两个rpm包:

pg_pathman10-1.4.13-1.rhel6.x86_64.rpmpython-psycopg2-2.7.4-1.rhel6.x86_64.rpm


pg_pathman分区测试


1,创建pathman分区表

CREATE TABLE pathman_emp_20190710 (emp_id SERIAL,emp_level INTEGER,emp_name TEXT,create_time TIMESTAMP NOT NULL);


2,插入数据:

INSERT INTO pathman_emp_20190710 (create_time, emp_level, emp_name) SELECT g, random() * 6,md5(g::text) FROM generate_series('2016-01-01'::date, '2019-12-31'::date, '1 minute') as g;


3,创建索引

CREATE INDEX ON pathman_emp_20190710(create_time);CREATE INDEX ON pathman_emp_20190710(emp_name);


4,创建分区子表:

SELECT create_range_partitions('pathman_emp_20190710',--主表名'create_time', --分区字段'2016-01-01'::date, --分区起始日期'1 month'::interval, --分区间隔null, --不指定分区数量,根据时间与间隔会自动计算出数量false --默认tue立即迁移数据,false是不迁移数据);


5,验证父表的数据量

select count(*) from only pathman_emp_20190710;


6,将数据并行的迁移到分区子表中:(需要一段时间)

select partition_table_concurrently('pathman_emp_20190710',10000,1.0);


7,等迁移完成,验证父、子表数据量:

select count(*) from only pathman_emp_20190710;postgres=#\dt+


8,编辑测试脚本文件select_pathman_emp_20190710.sql 、insert_pathman_emp_20190710.sql

postgres@VECS04164:~$ cat select_pathman_new.sqlselect * from public.pathman_emp where emp_name ='e0cf722200f2833a04415347324a85f3' and create_time >='2016-07-01' and create_time<'2016-08-01';postgres@VECS04164:~$ cat insert_pathman_new.sqlinsert into pathman_emp_new values(emp_name,emp_level,create_time) values('测试',100,now());


9,调用pgbench进行测试:

/usr/pgsql-10/bin/pgbench -f select_pathman_new.sql -c 4/10 -j 4/10 -n -P 10 -r -T 180 -R 10000 -p 7474 -d postgres -U postgres -h localhost/usr/pgsql-10/bin/pgbench -f insert_pathman_new.sql -c 4/10 -j 4/10 -n -P 10 -r -T 180 -R 10000 -p 7474 -d postgres -U postgres -h localhost


native 分区测试


1,创建native分区表

CREATE TABLE native_emp_20190710 (emp_id SERIAL,emp_level INTEGER,emp_name TEXT,create_time TIMESTAMP NOT NULL)partition by range(create_time);


2,创建子分区:

create table native_emp_20190710_201601 partition of native_emp_20190710 for values from ('20160101') to ('20160201');create table native_emp_20190710_201602 partition of native_emp_20190710 for values from ('20160201') to ('20160301');create table native_emp_20190710_201603 partition of native_emp_20190710 for values from ('20160301') to ('20160401');.........create table native_emp_20190710_201910 partition of native_emp_20190710 for values from ('20191001') to ('20191101');create table native_emp_20190710_201911 partition of native_emp_20190710 for values from ('20191101') to ('20191201');create table native_emp_20190710_201912 partition of native_emp_20190710 for values from ('20191201') to ('20200101');


3,导入数据到native_emp_20190710

insert into native_emp_20190710 select * from pathman_emp_new;


4,创建子分区表索引

CREATE INDEX ON native_emp_20190710_201601(emp_name);CREATE INDEX ON native_emp_20190710_201602(emp_name);CREATE INDEX ON native_emp_20190710_201603(emp_name);.....CREATE INDEX ON native_emp_20190710_201910(emp_name);CREATE INDEX ON native_emp_20190710_201911(emp_name);CREATE INDEX ON native_emp_20190710_201912(emp_name);

CREATE INDEX ON native_emp_20190710_201601(create_time);CREATE INDEX ON native_emp_20190710_201602(create_time);CREATE INDEX ON native_emp_20190710_201603(create_time);.....CREATE INDEX ON native_emp_20190710_201910(create_time);CREATE INDEX ON native_emp_20190710_201911(create_time);CREATE INDEX ON native_emp_20190710_201912(create_time);


5,验证数据是否已经到分区表

postgres=# select count(*) from native_emp_20190710;count---------2102400(1 row)postgres=# select count(*) from only native_emp_20190710;count-------0(1 row)


6,编辑测试脚本文件select_native_emp_20190710.sql 、insert_native_emp_20190710.sql

postgres@VECS04164:~$ cat select_native_new.sqlselect * from public.native_emp_new where emp_id=87289589  and create_time >='2016-07-01' and create_time<'2016-08-01' limit 1;postgres@VECS04164:~$ cat insert_native_emp_20190710.sqlinsert into native_emp_20190710 (emp_name,emp_level,create_time) values('测试',100,now());


7,调用pgbench进行测试:

/usr/pgsql-10/bin/pgbench -f select_native_emp_20190710.sql -c 4/10 -j 4/10 -n -P 10 -r -T 180 -R 10000 -p 7474 -d postgres -U postgres -h localhost/usr/pgsql-10/bin/pgbench -f insert_native_emp_20190710 -c 4/10 -j 4/10 -n -P 10 -r -T 180 -R 10000 -p 7474 -d postgres -U postgres -h localhost


Inherits 分区测试


1,建父表:

CREATE TABLE inherits_emp_20190710 (emp_id SERIAL,emp_level INTEGER,emp_name TEXT,create_time TIMESTAMP NOT NULL);


2,继承父表建分区子表:

CREATE TABLE inherits_emp_20190710_201601 ( CHECK ( create_time >= DATE '2016-01-01' AND create_time < DATE '2016-02-01' )) INHERITS (inherits_emp_20190710);CREATE TABLE inherits_emp_20190710_201602 ( CHECK ( create_time >= DATE '2016-02-01' AND create_time < DATE '2016-03-01' )) INHERITS (inherits_emp_20190710);CREATE TABLE inherits_emp_20190710_201603 ( CHECK ( create_time >= DATE '2016-03-01' AND create_time < DATE '2016-04-01' )) INHERITS (inherits_emp_20190710);.....CREATE TABLE inherits_emp_20190710_201910 ( CHECK ( create_time >= DATE '2019-10-01' AND create_time < DATE '2019-11-01' )) INHERITS (inherits_emp_20190710);CREATE TABLE inherits_emp_20190710_201911 ( CHECK ( create_time >= DATE '2019-11-01' AND create_time < DATE '2019-12-01' )) INHERITS (inherits_emp_20190710);CREATE TABLE inherits_emp_20190710_201912 ( CHECK ( create_time >= DATE '2019-12-01' AND create_time < DATE '2020-01-01' )) INHERITS (inherits_emp_20190710);


3,建函数和触发器:

CREATE OR REPLACE FUNCTION insert_inherits_emp_20190710_trigger()RETURNS trigger AS$BODY$DECLAREpartition_date TEXT;partition TEXT;BEGINpartition_date := to_char(NEW.create_time,'YYYYMM');partition := TG_TABLE_NAME || '_' || partition_date;EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';RETURN NULL;END;$BODY$LANGUAGE plpgsql VOLATILECOST 100;
CREATE TRIGGER insert_inherits_emp_20190710BEFORE INSERT ON inherits_emp_20190710FOR EACH ROW EXECUTE PROCEDURE insert_inherits_emp_20190710_trigger();


4,导入数据:

insert into inherits_emp_20190710 select * from pathman_emp_new;INSERT 0 0


5,查看是否进到分区子表

postgres=#\dt+


6,建索引

CREATE INDEX ON inherits_emp_20190710_201601(create_time);CREATE INDEX ON inherits_emp_20190710_201602(create_time);CREATE INDEX ON inherits_emp_20190710_201603(create_time);......CREATE INDEX ON inherits_emp_20190710_201910(create_time);CREATE INDEX ON inherits_emp_20190710_201911(create_time);CREATE INDEX ON inherits_emp_20190710_201912(create_time);

CREATE INDEX ON inherits_emp_20190710_201601(emp_name);CREATE INDEX ON inherits_emp_20190710_201602(emp_name);CREATE INDEX ON inherits_emp_20190710_201603(emp_name);......CREATE INDEX ON inherits_emp_20190710_201910(emp_name);CREATE INDEX ON inherits_emp_20190710_201911(emp_name);CREATE INDEX ON inherits_emp_20190710_201912(emp_name);


7,编辑测试脚本文件select_inherits_emp_20190710.sql 、insert_inherits_emp_20190710.sql

postgres@VECS04164:~$ cat select_inherits_emp_20190710.sql select * from public.inherits_emp_20190710 where emp_name ='e0cf722200f2833a04415347324a85f3' and create_time >='2016-07-01' and create_time<'2016-08-01';postgres@VECS04164:~$ cat insert_inherits_emp_20190710.sql insert into inherits_emp_20190710 (emp_name,emp_level,create_time) values('测试',100,now());


8,调用pgbench进行测试:

/usr/pgsql-10/bin/pgbench -f select_inherits_emp_20190710 -c 4/10 -j 4/10 -n -P 10 -r -T 180 -R 10000 -p 7474 -d postgres -U postgres -h localhost/usr/pgsql-10/bin/pgbench -f insert_inherits_emp_20190710.sql -c 4/10 -j 4/10 -n -P 10 -r -T 180 -R 10000 -p 7474 -d postgres -U postgres -h localhost


非分区表测试


CREATE TABLE test_tmp_14m (emp_id SERIAL,emp_level INTEGER,emp_name TEXT,create_time TIMESTAMP NOT NULL);


导入同等数据到test_tmp_14m,然后创建索引,之后进行select,insert的10,4并发测试

调用pgbench进行测试:

/usr/pgsql-10/bin/pgbench -f select_test_tmp_14m.sql -c 4/10 -j 4/10 -n -P 10 -r -T 180 -R 10000 -p 7474 -d postgres -U postgres -h localhost/usr/pgsql-10/bin/pgbench -f insert_test_tmp_14m.sql -c 4/10 -j 4/10 -n -P 10 -r -T 180 -R 10000 -p 7474 -d postgres -U postgres -h localhost


压测曲线


01

峰值qps压测

调用pgbench 以三种分区方式和不分区方式进行压测,调整 -c / -j / -t / -T -R等参数把cpu压满时的tps/qps。


02

CPU利用率监控视图


1),10个并发select的CPU使用率对比:


pg_pathman 10个并发select的cpu:


native 10个并发select的cpu:


inherit 10个并发select的cpu:


不分区 10个并发select的cpu:


2),4个并发select的CPU使用率对比:


pg_pathman 4个并发select的cpu:


native 4个并发select的cpu:


Inherit 4个并发select的cpu:


不分区 4个并发select的cpu:


3),10个并发insert的CPU使用率对比:


pg_pathman 10个并发insert的cpu:


native 10个并发insert的cpu:


inherit 10个并发insert的cpu:


不分区 10个并发insert的cpu:


4),4个并发insert的CPU使用率对比:


pg_pathman 4个并发insert的cpu:


native 4个并发insert的cpu:


inherit 4个并发insert的cpu:


不分区4个并发insert的cpu:


  推荐站点

  • At-lib分类目录At-lib分类目录

    At-lib网站分类目录汇集全国所有高质量网站,是中国权威的中文网站分类目录,给站长提供免费网址目录提交收录和推荐最新最全的优秀网站大全是名站导航之家

    www.at-lib.cn
  • 中国链接目录中国链接目录

    中国链接目录简称链接目录,是收录优秀网站和淘宝网店的网站分类目录,为您提供优质的网址导航服务,也是网店进行收录推广,站长免费推广网站、加快百度收录、增加友情链接和网站外链的平台。

    www.cnlink.org
  • 35目录网35目录网

    35目录免费收录各类优秀网站,全力打造互动式网站目录,提供网站分类目录检索,关键字搜索功能。欢迎您向35目录推荐、提交优秀网站。

    www.35mulu.com
  • 就要爱网站目录就要爱网站目录

    就要爱网站目录,按主题和类别列出网站。所有提交的网站都经过人工审查,确保质量和无垃圾邮件的结果。

    www.912219.com
  • 伍佰目录伍佰目录

    伍佰网站目录免费收录各类优秀网站,全力打造互动式网站目录,提供网站分类目录检索,关键字搜索功能。欢迎您向伍佰目录推荐、提交优秀网站。

    www.wbwb.net