Star Schema Benchmark

概述

星型模式基准测试（SSB）Star Schema Benchmark是一个轻量级的数仓场景下的性能测试集。SSB基于事务处理性能委员会（TPC-H）Transaction Processing Performance Council，提供了一个简化版的星型模型数据集，主要用于测试在星型模型下，多表关联查询的性能表现。此外，业界通常也将SSB称为宽表模型（简称SSB FLAT），以测试查询引擎的性能。本文档主要介绍SelectDB在SSB 100 GB测试集上的性能表现。

测试环境

• 数据库环境。

  环境配置项	配置说明
  地域和可用区	华东1（杭州）地域，可用区K。
  规格	64核 512 GB
  磁盘	800 GB高性能云硬盘
  云数据库 SelectDB 版内核版本	3.0.6

• 客户端环境。

  环境配置项	配置说明
  下载测试工具的设备	云服务器ECS实例，详情请参见创建实例。
  地域和可用区	华东1（杭州）地域
  实例规格	ecs.g7.2xlarge
  操作系统	Ubuntu 22.04.1 LTS
  网络	与云数据库 SelectDB 版版实例为相同专有网络（VPC）。

测试数据集

整个测试数据集模拟生成SSB 100 GB和500 GB的数据并导入SelectDB进行测试，下面是测试100 GB数据表的相关说明及数据量。

测试步骤

如下介绍进行测试所需要的前置工作和测试步骤。涉及的脚本获取请参见瑶池测试工具。

步骤一：下载安装SSB数据生成工具

从上述脚本库中获取脚本后，解压脚本文件并进入对应目录，执行以下指令，下载并编译ssb-dbgen工具，示例如下。

tar -zxvf yaochi_performance_tool.tar.gz
cd ./yaochi_performance_tool/ssb-tools/bin
bash build-ssb-dbgen.sh

安装成功后，将在./yaochi_performance_tool/ssb-tools/bin/ssb-dbgen/目录下生成dbgen二进制文件。

步骤二：生成SSB测试集

在安装测试工具目录执行下脚本以生成SSB数据集，示例如下。

cd ./yaochi_performance_tool/ssb-tools/bin
bash gen-ssb-data.sh -s 100 -c 100

数据会以.tbl为后缀生成在ssb-data/目录下。

其中，-s 100表示测试集大小系数为100，-c 100表示并发100个线程生成lineorder表的数据。-c参数也决定了最终lineorder表的文件数量。参数越大，文件数越多，每个文件越小。

在-s 100参数下，生成的数据集文件总大小约60GB。生成时间可能在数分钟到1小时不等。具体数据信息如下表。

步骤三：建表

1. 准备doris-cluster.conf文件

   在调用导入脚本前，需要将测试用信息写在doris-cluster.conf文件中。文件位置在ssb-tools/conf/目录下，内容包括连接集群的地址、HTTP端口、用户名、密码和待导入数据的DB。

   说明

   您可以在云数据库 SelectDB 版控制台的实例详情 > 网络信息中获取VPC地址（或公网地址）和HTTP协议端口。

   export FE_HOST="xxx"
   export FE_HTTP_PORT="8080"
   export FE_QUERY_PORT="9030"
   export USER="root"
   export PASSWORD='xxx'
   export DB="ssb"

2. 创建SSB表

   在./yaochi_performance_tool/ssb-tools/bin/目录下执行脚本来自动创建测试用表。

   bash create-ssb-tables.sh

   可以考虑通过修改建表语句中的配置，以让性能测试更加贴合实际生产的环境。以lineorder_flat表建表语句为例，以下的建表语句中，设置了默认分桶为48个桶。您可以根据您的集群规模节点配置对这个分桶数进行调整，这样可以获取到更好的测试效果。

   CREATE TABLE `lineorder_flat` (
     `LO_ORDERDATE` date NOT NULL COMMENT "",
     `LO_ORDERKEY` int(11) NOT NULL COMMENT "",
     `LO_LINENUMBER` tinyint(4) NOT NULL COMMENT "",
     `LO_CUSTKEY` int(11) NOT NULL COMMENT "",
     `LO_PARTKEY` int(11) NOT NULL COMMENT "",
     `LO_SUPPKEY` int(11) NOT NULL COMMENT "",
     `LO_ORDERPRIORITY` varchar(100) NOT NULL COMMENT "",
     `LO_SHIPPRIORITY` tinyint(4) NOT NULL COMMENT "",
     `LO_QUANTITY` tinyint(4) NOT NULL COMMENT "",
     `LO_EXTENDEDPRICE` int(11) NOT NULL COMMENT "",
     `LO_ORDTOTALPRICE` int(11) NOT NULL COMMENT "",
     `LO_DISCOUNT` tinyint(4) NOT NULL COMMENT "",
     `LO_REVENUE` int(11) NOT NULL COMMENT "",
     `LO_SUPPLYCOST` int(11) NOT NULL COMMENT "",
     `LO_TAX` tinyint(4) NOT NULL COMMENT "",
     `LO_COMMITDATE` date NOT NULL COMMENT "",
     `LO_SHIPMODE` varchar(100) NOT NULL COMMENT "",
     `C_NAME` varchar(100) NOT NULL COMMENT "",
     `C_ADDRESS` varchar(100) NOT NULL COMMENT "",
     `C_CITY` varchar(100) NOT NULL COMMENT "",
     `C_NATION` varchar(100) NOT NULL COMMENT "",
     `C_REGION` varchar(100) NOT NULL COMMENT "",
     `C_PHONE` varchar(100) NOT NULL COMMENT "",
     `C_MKTSEGMENT` varchar(100) NOT NULL COMMENT "",
     `S_NAME` varchar(100) NOT NULL COMMENT "",
     `S_ADDRESS` varchar(100) NOT NULL COMMENT "",
     `S_CITY` varchar(100) NOT NULL COMMENT "",
     `S_NATION` varchar(100) NOT NULL COMMENT "",
     `S_REGION` varchar(100) NOT NULL COMMENT "",
     `S_PHONE` varchar(100) NOT NULL COMMENT "",
     `P_NAME` varchar(100) NOT NULL COMMENT "",
     `P_MFGR` varchar(100) NOT NULL COMMENT "",
     `P_CATEGORY` varchar(100) NOT NULL COMMENT "",
     `P_BRAND` varchar(100) NOT NULL COMMENT "",
     `P_COLOR` varchar(100) NOT NULL COMMENT "",
     `P_TYPE` varchar(100) NOT NULL COMMENT "",
     `P_SIZE` tinyint(4) NOT NULL COMMENT "",
     `P_CONTAINER` varchar(100) NOT NULL COMMENT ""
   ) ENGINE=OLAP
   DUPLICATE KEY(`LO_ORDERDATE`, `LO_ORDERKEY`)
   COMMENT "OLAP"
   PARTITION BY RANGE(`LO_ORDERDATE`)
   (PARTITION p1 VALUES [('0000-01-01'), ('1993-01-01')),
   PARTITION p2 VALUES [('1993-01-01'), ('1994-01-01')),
   PARTITION p3 VALUES [('1994-01-01'), ('1995-01-01')),
   PARTITION p4 VALUES [('1995-01-01'), ('1996-01-01')),
   PARTITION p5 VALUES [('1996-01-01'), ('1997-01-01')),
   PARTITION p6 VALUES [('1997-01-01'), ('1998-01-01')),
   PARTITION p7 VALUES [('1998-01-01'), ('1999-01-01')))
   DISTRIBUTED BY HASH(`LO_ORDERKEY`) BUCKETS 48
   PROPERTIES (
   "replication_num" = "1",
   "colocate_with" = "groupxx1",
   "in_memory" = "false",
   "storage_format" = "DEFAULT"
   );

步骤四：导入数据​

在./yaochi_performance_tool/ssb-tools/bin/目录下执行脚本完成SSB测试集所有数据的导入。

bash load-ssb-data.sh -c 5

其中，-c 5表示启动5个并发线程导入（默认为 5）。如果开启更多线程，可以加快导入速度，但会增加额外的内存开销。

步骤五：检查导入数据

SELECT COUNT(*) FROM part;
SELECT COUNT(*) FROM customer;
SELECT COUNT(*) FROM supplier;
SELECT COUNT(*) FROM dates;
SELECT COUNT(*) FROM lineorder;
SELECT COUNT(*) FROM lineorder_flat;

按照上述流程和参数执行的场合，数据量应和以下表格给出的生成数据的行数一致。

步骤六：查询测试

标准SSB查询语句，示例如下。其他语句详情请参见ssb-queries。

--Q1.1
SELECT SUM(lo_extendedprice * lo_discount) AS REVENUE
FROM lineorder, dates
WHERE
    lo_orderdate = d_datekey
    AND d_year = 1993
    AND lo_discount BETWEEN 1 AND 3
    AND lo_quantity < 25;
--Q1.2
SELECT SUM(lo_extendedprice * lo_discount) AS REVENUE
FROM lineorder, dates
WHERE
    lo_orderdate = d_datekey
    AND d_yearmonth = 'Jan1994'
    AND lo_discount BETWEEN 4 AND 6
    AND lo_quantity BETWEEN 26 AND 35;
    
--Q1.3
SELECT
    SUM(lo_extendedprice * lo_discount) AS REVENUE
FROM lineorder, dates
WHERE
    lo_orderdate = d_datekey
    AND d_weeknuminyear = 6
    AND d_year = 1994
    AND lo_discount BETWEEN 5 AND 7
    AND lo_quantity BETWEEN 26 AND 35;
    
--Q2.1
SELECT SUM(lo_revenue), d_year, p_brand
FROM lineorder, dates, part, supplier
WHERE
    lo_orderdate = d_datekey
    AND lo_partkey = p_partkey
    AND lo_suppkey = s_suppkey
    AND p_category = 'MFGR#12'
    AND s_region = 'AMERICA'
GROUP BY d_year, p_brand
ORDER BY p_brand;

--Q2.2
SELECT SUM(lo_revenue), d_year, p_brand
FROM lineorder, dates, part, supplier
WHERE
    lo_orderdate = d_datekey
    AND lo_partkey = p_partkey
    AND lo_suppkey = s_suppkey
    AND p_brand BETWEEN 'MFGR#2221' AND 'MFGR#2228'
    AND s_region = 'ASIA'
GROUP BY d_year, p_brand
ORDER BY d_year, p_brand;

--Q2.3
SELECT SUM(lo_revenue), d_year, p_brand
FROM lineorder, dates, part, supplier
WHERE
    lo_orderdate = d_datekey
    AND lo_partkey = p_partkey
    AND lo_suppkey = s_suppkey
    AND p_brand = 'MFGR#2239'
    AND s_region = 'EUROPE'
GROUP BY d_year, p_brand
ORDER BY d_year, p_brand;

--Q3.1
SELECT
    c_nation,
    s_nation,
    d_year,
    SUM(lo_revenue) AS REVENUE
FROM customer, lineorder, supplier, dates
WHERE
    lo_custkey = c_custkey
    AND lo_suppkey = s_suppkey
    AND lo_orderdate = d_datekey
    AND c_region = 'ASIA'
    AND s_region = 'ASIA'
    AND d_year >= 1992
    AND d_year <= 1997
GROUP BY c_nation, s_nation, d_year
ORDER BY d_year ASC, REVENUE DESC;

--Q3.2
SELECT
    c_city,
    s_city,
    d_year,
    SUM(lo_revenue) AS REVENUE
FROM customer, lineorder, supplier, dates
WHERE
    lo_custkey = c_custkey
    AND lo_suppkey = s_suppkey
    AND lo_orderdate = d_datekey
    AND c_nation = 'UNITED STATES'
    AND s_nation = 'UNITED STATES'
    AND d_year >= 1992
    AND d_year <= 1997
GROUP BY c_city, s_city, d_year
ORDER BY d_year ASC, REVENUE DESC;

--Q3.3
SELECT
    c_city,
    s_city,
    d_year,
    SUM(lo_revenue) AS REVENUE
FROM customer, lineorder, supplier, dates
WHERE
    lo_custkey = c_custkey
    AND lo_suppkey = s_suppkey
    AND lo_orderdate = d_datekey
    AND (
        c_city = 'UNITED KI1'
        OR c_city = 'UNITED KI5'
    )
    AND (
        s_city = 'UNITED KI1'
        OR s_city = 'UNITED KI5'
    )
    AND d_year >= 1992
    AND d_year <= 1997
GROUP BY c_city, s_city, d_year
ORDER BY d_year ASC, REVENUE DESC;

--Q3.4
SELECT
    c_city,
    s_city,
    d_year,
    SUM(lo_revenue) AS REVENUE
FROM customer, lineorder, supplier, dates
WHERE
    lo_custkey = c_custkey
    AND lo_suppkey = s_suppkey
    AND lo_orderdate = d_datekey
    AND (
        c_city = 'UNITED KI1'
        OR c_city = 'UNITED KI5'
    )
    AND (
        s_city = 'UNITED KI1'
        OR s_city = 'UNITED KI5'
    )
    AND d_yearmonth = 'Dec1997'
GROUP BY c_city, s_city, d_year
ORDER BY d_year ASC, REVENUE DESC;

--Q4.1
SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=4, enable_vectorized_engine=true, batch_size=4096, enable_cost_based_join_reorder=true, enable_projection=true) */
    d_year,
    c_nation,
    SUM(lo_revenue - lo_supplycost) AS PROFIT
FROM dates, customer, supplier, part, lineorder
WHERE
    lo_custkey = c_custkey
    AND lo_suppkey = s_suppkey
    AND lo_partkey = p_partkey
    AND lo_orderdate = d_datekey
    AND c_region = 'AMERICA'
    AND s_region = 'AMERICA'
    AND (
        p_mfgr = 'MFGR#1'
        OR p_mfgr = 'MFGR#2'
    )
GROUP BY d_year, c_nation
ORDER BY d_year, c_nation;

--Q4.2
SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true, batch_size=4096, enable_cost_based_join_reorder=true, enable_projection=true) */  
    d_year,
    s_nation,
    p_category,
    SUM(lo_revenue - lo_supplycost) AS PROFIT
FROM dates, customer, supplier, part, lineorder
WHERE
    lo_custkey = c_custkey
    AND lo_suppkey = s_suppkey
    AND lo_partkey = p_partkey
    AND lo_orderdate = d_datekey
    AND c_region = 'AMERICA'
    AND s_region = 'AMERICA'
    AND (
        d_year = 1997
        OR d_year = 1998
    )
    AND (
        p_mfgr = 'MFGR#1'
        OR p_mfgr = 'MFGR#2'
    )
GROUP BY d_year, s_nation, p_category
ORDER BY d_year, s_nation, p_category;

--Q4.3
SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true, batch_size=4096, enable_cost_based_join_reorder=true, enable_projection=true) */
    d_year,
    s_city,
    p_brand,
    SUM(lo_revenue - lo_supplycost) AS PROFIT
FROM dates, customer, supplier, part, lineorder
WHERE
    lo_custkey = c_custkey
    AND lo_suppkey = s_suppkey
    AND lo_partkey = p_partkey
    AND lo_orderdate = d_datekey
    AND s_nation = 'UNITED STATES'
    AND (
        d_year = 1997
        OR d_year = 1998
    )
    AND p_category = 'MFGR#14'
GROUP BY d_year, s_city, p_brand
ORDER BY d_year, s_city, p_brand;

步骤七：测试结果

以下是SSB 100GB和500GB的测试结果。