全部产品

时空性能白皮书

更新时间:2019-12-26 18:22:18

一. 测试工具

测试采用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
  1. CREATE TABLE nypoint(
  2. total_amount float,
  3. improvement_surcharge float,
  4. pickup_location GEO_SHAPE,
  5. pickup_datetime timestamp,
  6. trip_type integer,
  7. dropoff_datetime timestamp,
  8. rate_code_id integer,
  9. tolls_amount float,
  10. dropoff_location GEO_SHAPE,
  11. passenger_count integer,
  12. fare_amount float,
  13. extra float,
  14. trip_distance float,
  15. tip_amount float,
  16. store_and_fwd_flag text,
  17. payment_type integer,
  18. mta_tax float,
  19. vendor_id integer
  20. ) with (number_of_replicas=0);
写入TPS
规格 1 batch 100 batch 1000 batch
单节点 596 4792 5887
三节点 1726 9673 11668

ny-tps

查询RT

Q1: 7天数据,group by聚合

  1. select payment_type, sum(passenger_count) from nypoint where
  2. dropoff_datetime < '2015-01-15 21:00' and dropoff_datetime > '2015-01-08 12:00'
  3. group by payment_type limit 100;

Q2: bbox 聚合(月度)

  1. select payment_type, sum(passenger_count) from nypoint where
  2. dropoff_datetime < '2015-02-08 12:00' and dropoff_datetime > '2015-01-08 12:00'
  3. and match("pickup_location",'POLYGON ((-74.007195 40.742258, -73.974251 40.730031,
  4. -73.994841 40.706612, -74.020579 40.707913, -74.007195 40.742258))') using within
  5. group by payment_type limit 100;

Q3: OD分析:统计7天时间段里,华盛顿广场(1平方公里范围)上车,特朗普大厦下车(1平方公里范围)的乘客数量。

  1. select payment_type, sum(passenger_count) from nypoint where
  2. dropoff_datetime < '2015-01-15 08:00' and dropoff_datetime > '2015-01-08 21:00' and
  3. match("pickup_location",'POLYGON ((-74.000735 40.735447, -73.989925 40.734277,
  4. -73.992842 40.725951, -74.002451 40.727382, -74.000735 40.735447))') using within
  5. and match("dropoff_location",'POLYGON ((-73.985406 40.767202, -73.977770 40.764276,
  6. -73.982403 40.758295, -73.989524 40.761351, -73.985406 40.767202))') using within
  7. group by payment_type;
规格 Q1 Q2 Q3
单节点 506ms 849ms 622ms
三节点 185ms 337ms 254ms

ny-rt

2.智慧交通场景

数据源

本测试提取智慧交通车辆行驶数据信息,做流量、车辆动态特征及出行目的地分析,总计3000W数据,三张表做关联查询。

建表DDL

表1:

  1. CREATE TABLE IF NOT EXISTS "doc"."dws_tfc_vhc_vhc_nd_trace_d" (
  2. "stat_date" TEXT,
  3. "vhc_trace_id" TEXT,
  4. "vhc_id" TEXT,
  5. "vhc_no" TEXT,
  6. "vhc_plate_type_no" BIGINT,
  7. "trace_start_step_index" BIGINT,
  8. "rid" TEXT,
  9. "rid_seq_no" BIGINT,
  10. "rid_lnglat_list" TEXT,
  11. "rid_lnglat_wkt" GEO_SHAPE INDEX USING GEOHASH,
  12. "step_index" BIGINT,
  13. "trace_cnt" BIGINT,
  14. "dt" TEXT,
  15. "tp" TEXT,
  16. "src_type" TEXT,
  17. "data_version" TEXT,
  18. "adcode" TEXT
  19. )
  20. CLUSTERED INTO 6 SHARDS
  21. PARTITIONED BY ("stat_date")

表2:

  1. CREATE TABLE IF NOT EXISTS "doc"."dws_tfc_vhc_vhctrace_nd_traceprof_d" (
  2. "stat_date" TEXT,
  3. "vhc_trace_id" TEXT,
  4. "vhc_id" TEXT,
  5. "vhc_no" TEXT,
  6. "vhc_plate_color_no" BIGINT,
  7. "trace_start_step_index" BIGINT,
  8. "trace_start_stat_time" TEXT,
  9. "start_tfcunit_id" TEXT,
  10. "trace_end_stat_time" TEXT,
  11. "end_tfcunit_id" TEXT,
  12. "trl_reason_no" BIGINT,
  13. "realiability_code" DOUBLE PRECISION,
  14. "trace_distance" DOUBLE PRECISION,
  15. "trace_time" DOUBLE PRECISION,
  16. "rid_cnt" BIGINT,
  17. "dt" TEXT,
  18. "src_type" TEXT,
  19. "data_version" TEXT,
  20. "adcode" TEXT
  21. )
  22. CLUSTERED INTO 6 SHARDS
  23. PARTITIONED BY ("stat_date")

表3:

  1. CREATE TABLE IF NOT EXISTS "doc"."dws_tfc_vhc_vhc_nd_prof_m" (
  2. "stat_month" TEXT,
  3. "vhc_id" TEXT,
  4. "vhc_no" TEXT,
  5. "vhc_plate_color_no" BIGINT,
  6. "day_tfcunit_id_1m" TEXT,
  7. "night_tfcunit_id_1m" TEXT,
  8. "vhc_trl_prof_no_list_1m" TEXT,
  9. "month" TEXT,
  10. "data_version" TEXT,
  11. "adcode" TEXT
  12. )
  13. CLUSTERED INTO 6 SHARDS
写入TPS
规格 batch 1 batch 100 batch 1000
单节点 517 5507 7716
三节点 809 6630 9663

city-tps

查询RT

Q1: 出行类型—出发

  1. select td.vhc_trace_id,td.vhc_no,pm.vhc_trl_prof_no_list_1m
  2. from dws_tfc_vhc_vhc_nd_trace_d td
  3. inner join dws_tfc_vhc_vhctrace_nd_traceprof_d dn
  4. on dn.vhc_trace_id = td.vhc_trace_id
  5. left join dws_tfc_vhc_vhc_nd_prof_m pm
  6. on pm.vhc_id = dn.vhc_id
  7. where td.rid_seq_no = 78 and td.adcode = '310000'
  8. and td.rid = '152ET09IAU0152EJ09IBO00'
  9. and td.step_index >= 0 and td.step_index <= 288
  10. and td.stat_date = '20190915'
  11. and dn.stat_date = '20190915';

Q2:出行类型—出发

  1. select sum(dm.trl_reason_no)
  2. from dws_tfc_vhc_vhc_nd_trace_d td
  3. inner join dws_tfc_vhc_vhctrace_nd_traceprof_d dn
  4. on dn.vhc_trace_id = td.vhc_trace_id
  5. left join dws_tfc_vhc_vhctrace_nd_traceprof_d dm
  6. on dm.vhc_id = dn.vhc_id
  7. where td.rid_seq_no = 0 and td.adcode = '310000'
  8. and td.rid = '153PA09G8K0153NQ09G8B00'
  9. and td.step_index>=0 and td.step_index<=288
  10. and td.stat_date = '20190915'
  11. and dn.stat_date = '20190915'
  12. and dm.stat_date = '20190915' limit 100;

Q3:轨迹分析,车俩某天出入某个区域

  1. select td.vhc_trace_id,td.vhc_no ,td.rid_lnglat_wkt
  2. from dws_tfc_vhc_vhc_nd_trace_d td
  3. inner join dws_tfc_vhc_vhctrace_nd_traceprof_d dn
  4. on dn.vhc_trace_id = td.vhc_trace_id
  5. where td.rid_seq_no = (dn.rid_cnt-1) and td.adcode = '310000'
  6. and td.step_index>=0 and td.step_index<=288
  7. and td.stat_date = '20190922' and dn.stat_date = '20190922'
  8. 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)
  9. limit 100;
规格 Q1 Q2 Q3
单节点 229ms 399ms 5040ms
三节点 176ms 227ms 1179ms

city-rt