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!

  1. What version of NodeJS driver are you using? ->snowflake-sdk -> Tried with 1.6.20, 1.9.3

  2. What operating system and processor architecture are you using? -> macOS 14.3.1, arm64

  3. What version of NodeJS are you using? -> v18.12.1

  4. What are the component versions in the environment (npm list)? -> NA

  5. Server version: -> 8.9.1

  6. What did you do? Tried running this (snowflake_OOM.txt) script to export records from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.CUSTOMER table, after limiting max-old-space-size=150 while running the script. A simple SELECT * FROM CUSTOMER; was used, but the node process exited after exporting some rows (200000) with FATAL ERROR: Reached heap limit Allocation failed - JavaScript heap out of memory error 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 the streamResult option in the connection config and while executing the query, tried degrading to 1.6.20. Following is the metadata of the source table image As it doesn’t have any VARIANT type column, so #536 and the related issues because of it shouldn’t matter in our case

  7. 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

  8. 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'
});
  1. 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)

Most upvoted comments

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_SIZE can 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_SF100 version of the CUSTOMER table 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