Ducklake slowness with embedded duckdb server.

[prakashbalaji]

We have a ducklake server connected to postgres + s3 parquet files all in AWS (RDS + S3). The workload was tuned and we are able to get 20 seconds performance on first time query execution when duckdb is started in the machine. The same duckdb process inside JVM for the same tpch query responds only after 60 to 70 seconds. The subsequent query executions are faster both in the machine and JVM with approximately 3 second response time.

Do we have to tune some specific setting for duckdb with ducklake and parquet files to be performant when running inside JVM?

Thanks,
Prakash

[staticlibs]

Hi, thanks for the report! Can you show how you setup the connection to DuckLake in CLI and in JDBC? When you run it with JDBC - is only one connection used for it?

[prakashbalaji]

Thanks @staticlibs for the question, Made me review our connection config and realized that I was not pointing to the ducklake s3 files which were optimized for loading. Fixing the S3 path gives us the performance number which is very close to what we got running as a direct process.

We are using :ducklake in the url and are getting the db to be pinned. I would still be curious if there are anything we have to be aware of running the duckdb process directly in the machine vs running it in JVM, we are particularly concerned about keeping the process reliable.

[staticlibs]

@prakashbalaji

Thanks for confirming! I can suggest the following points:

1. to keep a look whether jdbc_stream_results and jdbc_pin_db options are used (enabled automatically with jdbc:duckdb:ducklake: URLs). I would think that jdbc_stream_results is more important with DuckLake (if any sizable amounts of Parquet data are going to be read) than with ordinary DuckDB operations. Without jdbc_pin_db the catalog still will be re-attached automatically, but with many short-lived connections this may add non-trivial overhead.

2. memory tuning for both DuckDB engine and JVM (a few details). By default DuckDB will use 80% of available OS memory, and JVM will use 25%. And for both of them these are soft limits with additional memory required for auxiliary stuff.

3. threads parameter to DuckDB, new thread pool is initialized for every DB being opened in this process, with the default pool size equal to number of available CPU cores

4. profiling information (Get profiling information api #135) and query progress (Expose query progress on statement #214) may be useful to monitor the performance, though I don't know how good they work specifically with DuckLake

5. when large result sets are returned, even with jdbc_stream_results=true the JDBC layer can add non-trivial overhead with per-row operations. For example, when date/timestamp fields are returned, per-JDBC spec it may require to do timezone lookup for every single value in every row (Result set Timestamp handling improvements #166), so returning dates as integers or strings instead may be more performant

6. running the duckdb process directly in the machine vs running it in JVM

These two variants should be very close, as all the compute is done locally the same way with the same DuckDB engine (only difference in CLI vs JVM layer wrapping). As an alternative, there was a recent article on organizing the compute with DuckDB remotely (that is mostly about GizmoSQL) - this may be even more useful with DuckLake than with plain DuckDB.

[staticlibs]

Closing the issue, please reopen if needed.