×
Community Blog PostgreSQL App Massive FEED LOG Real-time Quality Statistics

PostgreSQL App Massive FEED LOG Real-time Quality Statistics

In this article, we'll discuss a case of implementing real-time quality statistics with Rotate Partitioned Table on PostgreSQL.

Background

Service quality control is an important part of refined quality management. For example, we could count the water level value, such as avg, sum, and count, in certain groups and at certain fixed intervals (1 minute, 5 minutes, 10 minutes) in real time.

This article introduces a case of real-time quality monitoring of a service.

Multiple point values are written in batches as arrays. For example, we write 2,500 points in a batch.

For efficient statistics and efficient cleaning of FEED LOG details, we can use the "list" partitioned table.

At the same time, the intarray plug-in is also needed to sort the values of the array, and the aggs_for_arrays plug-in is used to calculate the water level values of the ordered array.

Even if we do not use these two plug-ins, we can also use the PG quantile aggregate function to achieve the same effect.

Hardware Environment:

ECS:
56Core
224G memory
2*Local SSD cloud disk

DB:
PostgreSQL

Install the aggs_for_arrays Plug-In

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  

Load the Plug-In

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

Customize the Array Aggregate Function to Aggregate Multiple Arrays into One-Dimensional Arrays

The built-in array_agg aggregate function of PostgreSQL aggregates multiple arrays into multi-dimensional arrays, but our goal is to aggregate multiple arrays into one-dimensional arrays. Therefore, we need to customize an aggregate function.

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)  

The aggregate function uses array_cat, which has a large number of MEMCOPY, so when the amount involved is relatively large, the performance is not very optimistic (much worse than the array_agg). We can customize an arragg with better performance later.

PostgreSQL multiple arrays are aggregated into one-dimensional arrays (array_agg)

Define a function that generates an array of random values

create or replace function gen_randarr(  
  int,  -- the upper limit of the random value range 
  int   -- generate the number  

) returns int[] as 
$$
  
  select array(select (random()*$1)::int from generate_series(1,$2));  

$$
 language sql strict;  
  


-- Return 10 random numbers within the range of 100, like for example:

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

Define a Feed Log Partitioned Detail Table

The streamed data is non-critical and does not need to be persisted, so you can choose the unlogged table, which greatly improves performance.

Example:

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;  

There are many FEED dimensions, so each FEED dimension defines a partitioned table. We assume there are 1,024 FEED dimensions and use the following method to quickly define the 1,024 partitioned tables.

Create 1,024 partitioned tables at a time, with six partitions per partitioned table.

do language plpgsql 
$$
  
declare  
begin  
  for i in 1..1024 loop  
    -- Create a primary table  
    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  
      -- Create partition  
      execute format('CREATE unlogged TABLE mx%s_%s PARTITION OF mx%s FOR VALUES IN (%L)', i, x, i, x);  
  
      -- PG 10 users should build an index as follows 
      execute format('create index idx_mx%s_%s_lt on mx%s_%s(lt)', i, x, i, x);  
    end loop;  
      
    -- PG11 users should do this instead  
    -- execute format('create index idx_mx%s_lt on mx%s(lt)', i, i);  
  end loop;  
end;  

$$
;  

Define the Statistical Partitioned Table Corresponding to the Feed Detail Table

Example:

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;  

Create 1,024 partitioned tables at a time, with 24 partitions per partitioned table.

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;  

$$
;  

Detail tables + statistical tables, a total of 30,720 tables.

For maintaining data, just truncate the earliest partition and the partition is recycled.

Define a Dynamic Write Function

For ease of stress testing, dynamic SQL is used to write data into the corresponding FEED detail table.

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; 

Example:

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)  

Stress Testing Design 1

Define Write Stress Testing 1

Dynamic; write multiple tables; array data is dynamic data

1,024 random tables, 1,500 random groups and 2 random arrays of 2,500 elements

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   

Define Write Stress Testing 2

Dynamic; write multiple tables; array data is static data

(The overhead of the database is gen_randarr, but this array is actually passed from the application, so using a static array here can avoid the extra overhead of the database and the performance is more realistic.)

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   

Define Write Stress Testing 3

Static; write a single table; array data is static data

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   

Define Statistical SQL

For example, the SQL for 1-minute statistics is as follows:

INSERT INTO agg1 (lt, tu, gv, mid, _cnt, _sum, avg, min, max, p90, p95, p99)  
SELECT  
  date_trunc('minute', current_timestamp) - INTERVAL '1 minutes' as lt,  
  1,  
  gv,  
  1,  
  _count,  
  array_to_mean(sorted1) * _count AS _sum,   
  array_to_mean(sorted1) as 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   
  gv,  
  count(rc) as _count,  
  sort_asc(arragg(mg.v1)) as sorted1  
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  

It can be changed to string_agg statistics, as follows:

INSERT INTO agg1 (lt, tu, gv, mid, _cnt, _sum, avg, min, max, p90, p95, p99)  
SELECT  
  date_trunc('minute', current_timestamp) - INTERVAL '1 minutes' as lt,
  1,  
  gv,  
  1,  
  _count,  
  array_to_mean(sorted1) * _count AS _sum,   
  array_to_mean(sorted1) as 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   
  gv,  
  count(rc) as _count,  
  sort_asc( 
    ( '{'||string_agg(rtrim(ltrim(mg.v1::text, '{'), '}'), ',')||'}' )::int[]  
  ) as sorted1  
FROM   
  mx1 mg   
where   
  lt >= date_trunc('minute', current_timestamp) - INTERVAL '1 minutes'  
and   
  lt < date_trunc('minute', current_timestamp)  
group by gv  
) t;  

Define a Dynamic Statistical Function

The statistical SQL of 1 minute, 5 minutes and 10 minutes is defined as dynamic statistical SQL. For stress testing convenience, enter the table name suffix of FEED LOG, the statistical interval, and the LIMIT of the number of statistical records in each interval. For example, LIMIT can satisfy the demand of 6 million points per minute per dimension, or the demand of any number of points, playing the role of setting the stress testing threshold.

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  
  date_trunc('minute', current_timestamp) - INTERVAL '%s' as lt,  -- $3  
  1,  
  gv,  
  1,  
  _count,  
  array_to_mean(sorted1) * _count AS _sum,   
  array_to_mean(sorted1) as 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   
  gv,  
  count(rc) as _count,  
  sort_asc(arragg(mg.v1)) as sorted1  
from
(
  select *  
  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 , 限流作用,协助压测量级调整
) mg    
group by gv  
) t  
$_$,  
$1, $3, $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  

Similarly, it can also be changed to string_agg statistics.

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  
  date_trunc('minute', current_timestamp) - INTERVAL '%s' as lt,  -- $3
  1,  
  gv,  
  1,  
  _count,  
  array_to_mean(sorted1) * _count AS _sum,   
  array_to_mean(sorted1) as 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   
  gv,  
  count(rc) as _count,  
  sort_asc(
    ( '{'||string_agg(rtrim(ltrim(mg.v1::text, '{'), '}'), ',')||'}' )::int[]  
  ) as sorted1  
from
(
  select *  
  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 , 限流作用,协助压测量级调整
) mg    
group by gv  
) t  
$_$,  
$1, $3, $1, $3, $2  
);  
end;  

$$
 language plpgsql strict;  

Display statistical results

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  

Stress Testing Design 2

Write and statistics tests are performed in parallel.

1. Statistical Stress Testing

For example, with the goal of writing 6 million elements per minute, the statistical indicators can be satisfied. Therefore, each interval set here is 2,400, 12,000, and 240,000 respectively. They represent 2400 * 2500, 12000 * 2500, and 24000 * 2500 respectively. That is, 6 million, 30 million, and 60 million.

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. Write Stress Testing

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

The results of the parallel test for write and statistics

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.  Write 27.73 million points/s. This is equivalent to 1.664 billion elements per minute. After dividing it by 1,024 tables, each table has about 1.625 million elements per minute.

If you want to meet the write expectation of 6 million elements per minute for a single table, you need to adjust the table to 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.  For 1-minute statistics, the count is 383 tables per second and 23,000 tables per minute.

This exceeds expectations.

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');  

3.  For 5-minute statistics, the count is 3.82 tables per second and 1,146 tables every 5 minutes.

This exceeds expectations.

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');  

4.  For 10-minute statistics, the count is 1.71 tables per second and 1,126 tables every 10 minutes.

This exceeds expectations.

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');  

Combining the three indicators above, if you want to meet the writing expectation of 6 million elements per minute for a single table, you need to adjust the number of tables to 256. In other words, PG10 under the hardware specifications involved in this example can support 256 tables, and each table can write and count 6 million elements per minute.

Both statistics and writing can meet the requirements.

Stress Testing Design 3

Adjust the suffix to 256, which means that the interval for writing and statistics is 256 tables, and the test results are as follows:

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   

Write

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  
......  

Statistics

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');  

Both the writing and statistics have met the indicator of 6 million per minute.

Data Maintenance and Scheduling

1.  Clean up details

The next partition is truncated at a specific time. For example, partition 1 is truncated at 0 minutes. Partition 2 is truncated at 10 minutes. Partition 0 is truncated at 50 minutes.

For example:

truncate mx1_1;

2.  Clean up statistical data

The next partition is truncated at a specific time. For example, partition 01 is truncated at 00:00. Partition 02 is truncated at 01:00. Partition 00 is truncated at 23:00.

For example:

truncate agg1_1;

The truncation operation cleans the file directly, which is very fast, and the cleaned data does not generate a WAL log.

Summary

1.  A 56 Core RDS PG 10 can satisfy the writing and statistics of 256 feed logs. (Each feed log table has 6 million write points per minute. That is, the write and statistical throughput of the entire library is about 1.5 billion points/minute.)

2.  When cleaning up historical data, you can enable scheduling tasks to truncate partitions.

3.  The function of "list" partition is used. Note that in the partition of PG 10, when an operation is performed on the primary table, the corresponding locks are applied to all the sub-tables, regardless of which sub-table you finally query, write, or update. As such, there will be conflicts between writing and truncating the sub-tables. Be sure to add LOCK_TIMEOUT to truncate the sub-table.

Later, a detailed description will be provided of the difference between the native partition and pg_pathman in lock granularity.

Lock granularity difference of partition tables — pg_pathman VS native partitioned table

4.  PostgreSQL multiple arrays are aggregated into one-dimensional arrays (array_agg)

0 0 0
Share on

digoal

282 posts | 24 followers

You may also like

Comments

digoal

282 posts | 24 followers

Related Products