一、测试内容
使用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。
近期评论