1、如何快速构建“海量&逼真”的测试数据
传统数据库测试通常使用标准套件tpcc,tpch,tpcb,tpcds等生成测试数据, 而当我们需要根据不同的业务场景来设计测试数据的特征, 并根据特征生成比较逼真的大规模数据时, 往往不太容易, 需要针对需求开发程序来实现.
另外, 传统数据库的测试模型也比较简单, 通常只能使用标准的tpcc,tpch,tpcb,tpcds等相关压测软件来实现测试. 无法根据特定业务需求来进行模拟压测.
PolarDB & PostgreSQL 自定义生成数据的方法非常多, 通过SRF, pgbench等可以快速加载特征数据, 可以根据实际的业务场景和需求进行数据的生成、压测. 可以实现提前预知业务压力问题, 帮助用户提前解决瓶颈.
开发者通常需要结合数据库的能力, 业务场景, 以及数据特征等构建符合业务真实情况的数据. 下面开始举例讲解, 现在很多需求也可以通过chatgpt来生成.
一、如何生成各种需求、各种类型的随机值
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) -- 请思考一下UUID的弊端? -- 还有哪些UUID类型/类似功能插件?
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)
《PostgreSQL 黑科技 range 类型及 gist index 20x+ speedup than Mysql index combine query》
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)
函数稳定性和在SQL语句中的调用次数、优化器(作为变量时是否可以走索引?)、是否能创建表达式索引等的关系:
- 《PostgreSQL - where x=round(random()*xx) 返回结果反常 - 背后的函数稳定性逻辑 (immutable stable volatile)》
- 《PostgreSQL Oracle 兼容性之 - PL/SQL DETERMINISTIC 与PG函数稳定性(immutable, stable, volatile)》
- 《函数稳定性讲解 - retalk PostgreSQL function's [ volatile|stable|immutable ]》
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文件, 高速写入
pgbench help:
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)
select uid,count(*),repeat('@',log(count(*))::int) as "logCNT" from tbl_log group by uid order by 1;
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());
注意\gset在PolarDB 11版本的pgbench中不支持, 需12+版本pgbench.
PG 18支持returning old,new value了, 不需要使用上面这种cte语法来返回了.
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 编写自定义压测脚本.
前面的例子都已经讲过了. 略.
2、pgbench 表名没法用变量补齐, 所以没法使用:client_id, 数据没法直接进入分区表.
为了让数据直接进入分区, 并且还能使用绑定变量, 可以使用plpgsql来实现.
《PostgreSQL Oracle 兼容性之 - DBMS_SQL(存储过程动态SQL中使用绑定变量-DB端prepare statement)》
五、思考
思考一下你熟悉的业务, 数据特征, 设计对应的表结构, 生成具有业务特征的数据, 编写业务相关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 函数去除字符串两端的空格，并返回生成的随机字符串。
