You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@ignite.apache.org by "Nikita Sivkov (Jira)" <ji...@apache.org> on 2024/04/25 11:18:00 UTC
[jira] [Updated] (IGNITE-22112) [SQL] TPC-H q9 query with sc=0.1 takes long time
[ https://issues.apache.org/jira/browse/IGNITE-22112?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Nikita Sivkov updated IGNITE-22112:
-----------------------------------
Description:
Benchmark: [https://github.com/cmu-db/benchbase/tree/main/src/main/java/com/oltpbenchmark/benchmarks/tpch]
h1. Setup
* 1 server node
* TPC-H with scale factor = 0.1
h1. Steps
# Start an Ignite node
# Run benchbase with {{-s 1 --create=true --load=true --execute=false}} to preload data
# Observe via the benchbase log that the data was successfully loaded
# Run {{benchbase with -s 1 --create=false --load=false --execute=true}} to run the benchmark
h1. Expected result
The benchmark finishes after warmup + duration time in about the same time as other requests.
h1. Actual result
The [Q9|https://github.com/cmu-db/benchbase/blob/main/src/main/java/com/oltpbenchmark/benchmarks/tpch/procedures/Q9.java] query has significantly longer execution time. For example, it may take 10 minutes, while the rest queries take ~1-15 seconds.
{noformat}
SELECT
nation,
o_year,
SUM(amount) AS sum_profit
FROM
(
SELECT
n_name AS nation,
EXTRACT(YEAR
FROM
o_orderdate) AS o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
FROM
part,
supplier,
lineitem,
partsupp,
orders,
nation
WHERE
s_suppkey = l_suppkey
AND ps_suppkey = l_suppkey
AND ps_partkey = l_partkey
AND p_partkey = l_partkey
AND o_orderkey = l_orderkey
AND s_nationkey = n_nationkey
AND p_name LIKE ?
)
AS profit
GROUP BY
nation,
o_year
ORDER BY
nation,
o_year DESC{noformat}
was:
Benchmark: [https://github.com/cmu-db/benchbase/tree/main/src/main/java/com/oltpbenchmark/benchmarks/tpch]
h1. Setup
* 1 server node
* TPC-H with scale factor = 0.1
h1. Steps
# Start an Ignite node
# Run benchbase with {{-s 1 --create=true --load=true --execute=false}} to preload data
# Observe via the benchbase log that the data was successfully loaded
# Run {{benchbase with -s 1 --create=false --load=false --execute=true}} to run the benchmark
h1. Expected result
The benchmark finishes after warmup + duration time
h1. Actual result
The benchmark hangs for hours on [Q21|https://github.com/cmu-db/benchbase/blob/main/src/main/java/com/oltpbenchmark/benchmarks/tpch/procedures/Q21.java] query:
{noformat}
SELECT
s_name,
COUNT(*) AS numwait
FROM
supplier,
lineitem l1,
orders,
nation
WHERE
s_suppkey = l1.l_suppkey
AND o_orderkey = l1.l_orderkey
AND o_orderstatus = 'F'
AND l1.l_receiptdate > l1.l_commitdate
AND EXISTS
(
SELECT
*
FROM
lineitem l2
WHERE
l2.l_orderkey = l1.l_orderkey
AND l2.l_suppkey <> l1.l_suppkey
)
AND NOT EXISTS
(
SELECT
*
FROM
lineitem l3
WHERE
l3.l_orderkey = l1.l_orderkey
AND l3.l_suppkey <> l1.l_suppkey
AND l3.l_receiptdate > l3.l_commitdate
)
AND s_nationkey = n_nationkey
AND n_name = ?
GROUP BY
s_name
ORDER BY
numwait DESC,
s_name LIMIT 100{noformat}
> [SQL] TPC-H q9 query with sc=0.1 takes long time
> ------------------------------------------------
>
> Key: IGNITE-22112
> URL: https://issues.apache.org/jira/browse/IGNITE-22112
> Project: Ignite
> Issue Type: Bug
> Components: sql
> Reporter: Nikita Sivkov
> Priority: Major
> Labels: ignite-3, ignite3_performance
>
> Benchmark: [https://github.com/cmu-db/benchbase/tree/main/src/main/java/com/oltpbenchmark/benchmarks/tpch]
> h1. Setup
> * 1 server node
> * TPC-H with scale factor = 0.1
> h1. Steps
> # Start an Ignite node
> # Run benchbase with {{-s 1 --create=true --load=true --execute=false}} to preload data
> # Observe via the benchbase log that the data was successfully loaded
> # Run {{benchbase with -s 1 --create=false --load=false --execute=true}} to run the benchmark
> h1. Expected result
> The benchmark finishes after warmup + duration time in about the same time as other requests.
> h1. Actual result
> The [Q9|https://github.com/cmu-db/benchbase/blob/main/src/main/java/com/oltpbenchmark/benchmarks/tpch/procedures/Q9.java] query has significantly longer execution time. For example, it may take 10 minutes, while the rest queries take ~1-15 seconds.
> {noformat}
> SELECT
> nation,
> o_year,
> SUM(amount) AS sum_profit
> FROM
> (
> SELECT
> n_name AS nation,
> EXTRACT(YEAR
> FROM
> o_orderdate) AS o_year,
> l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
> FROM
> part,
> supplier,
> lineitem,
> partsupp,
> orders,
> nation
> WHERE
> s_suppkey = l_suppkey
> AND ps_suppkey = l_suppkey
> AND ps_partkey = l_partkey
> AND p_partkey = l_partkey
> AND o_orderkey = l_orderkey
> AND s_nationkey = n_nationkey
> AND p_name LIKE ?
> )
> AS profit
> GROUP BY
> nation,
> o_year
> ORDER BY
> nation,
> o_year DESC{noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)