GizmoSQL (powered by DuckDB) - 2 min 09s for $0.51 ($0.10 spot) on AWS - EC/2 instance type: r8gd.metal-48xl

[prmoore77]

GizmoSQL One-Trillion Row Challenge results

GizmoSQL completed the 1 trillion row challenge! GizmoSQL is powered by DuckDB and Apache Arrow Flight SQL

We launched a r8gd.metal-48xl EC/2 instance (costing $14.1082 / hr on-demand, and $2.8216 / hr spot) in region: us-east-1 using script: launch_aws_instance.sh in the attached zip file. We have an S3 end-point in the VPC to avoid egress costs.

That script calls script: scripts/mount_nvme_aws.sh which creates a RAID 0 storage array from the local NVMe disks - creating a single volume that has: 11.4TB in storage.

We launched the GizmoSQL Docker container using scripts/run_gizmosql_aws.sh - which includes the AWS S3 CLI utilities (so we can copy data, etc.).

We then copied the S3 data from s3://coiled-datasets-rp/1trc/ to the local NVMe RAID 0 array volume - using attached script: scripts/copy_coiled_data_from_s3.sh - and it used: 2.3TB of the storage space. This copy step took: 11m23.702s (costing $2.78 on-demand, and $0.54 spot).

We then launched GizmoSQL via the steps after the docker stuff in: scripts/run_gizmosql_aws.sh - and connected remotely from our laptop via the Arrow Flight SQL JDBC Driver - (see repo: https://github.com/gizmodata/gizmosql for details) - and ran this SQL to create a view on top of the parquet datasets:

CREATE VIEW measurements_1trc
AS
SELECT *
  FROM read_parquet('data/coiled-datasets-rp/1trc/*.parquet');

Row count:

We then ran the test query:

SELECT station, min(measure), max(measure), avg(measure)
FROM measurements_1trc
GROUP BY station
ORDER BY station;

It took: 0:02:22 (142s) the first execution (cold-start) - at an EC/2 on-demand cost of: $0.56, and a spot cost of: $0.11

It took: 0:02:09 (129s) the second execution (warm-start) - at an EC/2 on-demand cost of: $0.51, and a spot cost of: $0.10

Here are the query results:
1trc_results.csv

Scripts:
1trc_gizmosql.zip

Side note:
Query: SELECT COUNT(*) FROM measurements_1trc; takes: 21.8s

[scharlottej13]

Nice @prmoore77! This looks comparable to #6.

We launched a r8gd.metal-48xl EC2 instance

That's fun! I haven't done too much experimenting with Graviton4 yet. Did you try any other instance types? I'm guessing doing this on a single machine you're essentially picking the cheapest instance type with enough storage (so maybe not much to experiment with there).

[prmoore77]

Nice @prmoore77! This looks comparable to #6.

We launched a r8gd.metal-48xl EC2 instance

That's fun! I haven't done too much experimenting with Graviton4 yet. Did you try any other instance types? I'm guessing doing this on a single machine you're essentially picking the cheapest instance type with enough storage (so maybe not much to experiment with there).

Hi @scharlottej13 - I picked the largest Graviton 4 instance I could find, because I knew it would take a lot of CPU and memory to plow through this huge dataset. It also has 6 NVMe drives - making the RAID 0 array fast for quick I/O.

I haven't tried other instance types yet, but I've found Graviton 4's to be VERY good in terms of performance to cost ratio, so I think they would likely be best for this exercise...

I did forget to record the timing of the aws s3 cp command, I think it took about 5 minutes - I will try to do that soon...

I'll also update the issue above to reflect spot cost (it drops considerably to: $0.11 and $0.10)...

[scharlottej13]

I picked the largest Graviton 4 instance I could find, because I knew it would take a lot of CPU and memory to plow through this huge dataset. It also has 6 NVMe drives - making the RAID 0 array fast for quick I/O.

Yup, that makes a lot of sense! Fun to see it in action.

I did forget to record the timing of the aws s3 cp command, I think it took about 5 minutes - I will try to do that soon...

Ah yeah that'd be great to include! I know some examples are pre-downloading the dataset vs. others are streaming it from s3 (and so are limited by network bandwidth) and it's nice to know how long those steps took for making comparisons.

[prmoore77]

cc @duckdb, @hannes, @Mytherin, @pdet

[prmoore77]

I picked the largest Graviton 4 instance I could find, because I knew it would take a lot of CPU and memory to plow through this huge dataset. It also has 6 NVMe drives - making the RAID 0 array fast for quick I/O.

Yup, that makes a lot of sense! Fun to see it in action.

I did forget to record the timing of the aws s3 cp command, I think it took about 5 minutes - I will try to do that soon...

Ah yeah that'd be great to include! I know some examples are pre-downloading the dataset vs. others are streaming it from s3 (and so are limited by network bandwidth) and it's nice to know how long those steps took for making comparisons.

Hi @scharlottej13 - I've updated the issue (several times, sorry) - and now it has the copy script duration and cost...

[scharlottej13]

We then copied the S3 data from s3://coiled-datasets-rp/1trc/ to the local NVMe RAID 0 array volume - using attached script: scripts/copy_coiled_data_from_s3.sh - and it used: 2.3TB of the storage space. This copy step took: 11m23.702s (costing $2.78 on-demand, and $0.54 spot).

That's great, thanks @prmoore77 for contributing!