全部产品

测试方法

更新时间:2019-05-07 14:41:36

1. 测试工具

pgbench

pgbench是针对PostgreSQL的一个基准测试程序。可以方便的用来测试兼容PostgreSQL协议的数据库性能,通过不断运行相同的sql(sql可以自己定义),最终计算出各项测试指标。时空数据库使用PostgreSQL协议,因此,采用pgbench进行基准测试。

2. 测试指标

写入TPS

  • 数据库不同batch,每秒insert的数据条数

只读QPS

  • 数据库只读时,每秒执行的SQL数(仅包含select)。

读写QPS

  • 数据库读写时,每秒执行的SQL数(insert、select)

测试环境

所有测试在华东1(杭州)地域完成,时空数据库的测试实例规格如下:

  • 2C 4G SSD云盘 100G
  • 4C 16G SSD 云盘 150G
  • 8C 32G SSD 云盘 500G

测试步骤

写入TPS

1、创建时空表

  1. CREATE TABLE "rides"(
  2. uid bigint,
  3. time timestamp without time zone not null,
  4. speed float,
  5. position geometry(Point,4326)
  6. );
  7. SELECT create_hypertable('rides', 'time', chunk_time_interval => interval '1 hour');

2、写入测试脚本

  • random(1552259212,1552295212):表示2019-03-11 07:06:52至2019-03-11 17:06:52之间10个小时内的随机数据。

insert_1.sql,单条插入

  1. #cat insert_1.sql
  2. \set t random(1552259212,1552295212)
  3. insert into rides select (random()*100000)::int,id,random()*1000,ST_SetSRID(ST_MakePoint( trunc( (0.5-random())*100), trunc( (0.5-random())*100) ),4326) from generate_series(to_timestamp(:t), to_timestamp(:t) + '0 second','1 second') as t(id);

insert_10.sql,batch 10插入

  1. #cat insert_10.sql, (1552259212,1552295212) 10个小时(2019-03-11 07:06:52,2019-03-11 17:06:52)
  2. \set t random(1552259212,1552295212)
  3. insert into rides select (random()*100000)::int,id,random()*1000,ST_SetSRID(ST_MakePoint( trunc( (0.5-random())*100), trunc( (0.5-random())*100) ),4326) from generate_series(to_timestamp(:t), to_timestamp(:t) + '9 second','1 second') as t(id);

insert_100.sql,batch 100插入

  1. #cat insert_100.sql
  2. \set t random(1552259212,1552295212)
  3. insert into rides select (random()*100000)::int,id,random()*1000,ST_SetSRID(ST_MakePoint( trunc( (0.5-random())*100), trunc( (0.5-random())*100) ),4326) from generate_series(to_timestamp(:t), to_timestamp(:t) + '99 second','1 second') as t(id);

insert_1000.sql,batch 1000插入

  1. #cat insert_1000.sql
  2. \set t random(1552259212,1552295212)
  3. insert into rides select (random()*100000)::int,id,random()*1000,ST_SetSRID(ST_MakePoint( trunc( (0.5-random())*100), trunc( (0.5-random())*100) ),4326) from generate_series(to_timestamp(:t), to_timestamp(:t) + '999 second','1 second') as t(id);

3、使用下面命令,按照不同batch,写入800W数据

  1. # xxx:用户创建的时空数据库初始账号。x.x.x.x:时空数据库ip地址。3242:时空数据库默认端口。postgres:默认库为postgres
  2. pgbench -U xxx -h x.x.x.x -p 3242 -d postgres -M prepared -n -r -P 1 -f ./insert_1.sql -c 16 -j 16 -t 500000
  3. pgbench -U xxx -h x.x.x.x -p 3242 -d postgres -M prepared -n -r -P 1 -f ./insert_10.sql -c 16 -j 16 -t 50000
  4. pgbench -U xxx -h x.x.x.x -p 3242 -d postgres -M prepared -n -r -P 1 -f ./insert_100.sql -c 16 -j 16 -t 5000
  5. pgbench -U xxx -h x.x.x.x -p 3242 -d postgres -M prepared -n -r -P 1 -f ./insert_1000.sql -c 16 -j 16 -t 500

查询QPS

1、创建时空表

  1. CREATE TABLE "myrides"(
  2. uid bigint,
  3. time timestamp without time zone not null,
  4. speed float,
  5. position geometry(Point,4326)
  6. );
  7. SELECT create_hypertable('myrides', 'time', chunk_time_interval => interval '1 hour');

2、初始化测试数据

  1. # 写入1000W数据,random(1552259212,1552295212):表示产生10个小时内时序数据
  2. cat insert_100_10hour.sql
  3. \set t random(1552259212,1552295212)
  4. insert into myrides select (random()*100000)::int,id,random()*1000,ST_SetSRID(ST_MakePoint( trunc( (0.5-random())*100), trunc( (0.5-random())*100)
  5. pgbench -U xxx -h x.x.x.x -p 3242 -d postgres -M prepared -n -r -P 1 -f ./insert_100_10hour.sql -c 20 -j 20 -t 5000
  6. create index on myrides using gist(position);
  7. # 写入1亿数据,random(1552259212,1552619212):表示产生100个小时内时序数据
  8. cat insert_100_100hour.sql
  9. \set t random(1552259212,1552619212)
  10. insert into myrides select (random()*100000)::int,id,random()*1000,ST_SetSRID(ST_MakePoint( trunc( (0.5-random())*100), trunc( (0.5-random())*100) ),4326) from generate_series(to_timestamp(:t), to_timestamp(:t) + '99 second','1 second') as t(id)
  11. pgbench -U xxx -h x.x.x.x -p 3242 -d postgres -M prepared -n -r -P 1 -f ./insert_100_100hour.sql -c 20 -j 20 -t 50000
  12. create index on myrides using gist(position);

3、只读测试脚本

  1. # cat select_ro.sql
  2. \set x random(1, 1 * :scale)
  3. \set y random(1, 1 * :scale)
  4. \set t random(1552259212,1552619212)
  5. select count(*) from myrides where time > to_timestamp(:t) and time < to_timestamp(:t+10) and ST_Contains(ST_SetSRID(ST_MakeBox2D(ST_Point(:x, :y),ST_Point(:x + 1,:y +1)),4326),position);

4、读写测试脚本

  1. # cat select_rw.sql
  2. \set x random(1, 1 * :scale)
  3. \set y random(1, 1 * :scale)
  4. \set t random(1552259212,1552619212)
  5. BEGIN;
  6. insert into myrides select (random()*100000)::int,id,random()*1000,ST_SetSRID(ST_MakePoint( trunc( (0.5-random())*100), trunc( (0.5-random())*100) ),4326) from generate_series(to_timestamp(:t), to_timestamp(:t) + '0 second','1 second') as t(id);
  7. select count(*) from myrides where time > to_timestamp(:t) and time < to_timestamp(:t+10) and ST_Contains(ST_SetSRID(ST_MakeBox2D(ST_Point(:x, :y),ST_Point(:x + 1,:y +1)),4326),position);
  8. END;

5、查询压测

  1. # 只读QPS压测:
  2. pgbench -U xxx -h x.x.x.x -p 3242 -d postgres -M prepared -r -P 1 -f ./select_ro.sql -c 8 -j 8 -T 100 -D scale=50
  3. # 读写QPS压测:
  4. pgbench -U xxx -h x.x.x.x -p 3242 -d postgres -M prepared -r -P 1 -f ./select_rw.sql -c 8 -j 8 -T 100 -D scale=50