PostgreSQL APP海量FEED LOG实时质量统计CASE(含percentile_disc)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
简介:

标签

PostgreSQL , list 分区 , 分区表 , 实时消费 , 实时统计 , rotate 分区 , 流式统计


背景

服务质量监控是精细化质量管理的重要环节:

例如实时统计FEED LOG在某些分组在某些固定区间(分钟、5分钟、10分钟)的水位值,avg, sum, count等。

本文将介绍一个CASE,某个服务的实时质量监控。

多个点值以数组的形式,批量合并写入。例如2500个点,一批写入。

为了实现高效率的统计,以及FEED LOG明细的高效率清除,我们可以使用list分区表。

同时还需要用到intarray 插件来对数组的数值进行排序,以及使用aggs_for_arrays 插件来计算有序数组的水位值。

当然即使我们不使用这两个插件,使用PG的分位数聚合函数,也可以达到同样达到效果。

                                                       List of functions  
   Schema   |                 Name                 |  Result data type  |             Argument data types              |  Type    
------------+--------------------------------------+--------------------+----------------------------------------------+--------  
 pg_catalog | percent_rank                         | double precision   | VARIADIC "any" ORDER BY VARIADIC "any"       | agg  
 pg_catalog | percentile_cont                      | double precision   | double precision ORDER BY double precision   | agg  
 pg_catalog | percentile_cont                      | interval           | double precision ORDER BY interval           | agg  
 pg_catalog | percentile_cont                      | double precision[] | double precision[] ORDER BY double precision | agg  
 pg_catalog | percentile_cont                      | interval[]         | double precision[] ORDER BY interval         | agg  
 pg_catalog | percentile_disc                      | anyelement         | double precision ORDER BY anyelement         | agg  
 pg_catalog | percentile_disc                      | anyarray           | double precision[] ORDER BY anyelement       | agg  

《PostgreSQL aggregate function 4 : Hypothetical-Set Aggregate Functions》

《PostgreSQL aggregate function 3 : Aggregate Functions for Ordered-Set》

《PostgreSQL aggregate function 2 : Aggregate Functions for Statistics》

《PostgreSQL aggregate function 1 : General-Purpose Aggregate Functions》

DEMO

硬件环境:

ECS:

56Core
224G memory
2*本地ssd云盘

DB:

PostgreSQL

安装aggs_for_arrays 插件

wget http://api.pgxn.org/dist/aggs_for_arrays/1.3.1/aggs_for_arrays-1.3.1.zip  
  
unzip aggs_for_arrays-1.3.1.zip  
  
cd aggs_for_arrays-1.3.1  
  
  
export PGHOME=/home/digoal/pg11  
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH    
export PATH=$PGHOME/bin:$PATH:.    
  
USE_PGXS=1 make clean  
USE_PGXS=1 make   
USE_PGXS=1 make install  

加载插件

postgres=# create extension intarray ;  
CREATE EXTENSION  
postgres=# create extension aggs_for_arrays ;  
CREATE EXTENSION  

自定义数组聚合函数,将多个数组聚合为一维数组

PostgreSQL内置的array_agg聚合函数会将多个数组聚合为多维数组,而我们的目的是要将多个数组聚合为一维数组。所以需要自定义一个聚合函数。

create aggregate arragg (anyarray) (sfunc = array_cat, stype=anyarray, PARALLEL=safe);    
  
postgres=# select arragg(info) from (values(array[1,2,3]),(array[3,4,5])) t(info);  
    arragg       
---------------  
 {1,2,3,3,4,5}  
(1 row)  
  
postgres=# select arragg(info) from (values(array[1,2,3]),(array[2,3,4,5])) t(info);  
     arragg        
-----------------  
 {1,2,3,2,3,4,5}  
(1 row)  

聚合函数用到了array_cat,这个函数存在大量的MEMCOPY,所以涉及的量比较大时,性能不太乐观(相比较array_agg要差很多)。后面我们可以自定义一个性能更好的arragg。

定义生成随机数值数组的函数

create or replace function gen_randarr(  
  int,  -- 随机值取值范围上限  
  int   -- 生成个数  
) returns int[] as $$  
  select array(select (random()*$1)::int from generate_series(1,$2));  
$$ language sql strict;  
  

返回值范围100内的随机数,返回10个,样例如下:

postgres=# select gen_randarr(100,10);  
          gen_randarr             
--------------------------------  
 {72,6,26,44,47,84,88,72,59,40}  
(1 row)  

定义FEED LOG明细分区表

由于流式处理的数据为非关键数据,不需要持久化,所以可以选择unlogged table,性能会有很大提升

例子:

CREATE unlogged TABLE mx (      
    id   serial8 not null,      
    lt   timestamp not null,      
    gv   text,  
    rc   int,  
    v1   int[],  
    v2   int[],  
    v3   int[],  
    v4   int[],  
    v5   int[],  
    v6   int[],  
    v7   int[],  
    v8   int[],  
    v9   int[]  
) PARTITION BY LIST ( substring(extract('min' from lt)::text, 1, 1) );      
CREATE unlogged TABLE mx0 PARTITION OF mx FOR VALUES IN ('0');      
CREATE unlogged TABLE mx1 PARTITION OF mx FOR VALUES IN ('1');      
CREATE unlogged TABLE mx2 PARTITION OF mx FOR VALUES IN ('2');      
CREATE unlogged TABLE mx3 PARTITION OF mx FOR VALUES IN ('3');      
CREATE unlogged TABLE mx4 PARTITION OF mx FOR VALUES IN ('4');      
CREATE unlogged TABLE mx5 PARTITION OF mx FOR VALUES IN ('5');      
create index idx_mx_lt on mx(lt);  
drop table mx;  

由于FEED维度较多,所以每个FEED维度定义一个分区表,我们假设有1024个FEED维度,使用下面的方法,快速定义1024个分区表。

一次创建1024个分区表,每个分区表6个分区。

do language plpgsql $$  
declare  
begin  
  for i in 1..1024 loop  
    -- 创建主表  
    execute format(  
    '  
      CREATE unlogged TABLE mx%s (      
        id   serial8 not null,      
        lt   timestamp not null,      
        gv   text,  
        rc   int,  
        v1   int[],  
        v2   int[],  
        v3   int[],  
        v4   int[],  
        v5   int[],  
        v6   int[],  
        v7   int[],  
        v8   int[],  
        v9   int[]  
      ) PARTITION BY LIST ( substring(extract(''min'' from lt)::text, 1, 1) )  
    ', i);  
    for x in 0..5 loop  
      -- 创建分区  
      execute format('CREATE unlogged TABLE mx%s_%s PARTITION OF mx%s FOR VALUES IN (%L)', i, x, i, x);  
  
      -- PG 10的话,请在这里建索引  
      -- execute format('create index idx_mx%s_%s_lt on mx%s_%s(lt)', i, x, i, x);  
    end loop;  
      
    -- 创建索引(PG 11支持)  
    execute format('create index idx_mx%s_lt on mx%s(lt)', i, i);  
  end loop;  
end;  
$$;  

定义FEED明细表对应的统计分区表

例子

CREATE unlogged TABLE agg (      
    id   serial8 not null,      
    lt   timestamp not null,      
    tu   int2,  
    gv   text,  
    mid  int2,  
    _cnt int,  
    _sum int8,  
    avg  float8,  
    min  int,  
    max  int,  
    p90  int,  
    p95  int,  
    p99  int  
)  
PARTITION BY LIST ( (extract('hour' from lt)::text) );      
CREATE unlogged TABLE agg0 PARTITION OF agg FOR VALUES IN ('0');      
CREATE unlogged TABLE agg1 PARTITION OF agg FOR VALUES IN ('1');      
CREATE unlogged TABLE agg2 PARTITION OF agg FOR VALUES IN ('2');      
CREATE unlogged TABLE agg3 PARTITION OF agg FOR VALUES IN ('3');      
CREATE unlogged TABLE agg4 PARTITION OF agg FOR VALUES IN ('4');      
CREATE unlogged TABLE agg5 PARTITION OF agg FOR VALUES IN ('5');      
CREATE unlogged TABLE agg6 PARTITION OF agg FOR VALUES IN ('6');      
CREATE unlogged TABLE agg7 PARTITION OF agg FOR VALUES IN ('7');      
CREATE unlogged TABLE agg8 PARTITION OF agg FOR VALUES IN ('8');      
CREATE unlogged TABLE agg9 PARTITION OF agg FOR VALUES IN ('9');      
CREATE unlogged TABLE agg10 PARTITION OF agg FOR VALUES IN ('10');      
CREATE unlogged TABLE agg11 PARTITION OF agg FOR VALUES IN ('11');      
CREATE unlogged TABLE agg12 PARTITION OF agg FOR VALUES IN ('12');      
CREATE unlogged TABLE agg13 PARTITION OF agg FOR VALUES IN ('13');      
CREATE unlogged TABLE agg14 PARTITION OF agg FOR VALUES IN ('14');      
CREATE unlogged TABLE agg15 PARTITION OF agg FOR VALUES IN ('15');      
CREATE unlogged TABLE agg16 PARTITION OF agg FOR VALUES IN ('16');      
CREATE unlogged TABLE agg17 PARTITION OF agg FOR VALUES IN ('17');      
CREATE unlogged TABLE agg18 PARTITION OF agg FOR VALUES IN ('18');      
CREATE unlogged TABLE agg19 PARTITION OF agg FOR VALUES IN ('19');      
CREATE unlogged TABLE agg20 PARTITION OF agg FOR VALUES IN ('20');      
CREATE unlogged TABLE agg21 PARTITION OF agg FOR VALUES IN ('21');      
CREATE unlogged TABLE agg22 PARTITION OF agg FOR VALUES IN ('22');      
CREATE unlogged TABLE agg23 PARTITION OF agg FOR VALUES IN ('23');      
create index idx_agg_lt on agg(lt);  
drop table agg;  

一次创建1024个分区表,每个分区表24个分区。

do language plpgsql $$  
declare  
begin  
  for i in 1..1024 loop  
    -- 创建主表  
    execute format(  
    '  
      CREATE unlogged TABLE agg%s (      
          id   serial8 not null,      
          lt   timestamp not null,      
          tu   int2,  
          gv   text,  
          mid  int2,  
          _cnt int,  
          _sum int8,  
          avg  float8,  
          min  int,  
          max  int,  
          p90  int,  
          p95  int,  
          p99  int  
      )  
      PARTITION BY LIST ( (extract(''hour'' from lt)::text) )  
    ', i);  
    for x in 0..23 loop  
      -- 创建分区  
      execute format('CREATE unlogged TABLE agg%s_%s PARTITION OF agg%s FOR VALUES IN (%L)', i, x, i, x);  
  
      -- PG 10的话,请在这里建索引  
      -- execute format('create index idx_agg%s_%s_lt on agg%s_%s(lt)', i, x, i, x);  
    end loop;  
      
    -- 创建索引(PG 11支持)  
    execute format('create index idx_agg%s_lt on agg%s(lt)', i, i);  
  end loop;  
end;  
$$;  

明细+统计,总共30720张表。

维护数据时,TRUNCATE最早的分区即可,分区循环使用。

定义动态写入函数

为了方便压测,使用动态SQL写入数据,写入到对应的FEED明细表。

create or replace function ins_mx(  
  int,    -- suffix  
  text,   -- GV  
  int,    -- RC  
  int[],  -- v1  
  int[],  -- v2  
  int[],  -- v3  
  int[],  -- v4  
  int[],  -- v5  
  int[],  -- v6  
  int[],  -- v7  
  int[],  -- v8  
  int[]   -- v9  
) returns void as $$  
declare  
begin  
  execute format(  
  '  
    insert into mx%s   
      (lt,gv,rc,v1,v2,v3,v4,v5,v6,v7,v8,v9)  
    values  
      (now(),%L,%s,%L,%L,%L,%L,%L,%L,%L,%L,%L)  
  ',  
  $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12  
  );  
end;  
$$ language plpgsql strict;  

例子

select ins_mx(1,'a',10,gen_randarr(100,10),gen_randarr(100,10),gen_randarr(100,10),gen_randarr(100,10),gen_randarr(100,10),gen_randarr(100,10),gen_randarr(100,10),gen_randarr(100,10),gen_randarr(100,10));  
 ins_mx   
--------  
   
(1 row)  
  
postgres=# select * from mx1;  
 id |             lt             | gv | rc |              v1               |              v2               |               v3                |               v4               |               v5                |               v6             
     |              v7              |               v8               |               v9                 
----+----------------------------+----+----+-------------------------------+-------------------------------+---------------------------------+--------------------------------+---------------------------------+----------------------------  
-----+------------------------------+--------------------------------+--------------------------------  
  1 | 2018-02-06 16:14:10.874121 | a  | 10 | {17,12,33,2,73,67,73,12,87,8} | {99,34,70,82,98,9,53,73,52,8} | {96,32,41,72,98,66,14,68,99,41} | {75,16,53,8,19,26,75,92,38,61} | {99,36,95,70,18,94,79,71,67,31} | {79,62,63,20,35,60,86,49,29  
,85} | {89,4,1,42,12,20,68,86,11,6} | {48,10,42,43,80,60,37,59,31,4} | {90,10,66,52,30,0,12,15,49,41}  
(1 row)  

压测设计1

定义写入压测1

动态,写多表,数组数据为动态数据

1024个随机表,1500个随机分组,2个2500个元素的随机数组。

vi test1.sql  
  
\set suffix random(1,1024)  
\set gv random(1,1500)  
select ins_mx(:suffix,:gv,2500,gen_randarr(100,2500),gen_randarr(100,2500),'{}','{}','{}','{}','{}','{}','{}');  
pgbench -M prepared -n -r -P 1 -f ./test1.sql -c 56 -j 56 -T 120  
  
  
transaction type: ./test1.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 56  
number of threads: 56  
duration: 120 s  
number of transactions actually processed: 1475459  
latency average = 4.554 ms  
latency stddev = 1.226 ms  
tps = 12267.216576 (including connections establishing)  
tps = 12269.288221 (excluding connections establishing)  
statement latencies in milliseconds:  
         0.002  \set suffix random(1,1024)  
         0.001  \set gv random(1,1500)  
         4.556  select ins_mx(:suffix,:gv,2500,gen_randarr(100,2500),gen_randarr(100,2500),'{}','{}','{}','{}','{}','{}','{}');  
top - 17:53:50 up 26 days, 22:41,  3 users,  load average: 34.95, 12.67, 8.52  
Tasks: 540 total,  58 running, 482 sleeping,   0 stopped,   0 zombie  
%Cpu(s): 95.5 us,  4.1 sy,  0.0 ni,  0.3 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st  
KiB Mem : 23094336+total, 18962992+free,  5971540 used, 35341904 buff/cache  
KiB Swap:        0 total,        0 free,        0 used. 21054910+avail Mem   

定义写入压测2

动态,写多表,数组数据为静态数据

(因为gen_randarr算数据库的开销,实际上这个数组是应用程序传过来的,这里使用静态的数组可以避免数据库这个额外开销,性能更加真实)

vi test2.sql  
  
\set suffix random(1,1024)  
\set gv random(1,1500)  
select ins_mx(:suffix,:gv,2500,'{12,22,65,18,1,60,69,9,52,28,73,94,95,93,65,99,46,40,64,18,5,6,81,81,99,21,19,35,90,83,48,2,5,13,20,5,73,89,15,25,17,88,18,12,81,84,11,27,24,75,45,28,80,26,9,80,47,28,15,37,11,63,38,16,76,58,21,49,47,36,73,64,24,92,76,5,76,87,32,99,62,77,28,42,3,37,22,49,65,36,86,76,100,25,92,76,83,13,24,30,49,98,94,74,89,71,79,65,58,11,64,19,88,92,62,90,29,83,40,94,20,26,70,19,51,61,95,33,75,19,63,24,17,57,98,6,28,76,71,86,87,36,5,75,28,67,65,57,50,5,51,70,31,21,90,82,82,85,15,57,4,78,81,21,35,78,27,63,55,99,49,42,35,54,17,62,21,82,20,72,87,71,42,18,91,31,0,74,16,15,30,20,93,11,41,29,89,68,92,44,67,41,86,1,95,3,64,17,85,83,88,72,54,30,91,45,61,91,19,77,6,49,97,99,60,38,27,50,6,19,94,73,60,79,74,55,82,38,72,67,21,60,40,75,90,30,20,51,21,39,28,26,88,25,25,49,63,53,98,70,72,92,42,32,71,16,87,53,54,59,21,75,19,60,50,9,91,70,60,11,10,88,38,98,14,63,46,77,16,44,47,88,36,89,20,7,6,7,61,60,66,81,35,86,42,85,95,33,56,55,44,65,43,82,63,57,45,9,34,60,54,81,48,90,70,68,97,75,75,58,35,41,39,70,27,81,56,22,14,12,77,58,77,20,39,40,76,84,50,10,44,3,91,92,93,61,60,91,36,34,48,71,76,88,42,3,69,97,24,82,9,1,40,86,21,79,26,97,63,76,7,7,79,98,99,73,58,58,63,94,93,11,65,68,99,7,71,68,4,95,50,13,96,90,99,17,69,25,14,32,1,21,38,80,18,37,52,77,95,15,71,88,27,37,56,26,43,27,93,48,22,43,61,19,34,60,35,3,85,49,34,86,70,73,65,88,10,18,65,5,33,36,93,60,73,49,86,16,76,79,64,98,22,25,17,56,85,52,59,70,1,93,55,71,66,21,59,76,39,24,81,72,61,73,32,33,22,17,50,98,96,14,96,18,38,13,74,23,65,33,93,66,26,48,37,91,69,97,67,8,21,48,79,82,21,11,15,43,28,65,41,25,78,37,43,17,50,17,40,15,50,32,81,76,80,19,67,49,15,34,57,36,82,36,18,3,48,33,46,76,98,87,1,76,25,43,92,75,61,32,90,11,64,71,86,45,90,53,94,5,88,51,41,69,87,59,72,35,93,18,11,90,6,11,66,30,55,59,5,16,91,95,26,55,66,12,100,56,66,94,61,53,45,2,23,32,62,95,67,54,13,78,45,19,90,11,49,45,70,54,60,61,49,86,16,15,99,16,71,65,10,32,18,55,35,40,87,97,35,54,51,48,33,96,67,22,7,16,67,77,70,27,38,20,13,54,35,12,69,6,77,79,39,95,33,74,35,20,71,70,74,22,19,7,18,86,29,25,2,96,3,72,23,40,92,37,94,27,49,63,33,26,42,72,21,75,46,56,95,17,26,70,39,45,77,57,31,6,82,33,2,85,5,25,25,97,62,19,24,11,82,58,37,24,30,58,99,76,13,94,93,39,64,32,84,41,89,15,47,71,48,49,56,53,74,81,51,37,0,75,48,82,33,85,6,63,42,6,39,56,100,32,95,64,64,80,5,53,95,52,24,43,1,80,96,76,61,47,12,61,22,60,43,55,45,50,18,87,55,56,43,55,89,38,19,53,18,24,5,13,77,29,56,78,9,52,53,70,99,66,31,21,25,74,76,70,24,93,58,79,50,1,34,39,39,53,91,57,77,96,69,54,26,25,32,35,77,85,5,76,50,36,97,76,10,73,46,33,67,4,12,17,4,45,55,43,98,46,100,75,43,69,29,69,94,61,3,71,46,8,48,96,44,45,72,53,19,18,86,85,21,98,2,26,43,57,69,41,4,68,17,47,37,46,15,32,7,19,3,52,27,51,48,70,96,20,23,15,38,9,100,60,7,2,85,50,60,54,91,64,22,8,10,59,54,25,91,61,44,94,13,70,45,62,41,41,82,64,55,20,73,55,80,80,57,65,30,17,18,21,81,41,29,91,100,83,16,91,44,60,85,58,31,30,20,71,70,2,35,25,22,8,80,1,88,38,66,18,55,84,39,35,25,69,26,25,52,42,16,96,3,0,54,33,30,74,4,0,75,39,25,97,47,6,98,35,44,64,53,98,48,92,34,73,60,60,97,12,2,13,9,5,13,63,38,43,37,43,44,12,82,69,9,29,75,7,63,18,70,16,17,18,8,50,91,68,10,88,80,13,1,89,18,15,52,56,58,89,98,2,0,80,71,9,9,45,16,72,64,86,88,81,4,96,31,95,63,41,83,44,54,84,33,72,99,84,28,57,73,26,58,73,6,29,83,15,74,98,87,38,84,75,19,89,71,50,84,34,91,67,78,45,51,10,17,50,95,45,7,68,71,65,41,78,94,23,93,69,22,80,7,6,55,26,95,26,76,79,61,67,46,38,12,97,49,30,47,44,75,53,11,46,18,52,24,12,75,17,81,97,97,88,3,52,14,98,78,89,77,39,56,23,77,69,20,26,99,66,70,74,20,81,20,38,33,44,51,9,61,32,6,58,20,9,10,33,8,88,23,85,27,79,7,5,48,27,31,46,93,1,20,13,82,40,51,15,84,2,23,45,33,29,3,53,39,13,86,46,1,8,31,28,87,38,33,35,65,64,82,58,65,2,71,47,42,22,62,26,24,85,71,57,14,74,10,53,86,95,99,87,4,30,16,91,68,49,26,33,13,8,92,77,10,63,24,51,85,86,78,9,71,49,65,86,23,75,39,9,70,38,96,74,68,12,65,36,61,91,69,73,99,61,51,9,24,75,60,8,61,38,17,32,87,82,17,9,57,56,18,28,93,15,2,61,27,67,97,87,59,66,60,58,27,11,67,50,86,27,59,46,65,76,78,51,58,95,61,16,51,79,43,44,94,45,5,20,12,2,7,71,68,68,29,94,78,95,44,64,22,3,10,87,79,88,38,37,83,99,52,34,78,96,78,71,41,83,92,53,85,99,24,53,67,53,47,45,48,91,9,70,94,19,57,73,7,96,10,90,95,63,24,73,58,2,44,99,85,36,52,70,35,76,23,1,29,70,46,77,61,55,48,55,73,5,29,80,1,39,70,96,1,94,68,59,95,12,59,81,48,11,51,82,88,73,83,17,43,29,94,4,84,42,59,57,47,88,37,48,26,7,43,28,1,11,87,97,23,46,77,71,57,28,53,44,1,36,61,44,65,55,48,49,97,7,6,44,95,43,92,22,51,35,49,52,46,36,48,69,82,26,40,39,53,93,83,54,29,44,98,94,99,46,43,96,54,49,40,49,92,32,71,43,67,20,95,13,57,43,82,39,69,23,78,22,16,61,76,45,6,75,39,5,21,82,2,75,30,42,24,23,74,95,65,40,15,60,53,72,3,35,11,72,58,89,94,74,51,71,19,56,45,58,62,67,39,63,42,70,5,66,92,79,61,58,19,76,18,72,48,21,7,59,93,65,49,87,39,99,57,57,55,3,15,17,69,54,80,11,24,85,76,16,64,37,74,83,13,92,54,61,12,62,21,5,27,69,92,66,68,49,23,24,52,38,41,21,93,21,32,17,6,8,33,70,46,7,53,59,99,7,20,11,69,41,16,96,10,9,61,79,58,85,2,10,23,43,31,16,64,63,32,70,72,65,40,17,72,93,76,71,100,96,82,68,37,99,64,47,7,25,26,65,10,28,76,33,71,7,48,35,70,81,6,42,46,46,60,18,38,36,90,38,32,72,6,69,71,70,16,79,96,42,44,6,70,20,38,42,27,87,77,97,68,83,39,14,28,99,32,67,34,22,5,66,94,11,35,65,81,51,44,77,93,88,83,64,7,21,5,34,8,82,31,76,65,70,89,93,69,22,60,3,44,65,69,38,76,5,3,57,56,46,34,49,34,17,13,41,38,18,75,46,1,5,22,66,75,11,59,44,33,19,47,76,84,16,14,59,20,16,17,76,62,51,26,96,68,39,37,6,57,11,52,58,16,73,24,91,84,82,35,17,1,82,93,85,98,6,44,18,23,61,94,85,11,20,81,79,59,17,85,17,29,36,74,45,9,98,36,93,80,71,10,82,53,3,66,51,9,10,69,32,71,63,16,82,83,97,61,43,14,46,59,43,82,34,88,91,32,24,85,12,96,95,93,49,97,60,99,7,70,68,38,41,31,55,23,14,52,85,57,66,31,16,9,13,50,97,4,81,21,89,93,17,83,87,66,81,47,66,87,17,34,26,58,65,80,81,79,32,65,36,98,96,53,7,9,2,4,13,84,26,1,77,43,85,64,9,66,11,75,53,27,8,79,85,73,59,66,52,91,31,88,89,27,41,96,36,43,100,48,27,25,49,4,68,34,69,77,100,79,52,53,7,60,31,92,33,90,57,85,81,89,73,70,16,13,66,51,56,65,99,83,91,49,88,59,83,56,36,83,36,88,35,42,48,67,34,81,57,91,66,38,80,39,8,96,52,74,47,8,39,46,92,30,95,80,89,78,36,25,61,71,13,96,14,62,63,47,43,20,39,9,58,18,48,66,14,100,40,61,8,79,7,100,8,3,79,97,81,15,22,42,86,35,38,100,97,1,47,40,21,86,49,80,4,96,46,18,96,86,79,3,64,86,3,72,89,82,69,70,97,91,12,84,26,50,84,23,51,31,62,72,17,11,52,21,7,98,39,3,83,18,6,48,4,9,20,93,92,89,63,89,80,75,73,6,24,56,29,76,87,91,48,4,2,100,24,9,98,63,12,81,81,18,29,85,27,49,78,19,38,41,8,18,16,80,24,40,37,53,16,24,44,64,28,46,64,52,55,61,15,67,43,96,85,72,81,12,21,59,30,59,0,38,77,16,18,1,56,55,54,72,79,98,35,6,44,99,59,99,60,74,66,3,70,51,75,51,62,95,11,93,54,11,30,30,27,48,31,83,3,85,55,82,83,90,88,28,89,47,27,49,21,93,52,91,44,26,43,6,21,53,99,75,64,29,5,91,78,37,74,81,22,29,63,6,19,51,33,8,98,60,57,19,53,8,10,97,35,53,4,56,6,2,31,70,32,37,62,9,73,36,90,96,65,53,1,84,5,34,91,3,94,48,22,48,56,32,45,91,85,49,47,91,51,78,62,83,15,23,92,88,59,83,84,24,36,85,8,41,19,99,43,14,47,65,62,3,97,7,94,82,55,40,73,6,18,35,89,33,58,81,21,17,64,5,41,100,89,49,41,9,48,84,22,94,49,84,97,46,90,91,29,46,31,2,52,49,37,41,82,95,22,3,13,86,7,54,86,97,2,27,5,50,11,28,44,60,11,41,6,2,32,34,47,63,36,99,12,74,40,94,69,62,97,81,49,4,35,35,1,38,62,6,88,73,34,32,32,45,73,38,47,5,72,94,68,9,93,80,82,33,74,51,95,71,33,44,75,68,79,76,5,41,82,93,14,15,25,46,60,98,84,7,3,56,1,71,65,93,51,47,26,25,99,22,95,31,66}','{70,99,45,46,4,86,27,97,100,43,22,45,3,20,29,10,22,85,10,93,50,4,44,97,30,68,96,52,64,27,18,34,26,63,79,30,49,7,27,49,49,49,94,52,68,23,62,91,8,72,84,59,76,28,56,6,96,52,57,60,79,75,94,5,38,73,35,87,80,62,35,29,11,29,82,79,53,44,70,61,16,54,20,91,82,76,97,78,28,54,38,7,29,31,12,67,5,48,54,84,10,89,14,21,18,95,0,71,39,70,32,55,24,52,46,6,28,43,84,56,97,21,64,26,53,76,93,57,24,47,42,34,36,56,55,54,51,55,25,90,25,58,44,49,10,90,55,38,33,39,94,29,60,58,55,13,34,48,70,58,95,12,91,31,68,46,85,18,1,10,8,27,68,53,76,78,43,31,16,76,70,10,5,30,68,60,43,2,9,13,60,4,26,51,34,93,97,19,12,98,29,20,25,97,73,1,75,16,32,90,91,2,1,96,32,69,57,76,71,66,89,30,69,15,81,4,8,79,23,19,77,52,39,2,49,12,3,24,28,35,14,19,37,14,15,70,83,72,45,54,38,34,84,7,49,65,11,56,44,34,76,21,86,15,23,35,27,26,59,54,61,73,73,98,87,89,68,70,61,13,24,98,47,7,6,96,73,17,52,16,51,27,37,37,42,59,73,69,85,32,23,46,4,96,44,91,85,11,61,46,24,85,44,71,92,50,67,65,67,18,81,18,46,18,55,88,77,28,57,62,59,80,8,63,76,52,55,61,63,16,7,87,1,51,58,93,1,24,58,67,43,39,85,89,57,40,77,35,68,34,97,27,14,5,91,90,56,45,51,19,62,58,6,63,9,64,56,10,89,14,77,31,53,62,20,11,2,97,45,70,31,42,98,44,47,88,34,3,34,86,23,95,44,29,58,53,93,14,63,81,28,40,13,81,3,33,92,5,30,37,76,60,79,73,5,26,62,39,29,96,24,52,91,68,81,49,21,73,63,84,55,90,25,68,71,27,1,63,33,31,100,8,91,79,82,96,4,43,34,34,39,58,85,30,26,66,79,48,40,41,32,94,32,57,62,3,84,63,66,17,93,66,25,84,45,7,80,49,50,14,83,89,72,68,19,99,34,98,46,74,40,78,68,71,35,30,75,19,93,41,35,86,7,61,70,52,68,50,1,18,64,84,7,36,52,27,34,87,25,81,60,64,59,29,36,93,59,10,12,51,51,47,37,59,8,7,11,75,57,12,93,20,96,1,56,48,27,91,35,52,71,95,16,30,24,52,23,82,63,35,34,14,82,71,73,90,78,84,66,35,96,59,55,92,59,12,40,87,3,75,39,74,70,55,4,94,7,27,77,70,62,11,84,45,82,57,35,60,40,1,95,36,59,50,28,19,62,68,5,65,43,44,39,14,99,43,8,6,70,85,76,32,95,60,77,77,16,12,37,56,13,32,92,72,82,20,90,44,88,96,9,31,40,47,45,38,90,53,44,60,37,20,92,32,80,69,9,96,81,46,52,94,78,44,65,60,64,56,4,52,51,13,83,91,60,28,29,50,80,74,9,17,94,1,50,73,70,59,69,51,5,21,45,83,66,10,42,30,66,46,82,17,59,65,8,19,93,38,68,73,11,77,90,5,79,40,78,49,99,47,0,4,69,45,87,34,55,29,64,21,75,46,38,34,11,46,53,4,84,21,77,95,98,67,0,77,7,79,26,5,26,26,9,95,71,96,29,26,25,94,47,0,40,85,34,51,31,87,55,15,8,32,10,6,99,11,83,6,90,9,11,16,35,21,11,6,17,40,31,42,34,78,42,74,63,77,25,94,64,81,9,71,13,19,77,12,30,60,18,20,69,29,35,3,49,46,9,66,86,40,8,20,18,50,94,81,27,19,74,91,100,83,63,13,2,40,24,32,100,42,52,68,71,87,72,20,33,80,86,19,20,94,39,39,45,33,19,72,52,94,63,52,77,26,64,79,65,89,11,65,31,62,34,2,49,5,22,83,85,8,2,6,3,41,44,48,74,64,20,27,57,83,79,34,9,43,12,74,32,23,40,63,86,73,64,35,78,86,18,63,95,19,69,98,61,14,45,35,77,65,62,35,49,40,68,57,83,81,32,15,4,71,78,90,44,42,25,23,28,42,86,23,61,55,21,22,69,66,57,47,32,19,81,80,59,50,38,42,31,69,57,35,41,34,24,85,76,49,8,5,91,94,28,52,49,49,75,18,15,32,65,47,50,46,28,9,96,65,51,27,35,8,61,76,42,86,61,18,34,68,23,25,62,51,78,11,0,52,29,15,84,94,63,34,40,90,43,36,56,94,62,90,2,23,66,44,9,27,62,44,95,85,69,57,36,47,68,36,99,97,52,83,91,15,17,30,5,60,66,61,54,28,51,56,52,17,100,61,43,62,4,38,47,73,95,84,20,63,20,19,59,72,2,50,87,19,80,92,79,46,52,33,75,3,89,26,20,89,87,63,51,91,2,98,65,97,82,85,59,2,4,19,74,5,69,61,24,49,53,3,95,5,37,70,8,26,96,28,15,83,92,66,75,93,64,40,90,46,24,49,49,28,68,23,33,36,84,58,85,37,61,81,42,97,51,50,23,47,78,38,31,70,4,6,63,69,45,53,15,69,2,63,97,69,86,31,6,70,88,91,7,49,72,48,46,23,98,69,70,76,7,1,46,11,7,9,80,52,61,95,21,63,58,18,32,44,49,38,14,37,29,21,86,1,69,32,24,67,1,95,43,8,96,89,20,2,98,100,54,60,95,75,23,53,93,55,98,42,93,12,79,22,33,65,24,2,97,48,69,98,43,13,7,38,2,26,41,1,26,94,60,21,69,83,74,63,38,72,5,31,84,84,53,17,49,77,19,46,25,89,44,68,1,50,6,4,77,47,4,3,41,65,25,10,47,99,73,85,71,77,16,55,61,70,72,10,46,91,56,72,80,100,39,81,50,45,85,27,92,89,30,33,54,55,43,1,54,16,86,25,94,3,80,55,72,52,65,19,43,21,90,23,21,30,4,71,75,88,98,67,78,28,0,31,83,43,32,37,59,19,61,53,22,41,8,94,93,73,13,36,94,3,59,15,32,62,86,7,51,84,74,28,12,74,60,95,18,92,31,77,11,93,30,33,34,37,26,27,10,39,63,4,42,22,19,74,84,5,81,35,89,56,63,1,30,23,96,48,15,28,25,26,21,54,58,55,92,85,82,2,24,45,6,66,67,26,40,51,31,21,86,20,77,49,22,7,72,18,55,86,46,80,12,66,34,71,21,26,55,3,28,79,48,34,45,15,60,85,66,91,6,52,12,83,1,34,91,72,52,46,59,98,25,71,64,59,41,85,85,97,88,13,76,36,48,21,51,8,6,17,99,13,69,11,96,70,45,87,42,97,32,1,94,58,72,59,17,13,44,2,10,32,15,87,68,63,8,19,71,14,36,70,27,5,81,23,75,26,10,18,23,42,19,17,0,91,75,17,4,19,19,14,51,35,1,19,98,9,39,69,23,75,39,50,81,20,73,56,46,83,74,69,25,92,86,25,83,61,42,87,80,62,1,32,97,2,51,95,11,90,64,34,65,3,84,45,23,57,1,69,40,75,38,65,67,24,91,50,85,33,37,65,95,38,97,92,41,48,87,52,38,51,86,3,54,70,48,77,27,50,46,67,24,85,32,92,9,23,42,93,56,79,59,51,17,56,43,58,4,30,9,42,81,95,45,34,65,93,11,92,43,58,59,67,42,92,59,51,15,1,44,71,80,3,22,97,59,65,55,63,95,65,6,76,60,51,10,25,44,21,17,87,79,76,55,21,68,14,72,83,15,16,53,95,20,76,92,79,41,47,42,36,12,48,12,71,98,22,96,42,43,13,29,22,89,84,43,57,98,15,39,12,31,93,7,51,68,99,30,9,46,72,45,58,19,56,30,18,78,26,60,21,39,89,43,28,73,86,84,71,1,24,83,32,16,91,83,84,90,13,93,36,84,38,94,4,95,24,21,73,50,81,94,88,70,36,16,44,22,0,15,22,24,98,54,40,89,37,24,78,50,18,15,34,56,9,38,51,33,59,23,83,40,17,71,10,53,87,54,75,87,68,97,10,66,51,50,55,88,74,33,38,92,48,72,48,57,10,98,90,69,22,73,9,39,43,19,92,30,73,67,16,41,64,26,7,15,76,62,3,50,96,41,42,43,14,89,0,24,88,90,92,10,63,1,48,6,20,40,36,92,7,52,34,71,79,41,86,55,3,90,5,99,31,47,42,45,37,43,68,24,33,61,34,96,62,82,2,81,23,38,74,30,90,7,1,69,48,87,24,51,77,29,50,8,76,92,53,12,35,22,37,68,82,71,63,44,53,65,25,75,3,99,5,93,6,6,62,54,94,86,5,71,15,55,80,91,48,33,3,82,55,40,50,37,10,13,81,63,79,6,38,82,5,44,75,11,50,37,66,44,23,71,15,38,27,95,28,74,28,31,56,83,71,6,20,81,20,1,44,99,8,82,80,13,26,55,24,75,93,90,19,15,61,34,53,87,29,81,62,57,12,18,39,83,24,59,64,44,60,7,43,68,90,23,81,15,79,5,91,71,95,10,87,56,44,39,43,73,20,5,30,32,23,69,15,47,29,79,92,89,86,34,56,76,58,37,91,36,42,82,7,36,92,94,92,36,33,35,9,54,40,39,86,63,9,2,10,37,80,2,26,67,36,82,43,94,19,34,30,61,15,37,97,7,31,89,43,65,25,52,19,65,92,5,27,1,6,38,38,87,39,64,54,75,47,96,69,66,30,99,27,46,37,24,53,68,14,96,33,38,48,51,3,40,56,31,40,63,68,78,50,7,43,3,83,89,100,52,55,30,51,83,75,87,7,28,55,21,24,88,59,71,39,62,11,96,93,51,59,61,30,8,68,72,11,51,62,11,2,17,41,53,100,16,40,7,44,96,27,67,84,86,39,23,49,50,19,41,1,78,2,31,86,70,3,97,20,65,8,22,82,48,75,82,64,16,89,8,12,16,75,95,2,14,19,51,64,38,92,65,15,94,96,1,64,99,98,84,65,6,6,47,54,82,29,19,98,18,27,9,34,2,5,36,17,24,87,81,61,79,46,77,73,42,78,36,41,76,20,6,81,26,53,36,8,82,54,6,99,81,15,33,83,20,69,100,44,55,80,5,34,26,82,7,68,59,43,9,35,63,15,16,90,68,52,98,50,6,4,49,87,19,82,70,40,51,70,83,6,50,88,40,76,70,47,45,29,90,54,64,53,69,80,43,37,32,41,87,38,45,36,24,65,18,95,4,69,65,88,74,15,76,15,91,46,62,36,76,52,90,40,6,59,20,49,97,51,90,84,89,35,20,14,100,38,8,4,7,73,92,81,88,68,96,79,14,58,15,90,10,5,29,15,64,49,64,61,0,54,45,89,89,65,3,89,3,11,92,10,84,84,91,72,52,87,51,66,45,66,56,55,71,85,70,35,34,34,96,34,88,41,23,76,7,26,65,10,37,57,20,21,42,12,93,94,99,43,60,44,9,15,99,80,100,69,15,33,3,11,67,90,52,90,67,59,16,32,69,53,89,89,74,31,1,67,24,100,10,84,44,19,99,43,99,99,12,14,32,14}','{}','{}','{}','{}','{}','{}','{}');  
pgbench -M prepared -n -r -P 1 -f ./test2.sql -c 56 -j 56 -T 120  
  
  
transaction type: ./test2.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 56  
number of threads: 56  
duration: 120 s  
number of transactions actually processed: 2404622  
latency average = 2.794 ms  
latency stddev = 2.429 ms  
tps = 19903.967701 (including connections establishing)  
tps = 19904.929587 (excluding connections establishing)  
statement latencies in milliseconds:  
         0.002  \set suffix random(1,1024)  
         0.001  \set gv random(1,1500)  
         2.795  select ins_mx(:suffix,:gv,2500,'{12,2........................  
top - 18:02:30 up 26 days, 22:50,  3 users,  load average: 16.13, 21.10, 15.97  
Tasks: 546 total,  60 running, 486 sleeping,   0 stopped,   0 zombie  
%Cpu(s): 92.5 us,  3.8 sy,  0.0 ni,  3.5 id,  0.1 wa,  0.0 hi,  0.0 si,  0.0 st  
KiB Mem : 23094336+total, 10642340+free,  8362460 used, 11615750+buff/cache  
KiB Swap:        0 total,        0 free,        0 used. 17643998+avail Mem   

定义写入压测3

静态,写单表,数组数据为静态数据

vi test3.sql  
  
\set gv random(1,1500)  
insert into mx1 (lt,gv,rc,v1,v2,v3,v4,v5,v6,v7,v8,v9) values (now(),:gv,2500,'{12,22,65,18,1,60,69,9,52,28,73,94,95,93,65,99,46,40,64,18,5,6,81,81,99,21,19,35,90,83,48,2,5,13,20,5,73,89,15,25,17,88,18,12,81,84,11,27,24,75,45,28,80,26,9,80,47,28,15,37,11,63,38,16,76,58,21,49,47,36,73,64,24,92,76,5,76,87,32,99,62,77,28,42,3,37,22,49,65,36,86,76,100,25,92,76,83,13,24,30,49,98,94,74,89,71,79,65,58,11,64,19,88,92,62,90,29,83,40,94,20,26,70,19,51,61,95,33,75,19,63,24,17,57,98,6,28,76,71,86,87,36,5,75,28,67,65,57,50,5,51,70,31,21,90,82,82,85,15,57,4,78,81,21,35,78,27,63,55,99,49,42,35,54,17,62,21,82,20,72,87,71,42,18,91,31,0,74,16,15,30,20,93,11,41,29,89,68,92,44,67,41,86,1,95,3,64,17,85,83,88,72,54,30,91,45,61,91,19,77,6,49,97,99,60,38,27,50,6,19,94,73,60,79,74,55,82,38,72,67,21,60,40,75,90,30,20,51,21,39,28,26,88,25,25,49,63,53,98,70,72,92,42,32,71,16,87,53,54,59,21,75,19,60,50,9,91,70,60,11,10,88,38,98,14,63,46,77,16,44,47,88,36,89,20,7,6,7,61,60,66,81,35,86,42,85,95,33,56,55,44,65,43,82,63,57,45,9,34,60,54,81,48,90,70,68,97,75,75,58,35,41,39,70,27,81,56,22,14,12,77,58,77,20,39,40,76,84,50,10,44,3,91,92,93,61,60,91,36,34,48,71,76,88,42,3,69,97,24,82,9,1,40,86,21,79,26,97,63,76,7,7,79,98,99,73,58,58,63,94,93,11,65,68,99,7,71,68,4,95,50,13,96,90,99,17,69,25,14,32,1,21,38,80,18,37,52,77,95,15,71,88,27,37,56,26,43,27,93,48,22,43,61,19,34,60,35,3,85,49,34,86,70,73,65,88,10,18,65,5,33,36,93,60,73,49,86,16,76,79,64,98,22,25,17,56,85,52,59,70,1,93,55,71,66,21,59,76,39,24,81,72,61,73,32,33,22,17,50,98,96,14,96,18,38,13,74,23,65,33,93,66,26,48,37,91,69,97,67,8,21,48,79,82,21,11,15,43,28,65,41,25,78,37,43,17,50,17,40,15,50,32,81,76,80,19,67,49,15,34,57,36,82,36,18,3,48,33,46,76,98,87,1,76,25,43,92,75,61,32,90,11,64,71,86,45,90,53,94,5,88,51,41,69,87,59,72,35,93,18,11,90,6,11,66,30,55,59,5,16,91,95,26,55,66,12,100,56,66,94,61,53,45,2,23,32,62,95,67,54,13,78,45,19,90,11,49,45,70,54,60,61,49,86,16,15,99,16,71,65,10,32,18,55,35,40,87,97,35,54,51,48,33,96,67,22,7,16,67,77,70,27,38,20,13,54,35,12,69,6,77,79,39,95,33,74,35,20,71,70,74,22,19,7,18,86,29,25,2,96,3,72,23,40,92,37,94,27,49,63,33,26,42,72,21,75,46,56,95,17,26,70,39,45,77,57,31,6,82,33,2,85,5,25,25,97,62,19,24,11,82,58,37,24,30,58,99,76,13,94,93,39,64,32,84,41,89,15,47,71,48,49,56,53,74,81,51,37,0,75,48,82,33,85,6,63,42,6,39,56,100,32,95,64,64,80,5,53,95,52,24,43,1,80,96,76,61,47,12,61,22,60,43,55,45,50,18,87,55,56,43,55,89,38,19,53,18,24,5,13,77,29,56,78,9,52,53,70,99,66,31,21,25,74,76,70,24,93,58,79,50,1,34,39,39,53,91,57,77,96,69,54,26,25,32,35,77,85,5,76,50,36,97,76,10,73,46,33,67,4,12,17,4,45,55,43,98,46,100,75,43,69,29,69,94,61,3,71,46,8,48,96,44,45,72,53,19,18,86,85,21,98,2,26,43,57,69,41,4,68,17,47,37,46,15,32,7,19,3,52,27,51,48,70,96,20,23,15,38,9,100,60,7,2,85,50,60,54,91,64,22,8,10,59,54,25,91,61,44,94,13,70,45,62,41,41,82,64,55,20,73,55,80,80,57,65,30,17,18,21,81,41,29,91,100,83,16,91,44,60,85,58,31,30,20,71,70,2,35,25,22,8,80,1,88,38,66,18,55,84,39,35,25,69,26,25,52,42,16,96,3,0,54,33,30,74,4,0,75,39,25,97,47,6,98,35,44,64,53,98,48,92,34,73,60,60,97,12,2,13,9,5,13,63,38,43,37,43,44,12,82,69,9,29,75,7,63,18,70,16,17,18,8,50,91,68,10,88,80,13,1,89,18,15,52,56,58,89,98,2,0,80,71,9,9,45,16,72,64,86,88,81,4,96,31,95,63,41,83,44,54,84,33,72,99,84,28,57,73,26,58,73,6,29,83,15,74,98,87,38,84,75,19,89,71,50,84,34,91,67,78,45,51,10,17,50,95,45,7,68,71,65,41,78,94,23,93,69,22,80,7,6,55,26,95,26,76,79,61,67,46,38,12,97,49,30,47,44,75,53,11,46,18,52,24,12,75,17,81,97,97,88,3,52,14,98,78,89,77,39,56,23,77,69,20,26,99,66,70,74,20,81,20,38,33,44,51,9,61,32,6,58,20,9,10,33,8,88,23,85,27,79,7,5,48,27,31,46,93,1,20,13,82,40,51,15,84,2,23,45,33,29,3,53,39,13,86,46,1,8,31,28,87,38,33,35,65,64,82,58,65,2,71,47,42,22,62,26,24,85,71,57,14,74,10,53,86,95,99,87,4,30,16,91,68,49,26,33,13,8,92,77,10,63,24,51,85,86,78,9,71,49,65,86,23,75,39,9,70,38,96,74,68,12,65,36,61,91,69,73,99,61,51,9,24,75,60,8,61,38,17,32,87,82,17,9,57,56,18,28,93,15,2,61,27,67,97,87,59,66,60,58,27,11,67,50,86,27,59,46,65,76,78,51,58,95,61,16,51,79,43,44,94,45,5,20,12,2,7,71,68,68,29,94,78,95,44,64,22,3,10,87,79,88,38,37,83,99,52,34,78,96,78,71,41,83,92,53,85,99,24,53,67,53,47,45,48,91,9,70,94,19,57,73,7,96,10,90,95,63,24,73,58,2,44,99,85,36,52,70,35,76,23,1,29,70,46,77,61,55,48,55,73,5,29,80,1,39,70,96,1,94,68,59,95,12,59,81,48,11,51,82,88,73,83,17,43,29,94,4,84,42,59,57,47,88,37,48,26,7,43,28,1,11,87,97,23,46,77,71,57,28,53,44,1,36,61,44,65,55,48,49,97,7,6,44,95,43,92,22,51,35,49,52,46,36,48,69,82,26,40,39,53,93,83,54,29,44,98,94,99,46,43,96,54,49,40,49,92,32,71,43,67,20,95,13,57,43,82,39,69,23,78,22,16,61,76,45,6,75,39,5,21,82,2,75,30,42,24,23,74,95,65,40,15,60,53,72,3,35,11,72,58,89,94,74,51,71,19,56,45,58,62,67,39,63,42,70,5,66,92,79,61,58,19,76,18,72,48,21,7,59,93,65,49,87,39,99,57,57,55,3,15,17,69,54,80,11,24,85,76,16,64,37,74,83,13,92,54,61,12,62,21,5,27,69,92,66,68,49,23,24,52,38,41,21,93,21,32,17,6,8,33,70,46,7,53,59,99,7,20,11,69,41,16,96,10,9,61,79,58,85,2,10,23,43,31,16,64,63,32,70,72,65,40,17,72,93,76,71,100,96,82,68,37,99,64,47,7,25,26,65,10,28,76,33,71,7,48,35,70,81,6,42,46,46,60,18,38,36,90,38,32,72,6,69,71,70,16,79,96,42,44,6,70,20,38,42,27,87,77,97,68,83,39,14,28,99,32,67,34,22,5,66,94,11,35,65,81,51,44,77,93,88,83,64,7,21,5,34,8,82,31,76,65,70,89,93,69,22,60,3,44,65,69,38,76,5,3,57,56,46,34,49,34,17,13,41,38,18,75,46,1,5,22,66,75,11,59,44,33,19,47,76,84,16,14,59,20,16,17,76,62,51,26,96,68,39,37,6,57,11,52,58,16,73,24,91,84,82,35,17,1,82,93,85,98,6,44,18,23,61,94,85,11,20,81,79,59,17,85,17,29,36,74,45,9,98,36,93,80,71,10,82,53,3,66,51,9,10,69,32,71,63,16,82,83,97,61,43,14,46,59,43,82,34,88,91,32,24,85,12,96,95,93,49,97,60,99,7,70,68,38,41,31,55,23,14,52,85,57,66,31,16,9,13,50,97,4,81,21,89,93,17,83,87,66,81,47,66,87,17,34,26,58,65,80,81,79,32,65,36,98,96,53,7,9,2,4,13,84,26,1,77,43,85,64,9,66,11,75,53,27,8,79,85,73,59,66,52,91,31,88,89,27,41,96,36,43,100,48,27,25,49,4,68,34,69,77,100,79,52,53,7,60,31,92,33,90,57,85,81,89,73,70,16,13,66,51,56,65,99,83,91,49,88,59,83,56,36,83,36,88,35,42,48,67,34,81,57,91,66,38,80,39,8,96,52,74,47,8,39,46,92,30,95,80,89,78,36,25,61,71,13,96,14,62,63,47,43,20,39,9,58,18,48,66,14,100,40,61,8,79,7,100,8,3,79,97,81,15,22,42,86,35,38,100,97,1,47,40,21,86,49,80,4,96,46,18,96,86,79,3,64,86,3,72,89,82,69,70,97,91,12,84,26,50,84,23,51,31,62,72,17,11,52,21,7,98,39,3,83,18,6,48,4,9,20,93,92,89,63,89,80,75,73,6,24,56,29,76,87,91,48,4,2,100,24,9,98,63,12,81,81,18,29,85,27,49,78,19,38,41,8,18,16,80,24,40,37,53,16,24,44,64,28,46,64,52,55,61,15,67,43,96,85,72,81,12,21,59,30,59,0,38,77,16,18,1,56,55,54,72,79,98,35,6,44,99,59,99,60,74,66,3,70,51,75,51,62,95,11,93,54,11,30,30,27,48,31,83,3,85,55,82,83,90,88,28,89,47,27,49,21,93,52,91,44,26,43,6,21,53,99,75,64,29,5,91,78,37,74,81,22,29,63,6,19,51,33,8,98,60,57,19,53,8,10,97,35,53,4,56,6,2,31,70,32,37,62,9,73,36,90,96,65,53,1,84,5,34,91,3,94,48,22,48,56,32,45,91,85,49,47,91,51,78,62,83,15,23,92,88,59,83,84,24,36,85,8,41,19,99,43,14,47,65,62,3,97,7,94,82,55,40,73,6,18,35,89,33,58,81,21,17,64,5,41,100,89,49,41,9,48,84,22,94,49,84,97,46,90,91,29,46,31,2,52,49,37,41,82,95,22,3,13,86,7,54,86,97,2,27,5,50,11,28,44,60,11,41,6,2,32,34,47,63,36,99,12,74,40,94,69,62,97,81,49,4,35,35,1,38,62,6,88,73,34,32,32,45,73,38,47,5,72,94,68,9,93,80,82,33,74,51,95,71,33,44,75,68,79,76,5,41,82,93,14,15,25,46,60,98,84,7,3,56,1,71,65,93,51,47,26,25,99,22,95,31,66}','{70,99,45,46,4,86,27,97,100,43,22,45,3,20,29,10,22,85,10,93,50,4,44,97,30,68,96,52,64,27,18,34,26,63,79,30,49,7,27,49,49,49,94,52,68,23,62,91,8,72,84,59,76,28,56,6,96,52,57,60,79,75,94,5,38,73,35,87,80,62,35,29,11,29,82,79,53,44,70,61,16,54,20,91,82,76,97,78,28,54,38,7,29,31,12,67,5,48,54,84,10,89,14,21,18,95,0,71,39,70,32,55,24,52,46,6,28,43,84,56,97,21,64,26,53,76,93,57,24,47,42,34,36,56,55,54,51,55,25,90,25,58,44,49,10,90,55,38,33,39,94,29,60,58,55,13,34,48,70,58,95,12,91,31,68,46,85,18,1,10,8,27,68,53,76,78,43,31,16,76,70,10,5,30,68,60,43,2,9,13,60,4,26,51,34,93,97,19,12,98,29,20,25,97,73,1,75,16,32,90,91,2,1,96,32,69,57,76,71,66,89,30,69,15,81,4,8,79,23,19,77,52,39,2,49,12,3,24,28,35,14,19,37,14,15,70,83,72,45,54,38,34,84,7,49,65,11,56,44,34,76,21,86,15,23,35,27,26,59,54,61,73,73,98,87,89,68,70,61,13,24,98,47,7,6,96,73,17,52,16,51,27,37,37,42,59,73,69,85,32,23,46,4,96,44,91,85,11,61,46,24,85,44,71,92,50,67,65,67,18,81,18,46,18,55,88,77,28,57,62,59,80,8,63,76,52,55,61,63,16,7,87,1,51,58,93,1,24,58,67,43,39,85,89,57,40,77,35,68,34,97,27,14,5,91,90,56,45,51,19,62,58,6,63,9,64,56,10,89,14,77,31,53,62,20,11,2,97,45,70,31,42,98,44,47,88,34,3,34,86,23,95,44,29,58,53,93,14,63,81,28,40,13,81,3,33,92,5,30,37,76,60,79,73,5,26,62,39,29,96,24,52,91,68,81,49,21,73,63,84,55,90,25,68,71,27,1,63,33,31,100,8,91,79,82,96,4,43,34,34,39,58,85,30,26,66,79,48,40,41,32,94,32,57,62,3,84,63,66,17,93,66,25,84,45,7,80,49,50,14,83,89,72,68,19,99,34,98,46,74,40,78,68,71,35,30,75,19,93,41,35,86,7,61,70,52,68,50,1,18,64,84,7,36,52,27,34,87,25,81,60,64,59,29,36,93,59,10,12,51,51,47,37,59,8,7,11,75,57,12,93,20,96,1,56,48,27,91,35,52,71,95,16,30,24,52,23,82,63,35,34,14,82,71,73,90,78,84,66,35,96,59,55,92,59,12,40,87,3,75,39,74,70,55,4,94,7,27,77,70,62,11,84,45,82,57,35,60,40,1,95,36,59,50,28,19,62,68,5,65,43,44,39,14,99,43,8,6,70,85,76,32,95,60,77,77,16,12,37,56,13,32,92,72,82,20,90,44,88,96,9,31,40,47,45,38,90,53,44,60,37,20,92,32,80,69,9,96,81,46,52,94,78,44,65,60,64,56,4,52,51,13,83,91,60,28,29,50,80,74,9,17,94,1,50,73,70,59,69,51,5,21,45,83,66,10,42,30,66,46,82,17,59,65,8,19,93,38,68,73,11,77,90,5,79,40,78,49,99,47,0,4,69,45,87,34,55,29,64,21,75,46,38,34,11,46,53,4,84,21,77,95,98,67,0,77,7,79,26,5,26,26,9,95,71,96,29,26,25,94,47,0,40,85,34,51,31,87,55,15,8,32,10,6,99,11,83,6,90,9,11,16,35,21,11,6,17,40,31,42,34,78,42,74,63,77,25,94,64,81,9,71,13,19,77,12,30,60,18,20,69,29,35,3,49,46,9,66,86,40,8,20,18,50,94,81,27,19,74,91,100,83,63,13,2,40,24,32,100,42,52,68,71,87,72,20,33,80,86,19,20,94,39,39,45,33,19,72,52,94,63,52,77,26,64,79,65,89,11,65,31,62,34,2,49,5,22,83,85,8,2,6,3,41,44,48,74,64,20,27,57,83,79,34,9,43,12,74,32,23,40,63,86,73,64,35,78,86,18,63,95,19,69,98,61,14,45,35,77,65,62,35,49,40,68,57,83,81,32,15,4,71,78,90,44,42,25,23,28,42,86,23,61,55,21,22,69,66,57,47,32,19,81,80,59,50,38,42,31,69,57,35,41,34,24,85,76,49,8,5,91,94,28,52,49,49,75,18,15,32,65,47,50,46,28,9,96,65,51,27,35,8,61,76,42,86,61,18,34,68,23,25,62,51,78,11,0,52,29,15,84,94,63,34,40,90,43,36,56,94,62,90,2,23,66,44,9,27,62,44,95,85,69,57,36,47,68,36,99,97,52,83,91,15,17,30,5,60,66,61,54,28,51,56,52,17,100,61,43,62,4,38,47,73,95,84,20,63,20,19,59,72,2,50,87,19,80,92,79,46,52,33,75,3,89,26,20,89,87,63,51,91,2,98,65,97,82,85,59,2,4,19,74,5,69,61,24,49,53,3,95,5,37,70,8,26,96,28,15,83,92,66,75,93,64,40,90,46,24,49,49,28,68,23,33,36,84,58,85,37,61,81,42,97,51,50,23,47,78,38,31,70,4,6,63,69,45,53,15,69,2,63,97,69,86,31,6,70,88,91,7,49,72,48,46,23,98,69,70,76,7,1,46,11,7,9,80,52,61,95,21,63,58,18,32,44,49,38,14,37,29,21,86,1,69,32,24,67,1,95,43,8,96,89,20,2,98,100,54,60,95,75,23,53,93,55,98,42,93,12,79,22,33,65,24,2,97,48,69,98,43,13,7,38,2,26,41,1,26,94,60,21,69,83,74,63,38,72,5,31,84,84,53,17,49,77,19,46,25,89,44,68,1,50,6,4,77,47,4,3,41,65,25,10,47,99,73,85,71,77,16,55,61,70,72,10,46,91,56,72,80,100,39,81,50,45,85,27,92,89,30,33,54,55,43,1,54,16,86,25,94,3,80,55,72,52,65,19,43,21,90,23,21,30,4,71,75,88,98,67,78,28,0,31,83,43,32,37,59,19,61,53,22,41,8,94,93,73,13,36,94,3,59,15,32,62,86,7,51,84,74,28,12,74,60,95,18,92,31,77,11,93,30,33,34,37,26,27,10,39,63,4,42,22,19,74,84,5,81,35,89,56,63,1,30,23,96,48,15,28,25,26,21,54,58,55,92,85,82,2,24,45,6,66,67,26,40,51,31,21,86,20,77,49,22,7,72,18,55,86,46,80,12,66,34,71,21,26,55,3,28,79,48,34,45,15,60,85,66,91,6,52,12,83,1,34,91,72,52,46,59,98,25,71,64,59,41,85,85,97,88,13,76,36,48,21,51,8,6,17,99,13,69,11,96,70,45,87,42,97,32,1,94,58,72,59,17,13,44,2,10,32,15,87,68,63,8,19,71,14,36,70,27,5,81,23,75,26,10,18,23,42,19,17,0,91,75,17,4,19,19,14,51,35,1,19,98,9,39,69,23,75,39,50,81,20,73,56,46,83,74,69,25,92,86,25,83,61,42,87,80,62,1,32,97,2,51,95,11,90,64,34,65,3,84,45,23,57,1,69,40,75,38,65,67,24,91,50,85,33,37,65,95,38,97,92,41,48,87,52,38,51,86,3,54,70,48,77,27,50,46,67,24,85,32,92,9,23,42,93,56,79,59,51,17,56,43,58,4,30,9,42,81,95,45,34,65,93,11,92,43,58,59,67,42,92,59,51,15,1,44,71,80,3,22,97,59,65,55,63,95,65,6,76,60,51,10,25,44,21,17,87,79,76,55,21,68,14,72,83,15,16,53,95,20,76,92,79,41,47,42,36,12,48,12,71,98,22,96,42,43,13,29,22,89,84,43,57,98,15,39,12,31,93,7,51,68,99,30,9,46,72,45,58,19,56,30,18,78,26,60,21,39,89,43,28,73,86,84,71,1,24,83,32,16,91,83,84,90,13,93,36,84,38,94,4,95,24,21,73,50,81,94,88,70,36,16,44,22,0,15,22,24,98,54,40,89,37,24,78,50,18,15,34,56,9,38,51,33,59,23,83,40,17,71,10,53,87,54,75,87,68,97,10,66,51,50,55,88,74,33,38,92,48,72,48,57,10,98,90,69,22,73,9,39,43,19,92,30,73,67,16,41,64,26,7,15,76,62,3,50,96,41,42,43,14,89,0,24,88,90,92,10,63,1,48,6,20,40,36,92,7,52,34,71,79,41,86,55,3,90,5,99,31,47,42,45,37,43,68,24,33,61,34,96,62,82,2,81,23,38,74,30,90,7,1,69,48,87,24,51,77,29,50,8,76,92,53,12,35,22,37,68,82,71,63,44,53,65,25,75,3,99,5,93,6,6,62,54,94,86,5,71,15,55,80,91,48,33,3,82,55,40,50,37,10,13,81,63,79,6,38,82,5,44,75,11,50,37,66,44,23,71,15,38,27,95,28,74,28,31,56,83,71,6,20,81,20,1,44,99,8,82,80,13,26,55,24,75,93,90,19,15,61,34,53,87,29,81,62,57,12,18,39,83,24,59,64,44,60,7,43,68,90,23,81,15,79,5,91,71,95,10,87,56,44,39,43,73,20,5,30,32,23,69,15,47,29,79,92,89,86,34,56,76,58,37,91,36,42,82,7,36,92,94,92,36,33,35,9,54,40,39,86,63,9,2,10,37,80,2,26,67,36,82,43,94,19,34,30,61,15,37,97,7,31,89,43,65,25,52,19,65,92,5,27,1,6,38,38,87,39,64,54,75,47,96,69,66,30,99,27,46,37,24,53,68,14,96,33,38,48,51,3,40,56,31,40,63,68,78,50,7,43,3,83,89,100,52,55,30,51,83,75,87,7,28,55,21,24,88,59,71,39,62,11,96,93,51,59,61,30,8,68,72,11,51,62,11,2,17,41,53,100,16,40,7,44,96,27,67,84,86,39,23,49,50,19,41,1,78,2,31,86,70,3,97,20,65,8,22,82,48,75,82,64,16,89,8,12,16,75,95,2,14,19,51,64,38,92,65,15,94,96,1,64,99,98,84,65,6,6,47,54,82,29,19,98,18,27,9,34,2,5,36,17,24,87,81,61,79,46,77,73,42,78,36,41,76,20,6,81,26,53,36,8,82,54,6,99,81,15,33,83,20,69,100,44,55,80,5,34,26,82,7,68,59,43,9,35,63,15,16,90,68,52,98,50,6,4,49,87,19,82,70,40,51,70,83,6,50,88,40,76,70,47,45,29,90,54,64,53,69,80,43,37,32,41,87,38,45,36,24,65,18,95,4,69,65,88,74,15,76,15,91,46,62,36,76,52,90,40,6,59,20,49,97,51,90,84,89,35,20,14,100,38,8,4,7,73,92,81,88,68,96,79,14,58,15,90,10,5,29,15,64,49,64,61,0,54,45,89,89,65,3,89,3,11,92,10,84,84,91,72,52,87,51,66,45,66,56,55,71,85,70,35,34,34,96,34,88,41,23,76,7,26,65,10,37,57,20,21,42,12,93,94,99,43,60,44,9,15,99,80,100,69,15,33,3,11,67,90,52,90,67,59,16,32,69,53,89,89,74,31,1,67,24,100,10,84,44,19,99,43,99,99,12,14,32,14}','{}','{}','{}','{}','{}','{}','{}');  
pgbench -M prepared -n -r -P 1 -f ./test3.sql -c 56 -j 56 -T 120  
  
  
transaction type: ./test3.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 56  
number of threads: 56  
duration: 120 s  
number of transactions actually processed: 2496879  
latency average = 2.691 ms  
latency stddev = 5.174 ms  
tps = 20802.191560 (including connections establishing)  
tps = 20803.019308 (excluding connections establishing)  
statement latencies in milliseconds:  
         0.001  \set gv random(1,1500)  
         2.689  insert into mx1 (lt,gv,rc,v1,v2,v3,v4,v5,v6,v7,v8,v9) values (now(),:gv,2500,'{12,22,65,18,.......................  
top - 18:04:01 up 26 days, 22:51,  3 users,  load average: 11.29, 18.53, 15.60  
Tasks: 546 total,  36 running, 510 sleeping,   0 stopped,   0 zombie  
%Cpu(s): 44.9 us,  5.4 sy,  0.0 ni, 49.4 id,  0.4 wa,  0.0 hi,  0.0 si,  0.0 st  
KiB Mem : 23094336+total, 10651434+free,  2351516 used, 12207750+buff/cache  
KiB Swap:        0 total,        0 free,        0 used. 18246070+avail Mem   

定义统计SQL

例如,1分钟统计的SQL如下:

INSERT INTO agg1 (lt, tu, gv, mid, _cnt, _sum, avg, min, max, p90, p95, p99)  
SELECT  
  lt,  
  1,  
  gv,  
  1,  
  _count,  
  avg * _count AS _sum,   
  avg,  
  (sorted_array_to_percentile(sorted1, 0)) AS min,  
  (sorted_array_to_percentile(sorted1, 1)) AS max,  
  (sorted_array_to_percentile(sorted1, 0.9)) AS P90,  
  (sorted_array_to_percentile(sorted1, 0.95)) AS P95,  
  (sorted_array_to_percentile(sorted1, 0.99)) AS P99  
FROM   
(  
select   
  date_trunc('minute', current_timestamp) - INTERVAL '1 minutes' as lt,  
  gv,  
  count(rc) as _count,  
  sort_asc(arragg(mg.v1)) as sorted1,  
  array_to_mean(sort_asc(arragg(mg.v1))) as avg  
FROM   
  mx1 mg   
where   
  lt >= date_trunc('minute', current_timestamp) - INTERVAL '1 minutes'  
and   
  lt < date_trunc('minute', current_timestamp)  
group by gv  
) t;   
  
  
  
INSERT 0 737  
Time: 410.264 ms  

定义动态统计函数

将1分钟、5分钟、10分钟的统计SQL定义为动态统计SQL。方便压测,输入FEED LOG的表名suffix,统计区间,以及每个区间的统计记录数限制(比如,通过LIMIT可以满足每个维度每分钟600万个点的需求,或者任意个点的需求,起到压测阈值设置作用.)

create or replace function stat(  
  int,    -- suffix  
  int,    -- limit  
  text    -- interval  
) returns void as $$  
declare  
begin  
  set enable_seqscan=off;  
  set max_parallel_workers_per_gather =0;  
  execute format(  
$_$  
INSERT INTO agg%s (lt, tu, gv, mid, _cnt, _sum, avg, min, max, p90, p95, p99)  -- $1  
SELECT  
  lt,  
  1,  
  gv,  
  1,  
  _count,  
  avg * _count AS _sum,   
  avg,  
  (sorted_array_to_percentile(sorted1, 0)) AS min,  
  (sorted_array_to_percentile(sorted1, 1)) AS max,  
  (sorted_array_to_percentile(sorted1, 0.9)) AS P90,  
  (sorted_array_to_percentile(sorted1, 0.95)) AS P95,  
  (sorted_array_to_percentile(sorted1, 0.99)) AS P99  
FROM   
(  
select   
  date_trunc('minute', current_timestamp) - INTERVAL '%s' as lt,  -- $3  
  gv,  
  count(rc) as _count,  
  sort_asc(arragg(mg.v1)) as sorted1,  
  array_to_mean(sort_asc(arragg(mg.v1))) as avg  
from mx%s mg      -- $1  
where ctid = any  
(array(  
select ctid  
FROM   
  mx%s mg    -- $1  
where   
  lt >= date_trunc('minute', current_timestamp) - INTERVAL '%s'   -- $3  
and   
  lt < date_trunc('minute', current_timestamp)  
limit %s))    -- $2 , 限流作用,协助压测量级调整
group by gv  
) t  
$_$,  
$1, $3, $1, $1, $3, $2  
);  
end;  
$$ language plpgsql strict;  
postgres=# select stat(2, 24000, '10 min');      -- 24000*2500=6000万 ,10分钟6000万个点的统计  
 stat   
------  
   
(1 row)  
  
Time: 235.821 ms  

统计结果展示

postgres=# select * from agg2;  
 id  |         lt          | tu |  gv  | mid | _cnt | _sum |       avg        | min | max | p90 | p95 | p99   
-----+---------------------+----+------+-----+------+------+------------------+-----+-----+-----+-----+-----  
   1 | 2018-02-06 18:00:00 |  1 | 845  |   1 |    2 |  101 |          50.6836 |   0 | 100 |  91 |  96 |  99  
   2 | 2018-02-06 18:00:00 |  1 | 1247 |   1 |    1 |   51 | 50.6835999999999 |   0 | 100 |  91 |  96 |  99  
   3 | 2018-02-06 18:00:00 |  1 | 1337 |   1 |    2 |  101 |          50.6836 |   0 | 100 |  91 |  96 |  99  
   4 | 2018-02-06 18:00:00 |  1 | 537  |   1 |    1 |   51 | 50.6835999999999 |   0 | 100 |  91 |  96 |  99  
   5 | 2018-02-06 18:00:00 |  1 | 75   |   1 |    1 |   51 | 50.6835999999999 |   0 | 100 |  91 |  96 |  99  

压测设计2

写入、统计并行测试

1、统计压测

例如,以每分钟写入600万个元素为目标,统计指标满足即可。所以这里设置每个区间分别为2400, 12000, 24000。分别代表24002500, 120002500, 24000*2500。即600万,3000万,6000万。

vi stat1.sql  
  
\set suffix random(1,1024)  
select stat(:suffix, 2400, '1 min');  
  
  
vi stat2.sql  
  
\set suffix random(1,1024)  
select stat(:suffix, 12000, '5 min');  
  
  
vi stat3.sql  
  
\set suffix random(1,1024)  
select stat(:suffix, 24000, '10 min');  
nohup pgbench -M prepared -n -r -P 1 -f ./stat1.sql -c 10 -j 10 -T 1200 >./log.stat1 2>&1 &  
nohup pgbench -M prepared -n -r -P 1 -f ./stat2.sql -c 10 -j 10 -T 1200 >./log.stat2 2>&1 &  
nohup pgbench -M prepared -n -r -P 1 -f ./stat3.sql -c 10 -j 10 -T 1200 >./log.stat3 2>&1 &  

2、写入压测

nohup pgbench -M prepared -n -r -P 1 -f ./test2.sql -c 42 -j 42 -T 1200 >./log.test2 2>&1 &  

统计、写入并行测试结果

top - 18:37:57 up 26 days, 23:25,  3 users,  load average: 16.60, 11.64, 12.11  
Tasks: 562 total,  70 running, 492 sleeping,   0 stopped,   0 zombie  
%Cpu(s): 90.7 us,  6.4 sy,  0.0 ni,  2.8 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st  
KiB Mem : 23094336+total, 73160104 free, 10510536 used, 14727273+buff/cache  
KiB Swap:        0 total,        0 free,        0 used. 19740713+avail Mem   

1、写入层面,2773万个点/s。相当于每分钟16.64亿个元素。除以1024个表,每个表约162.5万个元素每分钟。

如果要满足单表600万元素每分钟的写入期望,需要将表调整为256个。

transaction type: ./test2.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 42  
number of threads: 42  
duration: 1200 s  
number of transactions actually processed: 13312185  
latency average = 3.786 ms  
latency stddev = 3.226 ms  
tps = 11091.503135 (including connections establishing)  
tps = 11091.624310 (excluding connections establishing)  
statement latencies in milliseconds:  
         0.002  \set suffix random(1,1024)  
         0.001  \set gv random(1,1500)  
         3.788  select ins_mx(:suffix,:gv,2500,'{12,22,65,18,1,  

2、1分钟级统计,每秒统计383个表,每分钟统计2.3万个表。

超过期望。

transaction type: ./stat1.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 10  
number of threads: 10  
duration: 1200 s  
number of transactions actually processed: 459639  
latency average = 26.107 ms  
latency stddev = 138.111 ms  
tps = 383.032007 (including connections establishing)  
tps = 383.033812 (excluding connections establishing)  
statement latencies in milliseconds:  
         0.002  \set suffix random(1,1024)  
        26.116  select stat(:suffix, 2400, '1 min');  

2、5分钟级统计,每秒统计3.82个表,每5分钟统计1146个表。

超过期望。

transaction type: ./stat2.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 10  
number of threads: 10  
duration: 1200 s  
number of transactions actually processed: 4586  
latency average = 2617.628 ms  
latency stddev = 1478.233 ms  
tps = 3.818981 (including connections establishing)  
tps = 3.819014 (excluding connections establishing)  
statement latencies in milliseconds:  
         0.004  \set suffix random(1,1024)  
      2617.625  select stat(:suffix, 12000, '5 min');  

3、10分钟级统计,每秒统计1.71个表,每10分钟统计1126个表。

超过期望。

transaction type: ./stat3.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 10  
number of threads: 10  
duration: 1200 s  
number of transactions actually processed: 2060  
latency average = 5830.904 ms  
latency stddev = 2480.596 ms  
tps = 1.713737 (including connections establishing)  
tps = 1.713839 (excluding connections establishing)  
statement latencies in milliseconds:  
         0.004  \set suffix random(1,1024)  
      5830.900  select stat(:suffix, 24000, '10 min');  

综合上面三个指标,如果要满足单表600万元素每分钟的写入期望,需要将表的数量调整为256个(换句话说,在本例涉及到的硬件规格下的PG10,可以支持256个表,每个表每分钟600万个元素的写入以及统计。)。

统计和写入都能达到要求。

压测设计3

将suffix调整为256,也就是说写入、统计针对的区间为256个表,测试结果:

top - 19:23:16 up 27 days, 10 min,  3 users,  load average: 33.14, 10.55, 15.39  
Tasks: 565 total,  71 running, 494 sleeping,   0 stopped,   0 zombie  
%Cpu(s): 92.4 us,  6.2 sy,  0.0 ni,  0.9 id,  0.4 wa,  0.0 hi,  0.0 si,  0.0 st  
KiB Mem : 23094336+total,  1028536 free, 11676488 used, 21823833+buff/cache  
KiB Swap:        0 total,        0 free,        0 used. 13886019+avail Mem   

写入

transaction type: ./test2.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 24  
number of threads: 24  
duration: 1200 s  
number of transactions actually processed: 12113510  
latency average = 2.377 ms  
latency stddev = 1.319 ms  
tps = 10094.572203 (including connections establishing)  
tps = 10094.659895 (excluding connections establishing)  
statement latencies in milliseconds:  
         0.002  \set suffix random(1,256)  
         0.001  \set gv random(1,1500)  
         2.374  select ins_mx(:suffix,:gv,2500,'{12,22,65,18,1,60,69,9,52,28,73,9  
......  

统计

1分钟统计2520张表  
  
transaction type: ./stat1.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 10  
number of threads: 10  
duration: 1200 s  
number of transactions actually processed: 50733  
latency average = 237.004 ms  
latency stddev = 736.468 ms  
tps = 42.130871 (including connections establishing)  
tps = 42.131272 (excluding connections establishing)  
statement latencies in milliseconds:  
         0.002  \set suffix random(1,256)  
       237.062  select stat(:suffix, 2400, '1 min');  
  
5分钟统计11100张表  
  
transaction type: ./stat2.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 10  
number of threads: 10  
duration: 1200 s  
number of transactions actually processed: 45781  
latency average = 263.367 ms  
latency stddev = 1712.181 ms  
tps = 37.788541 (including connections establishing)  
tps = 37.788898 (excluding connections establishing)  
statement latencies in milliseconds:  
         0.002  \set suffix random(1,256)  
       263.433  select stat(:suffix, 12000, '5 min');  
  
10分钟统计19800张表  
  
transaction type: ./stat3.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 10  
number of threads: 10  
duration: 1200 s  
number of transactions actually processed: 41290  
latency average = 292.340 ms  
latency stddev = 2389.384 ms  
tps = 33.831941 (including connections establishing)  
tps = 33.832121 (excluding connections establishing)  
statement latencies in milliseconds:  
         0.002  \set suffix random(1,256)  
       292.436  select stat(:suffix, 24000, '10 min');  

写入、统计均满足了600万每分钟的指标。

数据维护调度

1、清理明细

每次TRUNCATE下一个分区。例如0分时,truncate 分区1。10分时,TRUNCATE分区2。... 50分时,TRUNCATE分区0。

例如:

truncate mx1_1;

2、清理统计数据

每次TRUNCATE下一个分区。例如0点时,truncate 分区01。1点时,TRUNCATE分区02。... 23点时,TRUNCATE分区00。

例如:

truncate agg1_1;

truncate操作直接清文件,被清除的数据不会产生WAL日志,很快很快。

小结

1、一个56 Core的RDS PG 10,可以满足256个FEED LOG的写入和统计。(每个feed log表每分钟的写入点数为600万。也就是说整库的写入和统计吞吐约: 15亿点/分钟 )

2、历史数据的清理可以启用的调度任务,对分区执行truncate.

3、使用到了list分区的功能。注意PG 10的分区,当操作主表时,不管你最终查询、写入、更新的是哪个子表,会对所有子表持对应的锁,所以写入和truncate子表会有冲突,务必加LOCK_TIMEOUT来TRUNCATE子表。

后面我会详细介绍native partition和pg_pathman在锁粒度这块的差异。

《分区表锁粒度差异 - pg_pathman VS native partition table》

参考

《PostgreSQL 11 分区表用法及增强 - 增加HASH分区支持 (hash, range, list)》

《PostgreSQL 查询涉及分区表过多导致的性能问题 - 性能诊断与优化(大量BIND, spin lock, SLEEP进程)》

《PostgreSQL aggregate function 4 : Hypothetical-Set Aggregate Functions》

《PostgreSQL aggregate function 3 : Aggregate Functions for Ordered-Set》

《PostgreSQL aggregate function 2 : Aggregate Functions for Statistics》

《PostgreSQL aggregate function 1 : General-Purpose Aggregate Functions》

https://github.com/pjungwir/aggs_for_arrays/

https://www.postgresql.org/docs/devel/static/intarray.html

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
5月前
|
存储 Oracle 关系型数据库
postgresql数据库|wal日志的开启以及如何管理
postgresql数据库|wal日志的开启以及如何管理
979 0
|
关系型数据库 物联网 PostgreSQL
沉浸式学习PostgreSQL|PolarDB 11: 物联网(IoT)、监控系统、应用日志、用户行为记录等场景 - 时序数据高吞吐存取分析
物联网场景, 通常有大量的传感器(例如水质监控、气象监测、新能源汽车上的大量传感器)不断探测最新数据并上报到数据库. 监控系统, 通常也会有采集程序不断的读取被监控指标(例如CPU、网络数据包转发、磁盘的IOPS和BW占用情况、内存的使用率等等), 同时将监控数据上报到数据库. 应用日志、用户行为日志, 也就有同样的特征, 不断产生并上报到数据库. 以上数据具有时序特征, 对数据库的关键能力要求如下: 数据高速写入 高速按时间区间读取和分析, 目的是发现异常, 分析规律. 尽量节省存储空间
747 1
|
5月前
|
SQL 大数据 API
每天一道大厂SQL题【Day08】服务日志SQL统计
每天一道大厂SQL题【Day08】服务日志SQL统计
62 0
|
2月前
|
开发框架 .NET Docker
【Azure 应用服务】App Service .NET Core项目在Program.cs中自定义添加的logger.LogInformation,部署到App Service上后日志不显示Log Stream中的问题
【Azure 应用服务】App Service .NET Core项目在Program.cs中自定义添加的logger.LogInformation,部署到App Service上后日志不显示Log Stream中的问题
|
2月前
|
应用服务中间件 Linux nginx
在Linux中,如何统计ip访问情况?分析 nginx 访问日志?如何找出访问页面数量在前十位的ip?
在Linux中,如何统计ip访问情况?分析 nginx 访问日志?如何找出访问页面数量在前十位的ip?
|
2月前
|
SQL 关系型数据库 PostgreSQL
PostgreSQL 如何通过身份证号码进行年龄段的统计?
【8月更文挑战第20天】PostgreSQL 如何通过身份证号码进行年龄段的统计?
230 2
|
2月前
|
API
【Azure 应用服务】当在Azure App Service的门户上 Log Stream 日志无输出,需要如何操作让其输出Application Logs呢?
【Azure 应用服务】当在Azure App Service的门户上 Log Stream 日志无输出,需要如何操作让其输出Application Logs呢?
图库,设计类软件,App视频截图软件,外加设计图库,在你截取视频就能够实现图片收录,通过设计类网站后台控制系统,可以提前设置好,统计的分类内容,定义好分类,自动收录图片,再将截图汇总整理展示
图库,设计类软件,App视频截图软件,外加设计图库,在你截取视频就能够实现图片收录,通过设计类网站后台控制系统,可以提前设置好,统计的分类内容,定义好分类,自动收录图片,再将截图汇总整理展示
图库,设计类软件,App视频截图软件,外加设计图库,在你截取视频就能够实现图片收录,通过设计类网站后台控制系统,可以提前设置好,统计的分类内容,定义好分类,自动收录图片,再将截图汇总整理展示
|
5月前
|
Java Unix Shell
苹果app代码行数统计
苹果app代码行数统计
43 1
|
5月前
|
存储 弹性计算 运维
统计/var/log 有多少个文件
【4月更文挑战第29天】
49 1

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版