1.背景
欢迎数据库应用开发者参与贡献场景, 在此issue回复即可, 共同建设《沉浸式数据库学习教学素材库》, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.
- 系列课程的核心目标是教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核. 所以面向的对象是数据库的用户、应用开发者、应用架构师、数据库厂商的产品经理、售前售后专家等角色.
本文的实验可以使用永久免费的阿里云云起实验室来完成.
如果你本地有docker环境也可以把镜像拉到本地来做实验:
x86_64机器使用以下docker image:
ARM机器使用以下docker image:
2.业务场景1 介绍: 如何快速构建 海量 逼真 测试数据
2.1实现和对照
2.1.1传统方法 设计和实验
传统数据库测试通常使用标准套件tpcc,tpch,tpcb生成测试数据, 而当我们需要根据不同的业务场景来设计测试数据的特征, 并根据特征生成比较逼真的大规模数据时, 往往不太容易, 需要针对需求开发程序来实现.
另外, 传统数据库的测试模型也比较简单, 通常只能使用标准的tpcc,tpch,tpcb压测软件来实现测试. 无法根据特定业务需求来进行模拟压测.
2.1.2PolarDB|PG新方法1 设计和实验
2.1.2.1如何生成各种需求、各种类型的随机值
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)
2.1.2.2如何快速生成大量数据
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
2.1.2.3如何生成按需求分布的随机值
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)
2.1.2.4压测
1、使用pgbench 编写自定义压测脚本.
前面的例子都已经讲过了. 略.
2.1.3对照
传统数据库通常只能做tpcc,tpch,tpcb标准测试, 无法根据实际的业务场景和需求进行数据的生成、压测, 因此也无法实现提前预知业务压力问题, 提前解决瓶颈的目的.
PostgreSQL|PolarDB 非常灵活, 自定义生成数据的方法非常多, 通过SRF, pgbench等可以快速加载特征数据, 可以根据实际的业务场景和需求进行数据的生成、压测. 可以实现提前预知业务压力问题, 帮助用户提前解决瓶颈.
3.知识点
pgbench
tpcc
tpcb
tpch
tpcds
随机数
4.思考
思考一下你熟悉的业务, 数据特征, 设计对应的表结构, 生成具有业务特征的数据, 编写业务相关SQL语句, 进行压力测试.
5.参考
- 《PostgreSQL 如何快速构建 海量 逼真 测试数据》
- 《股票涨跌幅概率符合高斯分布特征吗? 如何使用PostgreSQL pgbench模拟较为逼真的股票数据?》
- 《PostgreSQL 15 preview - pgbench copy freeze 加速tpc-b测试数据生成》
- 《PostgreSQL 15 preview - pgbench pipeline压测代码优化, 提升性能》
- 《PostgreSQL 14 preview - pgbench 压测工具新增 随机函数 permute(i, size, [seed]) 返回 i 经过重新(随机)映射后 在 [0,size) 范围内的一个值》
- 《PostgreSQL 14 preview - copy freeze 增强, pgbench 支持copy freeze 大幅提升导入性能》
- 《PostgreSQL 14 preview - pgbench 支持冒号常量, 例如时间,timestamp常量》
- 《PostgreSQL 13 preview - pgbench server-side产生tpc-b测试数据(使用generate_series产生大量测试数据)》
- 《PostgreSQL 13 preview - pgbench 内置tpcb支持 pgbench_account 分区表》
- 《PostgreSQL pgbench client_id 变量用途 - 压测时防止线程间锁冲突(未来,代替动态表名,分区表)》
- 《PostgreSQL 12 preview - pgbench 自定义压测script支持一条SQL最多绑定256个变量》
- 《PostgreSQL 12 preview - pgbench 压测工具编程能力增强 - gset 支持SQL结果返回并存入变量使用》
- 《PostgreSQL pgbench tpcb 海量数据库测试 - 分区表测试优化》
- 《PostgreSQL pgbench tpcb 数据生成与SQL部分源码解读》
- 《PostgreSQL 11 preview - pgbench 压测软件增强》
- 《PostgreSQL pgbench 支持100万连接》
- 《PostgreSQL 11 preview - pgbench 支持大于1000链接(ppoll()代替select())》
- 《PostgreSQL pgbench : 冒号处理 常量包含冒号。》
- 《PostgreSQL 11 preview - pgbench 变量、函数扩展 - 暨pgbench 自定义 benchmark讲解》
- 《PostgreSQL 使用 pgbench 测试 sysbench 相关case - pg_oltp_bench》
- 《PostgreSQL pgbench SQL RT 与 事务RT 浅析》
- 《生成泊松、高斯、指数、随机分布数据 - PostgreSQL 9.5 new feature - pgbench improve, gaussian (standard normal) & exponential distribution》
- 《PostgreSQL native partition 分区表性能优化之 - 动态SQL+服务端绑定变量》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 47 - (OLTP多模优化) 空间应用 - 高并发空间位置更新、多属性KNN搜索并测(含空间索引)末端配送、新零售类项目》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 46 - (OLTP) 大json字段的高并发更新》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 45 - (OLTP) 数据量与性能的线性关系(10亿+无衰减), 暨单表多大需要分区》
- 《[未完待续] HTAP数据库 PostgreSQL 场景与性能测试之 44 - (OLTP) 空间应用 - 空间包含查询(输入多边形 包含 表内空间对象)》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 43 - (OLTP+OLAP) unlogged table 含索引多表批量写入》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 42 - (OLTP+OLAP) unlogged table 不含索引多表批量写入》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 41 - (OLTP+OLAP) 含索引多表批量写入》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 40 - (OLTP+OLAP) 不含索引多表批量写入》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 39 - (OLTP+OLAP) logged & unlogged table 含索引多表单点写入》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 38 - (OLTP+OLAP) logged & unlogged table 不含索引多表单点写入》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 37 - (OLTP+OLAP) 含索引单表批量写入》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 36 - (OLTP+OLAP) 不含索引单表批量写入》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 35 - (OLTP+OLAP) 含索引单表单点写入》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 34 - (OLTP+OLAP) 不含索引单表单点写入》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 33 - (OLAP) 物联网 - 线性字段区间实时统计》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 32 - (OLTP) 高吞吐数据进出(堆存、行扫、无需索引) - 阅后即焚(JSON + 函数流式计算)》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 31 - (OLTP) 高吞吐数据进出(堆存、行扫、无需索引) - 阅后即焚(读写大吞吐并测)》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 30 - (OLTP) 秒杀 - 高并发单点更新》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 29 - (OLTP) 空间应用 - 高并发空间位置更新(含空间索引)》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 28 - (OLTP) 高并发点更新》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 27 - (OLTP) 物联网 - FEED日志, 流式处理 与 阅后即焚 (CTE)》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 26 - (OLTP) NOT IN、NOT EXISTS 查询》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 25 - (OLTP) IN , EXISTS 查询》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 24 - (OLTP) 物联网 - 时序数据并发写入(含时序索引BRIN)》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 23 - (OLAP) 并行计算》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 22 - (OLTP) merge insert|upsert|insert on conflict|合并写入》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 21 - (OLTP+OLAP) 排序、建索引》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 20 - (OLAP) 用户画像圈人场景 - 多个字段任意组合条件筛选与透视》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 19 - (OLAP) 用户画像圈人场景 - 数组相交查询与聚合》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 18 - (OLAP) 用户画像圈人场景 - 数组包含查询与聚合》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 17 - (OLTP) 数组相似查询》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 16 - (OLTP) 文本特征向量 - 相似特征(海明...)查询》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 15 - (OLTP) 物联网 - 查询一个时序区间的数据》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 14 - (OLTP) 字符串搜索 - 全文检索》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 13 - (OLTP) 字符串搜索 - 相似查询》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 12 - (OLTP) 字符串搜索 - 前后模糊查询》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 11 - (OLTP) 字符串搜索 - 后缀查询》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 10 - (OLTP) 字符串搜索 - 前缀查询》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 9 - (OLTP) 字符串模糊查询 - 含索引实时写入》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 8 - (OLTP) 多值类型(数组)含索引实时写入》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 7 - (OLTP) 全文检索 - 含索引实时写入》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 6 - (OLTP) 空间应用 - KNN查询(搜索附近对象,由近到远排序输出)》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 5 - (OLTP) 空间应用 - 空间包含查询(表内多边形 包含 输入空间对象)》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 4 - (OLAP) 大表OUTER JOIN统计查询》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 3.1 - (OLAP) 大表JOIN统计查询-10亿 join 1亿 agg》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 3 - (OLAP) 大表JOIN统计查询》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 2 - (OLTP) 多表JOIN》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 1 - (OLTP) 点查》
- 《PostgreSQL 源码性能诊断(perf profiling)指南(含火焰图生成分析FlameGraph) - 珍藏级》
- https://www.cnblogs.com/xianghuaqiang/p/14425274.html
- 《DuckDB 采用外部 parquet 格式存储 - tpch 测试 - in_memory VS in_parquet》
- 《DuckDB vs PostgreSQL TPC-H 测试》
- 《DuckDB TPC-H, TPC-DS 测试》
- 《DuckDB 定位OLAP方向的SQLite, 适合嵌入式数据分析 - tpch测试与简单试用》
- 《PostgreSQL 15 preview - pgbench copy freeze 加速tpc-b测试数据生成》
- 《PostgreSQL 13 新增 hash 内存参数, 提高hash操作性能 - hash_mem_multiplier - work_mem的倍数 - 支持超越内存时采用disk 存储hash表, 支持更多场景使用hashagg, TPCH性能大幅提升》
- 《PostgreSQL 13 preview - pgbench server-side产生tpc-b测试数据(使用generate_series产生大量测试数据)》
- 《阿里云RDS PG 11支持ESSD pl3 存储,满足企业超高性能数据库要求 - 含tpcc测试结果》
- 《PostgreSQL 12 tpcc 测试 - use sysbench-tpcc by Percona-Lab》
- 《PostgreSQL 13 preview - pgbench 内置tpcb支持 pgbench_account 分区表》
- 《PostgreSQL 11 1Kw TPCC , 1亿 TPCB 7*24 强压耐久测试》
- 《PostgreSQL pgbench tpcb 海量数据库测试 - 分区表测试优化》
- 《PostgreSQL pgbench tpcb 数据生成与SQL部分源码解读》
- 《PostgreSQL 11 1万亿 tpcb 性能测试 on 阿里云ECS + ESSD + zfs/lvm2条带 + block_size=32K》
- 《PostgreSQL 11 1000亿 tpcb、1000W tpcc 性能测试 - on 阿里云ECS + ESSD (含quorum based 0丢失多副本配置与性能测试)》
- 《PostgreSQL 11 100亿 tpcb 性能测试 on ECS》
- 《PostgreSQL 11 tpcc 测试(103万tpmC on ECS) - use sysbench-tpcc by Percona-Lab》
- 《Deepgreen(Greenplum) 多机部署测试 , TPC-H VS citus》
- 《PostgreSQL sharding : citus 系列2 - TPC-H》
- 《[转载] TPCH 22条SQL语句分析》
- 《PostgreSQL sharding : citus 系列1 - 多机部署(含OLTP(TPC-B)测试)- 含Citus MX模式》
- 《(TPC-H测试 SF=10,SF=200) PostgreSQL 11 vs 10 vs Deepgreen》
- 《PostgreSQL 推荐 TPC-C 测试工具 sqlbench》
- 《TPC-H测试 - PostgreSQL 10 vs Deepgreen(Greenplum)》
- 《[转自叶大师博文] tpcc-mysql安装、使用、结果解读》
- 《PostgreSQL 并行计算tpc-h测试和优化分析》
- 《Greenplum,HAWQ interval parser带来的问题 - TPCH 测试注意啦》
- 《PostgreSQL on ECS 横向版本TPCB性能测试》
- 《BenchmarkSQL 测试Oracle 12c TPC-C 性能 (含个人测试结果)》
- 《BenchmarkSQL 测试PostgreSQL 9.5.0 TPC-C 性能》
- 《BenchmarkSQL 测试Oracle 12c TPC-C 性能》