native 数据迁移_PostgreSQL10 pg_pathman、native、Inherits 分区测试报告

于 2021-01-14 11:51:55 发布
阅读量346 收藏
点赞数
文章标签： native 数据迁移
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_31894867/article/details/113489457
版权

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

1)：只vacuum 最近分区子表而不是vacuum大表，降低io消耗，减少表膨胀。 2)：方便维护表，如创建索引耗时更短，通过清理历史分区释放磁盘空间。 3)：减少数据扫描等

一.测试描述

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

二.测试说明

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

三.测试CASE

61eb642e5b4b4060a56bf303f731611e.png

四.测试结论

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分钟)：
客户端并发数数据量/分区个数pathman查主表(cpu利用率/qps/峰值qps)native查主表(cpu利用率/qps/峰值qps)inherits查主表(cpu利用率/qps/峰值qps)不分区表(cpu利用率/qps/峰值qps)
10个5612.5504万/20个39%/3489/1480091%/3620/540095%/3327/450028%/3828/7000
4个5612.5504万/20个39%/347065%/331280%/297125%/3998
insert记录(取二次执行记录的平均数，每次执行时间3分钟)：
客户端并发数数据量/分区个数pathman查主表(cpu利用率/tps)native查主表(cpu利用率/tps)inherits查主表(cpu利用率/tps)不分区表(cpu利用率/tps)
10个5612.5504万/20个28%/367654%/437138%/376228%/4111
4个5612.5504万/20个27%/267335%/256932%/254225%/2662

五.测试环境硬件配置信息

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.rpm python-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 --默认true立即迁移数据，false是不迁移数据);
5. 验证父表的数据量： 
select count(*) from only pathman_emp_20190710;
6. 将数据并行的迁移到分区子表中 (需要一段时间)： 
selectpartition_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.sqlpostgres@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.sqlpostgres@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 triggerCREATE 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.sqlpostgres@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

十.峰值qps压测

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

十一.CPU利用率监控视图

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

pg_pathman 10个并发select的cpu：

7bce78eb5a5b290dd81bb05ccee0872f.png

native 10个并发select的cpu：

2b8496a2d429be90ca14e685541f8950.png

inherit 10个并发select的cpu：

037b3837ff4f1fdda749ffd0aa9bf2bf.png

不分区 10个并发select的cpu：

02534042bb4c11f9758afa8fa7648fd3.png

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

pg_pathman 4个并发select的cpu：

3d864b90c4dc25b44730594ec952cdae.png

native 4个并发select的cpu：

152bb5ccedb8c82f1b9cd915eec6c831.png

Inherit 4个并发select的cpu：

301804853f8f3700f867f2211db040ce.png

不分区 4个并发select的cpu：

871f2a793b21ef64851fbb1b8b3de841.png

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

pg_pathman 10个并发insert的cpu：

490538f07fdd5b62fc1ac9ee3fd8ba1d.png

native 10个并发insert的cpu：

8f25d7aef9b12ce7c47819d771b432dc.png

inherit 10个并发insert的cpu：不分区 10个并发insert的cpu：

c13953574b4da39ac34e86f369122d14.png

不分区 10个并发insert的cpu：

57a45f68195e59211e80d1d0dea1f000.png

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

pg_pathman 4个并发insert的cpu：

2e3d39388becfe8002f7fd808311d171.png

native 4个并发insert的cpu：

2a649807f2d2290e04d10ab02b26d774.png

inherit 4个并发insert的cpu：

07f34a7ce5d797a039be15561c3edb52.png

不分区4个并发insert的cpu：

5473143a7a91c6a43b2e02a8f5cabb47.png

作者简介：田磊磊 PostgreSQL、Greenplum高级数据库工程师，熟悉PG技术栈，对OLTP/OLAP均有深入实操经验，目前就职于哈啰出行，维护着国内最大的PG集群之一，超过400个PG实例。 微信号：TLL-PostgreSQL-MySQL 
I Love PG

关于我们

中国开源软件推进联盟PostgreSQL分会(简称：中国PG分会)于2017年成立，由国内多家PostgreSQL生态企业所共同发起，业务上接受工信部中国电子信息产业发展研究院指导。中国PG分会是一个非盈利行业协会组织。我们致力于在中国构建PostgreSQL产业生态，推动PostgreSQL产学研用发展。

技术文章精彩回顾PostgreSQL学习的九层宝塔PostgreSQL职业发展与学习攻略2019，年度数据库舍 PostgreSQL 其谁?Postgres是最好的开源软件PostgreSQL是世界上最好的数据库从Oracle迁移到PostgreSQL的十大理由从“非主流”到“潮流”，开源早已值得拥有PG活动精彩回顾创建PG全球生态！PostgresConf.CN2019大会盛大召开首站起航！2019“让PG‘象’前行”上海站成功举行走进蓉城丨2019“让PG‘象’前行”成都站成功举行中国PG象牙塔计划发布，首批合作高校授牌仪式在天津举行群英论道聚北京，共话PostgreSQL相聚巴厘岛| PG Conf.Asia 2019  DAY0、DAY1简报相知巴厘岛| PG Conf.Asia 2019 DAY2简报独家|硅谷Postgres大会简报直播回顾 | Bruce Momjian：原生分布式将在PG 14版本发布PG培训认证精彩回顾中国首批PGCA认证考试圆满结束，203位考生成功获得认证！中国第二批PGCA认证考试圆满结束，115位考生喜获认证！重要通知：三方共建，中国PostgreSQL认证权威升级！近500人参与！首次PGCE中级、第三批次PGCA初级认证考试落幕！2020年首批 | 中国PostgreSQL初级认证考试圆满结束一分耕耘一分收获，第五批次PostgreSQL认证考试成绩公布

df816c27c965fba1e2c011ad2466568b.png

李菲安
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值