时空性能白皮书
一. 测试工具
测试采用TSDB benchmark工具,该工具可以测试不同并发度下,数据写入和查询的各项性能指标,包括RT的mean/min/max,以及百分位等。
二. 测试指标
分别测试TSDB2.0 在单节点和三节点下性能指标。
写入TPS
- 不同batch下,数据库实例每秒写入的数据条数。
查询RT
- 一次SQL查询请求数据库实例的平均执行时间。
三. 测试环境
TSDB2.0实例,华东1(杭州):
- 单节点:2C 8G SSD云盘 100G 1台
- 三节点:2C 8G SSD云盘 100G 3台
测试机,华东1(杭州):
- 32 vCPU 64 GiB SSD云盘 100G
四. 测试场景
1. 出租车轨迹场景
数据源
利用纽约开放的海量出租车数据:2015年纽约黄色的士轨迹数据,做聚合,OD等场景分析。数据下载地址:http://www.nyc.gov/html/tlc/html/about/trip_record_data.shtml
建表DDL
CREATE TABLE nypoint(
total_amount float,
improvement_surcharge float,
pickup_location GEO_SHAPE,
pickup_datetime timestamp,
trip_type integer,
dropoff_datetime timestamp,
rate_code_id integer,
tolls_amount float,
dropoff_location GEO_SHAPE,
passenger_count integer,
fare_amount float,
extra float,
trip_distance float,
tip_amount float,
store_and_fwd_flag text,
payment_type integer,
mta_tax float,
vendor_id integer
) with (number_of_replicas=0);
写入TPS
规格 | 1 batch | 100 batch | 1000 batch |
---|---|---|---|
单节点 | 596 | 4792 | 5887 |
三节点 | 1726 | 9673 | 11668 |
查询RT
Q1: 7天数据,group by聚合
select payment_type, sum(passenger_count) from nypoint where
dropoff_datetime < '2015-01-15 21:00' and dropoff_datetime > '2015-01-08 12:00'
group by payment_type limit 100;
Q2: bbox 聚合(月度)
select payment_type, sum(passenger_count) from nypoint where
dropoff_datetime < '2015-02-08 12:00' and dropoff_datetime > '2015-01-08 12:00'
and match("pickup_location",'POLYGON ((-74.007195 40.742258, -73.974251 40.730031,
-73.994841 40.706612, -74.020579 40.707913, -74.007195 40.742258))') using within
group by payment_type limit 100;
Q3: OD分析:统计7天时间段里,华盛顿广场(1平方公里范围)上车,特朗普大厦下车(1平方公里范围)的乘客数量。
select payment_type, sum(passenger_count) from nypoint where
dropoff_datetime < '2015-01-15 08:00' and dropoff_datetime > '2015-01-08 21:00' and
match("pickup_location",'POLYGON ((-74.000735 40.735447, -73.989925 40.734277,
-73.992842 40.725951, -74.002451 40.727382, -74.000735 40.735447))') using within
and match("dropoff_location",'POLYGON ((-73.985406 40.767202, -73.977770 40.764276,
-73.982403 40.758295, -73.989524 40.761351, -73.985406 40.767202))') using within
group by payment_type;
规格 | Q1 | Q2 | Q3 |
---|---|---|---|
单节点 | 506ms | 849ms | 622ms |
三节点 | 185ms | 337ms | 254ms |
2.智慧交通场景
数据源
本测试提取智慧交通车辆行驶数据信息,做流量、车辆动态特征及出行目的地分析,总计3000W数据,三张表做关联查询。
建表DDL
表1:
CREATE TABLE IF NOT EXISTS "doc"."dws_tfc_vhc_vhc_nd_trace_d" (
"stat_date" TEXT,
"vhc_trace_id" TEXT,
"vhc_id" TEXT,
"vhc_no" TEXT,
"vhc_plate_type_no" BIGINT,
"trace_start_step_index" BIGINT,
"rid" TEXT,
"rid_seq_no" BIGINT,
"rid_lnglat_list" TEXT,
"rid_lnglat_wkt" GEO_SHAPE INDEX USING GEOHASH,
"step_index" BIGINT,
"trace_cnt" BIGINT,
"dt" TEXT,
"tp" TEXT,
"src_type" TEXT,
"data_version" TEXT,
"adcode" TEXT
)
CLUSTERED INTO 6 SHARDS
PARTITIONED BY ("stat_date")
表2:
CREATE TABLE IF NOT EXISTS "doc"."dws_tfc_vhc_vhctrace_nd_traceprof_d" (
"stat_date" TEXT,
"vhc_trace_id" TEXT,
"vhc_id" TEXT,
"vhc_no" TEXT,
"vhc_plate_color_no" BIGINT,
"trace_start_step_index" BIGINT,
"trace_start_stat_time" TEXT,
"start_tfcunit_id" TEXT,
"trace_end_stat_time" TEXT,
"end_tfcunit_id" TEXT,
"trl_reason_no" BIGINT,
"realiability_code" DOUBLE PRECISION,
"trace_distance" DOUBLE PRECISION,
"trace_time" DOUBLE PRECISION,
"rid_cnt" BIGINT,
"dt" TEXT,
"src_type" TEXT,
"data_version" TEXT,
"adcode" TEXT
)
CLUSTERED INTO 6 SHARDS
PARTITIONED BY ("stat_date")
表3:
CREATE TABLE IF NOT EXISTS "doc"."dws_tfc_vhc_vhc_nd_prof_m" (
"stat_month" TEXT,
"vhc_id" TEXT,
"vhc_no" TEXT,
"vhc_plate_color_no" BIGINT,
"day_tfcunit_id_1m" TEXT,
"night_tfcunit_id_1m" TEXT,
"vhc_trl_prof_no_list_1m" TEXT,
"month" TEXT,
"data_version" TEXT,
"adcode" TEXT
)
CLUSTERED INTO 6 SHARDS
写入TPS
规格 | batch 1 | batch 100 | batch 1000 |
---|---|---|---|
单节点 | 517 | 5507 | 7716 |
三节点 | 809 | 6630 | 9663 |
查询RT
Q1: 出行类型—出发
select td.vhc_trace_id,td.vhc_no,pm.vhc_trl_prof_no_list_1m
from dws_tfc_vhc_vhc_nd_trace_d td
inner join dws_tfc_vhc_vhctrace_nd_traceprof_d dn
on dn.vhc_trace_id = td.vhc_trace_id
left join dws_tfc_vhc_vhc_nd_prof_m pm
on pm.vhc_id = dn.vhc_id
where td.rid_seq_no = 78 and td.adcode = '310000'
and td.rid = '152ET09IAU0152EJ09IBO00'
and td.step_index >= 0 and td.step_index <= 288
and td.stat_date = '20190915'
and dn.stat_date = '20190915';
Q2:出行类型—出发
select sum(dm.trl_reason_no)
from dws_tfc_vhc_vhc_nd_trace_d td
inner join dws_tfc_vhc_vhctrace_nd_traceprof_d dn
on dn.vhc_trace_id = td.vhc_trace_id
left join dws_tfc_vhc_vhctrace_nd_traceprof_d dm
on dm.vhc_id = dn.vhc_id
where td.rid_seq_no = 0 and td.adcode = '310000'
and td.rid = '153PA09G8K0153NQ09G8B00'
and td.step_index>=0 and td.step_index<=288
and td.stat_date = '20190915'
and dn.stat_date = '20190915'
and dm.stat_date = '20190915' limit 100;
Q3:轨迹分析,车俩某天出入某个区域
select td.vhc_trace_id,td.vhc_no ,td.rid_lnglat_wkt
from dws_tfc_vhc_vhc_nd_trace_d td
inner join dws_tfc_vhc_vhctrace_nd_traceprof_d dn
on dn.vhc_trace_id = td.vhc_trace_id
where td.rid_seq_no = (dn.rid_cnt-1) and td.adcode = '310000'
and td.step_index>=0 and td.step_index<=288
and td.stat_date = '20190922' and dn.stat_date = '20190922'
and st_contains('POLYGON ((121.480683 31.220871, 121.487238 31.220871, 121.487238 31.22523, 121.480683 31.22523, 121.480683 31.220871))', td.rid_lnglat_wkt)
limit 100;
规格 | Q1 | Q2 | Q3 |
---|---|---|---|
单节点 | 229ms | 399ms | 5040ms |
三节点 | 176ms | 227ms | 1179ms |
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论