PostgreSQL 11 preview - pgbench 压测软件增强



PostgreSQL , pgbench , 增强 , 11


PostgreSQL 11 pgbench压测软件增强。

详细用法参考, pgbench非常强大,且性能非常好。


《HTAP数据库 PostgreSQL 场景与性能测试之 45 - (OLTP) 数据量与性能的线性关系(10亿+无衰减), 暨单表多大需要分区》

《[未完待续] HTAP数据库 PostgreSQL 场景与性能测试之 44 - (OLTP) 空间应用 - 空间包含查询(输入多边形 包含 表内空间对象)》

《HTAP数据库 PostgreSQL 场景与性能测试之 43 - (OLTP+OLAP) unlogged table 含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 42 - (OLTP+OLAP) unlogged table 不含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 41 - (OLTP+OLAP) 含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 40 - (OLTP+OLAP) 不含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 39 - (OLTP+OLAP) 含索引多表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 38 - (OLTP+OLAP) 不含索引多表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 37 - (OLTP+OLAP) 含索引单表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 36 - (OLTP+OLAP) 不含索引单表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 35 - (OLTP+OLAP) 含索引单表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 34 - (OLTP+OLAP) 不含索引单表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 33 - (OLAP) 物联网 - 线性字段区间实时统计》

《HTAP数据库 PostgreSQL 场景与性能测试之 32 - (OLTP) 高吞吐数据进出(堆存、行扫、无需索引) - 阅后即焚(JSON + 函数流式计算)》

《HTAP数据库 PostgreSQL 场景与性能测试之 31 - (OLTP) 高吞吐数据进出(堆存、行扫、无需索引) - 阅后即焚(读写大吞吐并测)》

《HTAP数据库 PostgreSQL 场景与性能测试之 30 - (OLTP) 秒杀 - 高并发单点更新》

《HTAP数据库 PostgreSQL 场景与性能测试之 29 - (OLTP) 空间应用 - 高并发空间位置更新(含空间索引)》

《HTAP数据库 PostgreSQL 场景与性能测试之 28 - (OLTP) 高并发点更新》

《HTAP数据库 PostgreSQL 场景与性能测试之 27 - (OLTP) 物联网 - FEED日志, 流式处理 与 阅后即焚 (CTE)》

《HTAP数据库 PostgreSQL 场景与性能测试之 26 - (OLTP) NOT IN、NOT EXISTS 查询》

《HTAP数据库 PostgreSQL 场景与性能测试之 25 - (OLTP) IN , EXISTS 查询》

《HTAP数据库 PostgreSQL 场景与性能测试之 24 - (OLTP) 物联网 - 时序数据并发写入(含时序索引BRIN)》

《HTAP数据库 PostgreSQL 场景与性能测试之 23 - (OLAP) 并行计算》

《HTAP数据库 PostgreSQL 场景与性能测试之 22 - (OLTP) merge insert|upsert|insert on conflict|合并写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 21 - (OLTP+OLAP) 排序、建索引》

《HTAP数据库 PostgreSQL 场景与性能测试之 20 - (OLAP) 用户画像圈人场景 - 多个字段任意组合条件筛选与透视》

《HTAP数据库 PostgreSQL 场景与性能测试之 19 - (OLAP) 用户画像圈人场景 - 数组相交查询与聚合》

《HTAP数据库 PostgreSQL 场景与性能测试之 18 - (OLAP) 用户画像圈人场景 - 数组包含查询与聚合》

《HTAP数据库 PostgreSQL 场景与性能测试之 17 - (OLTP) 数组相似查询》

《HTAP数据库 PostgreSQL 场景与性能测试之 16 - (OLTP) 文本特征向量 - 相似特征(海明...)查询》

《HTAP数据库 PostgreSQL 场景与性能测试之 15 - (OLTP) 物联网 - 查询一个时序区间的数据》

《HTAP数据库 PostgreSQL 场景与性能测试之 14 - (OLTP) 字符串搜索 - 全文检索》

《HTAP数据库 PostgreSQL 场景与性能测试之 13 - (OLTP) 字符串搜索 - 相似查询》

《HTAP数据库 PostgreSQL 场景与性能测试之 12 - (OLTP) 字符串搜索 - 前后模糊查询》

《HTAP数据库 PostgreSQL 场景与性能测试之 11 - (OLTP) 字符串搜索 - 后缀查询》

《HTAP数据库 PostgreSQL 场景与性能测试之 10 - (OLTP) 字符串搜索 - 前缀查询》

《HTAP数据库 PostgreSQL 场景与性能测试之 9 - (OLTP) 字符串模糊查询 - 含索引实时写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 8 - (OLTP) 多值类型(数组)含索引实时写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 7 - (OLTP) 全文检索 - 含索引实时写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 6 - (OLTP) 空间应用 - KNN查询(搜索附近对象,由近到远排序输出)》

《HTAP数据库 PostgreSQL 场景与性能测试之 5 - (OLTP) 空间应用 - 空间包含查询(表内多边形 包含 输入空间对象)》

《HTAP数据库 PostgreSQL 场景与性能测试之 4 - (OLAP) 大表OUTER JOIN统计查询》

《HTAP数据库 PostgreSQL 场景与性能测试之 3 - (OLAP) 大表JOIN统计查询》

《HTAP数据库 PostgreSQL 场景与性能测试之 2 - (OLTP) 多表JOIN》

《HTAP数据库 PostgreSQL 场景与性能测试之 1 - (OLTP) 点查》

E. pgbench

  • Add pgbench expressions support for NULLs, booleans, and some functions and operators (Fabien Coelho)


\set varname expression  
    Sets variable varname to a value calculated from expression. 
   The expression may contain the NULL constant, boolean 
   constants TRUE and FALSE, integer constants such as 5432, 
   double constants such as 3.14159, references to  
    variables :variablename, operators with their usual SQL 
   precedence and associativity, function calls, SQL CASE 
   generic conditional expressions and parentheses.  

    Functions and most operators return NULL on NULL input.  

    For conditional purposes, non zero numerical values are 
   TRUE, zero numerical values and NULL are FALSE.  

    When no final ELSE clause is provided to a CASE, the default value is NULL.  


        \set ntellers 10 * :scale  
        \set aid (1021 * random(1, 100000 * :scale)) % \  
                   (100000 * :scale) + 1  
        \set divx CASE WHEN :x <> 0 THEN :y/:x ELSE NULL END  
  • Add \if conditional support to pgbench (Fabien Coelho)


\if expression  
\elif expression  
  This group of commands implements nestable conditional blocks, 
  similarly to psql's \if expression. Conditional expressions 
  are identical to those with \set, with non-zero values 
  interpreted as true.  


\if expression  
\elif expression  
  This group of commands implements nestable conditional blocks. 
  A conditional block must begin with an \if and end with an \endif. 
  In between there may be any number of \elif clauses, which 
  may optionally be followed by a  
  single \else clause. Ordinary queries and other types of backslash 
  commands may (and usually do) appear between the commands forming 
  a conditional block.  
  The \if and \elif commands read their argument(s) and evaluate 
  them as a boolean expression. If the expression yields true then 
  processing continues normally; otherwise, lines are skipped until 
  a matching \elif, \else,  
  or \endif is reached. Once an \if or \elif test has succeeded, 
  the arguments of later \elif commands in the same block are not 
  evaluated but are treated as false. Lines following an \else are 
  processed only if no earlier  
  matching \if or \elif succeeded.  
  The expression argument of an \if or \elif command is subject to 
  variable interpolation and backquote expansion, just like any 
  other backslash command argument. After that it is evaluated 
  like the value of an on/off  
  option variable. So a valid value is any unambiguous case-insensitive 
  match for one of: true, false, 1, 0, on, off, yes, no. 
  For example, t, T, and tR will all be considered to be true.  
  Expressions that do not properly evaluate to true or false will 
  generate a warning and be treated as false.  
  Lines being skipped are parsed normally to identify queries and 
  backslash commands, but queries are not sent to the server, 
  and backslash commands other than conditionals (\if, \elif, \else, \endif) are ignored.  
  Conditional commands are checked only for valid nesting.
  Variable references in skipped lines are not expanded, and backquote 
  expansion is not performed either.  
  All the backslash commands of a given conditional block must appear 
  in the same source file. If EOF is reached on the main input file 
  or an \include-ed file before all local \if-blocks have been closed, 
  then psql will  
  raise an error.  
  Here is an example:  
    -- check for the existence of two separate records in the database and store  
    -- the results in separate psql variables  
        EXISTS(SELECT 1 FROM customer WHERE customer_id = 123) as is_customer,  
        EXISTS(SELECT 1 FROM employee WHERE employee_id = 456) as is_employee  
    \if :is_customer  
        SELECT * FROM customer WHERE customer_id = 123;  
    \elif :is_employee  
        \echo 'is not a customer but is an employee'  
        SELECT * FROM employee WHERE employee_id = 456;  
        \if yes  
            \echo 'not a customer or employee'  
            \echo 'this will never print'  
  • Allow the use of non-ASCII characters in pgbench variable names (Fabien Coelho)

  • Add pgbench option --init-steps to control the initialization steps performed (Masahiko Sawada)


-I init_steps  
    Perform just a selected set of the normal initialization steps.  
   init_steps specifies the initialization steps to be performed, 
   using one character per step. Each step is invoked in the 
   specified order. The default is  
    dtgvp. The available steps are:  

    d (Drop)  
        Drop any existing pgbench tables.  

    t (create Tables)  
        Create the tables used by the standard pgbench scenario, 
       namely pgbench_accounts, pgbench_branches, pgbench_history, 
       and pgbench_tellers.  

    g (Generate data)  
        Generate data and load it into the standard tables, 
       replacing any data already present.  

    v (Vacuum)  
        Invoke VACUUM on the standard tables.  

    p (create Primary keys)  
        Create primary key indexes on the standard tables.  

    f (create Foreign keys)  
        Create foreign key constraints between the standard tables. (Note that this step is not performed by default.)  
  • Add approximated Zipfian-distributed random generator to pgbench (Alik Khilazhev)










早在上个世纪30年代,就有人(Zipf)对此作出了研究,并给出了量化的表达——齐普夫定律(Zipf's Law):一个词在一个有相当长度的语篇中的等级序号(该词在按出现次数排列的词表中的位置,他称之为rank,简称r)与该词的出现频率(他称为frequency,简称f)的乘积几乎是一个常数(constant,简称C)。用公式表示,就是 r × f = C 。

·   random_zipfian generates an approximated bounded zipfian 
distribution. For parameter in (0, 1), an approximated algorithm 
is taken from "Quickly Generating Billion-Record Synthetic Databases", 
Jim Gray et al, SIGMOD  
    1994. For parameter in (1, 1000), a rejection method is used, 
   based on "Non-Uniform Random Variate Generation", Luc Devroye, 
   p. 550-551, Springer 1986. The distribution is not defined 
   when the parameter's value is 1.0.  
    The drawing performance is poor for parameter values close 
   and above 1.0 and on a small range.  

    parameter defines how skewed the distribution is. The larger 
   the parameter, the more frequently values to the beginning 
   of the interval are drawn. The closer to 0 parameter is, 
   the flatter (more uniform) the access  

Hash functions hash, hash_murmur2 and hash_fnv1a accept an input 
value and an optional seed parameter. In case the seed isn't 
provided the value of :default_seed is used, which is initialized 
randomly unless set by the  
command-line -D option. Hash functions can be used to scatter the 
distribution of random functions such as random_zipfian or 
random_exponential. For instance, the following pgbench script 
simulates possible real world  
workload typical for social media and blogging platforms where 
few accounts generate excessive load:  

    \set r random_zipfian(0, 100000000, 1.07)  
  • Allow the random seed to be set in pgbench (Fabien Coelho)
  Set random generator seed. Seeds the system random number generator, 
  which then produces a sequence of initial generator states, one for 
  each thread. Values for SEED may be: time (the default, the seed is 
  based on the  
  current time), rand (use a strong random source, failing if none is 
  available), or an unsigned decimal integer value. The random generator 
  is invoked explicitly from a pgbench script (random...  functions) 
  or implicitly  
  (for instance option --rate uses it to schedule transactions). When 
  explicitly set, the value used for seeding is shown on the terminal. 
  Any value allowed for SEED may also be provided through the environment variable  
  PGBENCH_RANDOM_SEED. To ensure that the provided seed impacts all 
  possible uses, put this option first or use the environment variable.  
  Setting the seed explicitly allows to reproduce a pgbench run exactly, 
  as far as random numbers are concerned. As the random state is managed 
  per thread, this means the exact same pgbench run for an identical invocation  
  if there is one client per thread and there are no external or data 
  dependencies. From a statistical viewpoint reproducing runs exactly 
  is a bad idea because it can hide the performance variability or improve performance  
  unduly, e.g. by hitting the same pages as a previous run. However, 
  it may also be of great help for debugging, for instance re-running 
  a tricky case which leads to an error. Use wisely.  
  • Allow pgbench to do exponentiation with pow() and power() (Raúl Marín Rodríguez)


  • Add hashing functions to pgbench (Ildar Musin)
Hash functions hash, hash_murmur2 and hash_fnv1a accept an input value and an 
optional seed parameter. In case the seed isn't provided the value of :default_seed is 
used, which is initialized randomly unless set by the command-line -D option. 
Hash functions can be used to scatter the distribution of random functions such 
as random_zipfian or random_exponential. For instance, the following pgbench 
script simulates possible real world workload typical for social media and 
blogging platforms where few accounts generate excessive load:  

\set r random_zipfian(0, 100000000, 1.07)  
\set k abs(hash(:r)) % 1000000  
  • Make pgbench statistics more accurate when using --latency-limit and --rate (Fabien Coelho)


阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品
关系型数据库 分布式数据库 PolarDB
沉浸式学习PostgreSQL|PolarDB 15: 企业ERP软件、网站、分析型业务场景、营销场景人群圈选, 任意字段组合条件数据筛选
在企业ERP软件、网站中经常会有一些让用户输入筛选条件(或者勾选筛选条件)的地方, 一个页面可能出现若干个选项, 每个选项用户可以进行勾选或下拉框选择. 例如淘宝网, 发货地是哪里, 商品价格范围, 商品类目, 内存大小, .... 很多选项提供选择. 分析业务场景, 经常会使用大宽表来表示对象的特征, 每个字段代表一个特征维度, 然后通过各个字段的组合条件来进行数据的统计分析. 营销场景, 和前面分析场景类似, 通过各个字段的组合条件圈选目标用户. 通常一个选项代表一个对象的某属性, 用户可能根据任意组合条件进行筛选, 本实验目标学习如何快速在任意字段组合条件输入搜索到满足条件的数据.
394 0
供应链 Oracle 关系型数据库
SQL 关系型数据库 数据挖掘
PolarDB for PostgreSQL 开源必读手册-最佳场景实践与压测(下)
PolarDB for PostgreSQL 开源必读手册-最佳场景实践与压测
PolarDB for PostgreSQL 开源必读手册-最佳场景实践与压测(下)
SQL 移动开发 缓存
AnalyticDB for PostgreSQL TPC-B性能测试
1 前言 AnalyticDB for PostgreSQL 6.0 (简称ADBPG6.0)在事务处理性能上相比上个版本ADBPG4.3有了质的飞跃,本文将以TPC-B业界标准事务性能测试benchmark来展示ADBPG6.0在事务上的处理能力。
1632 0
AnalyticDB for PostgreSQL TPC-B性能测试
SQL 弹性计算 关系型数据库
HTAP数据库 PostgreSQL 场景与性能测试之 3.1 - (OLAP) 大表JOIN统计查询-10亿 join 1亿 agg
标签 PostgreSQL , HTAP , OLTP , OLAP , 场景与性能测试 背景 PostgreSQL是一个历史悠久的数据库,历史可以追溯到1973年,最早由2014计算机图灵奖得主,关系数据库的鼻祖Michael_Stonebraker 操刀设计,PostgreSQL具备与Oracle类似的功能、性能、架构以及稳定性。 PostgreSQL社区的贡献者众多
1613 0
数据采集 并行计算 Oracle
【点击订阅PostgreSQL技术周刊】 PostgreSQL(简称PG)的开发者们:云栖社区已有5000位PG开发者,发布了3000+PG文章(文章列表),沉淀了700+的PG精品问答(问答列表)。
11143 0
弹性计算 关系型数据库 测试技术
PostgreSQL 11 1万亿 tpcb 性能测试 on 阿里云ECS + ESSD + zfs/lvm2条带 + block_size=32K
标签 PostgreSQL , pgbench , tpcb , tpcc , tpch , lvm2 , zfs , 条带 背景 最近的几个PostgreSQL OLTP与OLAP的测试: 《PostgreSQL 11 tpcc 测试(103万tpmC on ECS) - use sysbe...
2998 0
弹性计算 关系型数据库 测试技术
PostgreSQL 11 1000亿 tpcb、1000W tpcc 性能测试 - on 阿里云ECS + ESSD (含quorum based 0丢失多副本配置与性能测试)
标签 PostgreSQL , pgbench , tpcb , tpcc , tpch 背景 阿里云ESSD提供了单盘32TB容量,100万IOPS,4GB/s读写吞吐的能力,单台ECS可以挂载16块ESSD盘,组成512 TB的大容量存储。
2576 0
弹性计算 关系型数据库 测试技术
PostgreSQL 11 100亿 tpcb 性能测试 on ECS
标签 PostgreSQL , tpcb , pgbench , 100亿 背景 PostgreSQL 11 发布在即,以下是tpcc与tpch测试的结果: 《PostgreSQL 11 tpcc 测试(103万tpmC on ECS) - use sysbench-tpcc by Percona-Lab》 《(TPC-H测试 SF=10,SF=200) PostgreSQL 11 vs 10 vs Deepgreen》 tpcb是pgbench标准测试CASE,本文使用与以上同样的测试机器(部署方式请参考以上),测试100亿tpch的性能。
1729 0
关系型数据库 测试技术 PostgreSQL
PostgreSQL 11 相似图像搜索插件 imgsmlr 性能测试与优化 3 - citus 8机128shard (4亿图像)
标签 PostgreSQL , imgsmlr , GiST , 图像特征值 , 小波转换 , citus 背景 续 《PostgreSQL 11 相似图像搜索插件 imgsmlr 性能测试 1 - 单机单表 (4亿图像)》 《PostgreSQL 11 相似图像搜索插件 imgsmlr 性能...
1409 0
云数据库 Redis 版
云数据库 MongoDB 版
云数据库 RDS