一、测试内容

使用pgbench测试分区表和不分区表的性能差异(tps)。查询某个时间范围某个条件的数据。

建表范围24个月，数据平均分布。分别按1、3、6个月间隔分区，即分区表分别为24、8、4个，每种分区方式按如下条件测试：

1. 500万数据

1.1. 并发会话数为4

1.1.1. 分区索引
1.1.2. 普通索引
1.1.3. 分区无索引
1.1.4. 普通无索引

1.2. 并发会话数为8

1.2.1. 分区索引
1.2.2. 普通索引
1.2.3. 分区无索引
1.2.4. 普通无索引

2. 1000万数据

2.1. 并发会话数为4

2.1.1. 分区索引
2.1.2. 普通索引
2.1.3. 分区无索引
2.1.4. 普通无索引

2.2. 并发会话数为8

2.2.1. 分区索引
2.2.2. 普通索引
2.2.3. 分区无索引
2.2.4. 普通无索引

二、建表结构

1. 分区表

partition_emp
- 字段
  - id(serial4)
  - name(varchar(20))
  - level(int4)
  - postal_code(varchar(20))
  - create_date(date)
- 分区
  - partition_emp_20191008
    - 索引
      - partition_emp_20191008_create_date_idx
      - partition_emp_20191008_postal_code_idx
  - ……
  - partition_emp_20210908
    - 索引
      - partition_emp_20210908_create_date_idx
      - partition_emp_20210908_postal_code_idx

表1 分区表结构partition_emp_20191011

列名	类型	示例	可否为空	注释
id	serial4	1	否	自增id
name	varchar	'123'	是	随机3位数字的字符串
level	int4	2	是	0~6随机整数
postal_code	varchar	'123'	是	随机3位数字的字符串
create_date	date	date '2021-01-01'	否

2. 普通表

all_emp
- 字段
  - id(serial4)
  - name(varchar(20))
  - level(int4)
  - postal_code(varchar(20))
  - create_date(date)
- 索引
  - all_emp_create_date_idx
  - all_emp_postal_code_idx

表2 普通表结构all_emp

列名	类型	示例	可否为空	注释
id	serial4	1	否	自增id
name	varchar	'123'	是	随机3位数字的字符串
level	int4	2	是	0~6随机整数
postal_code	varchar	'123'	是	随机3位数字的字符串
create_date	date	date '2021-01-01'	否

三、测试的机器配置

系统:Linux version 5.4.0-87-generic
cpu: 6 Intel(R) Core(TM) i5-8500 CPU @ 3.00GHz
内存:16G
postgresql版本:10.18

四、测试流程

sql语句脚本

prepare-partition.sql

创建不分区表和索引
创建分区主表，以create_date按RANGE分
定义创建分区表的函数，待测试脚本执行
- 参数为分区间隔月数
- 起始日期为现在往前推算24个月
定义插入数据函数，待测试脚本执行
- 参数为数据总量
- 先往分区表插入数据，再拷贝这些数据到非分区表，以保证测试的一致性
- 待测试查询字段name 、postal_code 插入随机字符串(((RANDOM()::NUMERIC(4, 3))*1000)::INTEGER)::VARCHAR ，每个分区表任意值的重复数约200个（足够数量能体现出分区和不分区查询之间的差异）

-- 1. 创建非分区表和索引
DROP TABLE IF EXISTS all_emp;
CREATE TABLE all_emp (
	id serial NOT NULL,
	name VARCHAR(20),
	level INTEGER,
	postal_code VARCHAR(20),
	create_date DATE NOT NULL
);
CREATE INDEX ON all_emp(postal_code);
CREATE INDEX ON all_emp(create_date);

-- 2. 创建分区主表
DROP TABLE IF EXISTS partition_emp;
CREATE TABLE partition_emp (
 	id SERIAL NOT NULL,
	name VARCHAR(20),
	level INTEGER,
	postal_code VARCHAR(20),
	create_date DATE NOT NULL
) PARTITION BY RANGE(create_date);

-- 3. 创建分区表和索引函数
DROP FUNCTION public.create_partition_tables;
CREATE OR REPLACE FUNCTION public.create_partition_tables(interval_count integer)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
	table_name varchar := '';
	start_date date := current_date - interval '2 year';
	date_interval varchar := CONCAT(interval_count, ' month');
	next_date date;
BEGIN
	WHILE start_date < current_date LOOP
        table_name := CONCAT('partition_emp_', to_char(start_date, 'YYYYMMDD'));
	next_date := start_date + date_interval::interval;
	EXECUTE 'DROP TABLE IF EXISTS ' || table_name;
	EXECUTE 'CREATE TABLE ' || table_name || ' PARTITION OF partition_emp FOR VALUES FROM (''' || cast(start_date as varchar) || ''') TO (''' ||  cast(next_date as varchar) ||  ''')';
	EXECUTE 'CREATE INDEX ON ' ||  table_name || '(postal_code)';
	EXECUTE 'CREATE INDEX ON ' ||  table_name || '(create_date)';
	start_date := next_date;
	END LOOP;
END;
$$;

-- 3. 插入数据函数
DROP FUNCTION public.insert_data;
CREATE OR REPLACE FUNCTION public.insert_data(quantity integer)
RETURNS TEXT
LANGUAGE plpgsql
AS $body$
BEGIN
	EXECUTE 'TRUNCATE partition_emp';
	EXECUTE 'INSERT INTO partition_emp (
		name,
		level,
		postal_code,
		create_date
	) SELECT (((RANDOM()::NUMERIC(4, 3))*1000)::INTEGER)::VARCHAR, RANDOM()*6, (((RANDOM()::NUMERIC(4, 3))*1000)::INTEGER)::VARCHAR, (current_date - (ceil(RANDOM()*(365+364)))::INTEGER) FROM generate_series(1, ' || quantity || ')';
	EXECUTE 'TRUNCATE all_emp';
	EXECUTE 'INSERT INTO all_emp SELECT * FROM partition_emp';
	RETURN '插入数据成功';
END;
$body$;

运行测试脚本

test.sh

psql链接数据库
按每1、3、6个月分区创建分区表，循环执行测试
导入sql脚本，执行创建数据表和索引 —— 插入数据500万/1000万条 —— 创建pgbench自定义脚本 —— 运行测试
查询条件postal_code字段是索引，name字段不是索引
查询条件的日期范围是 '2021-01-01' —— '2021-02-01'，如果当前日期不是1号，则跨了分区
每个case运行300秒

#!/bin/bash

# 数据库参数
USER="yanjing"
DB="yanjing"
RESULT="result.txt"

# sql脚本
echo "=============链接数据库======================="
echo "=============执行创建不分区数据表和索引，定义创建分区表和索引的函数=================="
psql -U $USER -d $DB > $RESULT << EOF
\i prepare-partition.sql
\q
EOF

function createPartitionTables() {

	echo "=============每$1个月建立一个分区表=================="
	psql -U $USER -d $DB >> $RESULT <<- EOF
    SELECT public.create_partition_tables($1);
	\q
	EOF
	echo "=============建立分区表结束=================="
	return $1;
}

function insertData() {

	echo "=============插入数据$1条=================="
	psql -U $USER -d $DB >> $RESULT <<- EOF
        SELECT public.insert_data($1);
        SELECT COUNT(*) FROM all_emp;
	\q
	EOF
	echo "=============插入数据结束=================="
	return $1;
}

function runTest() {

	SUBJECT="$1条数据，并发会话数量$2，每$3个月分区"
	TIME=300
	sleep 10
	echo "============$SUBJECT" >> $RESULT
	echo "------------$SUBJECT：分区索引" >> $RESULT
	cat > select.sql <<- EOF
            BEGIN;
                SELECT * FROM partition_emp WHERE create_date > '2021-01-01' AND create_date < '2021-02-01' AND postal_code = '123';
            END;
	EOF
	pgbench -f select.sql -c $2 -P 30 -r -T $TIME -d -U yanjing >> $RESULT

	sleep 10
	echo "------------$SUBJECT：普通索引" >> $RESULT
	cat > select.sql <<- EOF
	BEGIN;
            SELECT * FROM all_emp WHERE create_date > '2021-01-01' AND create_date < '2021-02-01' AND postal_code = '123';
	END;
	EOF
	pgbench -f select.sql -c $2 -P 30 -r -T $TIME -d -U yanjing >> $RESULT

	sleep 10
	echo "-------------$SUBJECT：无分区索引" >> $RESULT
	cat > select.sql <<- EOF
	BEGIN;
            SELECT * FROM partition_emp WHERE create_date > '2021-01-01' AND create_date < '2021-02-01' AND name = '123';
	END;
	EOF
	pgbench -f select.sql -c $2 -P 30 -r -T $TIME -d -U yanjing >> $RESULT

	sleep 10
	echo "--------------$SUBJECT：无普通索引" >> $RESULT
	cat > select.sql <<- EOF
	BEGIN;
            SELECT * FROM all_emp WHERE create_date > '2021-01-01' AND create_date < '2021-02-01' AND name = '123';
	END;
	EOF
	pgbench -f select.sql -c $2 -P 30 -r -T $TIME -d -U yanjing >> $RESULT
}

echo "=============开始测试=========================" 
# 分别按照1、3、6个月分区
DATE_INTERVALS=(1 3 6)
for i in ${DATE_INTERVALS[*]}
do
	createPartitionTables $i
	DATA_TOTAL=5000000
	insertData $DATA_TOTAL
	runTest $DATA_TOTAL 4 $i
	runTest $DATA_TOTAL 8 $i
	DATA_TOTAL=10000000
	insertData $DATA_TOTAL
	runTest $DATA_TOTAL 4 $i
	runTest $DATA_TOTAL 8 $i
done

五、测试结果

测试执行时间: 4h26min

表3 跨分区查询测试结果(tps)

24分区	500万数据/客户端4	500万数据/客户端8	1000万数据/客户端4	1000万数据/客户端8
分区索引	2683	5700	2983	3700
普通索引	454	644	221	315
分区无索引	163	175	88	93
普通无索引	98	108	49	52
8分区	----	----	----	----
分区索引	4795	5459	2894	3652
普通索引	452	641	222	315
分区无索引	163	175	88	93
普通无索引	99	108	49	52
4分区	----	----	----	----
分区索引	4870	5596	3129	3886
普通索引	454	643	222	316
分区无索引	164	176	88	93
普通无索引	99	109	49	52

表4 不跨分区查询测试结果(tps)

24分区	500万数据/客户端4	500万数据/客户端8	1000万数据/客户端4	1000万数据/客户端8
分区索引	3725	6799	4289	4228
普通索引	496	692	244	290
分区无索引	241	248	120	117
普通无索引	106	114	47	48
8分区	----	----	----	----
分区索引	5223	5538	3722	4048
普通索引	483	584	240	287
分区无索引	207	216	113	116
普通无索引	87	95	44	47
4分区	----	----	----	----
分区索引	5137	5398	3982	4313
普通索引	480	582	241	288
分区无索引	206	216	113	117
普通无索引	87	95	44	47

结论（以跨分区查询测试结果为统计对象）

分区与不分区性能对比
- 有索引：性能平均提升约9~13倍
- 无索引：性能平均提升约60%~70%
数据量的影响（只分析分区有索引查询）
- 数据量1000万是数据量500万查询效率的30%~40%
分区数量的影响
- 分区有索引查询，同一条件下不同分区数量的tps只有细微差别**。可能需要改变查询数据落位情况（是否跨分区和跨分区数量）重新验证。
- 其它3钟查询，同一条件下不同分区数量的tps完全一致。说明如果不加索引的话，分区和不分区对查询效率没有影响。
客户端数量的影响（只分析分区有索引查询）

客户端数量8相对客户端数量4，tps提高20%左右。
第一个case查询数据比较反常，考虑是缓存的影响。

下一步验证计划

PG升级到11（11相对10在分区方面的性能优化）
改变查询数据落位情况（是否跨分区和跨分区数量）

结果：不跨分区查询的tps相比跨分区查询提高，并且分区数越多（24分区），提升越大。
查询缓存影响

结果：24分区/500万数据/4个客户端/分区索引查询，首次查询tps为3250，再次相同条件查询tps为5570。

使用pgbench对PostgreSQL分区表性能测试