postgresql 13 数据库分区表性能之二 pg_pathman 在分区数为 64、128、256、512、1024 的 pgbench 压测

ゞ浴缸里的玫瑰 2022-12-29 09:41 213阅读 0赞

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;

参考：