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

简介: 本文主要教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核.

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.参考


  1. 《PostgreSQL 如何快速构建 海量 逼真 测试数据》
  2. 《股票涨跌幅概率符合高斯分布特征吗? 如何使用PostgreSQL pgbench模拟较为逼真的股票数据?》
  3. 《PostgreSQL 15 preview - pgbench copy freeze 加速tpc-b测试数据生成》
  4. 《PostgreSQL 15 preview - pgbench pipeline压测代码优化, 提升性能》
  5. 《PostgreSQL 14 preview - pgbench 压测工具新增 随机函数 permute(i, size, [seed]) 返回 i 经过重新(随机)映射后 在 [0,size) 范围内的一个值》
  6. 《PostgreSQL 14 preview - copy freeze 增强, pgbench 支持copy freeze 大幅提升导入性能》
  7. 《PostgreSQL 14 preview - pgbench 支持冒号常量, 例如时间,timestamp常量》
  8. 《PostgreSQL 13 preview - pgbench server-side产生tpc-b测试数据(使用generate_series产生大量测试数据)》
  9. 《PostgreSQL 13 preview - pgbench 内置tpcb支持 pgbench_account 分区表》
  10. 《PostgreSQL pgbench client_id 变量用途 - 压测时防止线程间锁冲突(未来,代替动态表名,分区表)》
  11. 《PostgreSQL 12 preview - pgbench 自定义压测script支持一条SQL最多绑定256个变量》
  12. 《PostgreSQL 12 preview - pgbench 压测工具编程能力增强 - gset 支持SQL结果返回并存入变量使用》
  13. 《PostgreSQL pgbench tpcb 海量数据库测试 - 分区表测试优化》
  14. 《PostgreSQL pgbench tpcb 数据生成与SQL部分源码解读》
  15. 《PostgreSQL 11 preview - pgbench 压测软件增强》
  16. 《PostgreSQL pgbench 支持100万连接》
  17. 《PostgreSQL 11 preview - pgbench 支持大于1000链接(ppoll()代替select())》
  18. 《PostgreSQL pgbench : 冒号处理 常量包含冒号。》
  19. 《PostgreSQL 11 preview - pgbench 变量、函数扩展 - 暨pgbench 自定义 benchmark讲解》
  20. 《PostgreSQL 使用 pgbench 测试 sysbench 相关case - pg_oltp_bench》
  21. 《PostgreSQL pgbench SQL RT 与 事务RT 浅析》
  22. 《生成泊松、高斯、指数、随机分布数据 - PostgreSQL 9.5 new feature - pgbench improve, gaussian (standard normal) & exponential distribution》
  23. 《PostgreSQL native partition 分区表性能优化之 - 动态SQL+服务端绑定变量》
  24. 《HTAP数据库 PostgreSQL 场景与性能测试之 47 - (OLTP多模优化) 空间应用 - 高并发空间位置更新、多属性KNN搜索并测(含空间索引)末端配送、新零售类项目》
  25. 《HTAP数据库 PostgreSQL 场景与性能测试之 46 - (OLTP) 大json字段的高并发更新》
  26. 《HTAP数据库 PostgreSQL 场景与性能测试之 45 - (OLTP) 数据量与性能的线性关系(10亿+无衰减), 暨单表多大需要分区》
  27. 《[未完待续] HTAP数据库 PostgreSQL 场景与性能测试之 44 - (OLTP) 空间应用 - 空间包含查询(输入多边形 包含 表内空间对象)》
  28. 《HTAP数据库 PostgreSQL 场景与性能测试之 43 - (OLTP+OLAP) unlogged table 含索引多表批量写入》
  29. 《HTAP数据库 PostgreSQL 场景与性能测试之 42 - (OLTP+OLAP) unlogged table 不含索引多表批量写入》
  30. 《HTAP数据库 PostgreSQL 场景与性能测试之 41 - (OLTP+OLAP) 含索引多表批量写入》
  31. 《HTAP数据库 PostgreSQL 场景与性能测试之 40 - (OLTP+OLAP) 不含索引多表批量写入》
  32. 《HTAP数据库 PostgreSQL 场景与性能测试之 39 - (OLTP+OLAP) logged & unlogged table 含索引多表单点写入》
  33. 《HTAP数据库 PostgreSQL 场景与性能测试之 38 - (OLTP+OLAP) logged & unlogged table 不含索引多表单点写入》
  34. 《HTAP数据库 PostgreSQL 场景与性能测试之 37 - (OLTP+OLAP) 含索引单表批量写入》
  35. 《HTAP数据库 PostgreSQL 场景与性能测试之 36 - (OLTP+OLAP) 不含索引单表批量写入》
  36. 《HTAP数据库 PostgreSQL 场景与性能测试之 35 - (OLTP+OLAP) 含索引单表单点写入》
  37. 《HTAP数据库 PostgreSQL 场景与性能测试之 34 - (OLTP+OLAP) 不含索引单表单点写入》
  38. 《HTAP数据库 PostgreSQL 场景与性能测试之 33 - (OLAP) 物联网 - 线性字段区间实时统计》
  39. 《HTAP数据库 PostgreSQL 场景与性能测试之 32 - (OLTP) 高吞吐数据进出(堆存、行扫、无需索引) - 阅后即焚(JSON + 函数流式计算)》
  40. 《HTAP数据库 PostgreSQL 场景与性能测试之 31 - (OLTP) 高吞吐数据进出(堆存、行扫、无需索引) - 阅后即焚(读写大吞吐并测)》
  41. 《HTAP数据库 PostgreSQL 场景与性能测试之 30 - (OLTP) 秒杀 - 高并发单点更新》
  42. 《HTAP数据库 PostgreSQL 场景与性能测试之 29 - (OLTP) 空间应用 - 高并发空间位置更新(含空间索引)》
  43. 《HTAP数据库 PostgreSQL 场景与性能测试之 28 - (OLTP) 高并发点更新》
  44. 《HTAP数据库 PostgreSQL 场景与性能测试之 27 - (OLTP) 物联网 - FEED日志, 流式处理 与 阅后即焚 (CTE)》
  45. 《HTAP数据库 PostgreSQL 场景与性能测试之 26 - (OLTP) NOT IN、NOT EXISTS 查询》
  46. 《HTAP数据库 PostgreSQL 场景与性能测试之 25 - (OLTP) IN , EXISTS 查询》
  47. 《HTAP数据库 PostgreSQL 场景与性能测试之 24 - (OLTP) 物联网 - 时序数据并发写入(含时序索引BRIN)》
  48. 《HTAP数据库 PostgreSQL 场景与性能测试之 23 - (OLAP) 并行计算》
  49. 《HTAP数据库 PostgreSQL 场景与性能测试之 22 - (OLTP) merge insert|upsert|insert on conflict|合并写入》
  50. 《HTAP数据库 PostgreSQL 场景与性能测试之 21 - (OLTP+OLAP) 排序、建索引》
  51. 《HTAP数据库 PostgreSQL 场景与性能测试之 20 - (OLAP) 用户画像圈人场景 - 多个字段任意组合条件筛选与透视》
  52. 《HTAP数据库 PostgreSQL 场景与性能测试之 19 - (OLAP) 用户画像圈人场景 - 数组相交查询与聚合》
  53. 《HTAP数据库 PostgreSQL 场景与性能测试之 18 - (OLAP) 用户画像圈人场景 - 数组包含查询与聚合》
  54. 《HTAP数据库 PostgreSQL 场景与性能测试之 17 - (OLTP) 数组相似查询》
  55. 《HTAP数据库 PostgreSQL 场景与性能测试之 16 - (OLTP) 文本特征向量 - 相似特征(海明...)查询》
  56. 《HTAP数据库 PostgreSQL 场景与性能测试之 15 - (OLTP) 物联网 - 查询一个时序区间的数据》
  57. 《HTAP数据库 PostgreSQL 场景与性能测试之 14 - (OLTP) 字符串搜索 - 全文检索》
  58. 《HTAP数据库 PostgreSQL 场景与性能测试之 13 - (OLTP) 字符串搜索 - 相似查询》
  59. 《HTAP数据库 PostgreSQL 场景与性能测试之 12 - (OLTP) 字符串搜索 - 前后模糊查询》
  60. 《HTAP数据库 PostgreSQL 场景与性能测试之 11 - (OLTP) 字符串搜索 - 后缀查询》
  61. 《HTAP数据库 PostgreSQL 场景与性能测试之 10 - (OLTP) 字符串搜索 - 前缀查询》
  62. 《HTAP数据库 PostgreSQL 场景与性能测试之 9 - (OLTP) 字符串模糊查询 - 含索引实时写入》
  63. 《HTAP数据库 PostgreSQL 场景与性能测试之 8 - (OLTP) 多值类型(数组)含索引实时写入》
  64. 《HTAP数据库 PostgreSQL 场景与性能测试之 7 - (OLTP) 全文检索 - 含索引实时写入》
  65. 《HTAP数据库 PostgreSQL 场景与性能测试之 6 - (OLTP) 空间应用 - KNN查询(搜索附近对象,由近到远排序输出)》
  66. 《HTAP数据库 PostgreSQL 场景与性能测试之 5 - (OLTP) 空间应用 - 空间包含查询(表内多边形 包含 输入空间对象)》
  67. 《HTAP数据库 PostgreSQL 场景与性能测试之 4 - (OLAP) 大表OUTER JOIN统计查询》
  68. 《HTAP数据库 PostgreSQL 场景与性能测试之 3.1 - (OLAP) 大表JOIN统计查询-10亿 join 1亿 agg》
  69. 《HTAP数据库 PostgreSQL 场景与性能测试之 3 - (OLAP) 大表JOIN统计查询》
  70. 《HTAP数据库 PostgreSQL 场景与性能测试之 2 - (OLTP) 多表JOIN》
  71. 《HTAP数据库 PostgreSQL 场景与性能测试之 1 - (OLTP) 点查》
  72. 《PostgreSQL 源码性能诊断(perf profiling)指南(含火焰图生成分析FlameGraph) - 珍藏级》
  73. https://www.cnblogs.com/xianghuaqiang/p/14425274.html
  74. 《DuckDB 采用外部 parquet 格式存储 - tpch 测试 - in_memory VS in_parquet》
  75. 《DuckDB vs PostgreSQL TPC-H 测试》
  76. 《DuckDB TPC-H, TPC-DS 测试》
  77. 《DuckDB 定位OLAP方向的SQLite, 适合嵌入式数据分析 - tpch测试与简单试用》
  78. 《PostgreSQL 15 preview - pgbench copy freeze 加速tpc-b测试数据生成》
  79. 《PostgreSQL 13 新增 hash 内存参数, 提高hash操作性能 - hash_mem_multiplier - work_mem的倍数 - 支持超越内存时采用disk 存储hash表, 支持更多场景使用hashagg, TPCH性能大幅提升》
  80. 《PostgreSQL 13 preview - pgbench server-side产生tpc-b测试数据(使用generate_series产生大量测试数据)》
  81. 《阿里云RDS PG 11支持ESSD pl3 存储,满足企业超高性能数据库要求 - 含tpcc测试结果》
  82. 《PostgreSQL 12 tpcc 测试 - use sysbench-tpcc by Percona-Lab》
  83. 《PostgreSQL 13 preview - pgbench 内置tpcb支持 pgbench_account 分区表》
  84. 《PostgreSQL 11 1Kw TPCC , 1亿 TPCB 7*24 强压耐久测试》
  85. 《PostgreSQL pgbench tpcb 海量数据库测试 - 分区表测试优化》
  86. 《PostgreSQL pgbench tpcb 数据生成与SQL部分源码解读》
  87. 《PostgreSQL 11 1万亿 tpcb 性能测试 on 阿里云ECS + ESSD + zfs/lvm2条带 + block_size=32K》
  88. 《PostgreSQL 11 1000亿 tpcb、1000W tpcc 性能测试 - on 阿里云ECS + ESSD (含quorum based 0丢失多副本配置与性能测试)》
  89. 《PostgreSQL 11 100亿 tpcb 性能测试 on ECS》
  90. 《PostgreSQL 11 tpcc 测试(103万tpmC on ECS) - use sysbench-tpcc by Percona-Lab》
  91. 《Deepgreen(Greenplum) 多机部署测试 , TPC-H VS citus》
  92. 《PostgreSQL sharding : citus 系列2 - TPC-H》
  93. 《[转载] TPCH 22条SQL语句分析》
  94. 《PostgreSQL sharding : citus 系列1 - 多机部署(含OLTP(TPC-B)测试)- 含Citus MX模式》
  95. 《(TPC-H测试 SF=10,SF=200) PostgreSQL 11 vs 10 vs Deepgreen》
  96. 《PostgreSQL 推荐 TPC-C 测试工具 sqlbench》
  97. 《TPC-H测试 - PostgreSQL 10 vs Deepgreen(Greenplum)》
  98. 《[转自叶大师博文] tpcc-mysql安装、使用、结果解读》
  99. 《PostgreSQL 并行计算tpc-h测试和优化分析》
  100. 《Greenplum,HAWQ interval parser带来的问题 - TPCH 测试注意啦》
  101. 《PostgreSQL on ECS 横向版本TPCB性能测试》
  102. 《BenchmarkSQL 测试Oracle 12c TPC-C 性能 (含个人测试结果)》
  103. 《BenchmarkSQL 测试PostgreSQL 9.5.0 TPC-C 性能》
  104. 《BenchmarkSQL 测试Oracle 12c TPC-C 性能》
作者介绍
目录

相关产品

  • 云原生数据库 PolarDB