PostgreSQL APP海量FEED LOG实时质量统计CASE(含percentile_disc) - 含rotate 分区表
背景
服务质量监控是精细化质量管理的重要环节：
例如实时统计FEED LOG在某些分组在某些固定区间（分钟、5分钟、10分钟）的水位值，avg, sum, count等。
本文将介绍一个CASE，某个服务的实时质量监控。
多个点值以数组的形式，批量合并写入。例如2500个点，一批写入。
为了实现高效率的统计，以及FEED LOG明细的高效率清除，我们可以使用list分区表。
同时还需要用到intarray 插件来对数组的数值进行排序，以及使用aggs_for_arrays 插件来计算有序数组的水位值。
当然即使我们不使用这两个插件，使用PG的分位数聚合函数，也可以达到同样达到效果。
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+--------------------------------------+--------------------+----------------------------------------------+--------
pg_catalog | percent_rank | double precision | VARIADIC "any" ORDER BY VARIADIC "any" | agg
pg_catalog | percentile_cont | double precision | double precision ORDER BY double precision | agg
pg_catalog | percentile_cont | interval | double precision ORDER BY interval | agg
pg_catalog | percentile_cont | double precision[] | double precision[] ORDER BY double precision | agg
pg_catalog | percentile_cont | interval[] | double precision[] ORDER BY interval | agg
pg_catalog | percentile_disc | anyelement | double precision ORDER BY anyelement | agg
pg_catalog | percentile_disc | anyarray | double precision[] ORDER BY anyelement | agg
《PostgreSQL aggregate function 4 : Hypothetical-Set Aggregate Functions》
《PostgreSQL aggregate function 3 : Aggregate Functions for Ordered-Set》
《PostgreSQL aggregate function 2 : Aggregate Functions for Statistics》
《PostgreSQL aggregate function 1 : General-Purpose Aggregate Functions》
DEMO
硬件环境：
ECS:
56Core
224G memory
2*本地ssd云盘
DB:
PostgreSQL
安装aggs_for_arrays 插件
wget http://api.pgxn.org/dist/aggs_for_arrays/1.3.1/aggs_for_arrays-1.3.1.zip
unzip aggs_for_arrays-1.3.1.zip
cd aggs_for_arrays-1.3.1
export PGHOME=/home/digoal/pg11
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH:.
USE_PGXS=1 make clean
USE_PGXS=1 make
USE_PGXS=1 make install
加载插件
postgres=# create extension intarray ;
CREATE EXTENSION
postgres=# create extension aggs_for_arrays ;
CREATE EXTENSION
自定义数组聚合函数，将多个数组聚合为一维数组
PostgreSQL内置的array_agg聚合函数会将多个数组聚合为多维数组，而我们的目的是要将多个数组聚合为一维数组。所以需要自定义一个聚合函数。
create aggregate arragg (anyarray) (sfunc = array_cat, stype=anyarray, PARALLEL=safe);
postgres=# select arragg(info) from (values(array[1,2,3]),(array[3,4,5])) t(info);
arragg
---------------
{1,2,3,3,4,5}
(1 row)
postgres=# select arragg(info) from (values(array[1,2,3]),(array[2,3,4,5])) t(info);
arragg
-----------------
{1,2,3,2,3,4,5}
(1 row)
聚合函数用到了array_cat，这个函数存在大量的MEMCOPY，所以涉及的量比较大时，性能不太乐观(相比较array_agg要差很多)。后面我们可以自定义一个性能更好的arragg。
《PostgreSQL 多个数组聚合为一维数组加速(array_agg)》
定义生成随机数值数组的函数
create or replace function gen_randarr(
int, -- 随机值取值范围上限
int -- 生成个数
) returns int[] as $$
select array(select (random()*$1)::int from generate_series(1,$2));
$$ language sql strict;
返回值范围100内的随机数，返回10个，样例如下：
postgres=# select gen_randarr(100,10);
gen_randarr
--------------------------------
{72,6,26,44,47,84,88,72,59,40}
(1 row)
定义FEED LOG明细分区表
由于流式处理的数据为非关键数据，不需要持久化，所以可以选择unlogged table，性能会有很大提升
例子：
CREATE unlogged TABLE mx (
id serial8 not null,
lt timestamp not null,
gv text,
rc int,
v1 int[],
v2 int[],
v3 int[],
v4 int[],
v5 int[],
v6 int[],
v7 int[],
v8 int[],
v9 int[]
) PARTITION BY LIST ( substring(extract('min' from lt)::text, 1, 1) );
-- CREATE unlogged TABLE mx0 PARTITION OF mx FOR VALUES IN ('0');
-- CREATE unlogged TABLE mx1 PARTITION OF mx FOR VALUES IN ('1');
-- CREATE unlogged TABLE mx2 PARTITION OF mx FOR VALUES IN ('2');
-- CREATE unlogged TABLE mx3 PARTITION OF mx FOR VALUES IN ('3');
-- CREATE unlogged TABLE mx4 PARTITION OF mx FOR VALUES IN ('4');
-- CREATE unlogged TABLE mx5 PARTITION OF mx FOR VALUES IN ('5');
-- create index idx_mx_lt on mx(lt);
-- drop table mx;
由于FEED维度较多，所以每个FEED维度定义一个分区表，我们假设有1024个FEED维度，使用下面的方法，快速定义1024个分区表。
一次创建1024个分区表，每个分区表6个分区。
do language plpgsql $$
declare
begin
for i in 1..1024 loop
-- 创建主表
execute format(
'
CREATE unlogged TABLE mx%s (
id serial8 not null,
lt timestamp not null,
gv text,
rc int,
v1 int[],
v2 int[],
v3 int[],
v4 int[],
v5 int[],
v6 int[],
v7 int[],
v8 int[],
v9 int[]
) PARTITION BY LIST ( substring(extract(''min'' from lt)::text, 1, 1) )
', i);
for x in 0..5 loop
-- 创建分区
execute format('CREATE unlogged TABLE mx%s_%s PARTITION OF mx%s FOR VALUES IN (%L)', i, x, i, x);
-- PG 10的话，请在这里建索引
execute format('create index idx_mx%s_%s_lt on mx%s_%s(lt)', i, x, i, x);
end loop;
-- 创建索引(PG 11支持)
-- execute format('create index idx_mx%s_lt on mx%s(lt)', i, i);
end loop;
end;
$$;
定义FEED明细表对应的统计分区表
例子
CREATE unlogged TABLE agg (
id serial8 not null,
lt timestamp not null,
tu int2,
gv text,
mid int2,
_cnt int,
_sum int8,
avg float8,
min int,
max int,
p90 int,
p95 int,
p99 int
)
PARTITION BY LIST ( (extract('hour' from lt)::text) );
-- CREATE unlogged TABLE agg0 PARTITION OF agg FOR VALUES IN ('0');
-- CREATE unlogged TABLE agg1 PARTITION OF agg FOR VALUES IN ('1');
-- CREATE unlogged TABLE agg2 PARTITION OF agg FOR VALUES IN ('2');
-- CREATE unlogged TABLE agg3 PARTITION OF agg FOR VALUES IN ('3');
-- CREATE unlogged TABLE agg4 PARTITION OF agg FOR VALUES IN ('4');
-- CREATE unlogged TABLE agg5 PARTITION OF agg FOR VALUES IN ('5');
-- CREATE unlogged TABLE agg6 PARTITION OF agg FOR VALUES IN ('6');
-- CREATE unlogged TABLE agg7 PARTITION OF agg FOR VALUES IN ('7');
-- CREATE unlogged TABLE agg8 PARTITION OF agg FOR VALUES IN ('8');
-- CREATE unlogged TABLE agg9 PARTITION OF agg FOR VALUES IN ('9');
-- CREATE unlogged TABLE agg10 PARTITION OF agg FOR VALUES IN ('10');
-- CREATE unlogged TABLE agg11 PARTITION OF agg FOR VALUES IN ('11');
-- CREATE unlogged TABLE agg12 PARTITION OF agg FOR VALUES IN ('12');
-- CREATE unlogged TABLE agg13 PARTITION OF agg FOR VALUES IN ('13');
-- CREATE unlogged TABLE agg14 PARTITION OF agg FOR VALUES IN ('14');
-- CREATE unlogged TABLE agg15 PARTITION OF agg FOR VALUES IN ('15');
-- CREATE unlogged TABLE agg16 PARTITION OF agg FOR VALUES IN ('16');
-- CREATE unlogged TABLE agg17 PARTITION OF agg FOR VALUES IN ('17');
-- CREATE unlogged TABLE agg18 PARTITION OF agg FOR VALUES IN ('18');
-- CREATE unlogged TABLE agg19 PARTITION OF agg FOR VALUES IN ('19');
-- CREATE unlogged TABLE agg20 PARTITION OF agg FOR VALUES IN ('20');
-- CREATE unlogged TABLE agg21 PARTITION OF agg FOR VALUES IN ('21');
-- CREATE unlogged TABLE agg22 PARTITION OF agg FOR VALUES IN ('22');
-- CREATE unlogged TABLE agg23 PARTITION OF agg FOR VALUES IN ('23');
-- create index idx_agg_lt on agg(lt);
-- drop table agg;
一次创建1024个分区表，每个分区表24个分区。
do language plpgsql $$
declare
begin
for i in 1..1024 loop
-- 创建主表
execute format(
'
CREATE unlogged TABLE agg%s (
id serial8 not null,
lt timestamp not null,
tu int2,
gv text,
mid int2,
_cnt int,
_sum int8,
avg float8,
min int,
max int,
p90 int,
p95 int,
p99 int
)
PARTITION BY LIST ( (extract(''hour'' from lt)::text) )
', i);
for x in 0..23 loop
-- 创建分区
execute format('CREATE unlogged TABLE agg%s_%s PARTITION OF agg%s FOR VALUES IN (%L)', i, x, i, x);
-- PG 10的话，请在这里建索引
execute format('create index idx_agg%s_%s_lt on agg%s_%s(lt)', i, x, i, x);
end loop;
-- 创建索引(PG 11支持)
-- execute format('create index idx_agg%s_lt on agg%s(lt)', i, i);
end loop;
end;
$$;
明细+统计，总共30720张表。
维护数据时，TRUNCATE最早的分区即可，分区循环使用。
定义动态写入函数
为了方便压测，使用动态SQL写入数据，写入到对应的FEED明细表。
create or replace function ins_mx(
int, -- suffix
text, -- GV
int, -- RC
int[], -- v1
int[], -- v2
int[], -- v3
int[], -- v4
int[], -- v5
int[], -- v6
int[], -- v7
int[], -- v8
int[] -- v9
) returns void as $$
declare
begin
execute format(
'
insert into mx%s
(lt,gv,rc,v1,v2,v3,v4,v5,v6,v7,v8,v9)
values
(now(),%L,%s,%L,%L,%L,%L,%L,%L,%L,%L,%L)
',
$1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12
);
end;
$$ language plpgsql strict;
例子
select ins_mx(1,'a',10,gen_randarr(100,10),gen_randarr(100,10),gen_randarr(100,10),gen_randarr(100,10),gen_randarr(100,10),gen_randarr(100,10),gen_randarr(100,10),gen_randarr(100,10),gen_randarr(100,10));
ins_mx
--------
(1 row)
postgres=# select * from mx1;
id | lt | gv | rc | v1 | v2 | v3 | v4 | v5 | v6
| v7 | v8 | v9
----+----------------------------+----+----+-------------------------------+-------------------------------+---------------------------------+--------------------------------+---------------------------------+----------------------------
-----+------------------------------+--------------------------------+--------------------------------
1 | 2018-02-06 16:14:10.874121 | a | 10 | {17,12,33,2,73,67,73,12,87,8} | {99,34,70,82,98,9,53,73,52,8} | {96,32,41,72,98,66,14,68,99,41} | {75,16,53,8,19,26,75,92,38,61} | {99,36,95,70,18,94,79,71,67,31} | {79,62,63,20,35,60,86,49,29
,85} | {89,4,1,42,12,20,68,86,11,6} | {48,10,42,43,80,60,37,59,31,4} | {90,10,66,52,30,0,12,15,49,41}
(1 row)
压测设计1
定义写入压测1
动态，写多表，数组数据为动态数据
1024个随机表，1500个随机分组，2个2500个元素的随机数组。
vi test1.sql
\set suffix random(1,1024)
\set gv random(1,1500)
select ins_mx(:suffix,:gv,2500,gen_randarr(100,2500),gen_randarr(100,2500),'{}','{}','{}','{}','{}','{}','{}');
pgbench -M prepared -n -r -P 1 -f ./test1.sql -c 56 -j 56 -T 120
transaction type: ./test1.sql
scaling factor: 1
query mode: prepared
number of clients: 56
number of threads: 56
duration: 120 s
number of transactions actually processed: 1475459
latency average = 4.554 ms
latency stddev = 1.226 ms
tps = 12267.216576 (including connections establishing)
tps = 12269.288221 (excluding connections establishing)
statement latencies in milliseconds:
0.002 \set suffix random(1,1024)
0.001 \set gv random(1,1500)
4.556 select ins_mx(:suffix,:gv,2500,gen_randarr(100,2500),gen_randarr(100,2500),'{}','{}','{}','{}','{}','{}','{}');
top - 17:53:50 up 26 days, 22:41, 3 users, load average: 34.95, 12.67, 8.52
Tasks: 540 total, 58 running, 482 sleeping, 0 stopped, 0 zombie
%Cpu(s): 95.5 us, 4.1 sy, 0.0 ni, 0.3 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 23094336+total, 18962992+free, 5971540 used, 35341904 buff/cache
KiB Swap: 0 total, 0 free, 0 used. 21054910+avail Mem
定义写入压测2
动态，写多表，数组数据为静态数据
(因为gen_randarr算数据库的开销，实际上这个数组是应用程序传过来的，这里使用静态的数组可以避免数据库这个额外开销，性能更加真实)
vi test2.sql
\set suffix random(1,1024)
\set gv random(1,1500)
select ins_mx(:suffix,:gv,2500,'{12,22,65,18,1,60,69,9,52,28,73,94,95,93,65,99,46,40,64,18,5,6,81,81,99,21,19,35,90,83,48,2,5,13,20,5,73,89,15,25,17,88,18,12,81,84,11,27,24,75,45,28,80,26,9,80,47,28,15,37,11,63,38,16,76,58,21,49,47,36,73,64,24,92,76,5,76,87,32,99,62,77,28,42,3,37,22,49,65,36,86,76,100,25,92,76,83,13,24,30,49,98,94,74,89,71,79,65,58,11,64,19,88,92,62,90,29,83,40,94,20,26,70,19,51,61,95,33,75,19,63,24,17,57,98,6,28,76,71,86,87,36,5,75,28,67,65,57,50,5,51,70,31,21,90,82,82,85,15,57,4,78,81,21,35,78,27,63,55,99,49,42,35,54,17,62,21,82,20,72,87,71,42,18,91,31,0,74,16,15,30,20,93,11,41,29,89,68,92,44,67,41,86,1,95,3,64,17,85,83,88,72,54,30,91,45,61,91,19,77,6,49,97,99,60,38,27,50,6,19,94,73,60,79,74,55,82,38,72,67,21,60,40,75,90,30,20,51,21,39,28,26,88,25,25,49,63,53,98,70,72,92,42,32,71,16,87,53,54,59,21,75,19,60,50,9,91,70,60,11,10,88,38,98,14,63,46,77,16,44,47,88,36,89,20,7,6,7,61,60,66,81,35,86,42,85,95,33,56,55,44,65,43,82,63,57,45,9,34,60,54,81,48,90,70,68,97,75,75,58,35,41,39,70,27,81,56,22,14,12,77,58,77,20,39,40,76,84,50,10,44,3,91,92,93,61,60,91,36,34,48,71,76,88,42,3,69,97,24,82,9,1,40,86,21,79,26,97,63,76,7,7,79,98,99,73,58,58,63,94,93,11,65,68,99,7,71,68,4,95,50,13,96,90,99,17,69,25,14,32,1,21,38,80,18,37,52,77,95,15,71,88,27,37,56,26,43,27,93,48,22,43,61,19,34,60,35,3,85,49,34,86,70,73,65,88,10,18,65,5,33,36,93,60,73,49,86,16,76,79,64,98,22,25,17,56,85,52,59,70,1,93,55,71,66,21,59,76,39,24,81,72,61,73,32,33,22,17,50,98,96,14,96,18,38,13,74,23,65,33,93,66,26,48,37,91,69,97,67,8,21,48,79,82,21,11,15,43,28,65,41,25,78,37,43,17,50,17,40,15,50,32,81,76,80,19,67,49,15,34,57,36,82,36,18,3,48,33,46,76,98,87,1,76,25,43,92,75,61,32,90,11,64,71,86,45,90,53,94,5,88,51,41,69,87,59,72,35,93,18,11,90,6,11,66,30,55,59,5,16,91,95,26,55,66,12,100,56,66,94,61,53,45,2,23,32,62,95,67,54,13,78,45,19,90,11,49,45,70,54,60,61,49,86,16,15,99,16,71,65,10,32,18,55,35,40,87,97,35,54,51,48,33,96,67,22,7,16,67,77,70,27,38,20,13,54,35,12,69,6,77,79,39,95,33,74,35,20,71,70,74,22,19,7,18,86,29,25,2,96,3,72,23,40,92,37,94,27,49,63,33,26,42,72,21,75,46,56,95,17,26,70,39,45,77,57,31,6,82,33,2,85,5,25,25,97,62,19,24,11,82,58,37,24,30,58,99,76,13,94,93,39,64,32,84,41,89,15,47,71,48,49,56,53,74,81,51,37,0,75,48,82,33,85,6,63,42,6,39,56,100,32,95,64,64,80,5,53,95,52,24,43,1,80,96,76,61,47,12,61,22,60,43,55,45,50,18,87,55,56,43,55,89,38,19,53,18,24,5,13,77,29,56,78,9,52,53,70,99,66,31,21,25,74,76,70,24,93,58,79,50,1,34,39,39,53,91,57,77,96,69,54,26,25,32,35,77,85,5,76,50,36,97,76,10,73,46,33,67,4,12,17,4,45,55,43,98,46,100,75,43,69,29,69,94,61,3,71,46,8,48,96,44,45,72,53,19,18,86,85,21,98,2,26,43,57,69,41,4,68,17,47,37,46,15,32,7,19,3,52,27,51,48,70,96,20,23,15,38,9,100,60,7,2,85,50,60,54,91,64,22,8,10,59,54,25,91,61,44,94,13,70,45,62,41,41,82,64,55,20,73,55,80,80,57,65,30,17,18,21,81,41,29,91,100,83,16,91,44,60,85,58,31,30,20,71,70,2,35,25,22,8,80,1,88,38,66,18,55,84,39,35,25,69,26,25,52,42,16,96,3,0,54,33,30,74,4,0,75,39,25,97,47,6,98,35,44,64,53,98,48,92,34,73,60,60,97,12,2,13,9,5,13,63,38,43,37,43,44,12,82,69,9,29,75,7,63,18,70,16,17,18,8,50,91,68,10,88,80,13,1,89,18,15,52,56,58,89,98,2,0,80,71,9,9,45,16,72,64,86,88,81,4,96,31,95,63,41,83,44,54,84,33,72,99,84,28,57,73,26,58,73,6,29,83,15,74,98,87,38,84,75,19,89,71,50,84,34,91,67,78,45,51,10,17,50,95,45,7,68,71,65,41,78,94,23,93,69,22,80,7,6,55,26,95,26,76,79,61,67,46,38,12,97,49,30,47,44,75,53,11,46,18,52,24,12,75,17,81,97,97,88,3,52,14,98,78,89,77,39,56,23,77,69,20,26,99,66,70,74,20,81,20,38,33,44,51,9,61,32,6,58,20,9,10,33,8,88,23,85,27,79,7,5,48,27,31,46,93,1,20,13,82,40,51,15,84,2,23,45,33,29,3,53,39,13,86,46,1,8,31,28,87,38,33,35,65,64,82,58,65,2,71,47,42,22,62,26,24,85,71,57,14,74,10,53,86,95,99,87,4,30,16,91,68,49,26,33,13,8,92,77,10,63,24,51,85,86,78,9,71,49,65,86,23,75,39,9,70,38,96,74,68,12,65,36,61,91,69,73,99,61,51,9,24,75,60,8,61,38,17,32,87,82,17,9,57,56,18,28,93,15,2,61,27,67,97,87,59,66,60,58,27,11,67,50,86,27,59,46,65,76,78,51,58,95,61,16,51,79,43,44,94,45,5,20,12,2,7,71,68,68,29,94,78,95,44,64,22,3,10,87,79,88,38,37,83,99,52,34,78,96,78,71,41,83,92,53,85,99,24,53,67,53,47,45,48,91,9,70,94,19,57,73,7,96,10,90,95,63,24,73,58,2,44,99,85,36,52,70,35,76,23,1,29,70,46,77,61,55,48,55,73,5,29,80,1,39,70,96,1,94,68,59,95,12,59,81,48,11,51,82,88,73,83,17,43,29,94,4,84,42,59,57,47,88,37,48,26,7,43,28,1,11,87,97,23,46,77,71,57,28,53,44,1,36,61,44,65,55,48,49,97,7,6,44,95,43,92,22,51,35,49,52,46,36,48,69,82,26,40,39,53,93,83,54,29,44,98,94,99,46,43,96,54,49,40,49,92,32,71,43,67,20,95,13,57,43,82,39,69,23,78,22,16,61,76,45,6,75,39,5,21,82,2,75,30,42,24,23,74,95,65,40,15,60,53,72,3,35,11,72,58,89,94,74,51,71,19,56,45,58,62,67,39,63,42,70,5,66,92,79,61,58,19,76,18,72,48,21,7,59,93,65,49,87,39,99,57,57,55,3,15,17,69,54,80,11,24,85,76,16,64,37,74,83,13,92,54,61,12,62,21,5,27,69,92,66,68,49,23,24,52,38,41,21,93,21,32,17,6,8,33,70,46,7,53,59,99,7,20,11,69,41,16,96,10,9,61,79,58,85,2,10,23,43,31,16,64,63,32,70,72,65,40,17,72,93,76,71,100,96,82,68,37,99,64,47,7,25,26,65,10,28,76,33,71,7,48,35,70,81,6,42,46,46,60,18,38,36,90,38,32,72,6,69,71,70,16,79,96,42,44,6,70,20,38,42,27,87,77,97,68,83,39,14,28,99,32,67,34,22,5,66,94,11,35,65,81,51,44,77,93,88,83,64,7,21,5,34,8,82,31,76,65,70,89,93,69,22,60,3,44,65,69,38,76,5,3,57,56,46,34,49,34,17,13,41,38,18,75,46,1,5,22,66,75,11,59,44,33,19,47,76,84,16,14,59,20,16,17,76,62,51,26,96,68,39,37,6,57,11,52,58,16,73,24,91,84,82,35,17,1,82,93,85,98,6,44,18,23,61,94,85,11,20,81,79,59,17,85,17,29,36,74,45,9,98,36,93,80,71,10,82,53,3,66,51,9,10,69,32,71,63,16,82,83,97,61,43,14,46,59,43,82,34,88,91,32,24,85,12,96,95,93,49,97,60,99,7,70,68,38,41,31,55,23,14,52,85,57,66,31,16,9,13,50,97,4,81,21,89,93,17,83,87,66,81,47,66,87,17,34,26,58,65,80,81,79,32,65,36,98,96,53,7,9,2,4,13,84,26,1,77,43,85,64,9,66,11,75,53,27,8,79,85,73,59,66,52,91,31,88,89,27,41,96,36,43,100,48,27,25,49,4,68,34,69,77,100,79,52,53,7,60,31,92,33,90,57,85,81,89,73,70,16,13,66,51,56,65,99,83,91,49,88,59,83,56,36,83,36,88,35,42,48,67,34,81,57,91,66,38,80,39,8,96,52,74,47,8,39,46,92,30,95,80,89,78,36,25,61,71,13,96,14,62,63,47,43,20,39,9,58,18,48,66,14,100,40,61,8,79,7,100,8,3,79,97,81,15,22,42,86,35,38,100,97,1,47,40,21,86,49,80,4,96,46,18,96,86,79,3,64,86,3,72,89,82,69,70,97,91,12,84,26,50,84,23,51,31,62,72,17,11,52,21,7,98,39,3,83,18,6,48,4,9,20,93,92,89,63,89,80,75,73,6,24,56,29,76,87,91,48,4,2,100,24,9,98,63,12,81,81,18,29,85,27,49,78,19,38,41,8,18,16,80,24,40,37,53,16,24,44,64,28,46,64,52,55,61,15,67,43,96,85,72,81,12,21,59,30,59,0,38,77,16,18,1,56,55,54,72,79,98,35,6,44,99,59,99,60,74,66,3,70,51,75,51,62,95,11,93,54,11,30,30,27,48,31,83,3,85,55,82,83,90,88,28,89,47,27,49,21,93,52,91,44,26,43,6,21,53,99,75,64,29,5,91,78,37,74,81,22,29,63,6,19,51,33,8,98,60,57,19,53,8,10,97,35,53,4,56,6,2,31,70,32,37,62,9,73,36,90,96,65,53,1,84,5,34,91,3,94,48,22,48,56,32,45,91,85,49,47,91,51,78,62,83,15,23,92,88,59,83,84,24,36,85,8,41,19,99,43,14,47,65,62,3,97,7,94,82,55,40,73,6,18,35,89,33,58,81,21,17,64,5,41,100,89,49,41,9,48,84,22,94,49,84,97,46,90,91,29,46,31,2,52,49,37,41,82,95,22,3,13,86,7,54,86,97,2,27,5,50,11,28,44,60,11,41,6,2,32,34,47,63,36,99,12,74,40,94,69,62,97,81,49,4,35,35,1,38,62,6,88,73,34,32,32,45,73,38,47,5,72,94,68,9,93,80,82,33,74,51,95,71,33,44,75,68,79,76,5,41,82,93,14,15,25,46,60,98,84,7,3,56,1,71,65,93,51,47,26,25,99,22,95,31,66}','{70,99,45,46,4,86,27,97,100,43,22,45,3,20,29,10,22,85,10,93,50,4,44,97,30,68,96,52,64,27,18,34,26,63,79,30,49,7,27,49,49,49,94,52,68,23,62,91,8,72,84,59,76,28,56,6,96,52,57,60,79,75,94,5,38,73,35,87,80,62,35,29,11,29,82,79,53,44,70,61,16,54,20,91,82,76,97,78,28,54,38,7,29,31,12,67,5,48,54,84,10,89,14,21,18,95,0,71,39,70,32,55,24,52,46,6,28,43,84,56,97,21,64,26,53,76,93,57,24,47,42,34,36,56,55,54,51,55,25,90,25,58,44,49,10,90,55,38,33,39,94,29,60,58,55,13,34,48,70,58,95,12,91,31,68,46,85,18,1,10,8,27,68,53,76,78,43,31,16,76,70,10,5,30,68,60,43,2,9,13,60,4,26,51,34,93,97,19,12,98,29,20,25,97,73,1,75,16,32,90,91,2,1,96,32,69,57,76,71,66,89,30,69,15,81,4,8,79,23,19,77,52,39,2,49,12,3,24,28,35,14,19,37,14,15,70,83,72,45,54,38,34,84,7,49,65,11,56,44,34,76,21,86,15,23,35,27,26,59,54,61,73,73,98,87,89,68,70,61,13,24,98,47,7,6,96,73,17,52,16,51,27,37,37,42,59,73,69,85,32,23,46,4,96,44,91,85,11,61,46,24,85,44,71,92,50,67,65,67,18,81,18,46,18,55,88,77,28,57,62,59,80,8,63,76,52,55,61,63,16,7,87,1,51,58,93,1,24,58,67,43,39,85,89,57,40,77,35,68,34,97,27,14,5,91,90,56,45,51,19,62,58,6,63,9,64,56,10,89,14,77,31,53,62,20,11,2,97,45,70,31,42,98,44,47,88,34,3,34,86,23,95,44,29,58,53,93,14,63,81,28,40,13,81,3,33,92,5,30,37,76,60,79,73,5,26,62,39,29,96,24,52,91,68,81,49,21,73,63,84,55,90,25,68,71,27,1,63,33,31,100,8,91,79,82,96,4,43,34,34,39,58,85,30,26,66,79,48,40,41,32,94,32,57,62,3,84,63,66,17,93,66,25,84,45,7,80,49,50,14,83,89,72,68,19,99,34,98,46,74,40,78,68,71,35,30,75,19,93,41,35,86,7,61,70,52,68,50,1,18,64,84,7,36,52,27,34,87,25,81,60,64,59,29,36,93,59,10,12,51,51,47,37,59,8,7,11,75,57,12,93,20,96,1,56,48,27,91,35,52,71,95,16,30,24,52,23,82,63,35,34,14,82,71,73,90,78,84,66,35,96,59,55,92,59,12,40,87,3,75,39,74,70,55,4,94,7,27,77,70,62,11,84,45,82,57,35,60,40,1,95,36,59,50,28,19,62,68,5,65,43,44,39,14,99,43,8,6,70,85,76,32,95,60,77,77,16,12,37,56,13,32,92,72,82,20,90,44,88,96,9,31,40,47,45,38,90,53,44,60,37,20,92,32,80,69,9,96,81,46,52,94,78,44,65,60,64,56,4,52,51,13,83,91,60,28,29,50,80,74,9,17,94,1,50,73,70,59,69,51,5,21,45,83,66,10,42,30,66,46,82,17,59,65,8,19,93,38,68,73,11,77,90,5,79,40,78,49,99,47,0,4,69,45,87,34,55,29,64,21,75,46,38,34,11,46,53,4,84,21,77,95,98,67,0,77,7,79,26,5,26,26,9,95,71,96,29,26,25,94,47,0,40,85,34,51,31,87,55,15,8,32,10,6,99,11,83,6,90,9,11,16,35,21,11,6,17,40,31,42,34,78,42,74,63,77,25,94,64,81,9,71,13,19,77,12,30,60,18,20,69,29,35,3,49,46,9,66,86,40,8,20,18,50,94,81,27,19,74,91,100,83,63,13,2,40,24,32,100,42,52,68,71,87,72,20,33,80,86,19,20,94,39,39,45,33,19,72,52,94,63,52,77,26,64,79,65,89,11,65,31,62,34,2,49,5,22,83,85,8,2,6,3,41,44,48,74,64,20,27,57,83,79,34,9,43,12,74,32,23,40,63,86,73,64,35,78,86,18,63,95,19,69,98,61,14,45,35,77,65,62,35,49,40,68,57,83,81,32,15,4,71,78,90,44,42,25,23,28,42,86,23,61,55,21,22,69,66,57,47,32,19,81,80,59,50,38,42,31,69,57,35,41,34,24,85,76,49,8,5,91,94,28,52,49,49,75,18,15,32,65,47,50,46,28,9,96,65,51,27,35,8,61,76,42,86,61,18,34,68,23,25,62,51,78,11,0,52,29,15,84,94,63,34,40,90,43,36,56,94,62,90,2,23,66,44,9,27,62,44,95,85,69,57,36,47,68,36,99,97,52,83,91,15,17,30,5,60,66,61,54,28,51,56,52,17,100,61,43,62,4,38,47,73,95,84,20,63,20,19,59,72,2,50,87,19,80,92,79,46,52,33,75,3,89,26,20,89,87,63,51,91,2,98,65,97,82,85,59,2,4,19,74,5,69,61,24,49,53,3,95,5,37,70,8,26,96,28,15,83,92,66,75,93,64,40,90,46,24,49,49,28,68,23,33,36,84,58,85,37,61,81,42,97,51,50,23,47,78,38,31,70,4,6,63,69,45,53,15,69,2,63,97,69,86,31,6,70,88,91,7,49,72,48,46,23,98,69,70,76,7,1,46,11,7,9,80,52,61,95,21,63,58,18,32,44,49,38,14,37,29,21,86,1,69,32,24,67,1,95,43,8,96,89,20,2,98,100,54,60,95,75,23,53,93,55,98,42,93,12,79,22,33,65,24,2,97,48,69,98,43,13,7,38,2,26,41,1,26,94,60,21,69,83,74,63,38,72,5,31,84,84,53,17,49,77,19,46,25,89,44,68,1,50,6,4,77,47,4,3,41,65,25,10,47,99,73,85,71,77,16,55,61,70,72,10,46,91,56,72,80,100,39,81,50,45,85,27,92,89,30,33,54,55,43,1,54,16,86,25,94,3,80,55,72,52,65,19,43,21,90,23,21,30,4,71,75,88,98,67,78,28,0,31,83,43,32,37,59,19,61,53,22,41,8,94,93,73,13,36,94,3,59,15,32,62,86,7,51,84,74,28,12,74,60,95,18,92,31,77,11,93,30,33,34,37,26,27,10,39,63,4,42,22,19,74,84,5,81,35,89,56,63,1,30,23,96,48,15,28,25,26,21,54,58,55,92,85,82,2,24,45,6,66,67,26,40,51,31,21,86,20,77,49,22,7,72,18,55,86,46,80,12,66,34,71,21,26,55,3,28,79,48,34,45,15,60,85,66,91,6,52,12,83,1,34,91,72,52,46,59,98,25,71,64,59,41,85,85,97,88,13,76,36,48,21,51,8,6,17,99,13,69,11,96,70,45,87,42,97,32,1,94,58,72,59,17,13,44,2,10,32,15,87,68,63,8,19,71,14,36,70,27,5,81,23,75,26,10,18,23,42,19,17,0,91,75,17,4,19,19,14,51,35,1,19,98,9,39,69,23,75,39,50,81,20,73,56,46,83,74,69,25,92,86,25,83,61,42,87,80,62,1,32,97,2,51,95,11,90,64,34,65,3,84,45,23,57,1,69,40,75,38,65,67,24,91,50,85,33,37,65,95,38,97,92,41,48,87,52,38,51,86,3,54,70,48,77,27,50,46,67,24,85,32,92,9,23,42,93,56,79,59,51,17,56,43,58,4,30,9,42,81,95,45,34,65,93,11,92,43,58,59,67,42,92,59,51,15,1,44,71,80,3,22,97,59,65,55,63,95,65,6,76,60,51,10,25,44,21,17,87,79,76,55,21,68,14,72,83,15,16,53,95,20,76,92,79,41,47,42,36,12,48,12,71,98,22,96,42,43,13,29,22,89,84,43,57,98,15,39,12,31,93,7,51,68,99,30,9,46,72,45,58,19,56,30,18,78,26,60,21,39,89,43,28,73,86,84,71,1,24,83,32,16,91,83,84,90,13,93,36,84,38,94,4,95,24,21,73,50,81,94,88,70,36,16,44,22,0,15,22,24,98,54,40,89,37,24,78,50,18,15,34,56,9,38,51,33,59,23,83,40,17,71,10,53,87,54,75,87,68,97,10,66,51,50,55,88,74,33,38,92,48,72,48,57,10,98,90,69,22,73,9,39,43,19,92,30,73,67,16,41,64,26,7,15,76,62,3,50,96,41,42,43,14,89,0,24,88,90,92,10,63,1,48,6,20,40,36,92,7,52,34,71,79,41,86,55,3,90,5,99,31,47,42,45,37,43,68,24,33,61,34,96,62,82,2,81,23,38,74,30,90,7,1,69,48,87,24,51,77,29,50,8,76,92,53,12,35,22,37,68,82,71,63,44,53,65,25,75,3,99,5,93,6,6,62,54,94,86,5,71,15,55,80,91,48,33,3,82,55,40,50,37,10,13,81,63,79,6,38,82,5,44,75,11,50,37,66,44,23,71,15,38,27,95,28,74,28,31,56,83,71,6,20,81,20,1,44,99,8,82,80,13,26,55,24,75,93,90,19,15,61,34,53,87,29,81,62,57,12,18,39,83,24,59,64,44,60,7,43,68,90,23,81,15,79,5,91,71,95,10,87,56,44,39,43,73,20,5,30,32,23,69,15,47,29,79,92,89,86,34,56,76,58,37,91,36,42,82,7,36,92,94,92,36,33,35,9,54,40,39,86,63,9,2,10,37,80,2,26,67,36,82,43,94,19,34,30,61,15,37,97,7,31,89,43,65,25,52,19,65,92,5,27,1,6,38,38,87,39,64,54,75,47,96,69,66,30,99,27,46,37,24,53,68,14,96,33,38,48,51,3,40,56,31,40,63,68,78,50,7,43,3,83,89,100,52,55,30,51,83,75,87,7,28,55,21,24,88,59,71,39,62,11,96,93,51,59,61,30,8,68,72,11,51,62,11,2,17,41,53,100,16,40,7,44,96,27,67,84,86,39,23,49,50,19,41,1,78,2,31,86,70,3,97,20,65,8,22,82,48,75,82,64,16,89,8,12,16,75,95,2,14,19,51,64,38,92,65,15,94,96,1,64,99,98,84,65,6,6,47,54,82,29,19,98,18,27,9,34,2,5,36,17,24,87,81,61,79,46,77,73,42,78,36,41,76,20,6,81,26,53,36,8,82,54,6,99,81,15,33,83,20,69,100,44,55,80,5,34,26,82,7,68,59,43,9,35,63,15,16,90,68,52,98,50,6,4,49,87,19,82,70,40,51,70,83,6,50,88,40,76,70,47,45,29,90,54,64,53,69,80,43,37,32,41,87,38,45,36,24,65,18,95,4,69,65,88,74,15,76,15,91,46,62,36,76,52,90,40,6,59,20,49,97,51,90,84,89,35,20,14,100,38,8,4,7,73,92,81,88,68,96,79,14,58,15,90,10,5,29,15,64,49,64,61,0,54,45,89,89,65,3,89,3,11,92,10,84,84,91,72,52,87,51,66,45,66,56,55,71,85,70,35,34,34,96,34,88,41,23,76,7,26,65,10,37,57,20,21,42,12,93,94,99,43,60,44,9,15,99,80,100,69,15,33,3,11,67,90,52,90,67,59,16,32,69,53,89,89,74,31,1,67,24,100,10,84,44,19,99,43,99,99,12,14,32,14}','{}','{}','{}','{}','{}','{}','{}');
pgbench -M prepared -n -r -P 1 -f ./test2.sql -c 56 -j 56 -T 120
transaction type: ./test2.sql
scaling factor: 1
query mode: prepared
number of clients: 56
number of threads: 56
duration: 120 s
number of transactions actually processed: 2404622
latency average = 2.794 ms
latency stddev = 2.429 ms
tps = 19903.967701 (including connections establishing)
tps = 19904.929587 (excluding connections establishing)
statement latencies in milliseconds:
0.002 \set suffix random(1,1024)
0.001 \set gv random(1,1500)
2.795 select ins_mx(:suffix,:gv,2500,'{12,2........................
top - 18:02:30 up 26 days, 22:50, 3 users, load average: 16.13, 21.10, 15.97
Tasks: 546 total, 60 running, 486 sleeping, 0 stopped, 0 zombie
%Cpu(s): 92.5 us, 3.8 sy, 0.0 ni, 3.5 id, 0.1 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 23094336+total, 10642340+free, 8362460 used, 11615750+buff/cache
KiB Swap: 0 total, 0 free, 0 used. 17643998+avail Mem
定义写入压测3
静态，写单表，数组数据为静态数据
vi test3.sql
\set gv random(1,1500)
insert into mx1 (lt,gv,rc,v1,v2,v3,v4,v5,v6,v7,v8,v9) values (now(),:gv,2500,'{12,22,65,18,1,60,69,9,52,28,73,94,95,93,65,99,46,40,64,18,5,6,81,81,99,21,19,35,90,83,48,2,5,13,20,5,73,89,15,25,17,88,18,12,81,84,11,27,24,75,45,28,80,26,9,80,47,28,15,37,11,63,38,16,76,58,21,49,47,36,73,64,24,92,76,5,76,87,32,99,62,77,28,42,3,37,22,49,65,36,86,76,100,25,92,76,83,13,24,30,49,98,94,74,89,71,79,65,58,11,64,19,88,92,62,90,29,83,40,94,20,26,70,19,51,61,95,33,75,19,63,24,17,57,98,6,28,76,71,86,87,36,5,75,28,67,65,57,50,5,51,70,31,21,90,82,82,85,15,57,4,78,81,21,35,78,27,63,55,99,49,42,35,54,17,62,21,82,20,72,87,71,42,18,91,31,0,74,16,15,30,20,93,11,41,29,89,68,92,44,67,41,86,1,95,3,64,17,85,83,88,72,54,30,91,45,61,91,19,77,6,49,97,99,60,38,27,50,6,19,94,73,60,79,74,55,82,38,72,67,21,60,40,75,90,30,20,51,21,39,28,26,88,25,25,49,63,53,98,70,72,92,42,32,71,16,87,53,54,59,21,75,19,60,50,9,91,70,60,11,10,88,38,98,14,63,46,77,16,44,47,88,36,89,20,7,6,7,61,60,66,81,35,86,42,85,95,33,56,55,44,65,43,82,63,57,45,9,34,60,54,81,48,90,70,68,97,75,75,58,35,41,39,70,27,81,56,22,14,12,77,58,77,20,39,40,76,84,50,10,44,3,91,92,93,61,60,91,36,34,48,71,76,88,42,3,69,97,24,82,9,1,40,86,21,79,26,97,63,76,7,7,79,98,99,73,58,58,63,94,93,11,65,68,99,7,71,68,4,95,50,13,96,90,99,17,69,25,14,32,1,21,38,80,18,37,52,77,95,15,71,88,27,37,56,26,43,27,93,48,22,43,61,19,34,60,35,3,85,49,34,86,70,73,65,88,10,18,65,5,33,36,93,60,73,49,86,16,76,79,64,98,22,25,17,56,85,52,59,70,1,93,55,71,66,21,59,76,39,24,81,72,61,73,32,33,22,17,50,98,96,14,96,18,38,13,74,23,65,33,93,66,26,48,37,91,69,97,67,8,21,48,79,82,21,11,15,43,28,65,41,25,78,37,43,17,50,17,40,15,50,32,81,76,80,19,67,49,15,34,57,36,82,36,18,3,48,33,46,76,98,87,1,76,25,43,92,75,61,32,90,11,64,71,86,45,90,53,94,5,88,51,41,69,87,59,72,35,93,18,11,90,6,11,66,30,55,59,5,16,91,95,26,55,66,12,100,56,66,94,61,53,45,2,23,32,62,95,67,54,13,78,45,19,90,11,49,45,70,54,60,61,49,86,16,15,99,16,71,65,10,32,18,55,35,40,87,97,35,54,51,48,33,96,67,22,7,16,67,77,70,27,38,20,13,54,35,12,69,6,77,79,39,95,33,74,35,20,71,70,74,22,19,7,18,86,29,25,2,96,3,72,23,40,92,37,94,27,49,63,33,26,42,72,21,75,46,56,95,17,26,70,39,45,77,57,31,6,82,33,2,85,5,25,25,97,62,19,24,11,82,58,37,24,30,58,99,76,13,94,93,39,64,32,84,41,89,15,47,71,48,49,56,53,74,81,51,37,0,75,48,82,33,85,6,63,42,6,39,56,100,32,95,64,64,80,5,53,95,52,24,43,1,80,96,76,61,47,12,61,22,60,43,55,45,50,18,87,55,56,43,55,89,38,19,53,18,24,5,13,77,29,56,78,9,52,53,70,99,66,31,21,25,74,76,70,24,93,58,79,50,1,34,39,39,53,91,57,77,96,69,54,26,25,32,35,77,85,5,76,50,36,97,76,10,73,46,33,67,4,12,17,4,45,55,43,98,46,100,75,43,69,29,69,94,61,3,71,46,8,48,96,44,45,72,53,19,18,86,85,21,98,2,26,43,57,69,41,4,68,17,47,37,46,15,32,7,19,3,52,27,51,48,70,96,20,23,15,38,9,100,60,7,2,85,50,60,54,91,64,22,8,10,59,54,25,91,61,44,94,13,70,45,62,41,41,82,64,55,20,73,55,80,80,57,65,30,17,18,21,81,41,29,91,100,83,16,91,44,60,85,58,31,30,20,71,70,2,35,25,22,8,80,1,88,38,66,18,55,84,39,35,25,69,26,25,52,42,16,96,3,0,54,33,30,74,4,0,75,39,25,97,47,6,98,35,44,64,53,98,48,92,34,73,60,60,97,12,2,13,9,5,13,63,38,43,37,43,44,12,82,69,9,29,75,7,63,18,70,16,17,18,8,50,91,68,10,88,80,13,1,89,18,15,52,56,58,89,98,2,0,80,71,9,9,45,16,72,64,86,88,81,4,96,31,95,63,41,83,44,54,84,33,72,99,84,28,57,73,26,58,73,6,29,83,15,74,98,87,38,84,75,19,89,71,50,84,34,91,67,78,45,51,10,17,50,95,45,7,68,71,65,41,78,94,23,93,69,22,80,7,6,55,26,95,26,76,79,61,67,46,38,12,97,49,30,47,44,75,53,11,46,18,52,24,12,75,17,81,97,97,88,3,52,14,98,78,89,77,39,56,23,77,69,20,26,99,66,70,74,20,81,20,38,33,44,51,9,61,32,6,58,20,9,10,33,8,88,23,85,27,79,7,5,48,27,31,46,93,1,20,13,82,40,51,15,84,2,23,45,33,29,3,53,39,13,86,46,1,8,31,28,87,38,33,35,65,64,82,58,65,2,71,47,42,22,62,26,24,85,71,57,14,74,10,53,86,95,99,87,4,30,16,91,68,49,26,33,13,8,92,77,10,63,24,51,85,86,78,9,71,49,65,86,23,75,39,9,70,38,96,74,68,12,65,36,61,91,69,73,99,61,51,9,24,75,60,8,61,38,17,32,87,82,17,9,57,56,18,28,93,15,2,61,27,67,97,87,59,66,60,58,27,11,67,50,86,27,59,46,65,76,78,51,58,95,61,16,51,79,43,44,94,45,5,20,12,2,7,71,68,68,29,94,78,95,44,64,22,3,10,87,79,88,38,37,83,99,52,34,78,96,78,71,41,83,92,53,85,99,24,53,67,53,47,45,48,91,9,70,94,19,57,73,7,96,10,90,95,63,24,73,58,2,44,99,85,36,52,70,35,76,23,1,29,70,46,77,61,55,48,55,73,5,29,80,1,39,70,96,1,94,68,59,95,12,59,81,48,11,51,82,88,73,83,17,43,29,94,4,84,42,59,57,47,88,37,48,26,7,43,28,1,11,87,97,23,46,77,71,57,28,53,44,1,36,61,44,65,55,48,49,97,7,6,44,95,43,92,22,51,35,49,52,46,36,48,69,82,26,40,39,53,93,83,54,29,44,98,94,99,46,43,96,54,49,40,49,92,32,71,43,67,20,95,13,57,43,82,39,69,23,78,22,16,61,76,45,6,75,39,5,21,82,2,75,30,42,24,23,74,95,65,40,15,60,53,72,3,35,11,72,58,89,94,74,51,71,19,56,45,58,62,67,39,63,42,70,5,66,92,79,61,58,19,76,18,72,48,21,7,59,93,65,49,87,39,99,57,57,55,3,15,17,69,54,80,11,24,85,76,16,64,37,74,83,13,92,54,61,12,62,21,5,27,69,92,66,68,49,23,24,52,38,41,21,93,21,32,17,6,8,33,70,46,7,53,59,99,7,20,11,69,41,16,96,10,9,61,79,58,85,2,10,23,43,31,16,64,63,32,70,72,65,40,17,72,93,76,71,100,96,82,68,37,99,64,47,7,25,26,65,10,28,76,33,71,7,48,35,70,81,6,42,46,46,60,18,38,36,90,38,32,72,6,69,71,70,16,79,96,42,44,6,70,20,38,42,27,87,77,97,68,83,39,14,28,99,32,67,34,22,5,66,94,11,35,65,81,51,44,77,93,88,83,64,7,21,5,34,8,82,31,76,65,70,89,93,69,22,60,3,44,65,69,38,76,5,3,57,56,46,34,49,34,17,13,41,38,18,75,46,1,5,22,66,75,11,59,44,33,19,47,76,84,16,14,59,20,16,17,76,62,51,26,96,68,39,37,6,57,11,52,58,16,73,24,91,84,82,35,17,1,82,93,85,98,6,44,18,23,61,94,85,11,20,81,79,59,17,85,17,29,36,74,45,9,98,36,93,80,71,10,82,53,3,66,51,9,10,69,32,71,63,16,82,83,97,61,43,14,46,59,43,82,34,88,91,32,24,85,12,96,95,93,49,97,60,99,7,70,68,38,41,31,55,23,14,52,85,57,66,31,16,9,13,50,97,4,81,21,89,93,17,83,87,66,81,47,66,87,17,34,26,58,65,80,81,79,32,65,36,98,96,53,7,9,2,4,13,84,26,1,77,43,85,64,9,66,11,75,53,27,8,79,85,73,59,66,52,91,31,88,89,27,41,96,36,43,100,48,27,25,49,4,68,34,69,77,100,79,52,53,7,60,31,92,33,90,57,85,81,89,73,70,16,13,66,51,56,65,99,83,91,49,88,59,83,56,36,83,36,88,35,42,48,67,34,81,57,91,66,38,80,39,8,96,52,74,47,8,39,46,92,30,95,80,89,78,36,25,61,71,13,96,14,62,63,47,43,20,39,9,58,18,48,66,14,100,40,61,8,79,7,100,8,3,79,97,81,15,22,42,86,35,38,100,97,1,47,40,21,86,49,80,4,96,46,18,96,86,79,3,64,86,3,72,89,82,69,70,97,91,12,84,26,50,84,23,51,31,62,72,17,11,52,21,7,98,39,3,83,18,6,48,4,9,20,93,92,89,63,89,80,75,73,6,24,56,29,76,87,91,48,4,2,100,24,9,98,63,12,81,81,18,29,85,27,49,78,19,38,41,8,18,16,80,24,40,37,53,16,24,44,64,28,46,64,52,55,61,15,67,43,96,85,72,81,12,21,59,30,59,0,38,77,16,18,1,56,55,54,72,79,98,35,6,44,99,59,99,60,74,66,3,70,51,75,51,62,95,11,93,54,11,30,30,27,48,31,83,3,85,55,82,83,90,88,28,89,47,27,49,21,93,52,91,44,26,43,6,21,53,99,75,64,29,5,91,78,37,74,81,22,29,63,6,19,51,33,8,98,60,57,19,53,8,10,97,35,53,4,56,6,2,31,70,32,37,62,9,73,36,90,96,65,53,1,84,5,34,91,3,94,48,22,48,56,32,45,91,85,49,47,91,51,78,62,83,15,23,92,88,59,83,84,24,36,85,8,41,19,99,43,14,47,65,62,3,97,7,94,82,55,40,73,6,18,35,89,33,58,81,21,17,64,5,41,100,89,49,41,9,48,84,22,94,49,84,97,46,90,91,29,46,31,2,52,49,37,41,82,95,22,3,13,86,7,54,86,97,2,27,5,50,11,28,44,60,11,41,6,2,32,34,47,63,36,99,12,74,40,94,69,62,97,81,49,4,35,35,1,38,62,6,88,73,34,32,32,45,73,38,47,5,72,94,68,9,93,80,82,33,74,51,95,71,33,44,75,68,79,76,5,41,82,93,14,15,25,46,60,98,84,7,3,56,1,71,65,93,51,47,26,25,99,22,95,31,66}','{70,99,45,46,4,86,27,97,100,43,22,45,3,20,29,10,22,85,10,93,50,4,44,97,30,68,96,52,64,27,18,34,26,63,79,30,49,7,27,49,49,49,94,52,68,23,62,91,8,72,84,59,76,28,56,6,96,52,57,60,79,75,94,5,38,73,35,87,80,62,35,29,11,29,82,79,53,44,70,61,16,54,20,91,82,76,97,78,28,54,38,7,29,31,12,67,5,48,54,84,10,89,14,21,18,95,0,71,39,70,32,55,24,52,46,6,28,43,84,56,97,21,64,26,53,76,93,57,24,47,42,34,36,56,55,54,51,55,25,90,25,58,44,49,10,90,55,38,33,39,94,29,60,58,55,13,34,48,70,58,95,12,91,31,68,46,85,18,1,10,8,27,68,53,76,78,43,31,16,76,70,10,5,30,68,60,43,2,9,13,60,4,26,51,34,93,97,19,12,98,29,20,25,97,73,1,75,16,32,90,91,2,1,96,32,69,57,76,71,66,89,30,69,15,81,4,8,79,23,19,77,52,39,2,49,12,3,24,28,35,14,19,37,14,15,70,83,72,45,54,38,34,84,7,49,65,11,56,44,34,76,21,86,15,23,35,27,26,59,54,61,73,73,98,87,89,68,70,61,13,24,98,47,7,6,96,73,17,52,16,51,27,37,37,42,59,73,69,85,32,23,46,4,96,44,91,85,11,61,46,24,85,44,71,92,50,67,65,67,18,81,18,46,18,55,88,77,28,57,62,59,80,8,63,76,52,55,61,63,16,7,87,1,51,58,93,1,24,58,67,43,39,85,89,57,40,77,35,68,34,97,27,14,5,91,90,56,45,51,19,62,58,6,63,9,64,56,10,89,14,77,31,53,62,20,11,2,97,45,70,31,42,98,44,47,88,34,3,34,86,23,95,44,29,58,53,93,14,63,81,28,40,13,81,3,33,92,5,30,37,76,60,79,73,5,26,62,39,29,96,24,52,91,68,81,49,21,73,63,84,55,90,25,68,71,27,1,63,33,31,100,8,91,79,82,96,4,43,34,34,39,58,85,30,26,66,79,48,40,41,32,94,32,57,62,3,84,63,66,17,93,66,25,84,45,7,80,49,50,14,83,89,72,68,19,99,34,98,46,74,40,78,68,71,35,30,75,19,93,41,35,86,7,61,70,52,68,50,1,18,64,84,7,36,52,27,34,87,25,81,60,64,59,29,36,93,59,10,12,51,51,47,37,59,8,7,11,75,57,12,93,20,96,1,56,48,27,91,35,52,71,95,16,30,24,52,23,82,63,35,34,14,82,71,73,90,78,84,66,35,96,59,55,92,59,12,40,87,3,75,39,74,70,55,4,94,7,27,77,70,62,11,84,45,82,57,35,60,40,1,95,36,59,50,28,19,62,68,5,65,43,44,39,14,99,43,8,6,70,85,76,32,95,60,77,77,16,12,37,56,13,32,92,72,82,20,90,44,88,96,9,31,40,47,45,38,90,53,44,60,37,20,92,32,80,69,9,96,81,46,52,94,78,44,65,60,64,56,4,52,51,13,83,91,60,28,29,50,80,74,9,17,94,1,50,73,70,59,69,51,5,21,45,83,66,10,42,30,66,46,82,17,59,65,8,19,93,38,68,73,11,77,90,5,79,40,78,49,99,47,0,4,69,45,87,34,55,29,64,21,75,46,38,34,11,46,53,4,84,21,77,95,98,67,0,77,7,79,26,5,26,26,9,95,71,96,29,26,25,94,47,0,40,85,34,51,31,87,55,15,8,32,10,6,99,11,83,6,90,9,11,16,35,21,11,6,17,40,31,42,34,78,42,74,63,77,25,94,64,81,9,71,13,19,77,12,30,60,18,20,69,29,35,3,49,46,9,66,86,40,8,20,18,50,94,81,27,19,74,91,100,83,63,13,2,40,24,32,100,42,52,68,71,87,72,20,33,80,86,19,20,94,39,39,45,33,19,72,52,94,63,52,77,26,64,79,65,89,11,65,31,62,34,2,49,5,22,83,85,8,2,6,3,41,44,48,74,64,20,27,57,83,79,34,9,43,12,74,32,23,40,63,86,73,64,35,78,86,18,63,95,19,69,98,61,14,45,35,77,65,62,35,49,40,68,57,83,81,32,15,4,71,78,90,44,42,25,23,28,42,86,23,61,55,21,22,69,66,57,47,32,19,81,80,59,50,38,42,31,69,57,35,41,34,24,85,76,49,8,5,91,94,28,52,49,49,75,18,15,32,65,47,50,46,28,9,96,65,51,27,35,8,61,76,42,86,61,18,34,68,23,25,62,51,78,11,0,52,29,15,84,94,63,34,40,90,43,36,56,94,62,90,2,23,66,44,9,27,62,44,95,85,69,57,36,47,68,36,99,97,52,83,91,15,17,30,5,60,66,61,54,28,51,56,52,17,100,61,43,62,4,38,47,73,95,84,20,63,20,19,59,72,2,50,87,19,80,92,79,46,52,33,75,3,89,26,20,89,87,63,51,91,2,98,65,97,82,85,59,2,4,19,74,5,69,61,24,49,53,3,95,5,37,70,8,26,96,28,15,83,92,66,75,93,64,40,90,46,24,49,49,28,68,23,33,36,84,58,85,37,61,81,42,97,51,50,23,47,78,38,31,70,4,6,63,69,45,53,15,69,2,63,97,69,86,31,6,70,88,91,7,49,72,48,46,23,98,69,70,76,7,1,46,11,7,9,80,52,61,95,21,63,58,18,32,44,49,38,14,37,29,21,86,1,69,32,24,67,1,95,43,8,96,89,20,2,98,100,54,60,95,75,23,53,93,55,98,42,93,12,79,22,33,65,24,2,97,48,69,98,43,13,7,38,2,26,41,1,26,94,60,21,69,83,74,63,38,72,5,31,84,84,53,17,49,77,19,46,25,89,44,68,1,50,6,4,77,47,4,3,41,65,25,10,47,99,73,85,71,77,16,55,61,70,72,10,46,91,56,72,80,100,39,81,50,45,85,27,92,89,30,33,54,55,43,1,54,16,86,25,94,3,80,55,72,52,65,19,43,21,90,23,21,30,4,71,75,88,98,67,78,28,0,31,83,43,32,37,59,19,61,53,22,41,8,94,93,73,13,36,94,3,59,15,32,62,86,7,51,84,74,28,12,74,60,95,18,92,31,77,11,93,30,33,34,37,26,27,10,39,63,4,42,22,19,74,84,5,81,35,89,56,63,1,30,23,96,48,15,28,25,26,21,54,58,55,92,85,82,2,24,45,6,66,67,26,40,51,31,21,86,20,77,49,22,7,72,18,55,86,46,80,12,66,34,71,21,26,55,3,28,79,48,34,45,15,60,85,66,91,6,52,12,83,1,34,91,72,52,46,59,98,25,71,64,59,41,85,85,97,88,13,76,36,48,21,51,8,6,17,99,13,69,11,96,70,45,87,42,97,32,1,94,58,72,59,17,13,44,2,10,32,15,87,68,63,8,19,71,14,36,70,27,5,81,23,75,26,10,18,23,42,19,17,0,91,75,17,4,19,19,14,51,35,1,19,98,9,39,69,23,75,39,50,81,20,73,56,46,83,74,69,25,92,86,25,83,61,42,87,80,62,1,32,97,2,51,95,11,90,64,34,65,3,84,45,23,57,1,69,40,75,38,65,67,24,91,50,85,33,37,65,95,38,97,92,41,48,87,52,38,51,86,3,54,70,48,77,27,50,46,67,24,85,32,92,9,23,42,93,56,79,59,51,17,56,43,58,4,30,9,42,81,95,45,34,65,93,11,92,43,58,59,67,42,92,59,51,15,1,44,71,80,3,22,97,59,65,55,63,95,65,6,76,60,51,10,25,44,21,17,87,79,76,55,21,68,14,72,83,15,16,53,95,20,76,92,79,41,47,42,36,12,48,12,71,98,22,96,42,43,13,29,22,89,84,43,57,98,15,39,12,31,93,7,51,68,99,30,9,46,72,45,58,19,56,30,18,78,26,60,21,39,89,43,28,73,86,84,71,1,24,83,32,16,91,83,84,90,13,93,36,84,38,94,4,95,24,21,73,50,81,94,88,70,36,16,44,22,0,15,22,24,98,54,40,89,37,24,78,50,18,15,34,56,9,38,51,33,59,23,83,40,17,71,10,53,87,54,75,87,68,97,10,66,51,50,55,88,74,33,38,92,48,72,48,57,10,98,90,69,22,73,9,39,43,19,92,30,73,67,16,41,64,26,7,15,76,62,3,50,96,41,42,43,14,89,0,24,88,90,92,10,63,1,48,6,20,40,36,92,7,52,34,71,79,41,86,55,3,90,5,99,31,47,42,45,37,43,68,24,33,61,34,96,62,82,2,81,23,38,74,30,90,7,1,69,48,87,24,51,77,29,50,8,76,92,53,12,35,22,37,68,82,71,63,44,53,65,25,75,3,99,5,93,6,6,62,54,94,86,5,71,15,55,80,91,48,33,3,82,55,40,50,37,10,13,81,63,79,6,38,82,5,44,75,11,50,37,66,44,23,71,15,38,27,95,28,74,28,31,56,83,71,6,20,81,20,1,44,99,8,82,80,13,26,55,24,75,93,90,19,15,61,34,53,87,29,81,62,57,12,18,39,83,24,59,64,44,60,7,43,68,90,23,81,15,79,5,91,71,95,10,87,56,44,39,43,73,20,5,30,32,23,69,15,47,29,79,92,89,86,34,56,76,58,37,91,36,42,82,7,36,92,94,92,36,33,35,9,54,40,39,86,63,9,2,10,37,80,2,26,67,36,82,43,94,19,34,30,61,15,37,97,7,31,89,43,65,25,52,19,65,92,5,27,1,6,38,38,87,39,64,54,75,47,96,69,66,30,99,27,46,37,24,53,68,14,96,33,38,48,51,3,40,56,31,40,63,68,78,50,7,43,3,83,89,100,52,55,30,51,83,75,87,7,28,55,21,24,88,59,71,39,62,11,96,93,51,59,61,30,8,68,72,11,51,62,11,2,17,41,53,100,16,40,7,44,96,27,67,84,86,39,23,49,50,19,41,1,78,2,31,86,70,3,97,20,65,8,22,82,48,75,82,64,16,89,8,12,16,75,95,2,14,19,51,64,38,92,65,15,94,96,1,64,99,98,84,65,6,6,47,54,82,29,19,98,18,27,9,34,2,5,36,17,24,87,81,61,79,46,77,73,42,78,36,41,76,20,6,81,26,53,36,8,82,54,6,99,81,15,33,83,20,69,100,44,55,80,5,34,26,82,7,68,59,43,9,35,63,15,16,90,68,52,98,50,6,4,49,87,19,82,70,40,51,70,83,6,50,88,40,76,70,47,45,29,90,54,64,53,69,80,43,37,32,41,87,38,45,36,24,65,18,95,4,69,65,88,74,15,76,15,91,46,62,36,76,52,90,40,6,59,20,49,97,51,90,84,89,35,20,14,100,38,8,4,7,73,92,81,88,68,96,79,14,58,15,90,10,5,29,15,64,49,64,61,0,54,45,89,89,65,3,89,3,11,92,10,84,84,91,72,52,87,51,66,45,66,56,55,71,85,70,35,34,34,96,34,88,41,23,76,7,26,65,10,37,57,20,21,42,12,93,94,99,43,60,44,9,15,99,80,100,69,15,33,3,11,67,90,52,90,67,59,16,32,69,53,89,89,74,31,1,67,24,100,10,84,44,19,99,43,99,99,12,14,32,14}','{}','{}','{}','{}','{}','{}','{}');
pgbench -M prepared -n -r -P 1 -f ./test3.sql -c 56 -j 56 -T 120
transaction type: ./test3.sql
scaling factor: 1
query mode: prepared
number of clients: 56
number of threads: 56
duration: 120 s
number of transactions actually processed: 2496879
latency average = 2.691 ms
latency stddev = 5.174 ms
tps = 20802.191560 (including connections establishing)
tps = 20803.019308 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set gv random(1,1500)
2.689 insert into mx1 (lt,gv,rc,v1,v2,v3,v4,v5,v6,v7,v8,v9) values (now(),:gv,2500,'{12,22,65,18,.......................
top - 18:04:01 up 26 days, 22:51, 3 users, load average: 11.29, 18.53, 15.60
Tasks: 546 total, 36 running, 510 sleeping, 0 stopped, 0 zombie
%Cpu(s): 44.9 us, 5.4 sy, 0.0 ni, 49.4 id, 0.4 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 23094336+total, 10651434+free, 2351516 used, 12207750+buff/cache
KiB Swap: 0 total, 0 free, 0 used. 18246070+avail Mem
定义统计SQL
例如，1分钟统计的SQL如下：
INSERT INTO agg1 (lt, tu, gv, mid, _cnt, _sum, avg, min, max, p90, p95, p99)
SELECT
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
改成string_agg的写法如下。
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;
定义动态统计函数
将1分钟、5分钟、10分钟的统计SQL定义为动态统计SQL。方便压测，输入FEED LOG的表名suffix，统计区间，以及每个区间的统计记录数限制（比如，通过LIMIT可以满足每个维度每分钟600万个点的需求，或者任意个点的需求，起到压测阈值设置作用.）
create or replace function stat(
int, -- suffix
int, -- limit
text -- interval
) returns void as $$
declare
begin
set enable_seqscan=off;
set max_parallel_workers_per_gather =0;
execute format(
$_$
INSERT INTO agg%s (lt, tu, gv, mid, _cnt, _sum, avg, min, max, p90, p95, p99) -- $1
SELECT
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
同样道理，也可以改成string_agg统计。
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;
统计结果展示
postgres=# select * from agg2;
id | lt | tu | gv | mid | _cnt | _sum | avg | min | max | p90 | p95 | p99
-----+---------------------+----+------+-----+------+------+------------------+-----+-----+-----+-----+-----
1 | 2018-02-06 18:00:00 | 1 | 845 | 1 | 2 | 101 | 50.6836 | 0 | 100 | 91 | 96 | 99
2 | 2018-02-06 18:00:00 | 1 | 1247 | 1 | 1 | 51 | 50.6835999999999 | 0 | 100 | 91 | 96 | 99
3 | 2018-02-06 18:00:00 | 1 | 1337 | 1 | 2 | 101 | 50.6836 | 0 | 100 | 91 | 96 | 99
4 | 2018-02-06 18:00:00 | 1 | 537 | 1 | 1 | 51 | 50.6835999999999 | 0 | 100 | 91 | 96 | 99
5 | 2018-02-06 18:00:00 | 1 | 75 | 1 | 1 | 51 | 50.6835999999999 | 0 | 100 | 91 | 96 | 99
压测设计2
写入、统计并行测试
1、统计压测
例如，以每分钟写入600万个元素为目标，统计指标满足即可。所以这里设置每个区间分别为2400, 12000, 24000。分别代表 2400*2500, 12000*2500, 24000*2500 。即600万，3000万，6000万。
vi stat1.sql
\set suffix random(1,1024)
select stat(:suffix, 2400, '1 min');
vi stat2.sql
\set suffix random(1,1024)
select stat(:suffix, 12000, '5 min');
vi stat3.sql
\set suffix random(1,1024)
select stat(:suffix, 24000, '10 min');
nohup pgbench -M prepared -n -r -P 1 -f ./stat1.sql -c 10 -j 10 -T 1200 >./log.stat1 2>&1 &
nohup pgbench -M prepared -n -r -P 1 -f ./stat2.sql -c 10 -j 10 -T 1200 >./log.stat2 2>&1 &
nohup pgbench -M prepared -n -r -P 1 -f ./stat3.sql -c 10 -j 10 -T 1200 >./log.stat3 2>&1 &
2、写入压测
nohup pgbench -M prepared -n -r -P 1 -f ./test2.sql -c 42 -j 42 -T 1200 >./log.test2 2>&1 &
统计、写入并行测试结果
top - 18:37:57 up 26 days, 23:25, 3 users, load average: 16.60, 11.64, 12.11
Tasks: 562 total, 70 running, 492 sleeping, 0 stopped, 0 zombie
%Cpu(s): 90.7 us, 6.4 sy, 0.0 ni, 2.8 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 23094336+total, 73160104 free, 10510536 used, 14727273+buff/cache
KiB Swap: 0 total, 0 free, 0 used. 19740713+avail Mem
1、写入层面，2773万个点/s。相当于每分钟16.64亿个元素。除以1024个表，每个表约162.5万个元素每分钟。
如果要满足单表600万元素每分钟的写入期望，需要将表调整为256个。
transaction type: ./test2.sql
scaling factor: 1
query mode: prepared
number of clients: 42
number of threads: 42
duration: 1200 s
number of transactions actually processed: 13312185
latency average = 3.786 ms
latency stddev = 3.226 ms
tps = 11091.503135 (including connections establishing)
tps = 11091.624310 (excluding connections establishing)
statement latencies in milliseconds:
0.002 \set suffix random(1,1024)
0.001 \set gv random(1,1500)
3.788 select ins_mx(:suffix,:gv,2500,'{12,22,65,18,1,
2、1分钟级统计，每秒统计383个表，每分钟统计2.3万个表。
超过期望。
transaction type: ./stat1.sql
scaling factor: 1
query mode: prepared
number of clients: 10
number of threads: 10
duration: 1200 s
number of transactions actually processed: 459639
latency average = 26.107 ms
latency stddev = 138.111 ms
tps = 383.032007 (including connections establishing)
tps = 383.033812 (excluding connections establishing)
statement latencies in milliseconds:
0.002 \set suffix random(1,1024)
26.116 select stat(:suffix, 2400, '1 min');
2、5分钟级统计，每秒统计3.82个表，每5分钟统计1146个表。
超过期望。
transaction type: ./stat2.sql
scaling factor: 1
query mode: prepared
number of clients: 10
number of threads: 10
duration: 1200 s
number of transactions actually processed: 4586
latency average = 2617.628 ms
latency stddev = 1478.233 ms
tps = 3.818981 (including connections establishing)
tps = 3.819014 (excluding connections establishing)
statement latencies in milliseconds:
0.004 \set suffix random(1,1024)
2617.625 select stat(:suffix, 12000, '5 min');
3、10分钟级统计，每秒统计1.71个表，每10分钟统计1126个表。
超过期望。
transaction type: ./stat3.sql
scaling factor: 1
query mode: prepared
number of clients: 10
number of threads: 10
duration: 1200 s
number of transactions actually processed: 2060
latency average = 5830.904 ms
latency stddev = 2480.596 ms
tps = 1.713737 (including connections establishing)
tps = 1.713839 (excluding connections establishing)
statement latencies in milliseconds:
0.004 \set suffix random(1,1024)
5830.900 select stat(:suffix, 24000, '10 min');
综合上面三个指标，如果要满足单表600万元素每分钟的写入期望，需要将表的数量调整为256个(换句话说，在本例涉及到的硬件规格下的PG10，可以支持256个表，每个表每分钟600万个元素的写入以及统计。)。
统计和写入都能达到要求。
压测设计3
将suffix调整为256，也就是说写入、统计针对的区间为256个表，测试结果：
top - 19:23:16 up 27 days, 10 min, 3 users, load average: 33.14, 10.55, 15.39
Tasks: 565 total, 71 running, 494 sleeping, 0 stopped, 0 zombie
%Cpu(s): 92.4 us, 6.2 sy, 0.0 ni, 0.9 id, 0.4 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 23094336+total, 1028536 free, 11676488 used, 21823833+buff/cache
KiB Swap: 0 total, 0 free, 0 used. 13886019+avail Mem
写入
transaction type: ./test2.sql
scaling factor: 1
query mode: prepared
number of clients: 24
number of threads: 24
duration: 1200 s
number of transactions actually processed: 12113510
latency average = 2.377 ms
latency stddev = 1.319 ms
tps = 10094.572203 (including connections establishing)
tps = 10094.659895 (excluding connections establishing)
statement latencies in milliseconds:
0.002 \set suffix random(1,256)
0.001 \set gv random(1,1500)
2.374 select ins_mx(:suffix,:gv,2500,'{12,22,65,18,1,60,69,9,52,28,73,9
......
统计
1分钟统计2520张表
transaction type: ./stat1.sql
scaling factor: 1
query mode: prepared
number of clients: 10
number of threads: 10
duration: 1200 s
number of transactions actually processed: 50733
latency average = 237.004 ms
latency stddev = 736.468 ms
tps = 42.130871 (including connections establishing)
tps = 42.131272 (excluding connections establishing)
statement latencies in milliseconds:
0.002 \set suffix random(1,256)
237.062 select stat(:suffix, 2400, '1 min');
5分钟统计11100张表
transaction type: ./stat2.sql
scaling factor: 1
query mode: prepared
number of clients: 10
number of threads: 10
duration: 1200 s
number of transactions actually processed: 45781
latency average = 263.367 ms
latency stddev = 1712.181 ms
tps = 37.788541 (including connections establishing)
tps = 37.788898 (excluding connections establishing)
statement latencies in milliseconds:
0.002 \set suffix random(1,256)
263.433 select stat(:suffix, 12000, '5 min');
10分钟统计19800张表
transaction type: ./stat3.sql
scaling factor: 1
query mode: prepared
number of clients: 10
number of threads: 10
duration: 1200 s
number of transactions actually processed: 41290
latency average = 292.340 ms
latency stddev = 2389.384 ms
tps = 33.831941 (including connections establishing)
tps = 33.832121 (excluding connections establishing)
statement latencies in milliseconds:
0.002 \set suffix random(1,256)
292.436 select stat(:suffix, 24000, '10 min');
写入、统计均满足了600万每分钟的指标。
数据维护调度
1、清理明细
每次TRUNCATE下一个分区。例如0分时，truncate 分区1。10分时，TRUNCATE分区2。… 50分时，TRUNCATE分区0。
例如：
truncate mx1_1;
2、清理统计数据
每次TRUNCATE下一个分区。例如0点时，truncate 分区01。1点时，TRUNCATE分区02。… 23点时，TRUNCATE分区00。
例如：
truncate agg1_1;
truncate操作直接清文件，被清除的数据不会产生WAL日志，很快很快。
小结
1、一个56 Core的RDS PG 10，可以满足256个FEED LOG的写入和统计。（每个feed log表每分钟的写入点数为600万。也就是说整库的写入和统计吞吐约： 15亿点/分钟 ）
2、历史数据的清理可以启用的调度任务，对分区执行truncate.
3、使用到了list分区的功能。注意PG 10的分区，当操作主表时，不管你最终查询、写入、更新的是哪个子表，会对所有子表持对应的锁，所以写入和truncate子表会有冲突，务必加LOCK_TIMEOUT来TRUNCATE子表。
后面我会详细介绍native partition和pg_pathman在锁粒度这块的差异。
《分区表锁粒度差异 - pg_pathman VS native partition table》
4、
《PostgreSQL 多个数组聚合为一维数组加速(array_agg)》
参考
《PostgreSQL 11 分区表用法及增强 - 增加HASH分区支持 (hash, range, list)》
《PostgreSQL 查询涉及分区表过多导致的性能问题 - 性能诊断与优化(大量BIND, spin lock, SLEEP进程)》
《PostgreSQL aggregate function 4 : Hypothetical-Set Aggregate Functions》
《PostgreSQL aggregate function 3 : Aggregate Functions for Ordered-Set》
《PostgreSQL aggregate function 2 : Aggregate Functions for Statistics》
《PostgreSQL aggregate function 1 : General-Purpose Aggregate Functions》
https://github.com/pjungwir/aggs_for_arrays/
https://www.postgresql.org/docs/devel/static/intarray.html