Gluten Trino Performance Tests in Alibaba Cloud.

[wuxueyang96]

Environments

Trino

• Version: 411
• Coordinator: 1 x ecs.g7.2xlarge(8 vcores, 32 GB, 5 Gbps, 1 x 100 GB ESSD)
• Worker: 1 x ecs.hfr7.2xlarge (8 vcores, 64 GB, 3 Gbps, 1 x 100 GB ESSD)

HDFS

• HDFS Version: 3.2.1
• NameNode: 1 x ecs.g6.xlarge(4 vcores, 16 GB, 1.5 Gbps, 1 x 80 Ultra Disk as System Disk, 1 x 40 Ultra Disk as Data Disk)
• DataNode: 3 x ecs.g6.xlarge(4 vcores, 16 GB, 1.5 Gbps, 1 x 80 Ultra Disk as System Disk, 2 x 40 Ultra Disk as Data Disk)

TPC-H SF 100 Test Results

Query	Trino	Gluten Trino	Improve Rate
1	33.26	12.68	162.27%
2	7.20	3.80	89.75%
3	26.16	21.81	19.93%
4	24.50	20.09	22.00%
5	40.51	21.07	92.29%
6	16.43	9.19	78.73%
7	59.90	41.69	43.68%
8	28.35	19.24	47.39%
9	77.10	51.57	49.50%
10	33.69	22.16	52.04%
11	7.71	3.02	155.43%
12	21.77	10.43	108.71%
13	30.17	23.84	26.52%
14	13.54	11.20	20.86%
15	27.08	19.07	42.00%
16	8.46	6.03	40.22%
17	29.47	14.86	98.34%
18	54.20	36.21	49.66%
19	21.65	11.93	81.51%
20	17.51	13.02	34.47%
21	91.40	56.08	62.99%
22	8.37	6.72	24.49%
AVERAGE	30.84	19.80	55.71%
TOTAL	678.42	435.71	55.71%
GEOMEAN	24.38	15.29	59.46%

[nelsonspark]

Just test Tpch q01, query performance is different from yours .

Trino
Version: 411
Coordinator: CentOS 7.9 (8 vcores, 8 GB )
Worker: 1 CentOS 7.9 (8 vcores, 32 GB )

[root@perf2 etc]# uname -a
Linux perf2 3.10.0-1160.59.1.el7.x86_64 #1 SMP Wed Feb 23 16:47:03 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux

[root@perf2 etc]# lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 8
On-line CPU(s) list: 0-7
Thread(s) per core: 2
Core(s) per socket: 4
座： 1
NUMA 节点： 1
厂商 ID： GenuineIntel
CPU 系列： 6
型号： 85
型号名称： Intel(R) Xeon(R) Gold 6278C CPU @ 2.60GHz
步进： 7
CPU MHz： 2600.000
BogoMIPS： 5200.00
超管理器厂商： KVM
虚拟化类型： 完全
L1d 缓存： 32K
L1i 缓存： 32K
L2 缓存： 1024K
L3 缓存： 36608K
NUMA 节点0 CPU： 0-7
Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx pdpe1gb rdtscp lm constant_tsc rep_good nopl xtopology nonstop_tsc eagerfpu pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch invpcid_single ssbd ibrs ibpb stibp ibrs_enhanced fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms invpcid rtm mpx avx512f avx512dq rdseed adx smap clflushopt clwb avx512cd avx512bw avx512vl xsaveopt xsavec xgetbv1 arat avx512_vnni md_clear spec_ctrl intel_stibp flush_l1d arch_capabilities

Performance Compare :

Original Trino generates 35 splits , but gluten trino generates only 18 splits , so gluten trino takes more time .

the difference bewteen original and gluten is item (query.launch-native-engine=true) in config.properties , others are the same .

[wuxueyang96]

Do you mind posting your query's JSON so that we can analyze the reason why the results of our tests are different? @nelsonspark

[wuxueyang96]

Do you mind posting your query's JSON so that we can analyze the reason why the results of our tests are different? @nelsonspark

tpch q01 execute in schema tpch.sf100，

SELECT l.returnflag, l.linestatus, sum(l.quantity) AS sum_qty, sum(l.extendedprice) AS sum_base_price, sum(l.extendedprice * (1 - l.discount)) AS sum_disc_price, sum(l.extendedprice * (1 - l.discount) * (1 + l.tax)) AS sum_charge, avg(l.quantity) AS avg_qty, avg(l.extendedprice) AS avg_price, avg(l.discount) AS avg_disc, count(*) AS count_order FROM "lineitem" AS l WHERE l.shipdate <= DATE '1998-12-01' - INTERVAL '90' DAY GROUP BY l.returnflag, l.linestatus ORDER BY l.returnflag, l.linestatus ;

I'm sorry for the unclear instruction. But we need the JSON for each query which can be found through WebUI -> query -> JSON instead of the query itself.

[wuxueyang96]

@nelsonspark Please edit your comment and attach two different JSON files there. Please don't paste all of its contents there.

[nelsonspark]

gluten_q01.txt is mapping to which query config query.launch-native-engine=true , the other is normal query .

gluten_q01.txt
normal_q01.txt

[wuxueyang96]

@nelsonspark I'm not sure but it seems like that your coordinator is not only responsible for coordination. Some tasks were scheduled there. Perhaps you could set node-scheduler.include-coordinator=false and retry. And also, I have some configurations added in my environment, maybe it will help:

native.async-data-cache-enabled=true
task.concurrency=8

[nelsonspark]

@nelsonspark I'm not sure but it seems like that your coordinator is not only responsible for coordination. Some tasks were scheduled there. Perhaps you could set node-scheduler.include-coordinator=false and retry. And also, I have some configurations added in my environment, maybe it will help:

native.async-data-cache-enabled=true
task.concurrency=8

Also the problem. why normal query splits is more than gluten query , do you have the same problem ?

such as

Original Trino generates 35 splits , but gluten trino generates only 18 splits , so gluten trino takes more time .

[wuxueyang96]

@nelsonspark I'm not sure but it seems like that your coordinator is not only responsible for coordination. Some tasks were scheduled there. Perhaps you could set node-scheduler.include-coordinator=false and retry. And also, I have some configurations added in my environment, maybe it will help:

native.async-data-cache-enabled=true
task.concurrency=8

Also the problem. why normal query splits is more than gluten query , do you have the same problem ?

such as

Original Trino generates 35 splits , but gluten trino generates only 18 splits , so gluten trino takes more time .

May I ask what's the scale factor in your TPC-H test? I have reviewed my test result for TPC-H SF 100, in Q1, the number of splits of Gluten is approximately equal to Trino's, it's about 370 splits.

[nelsonspark]

Environments

Trino

• Version: 411
• Coordinator: 1 x ecs.g7.2xlarge(8 vcores, 32 GB, 5 Gbps, 1 x 100 GB ESSD)
• Worker: 1 x ecs.hfr7.2xlarge (8 vcores, 64 GB, 3 Gbps, 1 x 100 GB ESSD)

HDFS

• HDFS Version: 3.2.1
• NameNode: 1 x ecs.g6.xlarge(4 vcores, 16 GB, 1.5 Gbps, 1 x 80 Ultra Disk as System Disk, 1 x 40 Ultra Disk as Data Disk)
• DataNode: 3 x ecs.g6.xlarge(4 vcores, 16 GB, 1.5 Gbps, 1 x 80 Ultra Disk as System Disk, 2 x 40 Ultra Disk as Data Disk)

TPC-H SF 100 Test Results

Query Trino Gluten Trino Improve Rate
1 33.26 12.68 162.27%
2 7.20 3.80 89.75%
3 26.16 21.81 19.93%
4 24.50 20.09 22.00%
5 40.51 21.07 92.29%
6 16.43 9.19 78.73%
7 59.90 41.69 43.68%
8 28.35 19.24 47.39%
9 77.10 51.57 49.50%
10 33.69 22.16 52.04%
11 7.71 3.02 155.43%
12 21.77 10.43 108.71%
13 30.17 23.84 26.52%
14 13.54 11.20 20.86%
15 27.08 19.07 42.00%
16 8.46 6.03 40.22%
17 29.47 14.86 98.34%
18 54.20 36.21 49.66%
19 21.65 11.93 81.51%
20 17.51 13.02 34.47%
21 91.40 56.08 62.99%
22 8.37 6.72 24.49%
AVERAGE 30.84 19.80 55.71%
TOTAL 678.42 435.71 55.71%
GEOMEAN 24.38 15.29 59.46%

Please provide specific steps for testing tpch, as well as some key configurations.
The main reason is that my test conclusion is very different from yours.

Q1 passed, but Q2 reported an error ,

SELECT s.acctbal, s.name, n.name, p.partkey, p.mfgr, s.address, s.phone, s.comment FROM "part" p, "supplier" s, "partsupp" ps, "nation" n, "region" r WHERE p.partkey = ps.partkey AND s.suppkey = ps.suppkey AND p.size = 15 AND p.type like '%BRASS' AND s.nationkey = n.nationkey AND n.regionkey = r.regionkey AND r.name = 'EUROPE' AND ps.supplycost = ( SELECT min(ps.supplycost) FROM "partsupp" ps, "supplier" s, "nation" n, "region" r WHERE p.partkey = ps.partkey AND s.suppkey = ps.suppkey AND s.nationkey = n.nationkey AND n.regionkey = r.regionkey AND r.name = 'EUROPE' ) ORDER BY s.acctbal desc, n.name, s.name, p.partkey ;

2023-09-27T11:55:17.937+0800 ERROR pool-14-thread-5 io.trino.velox.execution.NativeSqlTaskExecutionManager Exception in fetchOutputFromNative: java.lang.IllegalStateException: Expected partition number to be zero
java.lang.IllegalStateException: Expected partition number to be zero
at com.google.common.base.Preconditions.checkState(Preconditions.java:502)
at io.trino.execution.buffer.ArbitraryOutputBuffer.enqueue(ArbitraryOutputBuffer.java:266)
at io.trino.execution.buffer.LazyOutputBuffer.enqueue(LazyOutputBuffer.java:271)
at io.trino.velox.execution.NativeSqlTaskExecution.updateOutput(NativeSqlTaskExecution.java:313)
at io.trino.velox.execution.NativeSqlTaskExecutionManager.lambda$fetchOutputFromNative$1(NativeSqlTaskExecutionManager.java:196)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:833)

[jikunshang]

Environments

Trino

• Version: 411
• Coordinator: 1 x ecs.g7.2xlarge(8 vcores, 32 GB, 5 Gbps, 1 x 100 GB ESSD)
• Worker: 1 x ecs.hfr7.2xlarge (8 vcores, 64 GB, 3 Gbps, 1 x 100 GB ESSD)

HDFS

• HDFS Version: 3.2.1
• NameNode: 1 x ecs.g6.xlarge(4 vcores, 16 GB, 1.5 Gbps, 1 x 80 Ultra Disk as System Disk, 1 x 40 Ultra Disk as Data Disk)
• DataNode: 3 x ecs.g6.xlarge(4 vcores, 16 GB, 1.5 Gbps, 1 x 80 Ultra Disk as System Disk, 2 x 40 Ultra Disk as Data Disk)

TPC-H SF 100 Test Results

Query Trino Gluten Trino Improve Rate
1 33.26 12.68 162.27%
2 7.20 3.80 89.75%
3 26.16 21.81 19.93%
4 24.50 20.09 22.00%
5 40.51 21.07 92.29%
6 16.43 9.19 78.73%
7 59.90 41.69 43.68%
8 28.35 19.24 47.39%
9 77.10 51.57 49.50%
10 33.69 22.16 52.04%
11 7.71 3.02 155.43%
12 21.77 10.43 108.71%
13 30.17 23.84 26.52%
14 13.54 11.20 20.86%
15 27.08 19.07 42.00%
16 8.46 6.03 40.22%
17 29.47 14.86 98.34%
18 54.20 36.21 49.66%
19 21.65 11.93 81.51%
20 17.51 13.02 34.47%
21 91.40 56.08 62.99%
22 8.37 6.72 24.49%
AVERAGE 30.84 19.80 55.71%
TOTAL 678.42 435.71 55.71%
GEOMEAN 24.38 15.29 59.46%

Please provide specific steps for testing tpch, as well as some key configurations. The main reason is that my test conclusion is very different from yours.

Q1 passed, but Q2 reported an error ,

SELECT s.acctbal, s.name, n.name, p.partkey, p.mfgr, s.address, s.phone, s.comment FROM "part" p, "supplier" s, "partsupp" ps, "nation" n, "region" r WHERE p.partkey = ps.partkey AND s.suppkey = ps.suppkey AND p.size = 15 AND p.type like '%BRASS' AND s.nationkey = n.nationkey AND n.regionkey = r.regionkey AND r.name = 'EUROPE' AND ps.supplycost = ( SELECT min(ps.supplycost) FROM "partsupp" ps, "supplier" s, "nation" n, "region" r WHERE p.partkey = ps.partkey AND s.suppkey = ps.suppkey AND s.nationkey = n.nationkey AND n.regionkey = r.regionkey AND r.name = 'EUROPE' ) ORDER BY s.acctbal desc, n.name, s.name, p.partkey ;

2023-09-27T11:55:17.937+0800 ERROR pool-14-thread-5 io.trino.velox.execution.NativeSqlTaskExecutionManager Exception in fetchOutputFromNative: java.lang.IllegalStateException: Expected partition number to be zero java.lang.IllegalStateException: Expected partition number to be zero at com.google.common.base.Preconditions.checkState(Preconditions.java:502) at io.trino.execution.buffer.ArbitraryOutputBuffer.enqueue(ArbitraryOutputBuffer.java:266) at io.trino.execution.buffer.LazyOutputBuffer.enqueue(LazyOutputBuffer.java:271) at io.trino.velox.execution.NativeSqlTaskExecution.updateOutput(NativeSqlTaskExecution.java:313) at io.trino.velox.execution.NativeSqlTaskExecutionManager.lambda$fetchOutputFromNative$1(NativeSqlTaskExecutionManager.java:196) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:833)

Can you provide your test env?

[nelsonspark]

Environments

Trino

• Version: 411
• Coordinator: 1 x ecs.g7.2xlarge(8 vcores, 32 GB, 5 Gbps, 1 x 100 GB ESSD)
• Worker: 1 x ecs.hfr7.2xlarge (8 vcores, 64 GB, 3 Gbps, 1 x 100 GB ESSD)

HDFS

• HDFS Version: 3.2.1
• NameNode: 1 x ecs.g6.xlarge(4 vcores, 16 GB, 1.5 Gbps, 1 x 80 Ultra Disk as System Disk, 1 x 40 Ultra Disk as Data Disk)
• DataNode: 3 x ecs.g6.xlarge(4 vcores, 16 GB, 1.5 Gbps, 1 x 80 Ultra Disk as System Disk, 2 x 40 Ultra Disk as Data Disk)

TPC-H SF 100 Test Results

Query Trino Gluten Trino Improve Rate
1 33.26 12.68 162.27%
2 7.20 3.80 89.75%
3 26.16 21.81 19.93%
4 24.50 20.09 22.00%
5 40.51 21.07 92.29%
6 16.43 9.19 78.73%
7 59.90 41.69 43.68%
8 28.35 19.24 47.39%
9 77.10 51.57 49.50%
10 33.69 22.16 52.04%
11 7.71 3.02 155.43%
12 21.77 10.43 108.71%
13 30.17 23.84 26.52%
14 13.54 11.20 20.86%
15 27.08 19.07 42.00%
16 8.46 6.03 40.22%
17 29.47 14.86 98.34%
18 54.20 36.21 49.66%
19 21.65 11.93 81.51%
20 17.51 13.02 34.47%
21 91.40 56.08 62.99%
22 8.37 6.72 24.49%
AVERAGE 30.84 19.80 55.71%
TOTAL 678.42 435.71 55.71%
GEOMEAN 24.38 15.29 59.46%

Please provide specific steps for testing tpch, as well as some key configurations. The main reason is that my test conclusion is very different from yours.
Q1 passed, but Q2 reported an error ,
SELECT s.acctbal, s.name, n.name, p.partkey, p.mfgr, s.address, s.phone, s.comment FROM "part" p, "supplier" s, "partsupp" ps, "nation" n, "region" r WHERE p.partkey = ps.partkey AND s.suppkey = ps.suppkey AND p.size = 15 AND p.type like '%BRASS' AND s.nationkey = n.nationkey AND n.regionkey = r.regionkey AND r.name = 'EUROPE' AND ps.supplycost = ( SELECT min(ps.supplycost) FROM "partsupp" ps, "supplier" s, "nation" n, "region" r WHERE p.partkey = ps.partkey AND s.suppkey = ps.suppkey AND s.nationkey = n.nationkey AND n.regionkey = r.regionkey AND r.name = 'EUROPE' ) ORDER BY s.acctbal desc, n.name, s.name, p.partkey ;
2023-09-27T11:55:17.937+0800 ERROR pool-14-thread-5 io.trino.velox.execution.NativeSqlTaskExecutionManager Exception in fetchOutputFromNative: java.lang.IllegalStateException: Expected partition number to be zero java.lang.IllegalStateException: Expected partition number to be zero at com.google.common.base.Preconditions.checkState(Preconditions.java:502) at io.trino.execution.buffer.ArbitraryOutputBuffer.enqueue(ArbitraryOutputBuffer.java:266) at io.trino.execution.buffer.LazyOutputBuffer.enqueue(LazyOutputBuffer.java:271) at io.trino.velox.execution.NativeSqlTaskExecution.updateOutput(NativeSqlTaskExecution.java:313) at io.trino.velox.execution.NativeSqlTaskExecutionManager.lambda$fetchOutputFromNative$1(NativeSqlTaskExecutionManager.java:196) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:833)

Can you provide your test env?

What environment variables do you want to see ?

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/root/trino-server-411/centos-shared/libs （libgluten_trino.so and so on in this path ）
export PATH=/usr/local/mysql/bin:$PATH
export HADOOP_HOME=/usr/hadoop/hadoop-3.2.4
export HADOOP_INSTALL=$HADOOP_HOME
export HADOOP_MAPRED_HOME=$HADOOP_HOME
export HADOOP_HDFS_HOME=$HADOOP_HOME
export HADOOP_COMMON_HOME=$HADOOP_HOME
export HADOOP_CONF_DIR=$HADOOP_HOME/etc/hadoop
export PATH=$PATH:$HADOOP_HOME/sbin:$HADOOP_HOME/bin
export HIVE_HOME=/usr/hive
export PATH=$PATH:$HIVE_HOME/bin

[jikunshang]

what's your hardware configuration? like how many nodes/workers you are using.

[nelsonspark]

what's your hardware configuration? like how many nodes/workers you are using.

Trino Info
Version: 411
Coordinator: 1 Node , 16 cores, 32 GB 1* 300G STAT
Worker: 3 Nodes , 16 cores, 32 GB 1* 300G STAT

HDFS Info
HDFS Version: 3.2.4

CPUInfo
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 16
On-line CPU(s) list: 0-15
Thread(s) per core: 2
Core(s) per socket: 8
座： 1
NUMA 节点： 1
厂商 ID： GenuineIntel
CPU 系列： 6
型号： 85
型号名称： Intel(R) Xeon(R) Gold 6161 CPU @ 2.20GHz

Other Info

HDFS and Trino hybrid department
Libgluten_trino.so is compiled in CentOS7.9 , run in the same environment

[zhanglistar]

@wuxueyang96 Not double the performance, why? Thanks.