postgresql 13 数据库分区表性能之二 pg_pathman 在分区数为 64、128、256、512、1024 的 pgbench 压测_postgrel一个库最多支持多少分区-程序员宅基地

技术标签： # postgresql opti table index  pgbench  pgsql  

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;

参考：
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ctypyb2002/article/details/111500789

智能推荐

华三SDN产业链分析-程序员宅基地

文章浏览阅读218次。为什么80%的码农都做不了架构师？>>> ..._h3c virtual converged framework切片

手把手教你开发第一个HarmonyOS （鸿蒙）移动应用_鸿蒙移动应用开发-程序员宅基地

文章浏览阅读1.9w次，点赞44次，收藏268次。AndroidIOSHarmonyOS （鸿蒙）文档概览-HarmonyOS应用开发官网2.1.1 系统的定位搭载该操作系统的设备在系统层⾯融为⼀体、形成超级终端，让设备的硬件能⼒可以弹性 扩展，实现设备之间 硬件互助，资源共享。对消费者⽽⾔，HarmonyOS能够将⽣活场景中的各类终端进⾏能⼒整合，实现不同终端 设备之间的快速连接、能⼒互助、资源共享，匹配合适的设备、提供流畅的全场景体验。⾯向开发者，实现⼀次开发，多端部署。_鸿蒙移动应用开发

AndroidStudio无代码高亮解决办法_android studio 高亮-程序员宅基地

文章浏览阅读2.8k次。AndroidStudio 升级到 4.2.2 版本后，没有代码高亮了，很蛋疼。解决办法是：点开上方的 File，先勾选 Power Save Mode 再取消就可以了。_android studio 高亮

swift4.0 valueForUndefinedKey:]: this class is not key value coding-compliant for the key unity.'_forundefinedkey swift4-程序员宅基地

文章浏览阅读1k次。使用swift4.0整合Unity出现[ valueForUndefinedKey:]: this class is not key value coding-compliant for the key unity.'在对应属性前加@objc 即可。或者调回swift3.2版本_forundefinedkey swift4

Spring Security2的COOKIE的保存时间设置_springsecurity 设置cookie失效时间-程序员宅基地

文章浏览阅读1.3k次。http auto-config="true" access-denied-page="/common/403.htm"> intercept-url pattern="/login.**" access="IS_AUTHENTICATED_ANONYMOUSLY"/> form-login login-page="/login.jsp" defau_springsecurity 设置cookie失效时间

view滑动冲突解决实战篇2（外部拦截法）_viewpage2外部拦截事件-程序员宅基地

文章浏览阅读1.1k次。继上篇内部拦截法需求还是跟上篇一样。只不过这次用外部拦截法来解决；只要在父容器添加如下代码就可以解决了滑动冲突，很简单，套模板就行 // 分别记录上次滑动的坐标(onInterceptTouchEvent) private int mLastXIntercept = 0; private int mLastYIntercept = 0; @Override public bo_viewpage2外部拦截事件

随便推点

Kotlin相关面试题_kotlin面试题-程序员宅基地

文章浏览阅读1.9w次，点赞26次，收藏185次。目录一.请简述下什么是kotlin？它有什么特性？二.Kotlin 中注解 @JvmOverloads 的作用？三.Kotlin中的MutableList与List有什么区别？四.kotlin实现单例的几种方式？五. kotlin中关键字data的理解？相对于普通的类有哪些特点？六.什么是委托属性？简单说一下应用场景？七.kotlin中with、run、apply、let函数的区别？一般用于什么场景？八.kotlin中Unit的应用以及和Java中void的区别？九.Ko_kotlin面试题

HEVC英文缩写及部分概念整理(1)--博主整理_反量化 英文缩写-程序员宅基地

文章浏览阅读2.8k次。有这个想法一方面是确实很多时候会记不得一些缩写是什么意思。另外也是受 http://blog.csdn.net/lin453701006/article/details/52797415这篇博客的启发，本文主要用于自己记忆 内容主要整理自http://blog.sina.com.cn/s/blog_520811730101hmj9.html http://blog.csdn.net/feix_反量化 英文缩写

超级简单的Python爬虫入门教程(非常详细)，通俗易懂，看一遍就会了_爬虫python入门-程序员宅基地

文章浏览阅读7.3k次，点赞6次，收藏36次。超级简单的Python爬虫入门教程(非常详细)，通俗易懂，看一遍就会了_爬虫python入门

python怎么输出logistic回归系数_python - Logistic回归scikit学习系数与统计模型的系数 - SO中文参考 - www.soinside.com...-程序员宅基地

文章浏览阅读1.2k次。您的代码存在一些问题。首先，您在此处显示的两个模型是not等效的：尽管您将scikit-learn LogisticRegression设置为fit_intercept=True(这是默认设置)，但您并没有这样做statsmodels一；来自statsmodels docs：默认情况下不包括拦截器，用户应添加。参见statsmodels.tools.add_constant。另一个问题是，尽管您处..._sm fit(method

VS2017、VS2019配置SFML_vsllfqm-程序员宅基地

文章浏览阅读518次。一、sfml官网下载32位的版本 一样的设置，64位的版本我没有成功，用不了。二、三、四以下这些内容拷贝过去：sfml-graphics-d.libsfml-window-d.libsfml-system-d.libsfml-audio-d.lib..._vsllfqm

vc——类似与beyondcompare工具的文本比较算法源代码_byoned compare 字符串比较算法-程序员宅基地

文章浏览阅读2.7k次。由于工作需要，要做一个类似bc2的文本比较工具，用红色字体标明不同的地方，研究了半天，自己写了一个简易版的。文本比较的规则是1.先比较文本的行数，2.再比较对应行的字符串的长度3.再比较每一个字符串是否相同。具体代码如下：其中m_basestr和m_mergestr里面存放是待比较的字符串int basecount=m_basestr.GetLength(); int mergec_byoned compare 字符串比较算法

推荐文章

热门文章

相关标签