digoal
2023-09-06
PostgreSQL , PolarDB , 数据库 , 教学
欢迎数据库应用开发者参与贡献场景, 在此issue回复即可, 共同建设《沉浸式数据库学习教学素材库》, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.
- 系列课程的核心目标是教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核. 所以面向的对象是数据库的用户、应用开发者、应用架构师、数据库厂商的产品经理、售前售后专家等角色.
本文的实验可以使用永久免费的阿里云云起实验室来完成.
如果你本地有docker环境也可以把镜像拉到本地来做实验:
x86_64机器使用以下docker image:
ARM机器使用以下docker image:
传统数据库测试通常使用标准套件tpcc,tpch,tpcb生成测试数据, 而当我们需要根据不同的业务场景来设计测试数据的特征, 并根据特征生成比较逼真的大规模数据时, 往往不太容易, 需要针对需求开发程序来实现.
另外, 传统数据库的测试模型也比较简单, 通常只能使用标准的tpcc,tpch,tpcb压测软件来实现测试. 无法根据特定业务需求来进行模拟压测.
一、如何生成各种需求、各种类型的随机值
1、100到500内的随机数
postgres=# select 100 + random()*400 ;
?column?
--------------------
335.81542324284186
(1 row)
2、100 到500内的随机整数
postgres=# select 100 + ceil(random()*400)::int ;
?column?
----------
338
(1 row)
3、uuid
postgres=# select gen_random_uuid();
gen_random_uuid
--------------------------------------
84e51794-e19c-40c1-9f8a-2dd80f29bc7a
(1 row)
4、md5
postgres=# select md5(now()::text);
md5
----------------------------------
5af6874991f7122e8db67170040fe0f7
(1 row)
postgres=# select md5(random()::text);
md5
----------------------------------
744094f5f76f66afe4fbacb663ae03dc
(1 row)
5、将任意类型转换为hashvalue
\df *.*hash*
postgres=# select hashtext('helloworld');
hashtext
------------
1836618988
(1 row)
6、随机点
postgres=# select point(random(), random());
point
-----------------------------------------
(0.1549642173067305,0.9623178115174227)
(1 row)
7、多边形
postgres=# select polygon(path '((0,0),(1,1),(2,0))');
polygon
---------------------
((0,0),(1,1),(2,0))
(1 row)
8、路径
postgres=# select path '((0,0),(1,1),(2,0))';
path
---------------------
((0,0),(1,1),(2,0))
(1 row)
9、50到150的随机范围
postgres=# select int8range(50, 50+(random()*100)::int);
int8range
-----------
[50,53)
(1 row)
postgres=# select int8range(50, 50+(random()*100)::int);
int8range
-----------
[50,108)
(1 row)
10、数组
postgres=# select array['a','b','c'];
array
---------
{a,b,c}
(1 row)
SELECT ARRAY(SELECT ARRAY[i, i*2] FROM generate_series(1,5) AS a(i));
array
----------------------------------
{{1,2},{2,4},{3,6},{4,8},{5,10}}
(1 row)
11、随机数组
create or replace function gen_rnd_array(int,int,int) returns int[] as $$
select array(select $1 + ceil(random()*($2-$1))::int from generate_series(1,$3));
$$ language sql strict;
-- 10个取值范围1到100的值组成的数组
postgres=# select gen_rnd_array(1,100,10);
gen_rnd_array
--------------------------------
{4,70,70,77,21,68,93,57,92,97}
(1 row)
下面10个参考:
12、生成随机整数 —— Generate a random integer
-- Function:
-- Generate a random integer
-- Parameters:
-- min_value: Minimum value
-- max_value: Maximum value
create or replace function gen_random_int(min_value int default 1, max_value int default 1000) returns int as
$$
begin
return min_value + round((max_value - min_value) * random());
end;
$$ language plpgsql;
select gen_random_int();
select gen_random_int(1,10);
13、生成随机字母字符串 —— Generate a random alphabetical string
-- Function:
-- Generate a random alphabetical string
-- Parameters:
-- str_length: Length of the string
-- letter_case: Case of letters. Values for option: lower, upper and mixed
create or replace function gen_random_alphabetical_string(str_length int default 10, letter_case text default 'lower') returns text as
$body$
begin
if letter_case in ('lower', 'upper', 'mixed') then
return
case letter_case
when 'lower' then array_to_string(array(select substr('abcdefghijklmnopqrstuvwxyz',(ceil(random()*26))::int, 1) FROM generate_series(1, str_length)), '')
when 'upper' then array_to_string(array(select substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ',(ceil(random()*26))::int, 1) FROM generate_series(1, str_length)), '')
when 'mixed' then array_to_string(array(select substr('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',(ceil(random()*52))::int, 1) FROM generate_series(1, str_length)), '')
else array_to_string(array(select substr('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',(ceil(random()*52))::int, 1) FROM generate_series(1, str_length)), '')
end;
else
RAISE EXCEPTION 'value % for parameter % is not recognized', letter_case, 'letter_case'
Using Hint = 'Use "lower", "upper" or "mixed". The default value is "lower"', ERRCODE ='22023';
end if;
end;
$body$
language plpgsql volatile;
select gen_random_alphabetical_string(10);
select gen_random_alphabetical_string(letter_case => 'lower');
14、生成随机字符串 —— Generate a random alphanumeric string
-- Function:
-- Generate a random alphanumeric string
-- Parameters:
-- str_length: Length of the string
create or replace function gen_random_string(str_length int default 10) returns text as
$body$
select array_to_string(array(select substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',(ceil(random()*62))::int, 1) FROM generate_series(1, $1)), '');
$body$
language sql volatile;
select gen_random_string(10);
15、生成随机时间戳 —— Generate a random timestamp
-- Function:
-- Generate a random timestamp
-- Parameters:
-- start_time: Lower bound of the time
-- end_time: Upper bound of the time
create or replace function gen_random_timestamp(start_time timestamp default date_trunc('year', now()), end_time timestamp default now()) returns timestamp as
$$
begin
return start_time + round((extract(epoch from end_time)- extract(epoch from start_time))* random()) * interval '1 second';
end;
$$ language plpgsql;
select gen_random_timestamp();
select gen_random_timestamp('2017-10-22 10:05:33','2017-10-22 10:05:35');
16、生成随机整型数组 —— Generate a random integer array
-- Function:
-- Generate a random integer array
-- Parameters:
-- max_value: Maximum value of the elements
-- max_length: Maximum length of the array
-- fixed_length: Whether the length of array is fixed. If it is true, the length of array will match max_length.
create or replace function gen_random_int_array(max_value int default 1000, max_length int default 10, fixed_length bool default true ) returns int[] as
$$
begin
return case when not fixed_length then array(select ceil(random()*max_value)::int from generate_series(1,ceil(random()*max_length)::int)) else array(select ceil(random()*max_value)::int from generate_series(1,max_length)) end ;
end;
$$ LANGUAGE plpgsql;
select gen_random_int_array();
17、生成随机字符串数组 —— Generate a random string array
-- Function:
-- Generate a random string array
-- Parameters:
-- str_length: Length of string
-- max_length: Maximum length of the array
-- fixed_length: Whether the length of array is fixed. If it is true, the length of array will match max_length.
create or replace function gen_random_string_array(str_length int default 10, max_length int default 10, fixed_length bool default TRUE ) returns text[] as
$$
declare v_array text[];
declare v_i int;
begin
v_array := array[]::text[];
if fixed_length then
for v_i in select generate_series(1, max_length) loop
v_array := array_append(v_array,array_to_string(array(select substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',(ceil(random()*62))::int, 1) FROM generate_series(1, str_length)), ''));
end loop;
else
for v_i in select generate_series(1,ceil(random()* max_length)::int) loop
v_array := array_append(v_array,array_to_string(array(select substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',(ceil(random()*62))::int, 1) FROM generate_series(1, str_length)), ''));
end loop;
end if;
return v_array;
end;
$$ language plpgsql;
select gen_random_string_array();
select gen_random_string_array(10,5,true);
18、从整数数组中随机选择一个元素 —— Randomly select one element from an integer array
-- Function:
-- Randomly select one element from an integer array
create or replace function select_random_one(list int[]) returns int as
$$
declare v_length int := array_length(list, 1);
begin
return list[1+round((v_length-1)*random())];
end;
$$ language plpgsql;
select select_random_one(array[1,2,3,4]);
19、从字符串数组中随机选择一个元素 —— Randomly select one element from an string-array
-- Function:
-- Randomly select one element from an string-array
-- str_length: Length of string
create or replace function select_random_one(list text[]) returns text as
$$
declare v_length int := array_length(list, 1);
begin
return list[1+round((v_length-1)*random())];
end;
$$ language plpgsql;
select select_random_one(array['abc','def','ghi']);
20、随机生成汉字字符串 —— Generate a random Chinese string
-- Generate a random Chinese string
create or replace function gen_ramdom_chinese_string(str_length int) returns text as
$$
declare
my_char char;
char_string varchar := '';
i int := 0;
begin
while (i < str_length) loop -- chinese 19968..40869
my_char = chr(19968 + round(20901 * random())::int);
char_string := char_string || my_char;
i = i + 1;
end loop;
return char_string;
end;
$$ language plpgsql;
select gen_ramdom_chinese_string(10);
21、随机手机号码生成器，11位手机号 —— Generate a random mobile number
-- Generate a random mobile number
create or replace function gen_random_mobile_number() returns text as
$body$
select 1 || string_agg(col,'') from (select substr('0123456789',(ceil(random()*10))::int, 1) as col FROM generate_series(1, 10)) result;
$body$
language sql volatile;
select gen_random_mobile_number();
22、通过SRF函数生成批量数据
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+------------------------------+-----------------------------------+--------------------------------------------------------------------+------
pg_catalog | generate_series | SETOF bigint | bigint, bigint | func
pg_catalog | generate_series | SETOF bigint | bigint, bigint, bigint | func
pg_catalog | generate_series | SETOF integer | integer, integer | func
pg_catalog | generate_series | SETOF integer | integer, integer, integer | func
pg_catalog | generate_series | SETOF numeric | numeric, numeric | func
pg_catalog | generate_series | SETOF numeric | numeric, numeric, numeric | func
pg_catalog | generate_series | SETOF timestamp with time zone | timestamp with time zone, timestamp with time zone, interval | func
pg_catalog | generate_series | SETOF timestamp without time zone | timestamp without time zone, timestamp without time zone, interval | func
pg_catalog | generate_subscripts | SETOF integer | anyarray, integer | func
pg_catalog | generate_subscripts | SETOF integer | anyarray, integer, boolean | func
返回一批数值、时间戳、或者数组的下标。
例子，生成一批顺序值。
postgres=# select id from generate_series(1,10) t(id);
id
----
1
2
3
4
5
6
7
8
9
10
(10 rows)
23、随机数
random()
例子，生成一批随机整型
postgres=# select (random()*100)::int from generate_series(1,10);
int4
------
14
82
25
75
4
75
26
87
84
22
(10 rows)
24、随机字符串
md5(random()::text)
例子，生成一批随机字符串
postgres=# select md5(random()::text) from generate_series(1,10);
md5
----------------------------------
ba1f4f4b0073f61145a821c14437230d
a76b09292c1449ebdccad39bcb5864c0
d58f5ebe43f631e7b5b82e070a05e929
0c0d3971205dc6bd355e9a60b29a4c6d
bd437e87fd904ed6ecc80ed782abac7d
71aea571d8c0cd536de53fd2be8dd461
e32e105db58f9d39245e3e2b27680812
174f491a2ec7a3498cab45d3ce8a4277
563a7c389722f746378987b9c4d9bede
6e8231c4b7d9a5cfaae2a3e0cef22f24
(10 rows)
25、重复字符串
repeat('abc', 10)
例子，生成重复2次的随机字符串
postgres=# select repeat(md5(random()::text),2) from generate_series(1,10);
repeat
------------------------------------------------------------------
616d0a07a2b61cd923a14cb3bef06252616d0a07a2b61cd923a14cb3bef06252
73bc0d516a46182b484530f5e153085e73bc0d516a46182b484530f5e153085e
e745a65dbe0b4ef0d2a063487bbbe3d6e745a65dbe0b4ef0d2a063487bbbe3d6
90f9b8b18b3eb095f412e3651f0a946c90f9b8b18b3eb095f412e3651f0a946c
b300f78b20ac9a9534a46e9dfd488761b300f78b20ac9a9534a46e9dfd488761
a3d55c275f1e0f828c4e6863d4751d06a3d55c275f1e0f828c4e6863d4751d06
40e609dbe208fc66372b1c829018097140e609dbe208fc66372b1c8290180971
f661298e28403bc3005ac3aebae49e16f661298e28403bc3005ac3aebae49e16
10d0641e40164a238224d2e16a28764710d0641e40164a238224d2e16a287647
450e599890935df576e20c457691c421450e599890935df576e20c457691c421
(10 rows)
26、随机中文
create or replace function gen_hanzi(int) returns text as $$
declare
res text;
begin
if $1 >=1 then
select string_agg(chr(19968+(random()*20901)::int), '') into res from generate_series(1,$1);
return res;
end if;
return null;
end;
$$ language plpgsql strict;
postgres=# select gen_hanzi(10) from generate_series(1,10);
gen_hanzi
----------------------
騾歵癮崪圚祯骤氾準赔
縬寱癱办戾薶窍爉充環
鷊赶輪肸蒹焷尮禀漽湯
庰槖诤蜞礀链惧珿憗腽
憭釃轮訞陡切瀰煈瘐獵
韸琵慆蝾啈響夐捶燚積
菥芉阣瀤樂潾敾糩镽礕
廂垅欳事鎤懯劑搯蔷窡
覤綊伱鳪散噹镄灳毯杸
鳀倯鰂錾牓晟挗觑镈壯
(10 rows)
27、随机数组
create or replace function gen_rand_arr(int,int) returns int[] as $$
select array_agg((random()*$1)::int) from generate_series(1,$2);
$$ language sql strict;
postgres=# select gen_rand_arr(100,10) from generate_series(1,10);
gen_rand_arr
---------------------------------
{69,11,12,70,7,41,81,95,83,17}
{26,79,20,21,64,64,51,90,38,38}
{3,64,46,28,26,55,39,12,69,76}
{66,38,87,78,8,94,18,88,89,1}
{6,14,81,26,36,45,90,87,35,28}
{25,38,91,71,67,17,26,5,29,95}
{82,94,32,69,72,40,63,90,29,51}
{91,34,66,72,60,1,17,50,88,51}
{77,13,89,69,84,56,86,10,61,14}
{5,43,8,38,11,80,78,74,70,6}
(10 rows)
28、连接符
postgres=# select concat('a', ' ', 'b');
concat
--------
a b
(1 row)
29、自定义函数
通过自定义函数，可以生成很多有趣的数据。 例如 随机身份证号
create or replace function gen_id(
a date,
b date
)
returns text as $$
select lpad((random()*99)::int::text, 2, '0') ||
lpad((random()*99)::int::text, 2, '0') ||
lpad((random()*99)::int::text, 2, '0') ||
to_char(a + (random()*(b-a))::int, 'yyyymmdd') ||
lpad((random()*99)::int::text, 2, '0') ||
random()::int ||
(case when random()*10 >9 then 'X' else (random()*9)::int::text end ) ;
$$ language sql strict;
postgres=# select gen_id('1900-01-01', '2017-10-16') from generate_series(1,10);
gen_id
--------------------
25614020061108330X
49507919010403271X
96764619970119860X
915005193407306113
551360192005045415
430005192611170108
299138191310237806
95149919670723980X
542053198501097403
482334198309182411
(10 rows)
二、如何快速生成大量数据
1、通过SRF函数genrate_series快速生成
drop table if exists tbl;
create unlogged table tbl (
id int primary key,
info text,
c1 int,
c2 float,
ts timestamp
);
-- 写入100万条
insert into tbl select id,md5(random()::text),random()*1000,random()*100,clock_timestamp() from generate_series(1,1000000) id;
INSERT 0 1000000
Time: 990.351 ms
postgres=# select * from tbl limit 10;
id | info | c1 | c2 | ts
----+----------------------------------+-----+--------------------+----------------------------
1 | 2861dff7a9005fd07bd565d4c222aefc | 731 | 35.985756074820685 | 2023-09-06 07:34:43.992953
2 | ada46617f699b439ac3749d339a17a37 | 356 | 6.641897326709056 | 2023-09-06 07:34:43.993349
3 | 53e5f281c152abbe2be107273f661dcf | 2 | 79.66681115076746 | 2023-09-06 07:34:43.993352
4 | 42a7ab47ac773966fd80bbfb4a381cc5 | 869 | 39.64575446230825 | 2023-09-06 07:34:43.993352
5 | fc1fe81740821e8099f28578fe602d47 | 300 | 23.26141144641234 | 2023-09-06 07:34:43.993353
6 | 54f85d06b05fa1ad3e6f6c25845a8c99 | 536 | 51.24406182086716 | 2023-09-06 07:34:43.993354
7 | 9aac2fa6715b5136ff08c984cf39b200 | 615 | 60.35335101210144 | 2023-09-06 07:34:43.993355
8 | 227f02f3ce4a6778ae8b95e4b161da8e | 665 | 35.615585743405376 | 2023-09-06 07:34:43.993356
9 | eb2f7c304e9139be23828b764a8334a2 | 825 | 60.37908523246465 | 2023-09-06 07:34:43.993356
10 | dce3b8e11fbcf85e6fd0abca9546447d | 438 | 45.88193344829534 | 2023-09-06 07:34:43.993357
(10 rows)
2、使用plpgsql或inline code, 快速创建分区表.
drop table if exists tbl;
create unlogged table tbl (
id int primary key,
info text,
c1 int,
c2 float,
ts timestamp
) PARTITION BY HASH(id);
do language plpgsql $$
declare
cnt int := 256;
begin
for i in 0..cnt-1 loop
execute format('create unlogged table tbl_%s PARTITION OF tbl FOR VALUES WITH ( MODULUS %s, REMAINDER %s)', i, cnt, i);
end loop;
end;
$$;
insert into tbl select id,md5(random()::text),random()*1000,random()*100,clock_timestamp() from generate_series(1,1000000) id;
INSERT 0 1000000
Time: 1577.707 ms (00:01.578)
3、使用 pgbench 调用自定义SQL文件, 高速写入
drop table if exists tbl;
create unlogged table tbl (
id serial4 primary key,
info text,
c1 int,
c2 float,
ts timestamp
);
vi t.sql
insert into tbl (info,c1,c2,ts) values (md5(random()::text), random()*1000, random()*100, clock_timestamp());
开启10个连接, 执行t.sql共120秒.
pgbench -M prepared -n -r -P 1 -f ./t.sql -c 10 -j 10 -T 120
transaction type: ./t.sql
scaling factor: 1
query mode: prepared
number of clients: 10
number of threads: 10
duration: 120 s
number of transactions actually processed: 18336072
latency average = 0.065 ms
latency stddev = 0.105 ms
initial connection time = 25.519 ms
tps = 152823.214015 (without initial connection time)
statement latencies in milliseconds:
0.065 insert into tbl (info,c1,c2,ts) values (md5(random()::text), random()*1000, random()*100, clock_timestamp());
4、使用 pgbench 内置的 tpcb模型, 自动创建表和数据.
初始化1000万条tpcb数据.
pgbench -i -s 100 --unlogged-tables
测试tpcb读请求
pgbench -M prepared -n -r -P 1 -c 10 -j 10 -S -T 120
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 10
number of threads: 10
duration: 120 s
number of transactions actually processed: 19554665
latency average = 0.061 ms
latency stddev = 0.051 ms
initial connection time = 15.302 ms
tps = 162975.776467 (without initial connection time)
statement latencies in milliseconds:
0.000 \set aid random(1, 100000 * :scale)
0.061 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
测试tpcb读写请求
pgbench -M prepared -n -r -P 1 -c 10 -j 10 -T 120
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: prepared
number of clients: 10
number of threads: 10
duration: 120 s
number of transactions actually processed: 2531643
latency average = 0.474 ms
latency stddev = 0.373 ms
initial connection time = 18.930 ms
tps = 21098.448090 (without initial connection time)
statement latencies in milliseconds:
0.000 \set aid random(1, 100000 * :scale)
0.000 \set bid random(1, 1 * :scale)
0.000 \set tid random(1, 10 * :scale)
0.000 \set delta random(-5000, 5000)
0.045 BEGIN;
0.095 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.068 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.069 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.077 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.061 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.056 END;
5、其他留作业, 读者可以自己思考一下怎么生成?
- tpcc
- tpcds
- tpch
三、如何生成按需求分布的随机值
https://www.postgresql.org/docs/16/pgbench.html
1、pgbench 内置生成按不同的概率特征分布的随机值的函数.
例如在电商业务、游戏业务中, 活跃用户可能占比只有20%, 极度活跃的更少, 如果有一表记录了每个用户的行为, 那么生成的数据可能是高斯分布的.
均匀分布
random ( lb, ub ) → integer
Computes a uniformly-distributed random integer in [lb, ub].
random(1, 10) → an integer between 1 and 10
指数分布
random_exponential ( lb, ub, parameter ) → integer
Computes an exponentially-distributed random integer in [lb, ub], see below.
random_exponential(1, 10, 3.0) → an integer between 1 and 10
高斯分布
random_gaussian ( lb, ub, parameter ) → integer
Computes a Gaussian-distributed random integer in [lb, ub], see below.
random_gaussian(1, 10, 2.5) → an integer between 1 and 10
Zipfian 分布
random_zipfian ( lb, ub, parameter ) → integer
Computes a Zipfian-distributed random integer in [lb, ub], see below.
random_zipfian(1, 10, 1.5) → an integer between 1 and 10
例如
drop table if exists tbl_log;
create unlogged table tbl_log (
uid int, -- 用户id
info text, -- 行为
ts timestamp -- 时间
);
vi t.sql
\set uid random_gaussian(1,1000,2.5)
insert into tbl_log values (:uid, md5(random()::text), now());
pgbench -M prepared -n -r -P 1 -f ./t.sql -c 10 -j 10 -T 120
transaction type: ./t.sql
scaling factor: 1
query mode: prepared
number of clients: 10
number of threads: 10
duration: 120 s
number of transactions actually processed: 21752866
latency average = 0.055 ms
latency stddev = 0.089 ms
initial connection time = 23.170 ms
tps = 181307.721398 (without initial connection time)
statement latencies in milliseconds:
0.000 \set uid random_gaussian(1,1000,2.5)
0.055 insert into tbl_log values (:uid, md5(random()::text), now());
-- 查看分布情况, 产生的记录条数符合高斯分布
select uid,count(*) from tbl_log group by uid order by 2 desc;
uid | count
------+-------
495 | 44221
505 | 44195
484 | 44128
478 | 44089
507 | 44074
499 | 44070
502 | 44069
506 | 44064
516 | 44057
513 | 44057
501 | 44019
....
10 | 2205
989 | 2187
990 | 2185
11 | 2174
9 | 2154
991 | 2139
7 | 2131
6 | 2120
993 | 2109
992 | 2087
5 | 2084
994 | 2066
8 | 2053
995 | 2052
996 | 2042
3 | 2003
4 | 1995
997 | 1985
2 | 1984
999 | 1966
1 | 1919
998 | 1915
1000 | 1890
(1000 rows)
2、pgbench 也可以将接收到的SQL结果作为变量, 从而执行有上下文交换的业务逻辑测试.
drop table if exists tbl;
create unlogged table tbl (
uid int primary key,
info text,
ts timestamp
);
insert into tbl select generate_series(1,1000000), md5(random()::text), now();
drop table if exists tbl_log;
create unlogged table tbl_log (
uid int,
info_before text,
info_after text,
client_inet inet,
client_port int,
ts timestamp
);
vi t.sql
\set uid random(1,1000000)
with a as (
select uid,info from tbl where uid=:uid
)
update tbl set info=md5(random()::text) from a where tbl.uid=a.uid returning a.info as info_before, tbl.info as info_after \gset
insert into tbl_log values (:uid, :info_before, :info_after, inet_client_addr(), inet_client_port(), now());
pgbench -M prepared -n -r -P 1 -f ./t.sql -c 10 -j 10 -T 120
transaction type: ./t.sql
scaling factor: 1
query mode: prepared
number of clients: 10
number of threads: 10
duration: 120 s
number of transactions actually processed: 8306176
latency average = 0.144 ms
latency stddev = 0.117 ms
initial connection time = 23.128 ms
tps = 69224.826220 (without initial connection time)
statement latencies in milliseconds:
0.000 \set uid random(1,1000000)
0.081 with a as (
0.064 insert into tbl_log values (:uid, :info_before, :info_after, inet_client_addr(), inet_client_port(), now());
select * from tbl_log limit 10;
postgres=# select * from tbl_log limit 10;
uid | info_before | info_after | client_inet | client_port | ts
--------+----------------------------------+----------------------------------+-------------+-------------+----------------------------
345609 | b1946507f8c128d18e6f7e41ce22440e | a2df0ff6272ea38a6629b216b61be6e6 | | | 2023-09-06 09:45:27.959822
110758 | 39b6e7ab8ee91edebcd8b20d0a9fc99e | 5996800e06a82ccf5af904e980020157 | | | 2023-09-06 09:45:27.959902
226098 | 71c1983845e006f59b1cb5bd44d34675 | 5ab57b88f67272f4567c17c9fd946d19 | | | 2023-09-06 09:45:27.961955
210657 | 4dc8e7aaeb7b2c323292c6f75c9c5e41 | 0a8a4d58f82639b7e23519b578a64dfa | | | 2023-09-06 09:45:27.962091
898076 | 6b65ce6281880d1922686a200604dee9 | e695ea569fc4747832f7bbada5acbc17 | | | 2023-09-06 09:45:27.962147
117448 | 09f6ab54fea2b6729ff5ea297dbb50e9 | 94da2a284ae4751a60165203e88f1ff7 | | | 2023-09-06 09:45:27.962234
208582 | e8cb577f92e6898ff384fa77c805da00 | 0188ddf40947257009336cdc0862143d | | | 2023-09-06 09:45:27.959403
2241 | cf4ae28a99a5f39f07f7d3e0bcee6940 | d98df58e39c3d8942471179961ffe95d | | | 2023-09-06 09:45:27.96257
466615 | b2c012d9a5db4957753f2b67c80943e3 | f9de82dad0b90138686c9c36774faf5e | | | 2023-09-06 09:45:27.962729
241103 | 9680999428704ffb103b10271e490d14 | d88e38aa975490fcb7e2d81c10fda3c9 | | | 2023-09-06 09:45:27.962784
(10 rows)
四、压测
1、使用pgbench 编写自定义压测脚本.
前面的例子都已经讲过了. 略.
传统数据库通常只能做tpcc,tpch,tpcb标准测试, 无法根据实际的业务场景和需求进行数据的生成、压测, 因此也无法实现提前预知业务压力问题, 提前解决瓶颈的目的.
PostgreSQL|PolarDB 非常灵活, 自定义生成数据的方法非常多, 通过SRF, pgbench等可以快速加载特征数据, 可以根据实际的业务场景和需求进行数据的生成、压测. 可以实现提前预知业务压力问题, 帮助用户提前解决瓶颈.
pgbench
tpcc
tpcb
tpch
tpcds
随机数
思考一下你熟悉的业务, 数据特征, 设计对应的表结构, 生成具有业务特征的数据, 编写业务相关SQL语句, 进行压力测试.
现在chatgpt已经非常的智能, 你可以尝试一下把你的想法告诉chatgpt, 让他来写SQL.
问题: 写一个postgresql函数, 生成随机单词组成的字符串.
回答: 下面是一个示例的 PostgreSQL 函数，用于生成由随机单词组成的字符串：
CREATE OR REPLACE FUNCTION generate_random_string(word_count INTEGER, word_length INTEGER)
RETURNS TEXT AS $$
DECLARE
alphabet TEXT[] := array['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z'];
random_string TEXT := '';
BEGIN
FOR i IN 1..word_count LOOP
DECLARE
random_word TEXT := '';
BEGIN
FOR j IN 1..word_length LOOP
random_word := random_word || alphabet[ceil(random() * array_length(alphabet, 1))];
END LOOP;
random_string := random_string || random_word || ' ';
END;
END LOOP;
RETURN TRIM(random_string);
END;
$$ LANGUAGE plpgsql;
这个函数使用了两个参数：word_count 表示要生成的随机单词的数量，word_length 表示每个随机单词的长度。它同样使用了一个字母表数组 alphabet，在循环中，它从字母表中随机选择一个字母构成单词，并将单词添加到随机字符串中。最后使用 TRIM 函数去除字符串两端的空格，并返回生成的随机字符串。
201711/20171121_01.md 《PostgreSQL 如何快速构建 海量 逼真 测试数据》
202209/20220906_01.md 《股票涨跌幅概率符合高斯分布特征吗? 如何使用PostgreSQL pgbench模拟较为逼真的股票数据?》
202109/20210902_01.md 《PostgreSQL 15 preview - pgbench copy freeze 加速tpc-b测试数据生成》
202108/20210805_08.md 《PostgreSQL 15 preview - pgbench pipeline压测代码优化, 提升性能》
202104/20210407_04.md 《PostgreSQL 14 preview - pgbench 压测工具新增 随机函数 permute(i, size, [seed]) 返回 i 经过重新(随机)映射后 在 [0,size) 范围内的一个值》
202103/20210311_04.md 《PostgreSQL 14 preview - copy freeze 增强, pgbench 支持copy freeze 大幅提升导入性能》
202101/20210116_02.md 《PostgreSQL 14 preview - pgbench 支持冒号常量, 例如时间,timestamp常量》
201912/20191204_05.md 《PostgreSQL 13 preview - pgbench server-side产生tpc-b测试数据(使用generate_series产生大量测试数据)》
201909/20190901_02.md 《PostgreSQL 13 preview - pgbench 内置tpcb支持 pgbench_account 分区表》
201908/20190828_02.md 《PostgreSQL pgbench client_id 变量用途 - 压测时防止线程间锁冲突(未来，代替动态表名,分区表)》
201903/20190331_07.md 《PostgreSQL 12 preview - pgbench 自定义压测script支持一条SQL最多绑定256个变量》
201903/20190331_05.md 《PostgreSQL 12 preview - pgbench 压测工具编程能力增强 - gset 支持SQL结果返回并存入变量使用》
201809/20180919_04.md 《PostgreSQL pgbench tpcb 海量数据库测试 - 分区表测试优化》
201809/20180919_03.md 《PostgreSQL pgbench tpcb 数据生成与SQL部分源码解读》
201805/20180519_11.md 《PostgreSQL 11 preview - pgbench 压测软件增强》
201805/20180516_02.md 《PostgreSQL pgbench 支持100万连接》
201803/20180325_03.md 《PostgreSQL 11 preview - pgbench 支持大于1000链接(ppoll()代替select())》
201712/20171222_01.md 《PostgreSQL pgbench : 冒号处理 常量包含冒号。》
201711/20171119_01.md 《PostgreSQL 11 preview - pgbench 变量、函数扩展 - 暨pgbench 自定义 benchmark讲解》
201610/20161031_02.md 《PostgreSQL 使用 pgbench 测试 sysbench 相关case - pg_oltp_bench》
201609/20160926_03.md 《PostgreSQL pgbench SQL RT 与 事务RT 浅析》
201506/20150618_01.md 《生成泊松、高斯、指数、随机分布数据 - PostgreSQL 9.5 new feature - pgbench improve, gaussian (standard normal) & exponential distribution》
201901/20190109_01.md 《PostgreSQL native partition 分区表性能优化之 - 动态SQL+服务端绑定变量》
201711/20171107_48.md 《HTAP数据库 PostgreSQL 场景与性能测试之 47 - (OLTP多模优化) 空间应用 - 高并发空间位置更新、多属性KNN搜索并测（含空间索引）末端配送、新零售类项目》
201711/20171107_47.md 《HTAP数据库 PostgreSQL 场景与性能测试之 46 - (OLTP) 大json字段的高并发更新》
201711/20171107_46.md 《HTAP数据库 PostgreSQL 场景与性能测试之 45 - (OLTP) 数据量与性能的线性关系(10亿+无衰减), 暨单表多大需要分区》
201711/20171107_45.md 《[未完待续] HTAP数据库 PostgreSQL 场景与性能测试之 44 - (OLTP) 空间应用 - 空间包含查询(输入多边形 包含 表内空间对象)》
201711/20171107_44.md 《HTAP数据库 PostgreSQL 场景与性能测试之 43 - (OLTP+OLAP) unlogged table 含索引多表批量写入》
201711/20171107_43.md 《HTAP数据库 PostgreSQL 场景与性能测试之 42 - (OLTP+OLAP) unlogged table 不含索引多表批量写入》
201711/20171107_42.md 《HTAP数据库 PostgreSQL 场景与性能测试之 41 - (OLTP+OLAP) 含索引多表批量写入》
201711/20171107_41.md 《HTAP数据库 PostgreSQL 场景与性能测试之 40 - (OLTP+OLAP) 不含索引多表批量写入》
201711/20171107_40.md 《HTAP数据库 PostgreSQL 场景与性能测试之 39 - (OLTP+OLAP) logged & unlogged table 含索引多表单点写入》
201711/20171107_39.md 《HTAP数据库 PostgreSQL 场景与性能测试之 38 - (OLTP+OLAP) logged & unlogged table 不含索引多表单点写入》
201711/20171107_38.md 《HTAP数据库 PostgreSQL 场景与性能测试之 37 - (OLTP+OLAP) 含索引单表批量写入》
201711/20171107_37.md 《HTAP数据库 PostgreSQL 场景与性能测试之 36 - (OLTP+OLAP) 不含索引单表批量写入》
201711/20171107_36.md 《HTAP数据库 PostgreSQL 场景与性能测试之 35 - (OLTP+OLAP) 含索引单表单点写入》
201711/20171107_35.md 《HTAP数据库 PostgreSQL 场景与性能测试之 34 - (OLTP+OLAP) 不含索引单表单点写入》
201711/20171107_34.md 《HTAP数据库 PostgreSQL 场景与性能测试之 33 - (OLAP) 物联网 - 线性字段区间实时统计》
201711/20171107_33.md 《HTAP数据库 PostgreSQL 场景与性能测试之 32 - (OLTP) 高吞吐数据进出(堆存、行扫、无需索引) - 阅后即焚(JSON + 函数流式计算)》
201711/20171107_32.md 《HTAP数据库 PostgreSQL 场景与性能测试之 31 - (OLTP) 高吞吐数据进出(堆存、行扫、无需索引) - 阅后即焚(读写大吞吐并测)》
201711/20171107_31.md 《HTAP数据库 PostgreSQL 场景与性能测试之 30 - (OLTP) 秒杀 - 高并发单点更新》
201711/20171107_30.md 《HTAP数据库 PostgreSQL 场景与性能测试之 29 - (OLTP) 空间应用 - 高并发空间位置更新（含空间索引）》
201711/20171107_29.md 《HTAP数据库 PostgreSQL 场景与性能测试之 28 - (OLTP) 高并发点更新》
201711/20171107_28.md 《HTAP数据库 PostgreSQL 场景与性能测试之 27 - (OLTP) 物联网 - FEED日志, 流式处理 与 阅后即焚 (CTE)》
201711/20171107_27.md 《HTAP数据库 PostgreSQL 场景与性能测试之 26 - (OLTP) NOT IN、NOT EXISTS 查询》
201711/20171107_26.md 《HTAP数据库 PostgreSQL 场景与性能测试之 25 - (OLTP) IN , EXISTS 查询》
201711/20171107_25.md 《HTAP数据库 PostgreSQL 场景与性能测试之 24 - (OLTP) 物联网 - 时序数据并发写入(含时序索引BRIN)》
201711/20171107_24.md 《HTAP数据库 PostgreSQL 场景与性能测试之 23 - (OLAP) 并行计算》
201711/20171107_23.md 《HTAP数据库 PostgreSQL 场景与性能测试之 22 - (OLTP) merge insert|upsert|insert on conflict|合并写入》
201711/20171107_22.md 《HTAP数据库 PostgreSQL 场景与性能测试之 21 - (OLTP+OLAP) 排序、建索引》
201711/20171107_21.md 《HTAP数据库 PostgreSQL 场景与性能测试之 20 - (OLAP) 用户画像圈人场景 - 多个字段任意组合条件筛选与透视》
201711/20171107_20.md 《HTAP数据库 PostgreSQL 场景与性能测试之 19 - (OLAP) 用户画像圈人场景 - 数组相交查询与聚合》
201711/20171107_19.md 《HTAP数据库 PostgreSQL 场景与性能测试之 18 - (OLAP) 用户画像圈人场景 - 数组包含查询与聚合》
201711/20171107_18.md 《HTAP数据库 PostgreSQL 场景与性能测试之 17 - (OLTP) 数组相似查询》
201711/20171107_17.md 《HTAP数据库 PostgreSQL 场景与性能测试之 16 - (OLTP) 文本特征向量 - 相似特征(海明...)查询》
201711/20171107_16.md 《HTAP数据库 PostgreSQL 场景与性能测试之 15 - (OLTP) 物联网 - 查询一个时序区间的数据》
201711/20171107_15.md 《HTAP数据库 PostgreSQL 场景与性能测试之 14 - (OLTP) 字符串搜索 - 全文检索》
201711/20171107_14.md 《HTAP数据库 PostgreSQL 场景与性能测试之 13 - (OLTP) 字符串搜索 - 相似查询》
201711/20171107_13.md 《HTAP数据库 PostgreSQL 场景与性能测试之 12 - (OLTP) 字符串搜索 - 前后模糊查询》
201711/20171107_12.md 《HTAP数据库 PostgreSQL 场景与性能测试之 11 - (OLTP) 字符串搜索 - 后缀查询》
201711/20171107_11.md 《HTAP数据库 PostgreSQL 场景与性能测试之 10 - (OLTP) 字符串搜索 - 前缀查询》
201711/20171107_10.md 《HTAP数据库 PostgreSQL 场景与性能测试之 9 - (OLTP) 字符串模糊查询 - 含索引实时写入》
201711/20171107_09.md 《HTAP数据库 PostgreSQL 场景与性能测试之 8 - (OLTP) 多值类型(数组)含索引实时写入》
201711/20171107_08.md 《HTAP数据库 PostgreSQL 场景与性能测试之 7 - (OLTP) 全文检索 - 含索引实时写入》
201711/20171107_07.md 《HTAP数据库 PostgreSQL 场景与性能测试之 6 - (OLTP) 空间应用 - KNN查询（搜索附近对象，由近到远排序输出）》
201711/20171107_06.md 《HTAP数据库 PostgreSQL 场景与性能测试之 5 - (OLTP) 空间应用 - 空间包含查询(表内多边形 包含 输入空间对象)》
201711/20171107_05.md 《HTAP数据库 PostgreSQL 场景与性能测试之 4 - (OLAP) 大表OUTER JOIN统计查询》
201711/20171107_49.md 《HTAP数据库 PostgreSQL 场景与性能测试之 3.1 - (OLAP) 大表JOIN统计查询-10亿 join 1亿 agg》
201711/20171107_04.md 《HTAP数据库 PostgreSQL 场景与性能测试之 3 - (OLAP) 大表JOIN统计查询》
201711/20171107_03.md 《HTAP数据库 PostgreSQL 场景与性能测试之 2 - (OLTP) 多表JOIN》
201711/20171107_02.md 《HTAP数据库 PostgreSQL 场景与性能测试之 1 - (OLTP) 点查》
201611/20161129_01.md 《PostgreSQL 源码性能诊断(perf profiling)指南(含火焰图生成分析FlameGraph) - 珍藏级》
https://www.cnblogs.com/xianghuaqiang/p/14425274.html
202209/20220901_05.md 《DuckDB 采用外部 parquet 格式存储 - tpch 测试 - in_memory VS in_parquet》
202208/20220829_01.md 《DuckDB vs PostgreSQL TPC-H 测试》
202208/20220828_01.md 《DuckDB TPC-H, TPC-DS 测试》
202208/20220826_02.md 《DuckDB 定位OLAP方向的SQLite, 适合嵌入式数据分析 - tpch测试与简单试用》
202109/20210902_01.md 《PostgreSQL 15 preview - pgbench copy freeze 加速tpc-b测试数据生成》
202008/20200803_07.md 《PostgreSQL 13 新增 hash 内存参数, 提高hash操作性能 - hash_mem_multiplier - work_mem的倍数 - 支持超越内存时采用disk 存储hash表, 支持更多场景使用hashagg, TPCH性能大幅提升》
201912/20191204_05.md 《PostgreSQL 13 preview - pgbench server-side产生tpc-b测试数据(使用generate_series产生大量测试数据)》
201911/20191121_01.md 《阿里云RDS PG 11支持ESSD pl3 存储，满足企业超高性能数据库要求 - 含tpcc测试结果》
201910/20191015_01.md 《PostgreSQL 12 tpcc 测试 - use sysbench-tpcc by Percona-Lab》
201909/20190901_02.md 《PostgreSQL 13 preview - pgbench 内置tpcb支持 pgbench_account 分区表》
201809/20180920_01.md 《PostgreSQL 11 1Kw TPCC , 1亿 TPCB 7*24 强压耐久测试》
201809/20180919_04.md 《PostgreSQL pgbench tpcb 海量数据库测试 - 分区表测试优化》
201809/20180919_03.md 《PostgreSQL pgbench tpcb 数据生成与SQL部分源码解读》
201809/20180919_01.md 《PostgreSQL 11 1万亿 tpcb 性能测试 on 阿里云ECS + ESSD + zfs/lvm2条带 + block_size=32K》
201809/20180917_01.md 《PostgreSQL 11 1000亿 tpcb、1000W tpcc 性能测试 - on 阿里云ECS + ESSD (含quorum based 0丢失多副本配置与性能测试)》
201809/20180916_01.md 《PostgreSQL 11 100亿 tpcb 性能测试 on ECS》
201809/20180913_01.md 《PostgreSQL 11 tpcc 测试(103万tpmC on ECS) - use sysbench-tpcc by Percona-Lab》
201809/20180903_02.md 《Deepgreen(Greenplum) 多机部署测试 , TPC-H VS citus》
201808/20180829_01.md 《PostgreSQL sharding : citus 系列2 - TPC-H》
201808/20180826_01.md 《[转载] TPCH 22条SQL语句分析》
201808/20180824_02.md 《PostgreSQL sharding : citus 系列1 - 多机部署（含OLTP(TPC-B)测试）- 含Citus MX模式》
201808/20180823_01.md 《(TPC-H测试 SF=10,SF=200) PostgreSQL 11 vs 10 vs Deepgreen》
201805/20180530_01.md 《PostgreSQL 推荐 TPC-C 测试工具 sqlbench》
201707/20170714_01.md 《TPC-H测试 - PostgreSQL 10 vs Deepgreen(Greenplum)》
201702/20170207_02.md 《[转自叶大师博文] tpcc-mysql安装、使用、结果解读》
201611/20161108_01.md 《PostgreSQL 并行计算tpc-h测试和优化分析》
201610/20161011_02.md 《Greenplum,HAWQ interval parser带来的问题 - TPCH 测试注意啦》
201606/20160604_01.md 《PostgreSQL on ECS 横向版本TPCB性能测试》
201601/20160111_02.md 《BenchmarkSQL 测试Oracle 12c TPC-C 性能 (含个人测试结果)》
201601/20160109_01.md 《BenchmarkSQL 测试PostgreSQL 9.5.0 TPC-C 性能》
201512/20151223_02.md 《BenchmarkSQL 测试Oracle 12c TPC-C 性能》
