alamb commented on code in PR #19042:
URL: https://github.com/apache/datafusion/pull/19042#discussion_r2593265058
##########
benchmarks/bench.sh:
##########
@@ -99,6 +99,9 @@ clickbench_partitioned: ClickBench queries against
partitioned (100 files) parqu
clickbench_pushdown: ClickBench queries against partitioned (100 files)
parquet w/ filter_pushdown enabled
clickbench_extended: ClickBench \"inspired\" queries against a single
parquet (DataFusion specific)
+# Sorted Data Benchmarks (ORDER BY Optimization)
+data_sorted_clickbench: ClickBench queries on pre-sorted data using
prefer_existing_sort (tests sort elimination optimization)
Review Comment:
I realize it is a bit late, but maybe calling this `clickbench_sorted` would
be more consistent with the benchmarks above
##########
benchmarks/bench.sh:
##########
@@ -1201,6 +1210,101 @@ compare_benchmarks() {
}
+# Creates sorted ClickBench data from hits.parquet (full dataset)
+# The data is sorted by EventTime in ascending order
+# Uses datafusion-cli to reduce dependencies
+data_sorted_clickbench() {
+ SORTED_FILE="${DATA_DIR}/hits_sorted.parquet"
+ ORIGINAL_FILE="${DATA_DIR}/hits.parquet"
+
+ # Default memory limit is 12GB, can be overridden with
DATAFUSION_MEMORY_GB env var
+ MEMORY_LIMIT_GB=${DATAFUSION_MEMORY_GB:-12}
+
+ echo "Creating sorted ClickBench dataset from hits.parquet..."
+ echo "Configuration:"
+ echo " Memory limit: ${MEMORY_LIMIT_GB}G"
+ echo " Row group size: 64K rows"
+ echo " Compression: uncompressed"
+
+ if [ ! -f "${ORIGINAL_FILE}" ]; then
+ echo "hits.parquet not found. Running data_clickbench_1 first..."
+ data_clickbench_1
+ fi
+
+ if [ -f "${SORTED_FILE}" ]; then
+ echo "Sorted hits.parquet already exists at ${SORTED_FILE}"
+ return 0
+ fi
+
+ echo "Sorting hits.parquet by EventTime (this may take several minutes)..."
+
+ pushd "${DATAFUSION_DIR}" > /dev/null
+ echo "Building datafusion-cli..."
+ cargo build --release --bin datafusion-cli
+ DATAFUSION_CLI="${DATAFUSION_DIR}/target/release/datafusion-cli"
+ popd > /dev/null
+
+ echo "Using datafusion-cli to create sorted parquet file..."
+ "${DATAFUSION_CLI}" << EOF
+-- Memory and performance configuration
+SET datafusion.runtime.memory_limit = '${MEMORY_LIMIT_GB}G';
+SET datafusion.execution.spill_compression = 'uncompressed';
+SET datafusion.execution.sort_spill_reservation_bytes = 10485760; -- 10MB
+SET datafusion.execution.batch_size = 8192;
+SET datafusion.execution.target_partitions = 1;
+
+-- Parquet output configuration
+SET datafusion.execution.parquet.max_row_group_size = 65536;
+SET datafusion.execution.parquet.compression = 'uncompressed';
+
+-- Execute sort and write
+COPY (SELECT * FROM '${ORIGINAL_FILE}' ORDER BY "EventTime")
+TO '${SORTED_FILE}'
+STORED AS PARQUET;
+EOF
+
+ local result=$?
+
+ if [ $result -eq 0 ]; then
+ echo "✓ Successfully created sorted ClickBench dataset"
+
+ INPUT_SIZE=$(stat -f%z "${ORIGINAL_FILE}" 2>/dev/null || stat -c%s
"${ORIGINAL_FILE}" 2>/dev/null)
+ OUTPUT_SIZE=$(stat -f%z "${SORTED_FILE}" 2>/dev/null || stat -c%s
"${SORTED_FILE}" 2>/dev/null)
+ INPUT_MB=$((INPUT_SIZE / 1024 / 1024))
+ OUTPUT_MB=$((OUTPUT_SIZE / 1024 / 1024))
+
+ echo " Input: ${INPUT_MB} MB"
Review Comment:
I ran this and it showed
```shell
✓ Successfully created sorted ClickBench dataset
Input: 14095 MB
Output: 36159 MB
```
I think that is due to the lack of compression
##########
benchmarks/bench.sh:
##########
@@ -1197,10 +1206,105 @@ compare_benchmarks() {
}
+# Creates sorted ClickBench data from hits.parquet (full dataset)
+# The data is sorted by EventTime in ascending order
+# Uses datafusion-cli to reduce dependencies
+data_sorted_clickbench() {
+ SORTED_FILE="${DATA_DIR}/hits_sorted.parquet"
+ ORIGINAL_FILE="${DATA_DIR}/hits.parquet"
+
+ # Default memory limit is 12GB, can be overridden with
DATAFUSION_MEMORY_GB env var
+ MEMORY_LIMIT_GB=${DATAFUSION_MEMORY_GB:-12}
+
+ echo "Creating sorted ClickBench dataset from hits.parquet..."
+ echo "Configuration:"
+ echo " Memory limit: ${MEMORY_LIMIT_GB}G"
+ echo " Row group size: 64K rows"
+ echo " Compression: uncompressed"
+
+ if [ ! -f "${ORIGINAL_FILE}" ]; then
+ echo "hits.parquet not found. Running data_clickbench_1 first..."
+ data_clickbench_1
+ fi
+
+ if [ -f "${SORTED_FILE}" ]; then
+ echo "Sorted hits.parquet already exists at ${SORTED_FILE}"
+ return 0
+ fi
+
+ echo "Sorting hits.parquet by EventTime (this may take several minutes)..."
+
+ pushd "${DATAFUSION_DIR}" > /dev/null
+ echo "Building datafusion-cli..."
+ cargo build --release --bin datafusion-cli
+ DATAFUSION_CLI="${DATAFUSION_DIR}/target/release/datafusion-cli"
+ popd > /dev/null
+
+ echo "Using datafusion-cli to create sorted parquet file..."
+ "${DATAFUSION_CLI}" << EOF
+-- Memory and performance configuration
+SET datafusion.runtime.memory_limit = '${MEMORY_LIMIT_GB}G';
+SET datafusion.execution.spill_compression = 'uncompressed';
+SET datafusion.execution.sort_spill_reservation_bytes = 10485760; -- 10MB
+SET datafusion.execution.batch_size = 8192;
+SET datafusion.execution.target_partitions = 1;
Review Comment:
> @alamb I tried locally for target_partitions, it only not OOM for setting
to 1, even for 2 it will OOM, so i setting 1 here. I am not sure why.
I think by default there is no memory limit.
```
andrewlamb@Andrews-MacBook-Pro-3:~/Software/datafusion$ datafusion-cli --help
Command Line Client for DataFusion query engine.
...
-m, --memory-limit <MEMORY_LIMIT>
The memory pool limitation (e.g. '10g'), default to None (no limit)
```
You can potentially limit the memory usage like this
```shell
diff --git a/benchmarks/bench.sh b/benchmarks/bench.sh
index e79cca1a2a..d227fffde6 100755
--- a/benchmarks/bench.sh
+++ b/benchmarks/bench.sh
@@ -1244,8 +1244,8 @@ data_sorted_clickbench() {
DATAFUSION_CLI="${DATAFUSION_DIR}/target/release/datafusion-cli"
popd > /dev/null
- echo "Using datafusion-cli to create sorted parquet file..."
- "${DATAFUSION_CLI}" << EOF
+ echo "Using datafusion-cli (4GB memory) to create sorted parquet
file..."
+ "${DATAFUSION_CLI}" -m 4 << EOF
-- Memory and performance configuration
SET datafusion.runtime.memory_limit = '${MEMORY_LIMIT_GB}G';
SET datafusion.execution.spill_compression = 'uncompressed';
```
However, whenI tried that it still wasn't able to re-sort the data 🤔
##########
benchmarks/bench.sh:
##########
@@ -1201,6 +1210,101 @@ compare_benchmarks() {
}
+# Creates sorted ClickBench data from hits.parquet (full dataset)
+# The data is sorted by EventTime in ascending order
+# Uses datafusion-cli to reduce dependencies
+data_sorted_clickbench() {
+ SORTED_FILE="${DATA_DIR}/hits_sorted.parquet"
+ ORIGINAL_FILE="${DATA_DIR}/hits.parquet"
+
+ # Default memory limit is 12GB, can be overridden with
DATAFUSION_MEMORY_GB env var
+ MEMORY_LIMIT_GB=${DATAFUSION_MEMORY_GB:-12}
+
+ echo "Creating sorted ClickBench dataset from hits.parquet..."
+ echo "Configuration:"
+ echo " Memory limit: ${MEMORY_LIMIT_GB}G"
+ echo " Row group size: 64K rows"
+ echo " Compression: uncompressed"
+
+ if [ ! -f "${ORIGINAL_FILE}" ]; then
+ echo "hits.parquet not found. Running data_clickbench_1 first..."
+ data_clickbench_1
+ fi
+
+ if [ -f "${SORTED_FILE}" ]; then
+ echo "Sorted hits.parquet already exists at ${SORTED_FILE}"
+ return 0
+ fi
+
+ echo "Sorting hits.parquet by EventTime (this may take several minutes)..."
+
+ pushd "${DATAFUSION_DIR}" > /dev/null
+ echo "Building datafusion-cli..."
+ cargo build --release --bin datafusion-cli
Review Comment:
nice
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
