1.背景

欢迎数据库应用开发者参与贡献场景, 在此issue回复即可, 共同建设《沉浸式数据库学习教学素材库》, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.

系列课程的核心目标是教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核. 所以面向的对象是数据库的用户、应用开发者、应用架构师、数据库厂商的产品经理、售前售后专家等角色.

本文的实验可以使用永久免费的阿里云云起实验室来完成.

如果你本地有docker环境也可以把镜像拉到本地来做实验:

x86_64机器使用以下docker image:

《amd64 image》

ARM机器使用以下docker image:

《arm64 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个参考:

https://www.cnblogs.com/xianghuaqiang/p/14425274.html

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语句, 进行压力测试.

沉浸式学习PostgreSQL|PolarDB 12: 如何快速构建海量逼真测试数据

1.背景

2.业务场景1 介绍: 如何快速构建海量逼真测试数据

2.1实现和对照

2.1.1传统方法设计和实验

2.1.2PolarDB|PG新方法1 设计和实验

2.1.2.1如何生成各种需求、各种类型的随机值

2.1.2.2如何快速生成大量数据

2.1.2.3如何生成按需求分布的随机值

2.1.2.4压测

2.1.3对照

3.知识点

4.思考

5.参考

相关产品

相关文章

相关实验场景

相关课程

热门文章

最新文章

相关电子书

沉浸式学习PostgreSQL|PolarDB 12: 如何快速构建 海量 逼真 测试数据

1.背景

2.业务场景1 介绍: 如何快速构建 海量 逼真 测试数据

2.1实现和对照

2.1.1传统方法 设计和实验

2.1.2PolarDB|PG新方法1 设计和实验

2.1.2.1如何生成各种需求、各种类型的随机值

2.1.2.2如何快速生成大量数据

2.1.2.3如何生成按需求分布的随机值

2.1.2.4压测

2.1.3对照

3.知识点

4.思考

5.参考

相关产品

相关文章

相关实验场景

相关课程

热门文章

最新文章

相关电子书

沉浸式学习PostgreSQL|PolarDB 12: 如何快速构建海量逼真测试数据

2.业务场景1 介绍: 如何快速构建海量逼真测试数据

2.1.1传统方法设计和实验