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

智能推荐

使用UmcFramework和unimrcpclient.xml连接多个SIP设置的配置指南及C代码示例

在多媒体通信领域，MRCP（Media Resource Control Protocol）协议被广泛用于控制语音识别和合成等媒体资源。UniMRCP是一个开源的MRCP实现，提供了客户端和服务端的库。UmcFramework是一个基于UniMRCP客户端库的示例应用程序框架，它帮助开发者快速集成和测试MRCP客户端功能。本文将详细介绍如何使用UmcFramework和unimrcpclient.xml配置文件连接到多个SIP设置，以及如何用C代码进行示例说明。

java.net.ProtocolException: Server redirected too many times (20)-程序员宅基地

文章浏览阅读3k次。报错：java.net.ProtocolException: Server redirected too many times (20)1.没有检查到cookie，一直循环重定向。解决：CookieHandler.setDefault(new CookieManager(null, CookiePolicy.ACCEPT_ALL));URL url = new URL(url); ..._java.net.protocolexception: server redirected too many times (20)

springboot启动报错 Failed to scan *****/derbyLocale_ja_JP.jar from classloader hierarchy_failed to scan from classloader hierarchy-程序员宅基地

文章浏览阅读4.1k次。问题这是部分报错信息2019-07-11 14:03:34.283 WARN [restartedMain][DirectJDKLog.java:175] - Failed to scan [file:/D:/repo/org/apache/derby/derby/10.14.2.0/derbyLocale_ja_JP.jar] from classloader hierarchyjava...._failed to scan from classloader hierarchy

MATLAB-ones函数_matlab中ones函数-程序员宅基地

文章浏览阅读2.8k次，点赞3次，收藏7次。在MATLAB中，ones函数用于创建一个指定大小的由1组成的矩阵或数组。_matlab中ones函数

解决PS等软件出现应用程序无法正常启动(0xc000007b)_photoshop应用程序无法正常启动0xc000007b。请单击“确认”关闭应用程序。-程序员宅基地

文章浏览阅读3.9w次，点赞2次，收藏9次。　　在使用电脑办公过程中，安装应用程序时难免遇到无法安装或者无法正常启动的问题，这对我们使用电脑带来了诸多不便。那遇到应用程序无法正常启动的问题要如何解决呢?相信大家肯定都是十分疑问的，每次都是只能忍痛重新安装软件。今天，小编就和大家探讨下应用程序无法正常启动的解决方法，帮助大家排忧解难。0xc000007b电脑图解1　　第一种方案：SFC检查系统完整性来尝试修复丢失文件　　1、打开电脑搜索输入cmd.exe，选择以管理员身份运行，跳出提示框时选择继续。0xc000007b电脑图解2_photoshop应用程序无法正常启动0xc000007b。请单击“确认”关闭应用程序。

oracle介质恢复和实例恢复的异同-程序员宅基地

文章浏览阅读396次。1、概念 REDO LOG是Oracle为确保已经提交的事务不会丢失而建立的一个机制。实际上REDO LOG的存在是为两种场景准备的：实例恢复（INSTANCE RECOVERY）；介质恢复（MEDIA RECOVERY）。 实例恢复的目的是在数据库发生故障时，确保BUFFER CACHE中的数据不会丢失，不会造成数据库的..._oracle 实例恢复和介质恢复

随便推点

轻松搭建CAS 5.x系列(5)-增加密码找回和密码修改功能-程序员宅基地

文章浏览阅读418次。概述说明CAS内置了密码找回和密码修改的功能； 密码找回功能是，系统会吧密码重置的连接通过邮件或短信方式发送给用户，用户点击链接后就可以重置密码，cas还支持预留密码重置的问题，只有回答对了，才可以重置密码；系统可配置密码重置后，是否自动登录； 密码修改功能是，用户登录后输入新密码即可完成密码修改。安装步骤`1. 首先，搭建好cas sso server您需要按..._修改cas默认用户密码

springcloud(七) feign + Hystrix 整合 、-程序员宅基地

文章浏览阅读141次。之前几章演示的熔断，降级 都是 RestTemplate + Ribbon 和RestTemplate + Hystrix ，但是在实际开发并不是这样，实际开发中都是 Feign 远程接口调用。Feign + Hystrix 演示：　　eruka(略)order 服务工程：　 pom.xml<?xml version="1.0" encoding="U..._this is order 服务工程

YOLOv7如何提高目标检测的速度和精度，基于优化算法提高目标检测速度-程序员宅基地

文章浏览阅读3.4k次，点赞35次，收藏43次。学习率是影响目标检测精度和速度的重要因素之一。合适的学习率调度策略可以加速模型的收敛和提高模型的精度。在YOLOv7算法中，可以使用基于余弦函数的学习率调度策略（Cosine Annealing Learning Rate Schedule）来调整学习率。

linux中进程退出函数：exit()和_exit()的区别_linux结束进程可以用哪些函数,它们之间有何区别?-程序员宅基地

文章浏览阅读4k次，点赞4次，收藏9次。 linux中进程退出函数：exit()和_exit()的区别（1）_exit()执行后立即返回给内核，而exit()要先执行一些清除操作，然后将控制权交给内核。（2）调用_exit函数时，其会关闭进程所有的文件描述符，清理内存以及其他一些内核清理函数，但不会刷新流(stdin, stdout, stderr ...). exit函数是在_exit..._linux结束进程可以用哪些函数,它们之间有何区别?

sqlserver55555_sqlserver把小数点后面多余的0去掉-程序员宅基地

文章浏览阅读134次。select 5000/10000.0 --想变成0.5select 5500/10000.0 --想变成0.55select 5550/10000.0 --想变成0.555select 5555/10000.0 --想变成0.5555其结果分别为：0.5000000 0.5500000 0.5550000 0.5555000一、如果想去掉数字5后面多余的0 ，需要转化一下：selec..._sql server 去小数 0

Angular6 和 RXJS6 的一些改动_angular6,requestoptions改成了什么-程序员宅基地

文章浏览阅读3.1k次。例一：import { Injectable } from '@angular/core';import { Observable } from 'rxjs';import { User } from "./model/User";import { map } from 'rxjs/operators';import { Http, Response, Headers, RequestOp..._angular6,requestoptions改成了什么

推荐文章

热门文章

相关标签