snowflake-connector-nodejs: SNOW-1747415 SNOW-1215393: Out of Memory Issue in memory-limited environment while streaming data
Please answer these questions before submitting your issue. In order to accurately debug the issue this information is required. Thanks!
-
What version of NodeJS driver are you using? ->snowflake-sdk -> Tried with 1.6.20, 1.9.3
-
What operating system and processor architecture are you using? -> macOS 14.3.1, arm64
-
What version of NodeJS are you using? -> v18.12.1
-
What are the component versions in the environment (
npm list)? -> NA -
Server version: -> 8.9.1
-
What did you do? Tried running this (snowflake_OOM.txt) script to export records from
SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.CUSTOMERtable, after limiting max-old-space-size=150 while running the script. A simpleSELECT * FROM CUSTOMER;was used, but the node process exited after exporting some rows (200000) withFATAL ERROR: Reached heap limit Allocation failed - JavaScript heap out of memoryerror but on modifying the same query with a LIMIT clause (with limit of 1,000,000), it was able to export 1M records with the same old-space limit. I tried workarounds mentioned in #43 and the linked issues but none worked. Tried using thestreamResultoption in the connection config and while executing the query, tried degrading to 1.6.20. Following is the metadata of the source tableAs it doesn’t have any VARIANT type column, so #536 and the related issues because of it shouldn’t matter in our case
-
What did you expect to see?
It should be able to export the same number of records before the OOM issue occurs, also it seems a large buffer is fetched if the LIMIT isn’t present in the query as it seems the GC triggers a bit late for query without LIMIT
-
Can you set logging to DEBUG and collect the logs? -> Can’t upload logs due to company security policies.
var snowflake = require('snowflake-sdk');
snowflake.configure(
{
logLevel: 'trace'
});
- What is your Snowflake account identifier, if any? (Optional)
About this issue
- Original URL
- State: open
- Created 3 years ago
- Comments: 23 (1 by maintainers)
I have some update. (Very) recently the server-side code has been changed to support smaller chunk sizes, which in turn hopefully results in less likely to be OOM. After the server-side code has been released, setting
CLIENT_RESULT_CHUNK_SIZE(values in MB, min: 16, max: 160, default: 160) should help in controlling the (uncompressed) size of the query result chunks generated on the server side which then needs to be downloaded by the client.I have yet to confirm of the Snowflake (server) version with which this new change will be released.
Again: very specially; this is not a client library release we’re waiting for, but the server side. Also consequentially, it is driver-independent, and affects all of our driver libraries, not just this. The client-side parameter (CLIENT_RESULT_CHUNK_SIZE) is already there for years, just did not work entirely as expected.
Will keep this thread posted.
It’s a session parameter, can be set with e.g.
ALTER SESSION SET .., but this whole issue is about it how it’s not working as expected, so please don’t expect much 😦hey @LalowiczB - the 8.39 indeed contained the announced fix, and i can confirm now
CLIENT_RESULT_CHUNK_SIZEcan be set to as low as 16MB (earlier: 48MB). I can also confirm unfortunately that this change did not seem to singlehandedly resolve the issue. Reproductions show similar results what you saw, so we need to look further into this what else needs to be done, for the driver to seamlessly be able to stream a large amount of rows even in a memory-limited environment.update: server-side changes seem to be rolled out with Snowflake version 8.39, which is scheduled for next week. Will update this thread when the appropriate Snowflake (server) version is live in production.
Hi @kartikgupta2607 We have internal discussion about this issue. We will inform you.
a very quick update: this seems to be deeply interlaced with how Snowflake as the backend behaves today when creating the query result chunks on the server-side, and might not be easily influenced solely on the client (driver) side - besides the workarounds already discussed. Multiple teams are working on this issue and I’ll keep this thread posted.
based on the same idea, perhaps the LIMIT … OFFSET … construct could be also a workaround as part of the big query, and alleviate the problem by ‘partitioning’ the query result and iterating over it. But it’s still just a workaround, until we find a solution.
You can try to fetch stream in specific range like this:
const stream = stmt.streamRows({ start, end })This way you can limit number of records fetched from snowflake. But you need to write your own loop and calculate start, end index.thank you again for the detailed reproduction, the issue could be reproduced even with the
TPCH_SF100version of theCUSTOMERtable which ‘only’ has 15000000 rows.Of course if one leaves the memory limit flag off, the query runs to completion and we can observe that memory usage goes up into the ~1G range with this table in this comment, then GC kicks in, and this goes until query completes. Additionally if a smaller table is created e.g. with 1M rows from the original table, and the smaller table is queried instead with
SELECT * FROM SMALLER_TABLE, then the query also runs to completion even with the memory limit.I’m also not sure if this is the expected behaviour with the ‘streaming rows’ functionality and also comparing some heap snapshots in the ‘bad’ and ‘good’ scenario raises additional questions so now I involved the driver team to take a look at this. Will keep this thread posted with the progress.
I have the same issue with node 20.9.0 and snowflake-sdk 1.9.0.
hi - thanks for raising this issue with us, i’ll take a look and see how we can proceed