DuckDB PK StarRocks，谁的性能更胜一筹？TPC-DS性能测试一览（文章最后有详细的测试结果）

环境准备

安装DuckDB

dnf install -y wget
wget https://github.com/duckdb/duckdb/releases/download/v1.0.0/duckdb_cli-linux-amd64.zip
unzip duckdb_cli-linux-amd64.zip
mv duckdb /usr/local/bin

安装StarRocks

配置系统环境

systemctl stop firewalld
systemctl disable firewalld
cat >> /etc/sysctl.conf << EOF
vm.overcommit_memory=1
EOF
sysctl -p
echo madvise | sudo tee /sys/kernel/mm/transparent_hugepage/enabled
echo madvise | sudo tee /sys/kernel/mm/transparent_hugepage/defrag
cat >> /etc/rc.d/rc.local << EOF
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
   echo madvise > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
   echo madvise > /sys/kernel/mm/transparent_hugepage/defrag
fi
EOF
chmod +x /etc/rc.d/rc.local
swapoff -a
cat >> /etc/sysctl.conf << EOF
vm.swappiness=0
EOF
sysctl -p
setenforce 0
sed -i 's/SELINUX=.*/SELINUX=disabled/' /etc/selinux/config
sed -i 's/SELINUXTYPE/#SELINUXTYPE/' /etc/selinux/config
echo "export LANG=en_US.UTF8" >> /etc/profile
source /etc/profile
cp -f /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
hwclock
cat >> /etc/security/limits.conf << EOF
* soft nproc 65535
* hard nproc 65535
* soft nofile 655350
* hard nofile 655350
* soft stack unlimited
* hard stack unlimited
* hard memlock unlimited
* soft memlock unlimited
EOF
cat >> /etc/security/limits.d/20-nproc.conf << EOF 
*          soft    nproc     65535
root       soft    nproc     65535
EOF
cat >> /etc/sysctl.conf << EOF
net.ipv4.tcp_abort_on_overflow=1
EOF
sysctl -p
cat >> /etc/sysctl.conf << EOF
net.core.somaxconn=1024
EOF
sysctl -p
cat >> /etc/sysctl.conf << EOF
vm.max_map_count = 262144
EOF
sysctl -p
echo 120000 > /proc/sys/kernel/threads-max
echo 200000 > /proc/sys/kernel/pid_max

安装JDK 11

dnf install java-11-openjdk java-11-openjdk-devel -y

编辑/etc/profile文件

export JAVA_HOME=/usr/lib/jvm/java-11
export PATH=$JAVA_HOME/bin:$PATH

刷新配置

source /etc/profile

下载StarRocks二进制包

wget https://releases.starrocks.io/starrocks/StarRocks-3.2.7.tar.gz
tar -xzvf StarRocks-3.2.7.tar.gz

部署FE节点

创建元数据目录

mkdir /data/meta

修改FE配置文件fe/conf/fe.conf

priority_networks = 10.1.0.0/20
meta_dir = /data/meta
JAVA_OPTS="-Xmx4096m -XX:+UseMembar -XX:SurvivorRatio=8 -XX:MaxTenuringThreshold=7 -XX:+PrintGCDateStamps -XX:+PrintGCDetails -XX:+UseConcMarkSweepGC -XX:+UseParNewGC -XX:+CMSClassUnloadingEnabled -XX:-CMSParallelRemarkEnabled -XX:CMSInitiatingOccupancyFraction=80 -XX:SoftRefLRUPolicyMSPerMB=0 -Xloggc:$DORIS_HOME/log/fe.gc.log.$DATE"
JAVA_HOME = /usr/lib/jvm/java-11

启动fe

./fe/bin/start_fe.sh --daemon

部署BE节点

创建数据存储目录

mkdir /data/be

修改 BE 配置文件 be/conf/be.conf

storage_root_path = /data/be
priority_networks = 10.1.0.0/20
JAVA_HOME = /usr/lib/jvm/java-11

启动BE节点

./be/bin/start_be.sh --daemon

搭建集群

下载mysql客户端

dnf install mysql

在fe节点，使用MYSQL客户端连接

mysql -uroot -P9030 -h 127.0.0.1

添加BE节点

ALTER SYSTEM ADD BACKEND "10.1.2.14:9050";

查看FE节点和BE节点状态

SHOW FRONTENDS\G;
SHOW BACKENDS\G;

TPC-DS测试

DuckDB的TPC-DS测试

创建测试目录

mkdir -p /tpcds/duckdb/

DuckDB创建测试数据库并安装tpcds扩展

[root@duckdb-test ~]# duckdb /tpcds/duckdb/tpcds-100.db
v1.0.0 1f98600c2c
Enter ".help" for usage hints.
D INSTALL tpcds;
100% ▕████████████████████████████████████████████████████████████▏ 
D LOAD tpcds;

生成测试数据，这里使用sf=100，100GB的数据库：

D CALL dsdgen(sf = 100);
100% ▕████████████████████████████████████████████████████████████▏ 
┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ 0 rows  │
└─────────┘

查看测试数据库

[root@duckdb-test duckdb]# ls -lh tpcds-100.db 
-rw-r--r--. 1 root root 27G Jun 11 10:03 tpcds-100.db

编写python测试脚本tpcds.py，跑一个完整的TPCDS测试，为了防止OOM，将DuckDB使用的内存大小限制为16GB，并行线程数为4个，测试脚本如下：

import time
import duckdb
import logging
logger = logging.getLogger(__name__)
fh = logging.FileHandler('tpcds.log')
logger.setLevel(logging.INFO)
fh.setLevel(logging.INFO)
formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
fh.setFormatter(formatter)
logger.addHandler(fh)
database_url = 'tpcds-100.db'
mem_limit = '16GB'
thread_limit = 4
con = duckdb.connect(database_url)
con.execute(f"SET memory_limit = '{mem_limit}';")
con.execute(f"SET threads TO {thread_limit};")
start_time = time.time()
logger.info("Starting TPC-DS queries")
print("Starting TPC-DS queries")
with open('tpcds-duckdb-results.csv', 'w+', encoding='utf-8') as f:
    for query_id in range(1, 100):
        query_start_time = time.time()
        query = f"PRAGMA tpcds({query_id});"
        con.execute(query)
        query_end_time = time.time()
        logger.info(f"Query {query_id} took {query_end_time - query_start_time} seconds")
        f.write(f"{query_id},{round(query_end_time - query_start_time, 2)}\n")
    f.flush()
end_time = time.time()
print(f"TPCDS 99 Query Time taken: {int(end_time - start_time)} seconds")

运行测试脚本，由于需要较久的时间，所以放在tmux里运行，在执行前清除内存中的buffer，防止缓存影响测试结果的准确性：

tmux new -s duckdb
pip install duckdb
sync
echo 1 > /proc/sys/vm/drop_caches
[root@duckdb-test duckdb]# python tpcds.py 
Starting TPC-DS queries
TPCDS 99 Query Time taken: 906 seconds

可以看到，执行完99个TPC-DS测试共用906秒。

StarRocks的TPC-DS测试

创建测试目录，下载TPCDS工具包

mkdir -p /tpcds/sr/
cd /tpcds/sr/
wget https://starrocks-public.oss-cn-zhangjiakou.aliyuncs.com/tpcds-poc-1.0.zip
unzip tpcds-poc-1.0
cd tpcds-poc-1.0

生成sf=100的数据

sh bin/gen_data/gen-tpcds.sh 100 data_100

修改配置文件conf/starrocks.conf，指定mysql的host和port等配置，然后创建表结构

sh bin/create_db_table.sh ddl_100

导入数据

sh bin/stream_load.sh data_100

运行TPC-DS测试

sh bin/benchmark.sh -p -d tpcds

测试结果

DuckDB和StarRocks跑完99个SQL测试的总耗时如下，DuckDB在一部分测试场景下性能会更好：

99条SQL执行耗时如下所示：