postgresql 13 数据库分区表性能之二 pg_pathman 在分区数为 64、128、256、512、1024 的 pgbench 压测
龙飞文
os: centos 7.8
db: postgresql 13.1
版本
# cat /etc/centos-release
CentOS Linux release 7.8.2003 (Core)
#
# yum list installed |grep -i postgresql
postgresql13.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-contrib.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-devel.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-docs.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-libs.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-llvmjit.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-plperl.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-plpython3.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-pltcl.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-server.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-test.x86_64 13.1-1PGDG.rhel7 @pgdg13
# yum list installed |grep -i pathman
pg_pathman_13.x86_64 1.5.12-1.rhel7 @pgdg13
# su - postgres
Last login: Fri Dec 18 15:39:51 CST 2020 on pts/0
$ psql
psql (13.1)
Type "help" for help.
postgres=# select name,setting from pg_settings where name like '%partition%';
name | setting
-----------------------------------+---------
enable_partition_pruning | on
enable_partitionwise_aggregate | off
enable_partitionwise_join | off
pg_pathman.enable_auto_partition | on
pg_pathman.enable_partitionfilter | on
pg_pathman.enable_partitionrouter | off
(6 rows)
postgres=# select name,setting from pg_settings where name like 'pg_pathman%' order by name;
name | setting
--------------------------------------+----------
pg_pathman.enable | on
pg_pathman.enable_auto_partition | on
pg_pathman.enable_bounds_cache | on
pg_pathman.enable_partitionfilter | on
pg_pathman.enable_partitionrouter | off
pg_pathman.enable_runtimeappend | on
pg_pathman.enable_runtimemergeappend | on
pg_pathman.insert_into_fdw | postgres
pg_pathman.override_copy | on
(9 rows)
postgres=# create database yewudb;
yewudb=# \c yewudb
yewudb=# create extension pg_pathman;
yewudb=# \dx
List of installed extensions
Name | Version | Schema | Description
------------+---------+------------+----------------------------------
pg_pathman | 1.5 | public | Partitioning tool for PostgreSQL
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
yewudb=# \timing
pgbench select压测
64分区
\set aid random(1, 1000000)
BEGIN;
select a.name from tmp_hash64 a where a.id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/select64.sql -r -c 200 -j 4 -T 60 yewudb
tps = 11612.727137 (including connections establishing)
tps = 11615.184407 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
5.175 BEGIN;
5.509 select a.name from tmp_hash64 a where a.id in (:aid);
5.853 END;
128分区
\set aid random(1, 1000000)
BEGIN;
select a.name from tmp_hash128 a where a.id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/select128.sql -r -c 200 -j 4 -T 60 yewudb
tps = 18402.839508 (including connections establishing)
tps = 18404.046885 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
3.264 BEGIN;
3.536 select a.name from tmp_hash128 a where a.id in (:aid);
3.735 END;
256分区
\set aid random(1, 1000000)
BEGIN;
select a.name from tmp_hash256 a where a.id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/select256.sql -r -c 200 -j 4 -T 60 yewudb
tps = 17544.427395 (including connections establishing)
tps = 17545.723398 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
3.375 BEGIN;
3.751 select a.name from tmp_hash256 a where a.id in (:aid);
3.911 END;
512分区
\set aid random(1, 1000000)
BEGIN;
select a.name from tmp_hash512 a where a.id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/select512.sql -r -c 200 -j 4 -T 60 yewudb
tps = 17541.912282 (including connections establishing)
tps = 17543.162897 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
3.323 BEGIN;
3.970 select a.name from tmp_hash512 a where a.id in (:aid);
3.779 END;
1024分区
\set aid random(1, 1000000)
BEGIN;
select a.name from tmp_hash1024 a where a.id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/select1024.sql -r -c 200 -j 4 -T 60 yewudb
tps = 10503.688950 (including connections establishing)
tps = 10504.402890 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
5.366 BEGIN;
7.871 select a.name from tmp_hash1024 a where a.id in (:aid);
5.477 END;
pgbench delete压测
64分区
\set aid random(1, 1000000)
BEGIN;
delete from tmp_hash64 where id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/delete64.sql -r -c 200 -j 4 -T 60 yewudb
tps = 4991.522388 (including connections establishing)
tps = 4994.342298 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
1.350 BEGIN;
13.651 delete from tmp_hash64 where id in (:aid);
23.626 END;
128分区
\set aid random(1, 1000000)
BEGIN;
delete from tmp_hash128 where id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/delete128.sql -r -c 200 -j 4 -T 60 yewudb
tps = 7669.556156 (including connections establishing)
tps = 7670.873011 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
1.414 BEGIN;
10.272 delete from tmp_hash128 where id in (:aid);
13.942 END;
256分区
\set aid random(1, 1000000)
BEGIN;
delete from tmp_hash256 where id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/delete256.sql -r -c 200 -j 4 -T 60 yewudb
tps = 6094.133358 (including connections establishing)
tps = 6094.588383 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
1.467 BEGIN;
12.754 delete from tmp_hash256 where id in (:aid);
18.211 END;
512分区
\set aid random(1, 1000000)
BEGIN;
delete from tmp_hash512 where id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/delete512.sql -r -c 200 -j 4 -T 60 yewudb
tps = 8107.010150 (including connections establishing)
tps = 8107.513521 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
1.643 BEGIN;
11.439 delete from tmp_hash512 where id in (:aid);
11.307 END;
1024分区
\set aid random(1, 1000000)
BEGIN;
delete from tmp_hash1024 where id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/delete1024.sql -r -c 200 -j 4 -T 60 yewudb
tps = 5972.929791 (including connections establishing)
tps = 5973.345274 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
2.096 BEGIN;
17.301 delete from tmp_hash1024 where id in (:aid);
13.748 END;
pgbench insert压测
64分区
\set aid random(1, 1000000)
BEGIN;
insert into tmp_hash64(id,name,insert_timestamp) values(:aid,:aid::text,now());
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/insert64.sql -r -c 200 -j 4 -T 60 yewudb
tps = 9849.273763 (including connections establishing)
tps = 9851.221707 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
1.577 BEGIN;
5.589 insert into tmp_hash64(id,name,insert_timestamp) values(:aid,:aid::text,now());
12.704 END;
128分区
\set aid random(1, 1000000)
BEGIN;
insert into tmp_hash128(id,name,insert_timestamp) values(:aid,:aid::text,now());
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/insert128.sql -r -c 200 -j 4 -T 60 yewudb
tps = 8109.773264 (including connections establishing)
tps = 8110.320924 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
1.800 BEGIN;
7.030 insert into tmp_hash128(id,name,insert_timestamp) values(:aid,:aid::text,now());
15.480 END;
256分区
\set aid random(1, 1000000)
BEGIN;
insert into tmp_hash256(id,name,insert_timestamp) values(:aid,:aid::text,now());
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/insert256.sql -r -c 200 -j 4 -T 60 yewudb
tps = 6923.211556 (including connections establishing)
tps = 6923.711604 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
1.722 BEGIN;
10.255 insert into tmp_hash256(id,name,insert_timestamp) values(:aid,:aid::text,now());
16.483 END;
512分区
\set aid random(1, 1000000)
BEGIN;
insert into tmp_hash512(id,name,insert_timestamp) values(:aid,:aid::text,now());
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/insert512.sql -r -c 200 -j 4 -T 60 yewudb
tps = 4648.734825 (including connections establishing)
tps = 4649.086852 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
1.784 BEGIN;
17.957 insert into tmp_hash512(id,name,insert_timestamp) values(:aid,:aid::text,now());
22.725 END;
1024分区
\set aid random(1, 1000000)
BEGIN;
insert into tmp_hash1024(id,name,insert_timestamp) values(:aid,:aid::text,now());
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/insert1024.sql -r -c 200 -j 4 -T 60 yewudb
tps = 1539.650232 (including connections establishing)
tps = 1539.769618 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
2.245 BEGIN;
42.197 insert into tmp_hash1024(id,name,insert_timestamp) values(:aid,:aid::text,now());
84.116 END;
pgbench update压测
64分区
\set aid random(1, 1000000)
BEGIN;
update tmp_hash64 set name=id where id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/update64.sql -r -c 200 -j 4 -T 60 yewudb
tps = 10133.327855 (including connections establishing)
tps = 10133.979600 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
1.851 BEGIN;
5.357 update tmp_hash64 set name=id where id in (:aid);
12.253 END;
128分区
\set aid random(1, 1000000)
BEGIN;
update tmp_hash128 set name=id where id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/update128.sql -r -c 200 -j 4 -T 60 yewudb
tps = 7784.243781 (including connections establishing)
tps = 7784.743137 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
1.806 BEGIN;
6.864 update tmp_hash128 set name=id where id in (:aid);
16.733 END;
256分区
\set aid random(1, 1000000)
BEGIN;
update tmp_hash256 set name=id where id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/update256.sql -r -c 200 -j 4 -T 60 yewudb
tps = 2735.077652 (including connections establishing)
tps = 2735.268440 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
2.057 BEGIN;
11.635 update tmp_hash256 set name=id where id in (:aid);
58.887 END;
512分区
\set aid random(1, 1000000)
BEGIN;
update tmp_hash512 set name=id where id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/update512.sql -r -c 200 -j 4 -T 60 yewudb
tps = 9494.414352 (including connections establishing)
tps = 9495.134094 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
1.997 BEGIN;
7.256 update tmp_hash512 set name=id where id in (:aid);
11.513 END;
1024分区
\set aid random(1, 1000000)
BEGIN;
update tmp_hash1024 set name=id where id in (:aid);
END;
/usr/pgsql-13/bin/pgbench -M simple -n -P 1 -f /var/lib/pgsql/bench/update1024.sql -r -c 200 -j 4 -T 60 yewudb
tps = 3657.653888 (including connections establishing)
tps = 3657.893136 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 1000000)
5.194 BEGIN;
17.650 update tmp_hash1024 set name=id where id in (:aid);
31.434 END;
参考：
类似资料：