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.
ECS:
56Core
224G memory
2*Local SSD cloud disk
DB:
PostgreSQL
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 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)
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) 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;
$$
; 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.
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) 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 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 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 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; 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 Write and statistics tests are performed in parallel.
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 & 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.
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.
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.
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)
digoal - May 16, 2019
digoal - September 20, 2019
digoal - March 25, 2020
digoal - May 16, 2019
digoal - June 26, 2019
Alibaba Cloud Storage - February 27, 2020
ApsaraDB RDS for PostgreSQL
An on-demand database hosting service for PostgreSQL with automated monitoring, backup and disaster recovery capabilities
Learn More
ApsaraDB RDS for MySQL
An on-demand database hosting service for MySQL with automated monitoring, backup and disaster recovery capabilities
Learn More
PolarDB for PostgreSQL
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn More
ApsaraDB RDS for MariaDB
ApsaraDB RDS for MariaDB supports multiple storage engines, including MySQL InnoDB to meet different user requirements.
Learn MoreMore Posts by digoal
