topic Re: OOM while loading a lot of data through JDBC in Data Engineering

OOM while loading a lot of data through JDBC

krocodl — Fri, 13 Oct 2023 08:31:38 GMT

public void bigDataTest() throws Exception { int rowsCount = 100_000; int colSize = 1024; int colCount = 12; String colValue = "'"+"x".repeat(colSize)+"'"; String query = "select explode(sequence(1, "+rowsCount+"))," + String.join(",", Collections.nCopies(colCount, colValue)); try ( Connection conn = dataSource.getConnection() ) { PreparedStatement ps = conn.prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); ps.setFetchSize(1); ResultSet rs = ps.executeQuery(); int count = 0; while(rs.next()) { if(count++ % 100 == 0) { LOG.info("Count = {}", count); } } } }

With -Xmx200m I can read about 50_000 rows and after that I receive "Exception in thread "pool-12-thread-50" Exception in thread "pool-12-thread-1" java.lang.OutOfMemoryError: Java heap space
java.lang.OutOfMemoryError: Java heap space"

The memory picture is classic for OOM:

What can I see in the heapdump:

Re: OOM while loading a lot of data through JDBC

krocodl — Fri, 13 Oct 2023 08:30:21 GMT

DATABRICKS_JDBC_URL = "jdbc:databricks://xxx.cloud.databricks.com:443/default;" +
        "transportMode=http;" +
        "ssl=1;" +
        "httpPath=sql/protocolv1/o/xxxxx;AuthMech=3;MaxConsecutiveResultFileDownloadRetries=50;fetchsize=1"

Without custom MaxConsecutiveResultFileDownloadRetries I received 500638 JDBC error and can read only about 20_000 rows

databricksDriver = "com.databricks:databricks-jdbc:2.6.33"

Re: OOM while loading a lot of data through JDBC

-werners- — Fri, 13 Oct 2023 09:28:11 GMT

I'd first ingest the raw data onto a data lake (using some ingest tool, databricks is not the best for this imo), then process the data using databricks.

Re: OOM while loading a lot of data through JDBC

krocodl — Fri, 13 Oct 2023 10:13:42 GMT

Perhaps for Some use cases this will be the solution.

But it does not cancel the fact that there is a memory leak bug in the driver.

Re: OOM while loading a lot of data through JDBC

-werners- — Fri, 13 Oct 2023 12:10:47 GMT

not necessarily a memory leak. possibly the raw data is fetched and the query is processed in memory. don't know if that is the case though.

Re: OOM while loading a lot of data through JDBC

krocodl — Mon, 16 Oct 2023 07:28:54 GMT

Ok, let's call it a temporary minor memory starvation issue causing the virtual machine to crash.

Re: OOM while loading a lot of data through JDBC

krocodl — Mon, 16 Oct 2023 07:31:37 GMT

And here's another extremely minor issue leading to uncontrolled reproduction of threads. https://community.databricks.com/t5/data-engineering/thread-leakage-when-connection-cannot-be-established/td-p/39435
For some reason nobody responds to it either....

Re: OOM while loading a lot of data through JDBC

-werners- — Mon, 16 Oct 2023 09:19:56 GMT

That is, at least I think, because the jdbc driver is not part of the databricks platform itself (and closed source afaik).
Chances are small that someone of the community knows the ins an outs of the driver-code.
Now, if you are convinced that there is an actual bug in the databricks driver, I suggest you open a ticket at databricks so someone can look into it.
Because maybe you stumbled upon something here.

Re: OOM while loading a lot of data through JDBC

krocodl — Tue, 17 Oct 2023 13:05:20 GMT

I solved this issue, but it requires to change several classes. The final result:

Re: OOM while loading a lot of data through JDBC

-werners- — Wed, 18 Oct 2023 05:36:03 GMT

Nice!
You might wanna share your improvements with the driver devs.

Re: OOM while loading a lot of data through JDBC

krocodl — Wed, 18 Oct 2023 06:35:21 GMT

Yes, I really want to, but I have absolutely no idea how to send these edits to them.

They do not have a public repository or public ticket system.

Re: OOM while loading a lot of data through JDBC

-werners- — Wed, 18 Oct 2023 07:12:33 GMT

@Retired_modany idea?

Re: OOM while loading a lot of data through JDBC

yunbodeng — Tue, 14 Jan 2025 20:08:33 GMT

Hi, I am from databricks eng and we have had the driver developer look into this and could not repro. A couple of things to note:

1. 2.6.33 is a pretty old driver that does not have Cloud Fetch support.

2. nowadays, later versions have Cloud Fetch enabled by default. The client/server interactions' shape has changed significantly in the later versions. Can you try the new versions?

3. if you do not mind, would you share your client-side fix, so I can pass it on to the driver developers to take a look and see whether they are still relevant to include in the improvement to the later versions?

Thanks for your patience and support!

eng-partner-eco-help@databricks.com