作者简介：田磊磊 PostgreSQL、Greenplum高级数据库工程师，熟悉PG技术栈，对OLTP/OLAP均有深入实操经验，目前就职于哈啰出行，维护着国内最大的PG集群之一，超过400个PG实例。微信号：TLL-PostgreSQL-MySQL
背景
哈啰出行旗下包括哈啰单车、助力车、顺风车、打车、电动车、换电等几乎所有业务都基于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个并发压测，压测脚本：
代码语言：javascript复制/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 locahost03
测试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:
代码语言：javascript复制postgres@VECS04164:~$ cat /proc/cpuinfo | grep "model name"
model name : Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHz
model name : Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHz
model name : Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHz
model name : Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHz内存：
代码语言：javascript复制postgres@VECS04164:~$ cat /proc/meminfo | grep -i total
MemTotal: 8193156 kB
SwapTotal: 0 kB
VmallocTotal: 34359738367 kB
HugePages_Total: 0操作系统版本：
代码语言：javascript复制postgres@VECS04164:~$ cat /etc/redhat-release
CentOS release 6.9 (Final)需要提前安装好pg_pathman插件：
下载地址：https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-6.9-x86_64/
需要两个rpm包：
代码语言：javascript复制pg_pathman10-1.4.13-1.rhel6.x86_64.rpm
python-psycopg2-2.7.4-1.rhel6.x86_64.rpmpg_pathman分区测试
1,创建pathman分区表
代码语言：javascript复制CREATE TABLE pathman_emp_20190710 (
emp_id SERIAL,
emp_level INTEGER,
emp_name TEXT,
create_time TIMESTAMP NOT NULL
);2,插入数据：
代码语言：javascript复制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,创建索引
代码语言：javascript复制CREATE INDEX ON pathman_emp_20190710(create_time);
CREATE INDEX ON pathman_emp_20190710(emp_name);4,创建分区子表：
代码语言：javascript复制SELECT create_range_partitions(
'pathman_emp_20190710',--主表名
'create_time', --分区字段
'2016-01-01'::date, --分区起始日期
'1 month'::interval, --分区间隔
null, --不指定分区数量，根据时间与间隔会自动计算出数量
false --默认tue立即迁移数据，false是不迁移数据
);5，验证父表的数据量
代码语言：javascript复制select count(*) from only pathman_emp_20190710;6,将数据并行的迁移到分区子表中：(需要一段时间)
代码语言：javascript复制select partition_table_concurrently('pathman_emp_20190710',10000,1.0);7，等迁移完成，验证父、子表数据量：
代码语言：javascript复制select count(*) from only pathman_emp_20190710;
postgres=#dt 8，编辑测试脚本文件select_pathman_emp_20190710.sql 、insert_pathman_emp_20190710.sql
代码语言：javascript复制postgres@VECS04164:~$ cat select_pathman_new.sql
select * 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.sql
insert into pathman_emp_new values(emp_name,emp_level,create_time) values('测试',100,now());9,调用pgbench进行测试：
代码语言：javascript复制/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 localhostnative 分区测试
1，创建native分区表
代码语言：javascript复制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，创建子分区：
代码语言：javascript复制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
代码语言：javascript复制insert into native_emp_20190710 select * from pathman_emp_new；4，创建子分区表索引
代码语言：javascript复制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，验证数据是否已经到分区表
代码语言：javascript复制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
代码语言：javascript复制postgres@VECS04164:~$ cat select_native_new.sql
select * 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.sql
insert into native_emp_20190710 (emp_name,emp_level,create_time) values('测试',100,now());7，调用pgbench进行测试：
代码语言：javascript复制/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 localhostInherits 分区测试
1，建父表：
代码语言：javascript复制CREATE TABLE inherits_emp_20190710 (
emp_id SERIAL,
emp_level INTEGER,
emp_name TEXT,
create_time TIMESTAMP NOT NULL
);2，继承父表建分区子表：
代码语言：javascript复制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,建函数和触发器：
代码语言：javascript复制CREATE OR REPLACE FUNCTION insert_inherits_emp_20190710_trigger()
RETURNS trigger AS
$BODY$
DECLARE
partition_date TEXT;
partition TEXT;
BEGIN
partition_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 VOLATILE
COST 100;CREATE TRIGGER insert_inherits_emp_20190710
BEFORE INSERT ON inherits_emp_20190710
FOR EACH ROW EXECUTE PROCEDURE insert_inherits_emp_20190710_trigger();4，导入数据：
代码语言：javascript复制insert into inherits_emp_20190710 select * from pathman_emp_new;
INSERT 0 05，查看是否进到分区子表
代码语言：javascript复制postgres=#dt 6，建索引
代码语言：javascript复制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
代码语言：javascript复制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进行测试：
代码语言：javascript复制/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进行测试：
代码语言：javascript复制/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：
