SNOW-1858311: Large memory usage (support for unbuffered queries)

[goetas]

1. What version of PDO/PHP driver are you using?
   PHP 8.2, Snowflake pdo 1.2.6

2. What operating system and processor architecture are you using?
   Debian buster

3. What version of C/C++ compiler are you using?
   gcc version 8.3.0 (Debian 8.3.0-6)

4. What did you do?

    $pdo = new PDO(
        'snowflake:account=XXX',
        'XXX',
        'XXX'
    );

    $query = 'select 1 from table (generator(rowcount => 2000000))';
    $r = $pdo->query($query);

    foreach ($r as $row) {
      // no op
    }
    var_dump(memory_get_peak_usage()/1024/1024); // ~8MB

5. What did you expect to see?

   PHP as expected reported a very low memory usage (~8mb) but the total memory used by the process peaked at 2gb
   (reported by top as VIRT).

My guess that the memory is used (and not released?) for the data buffering/transfer by the php extension.

6. Can you set logging to DEBUG and collect the logs?

   https://community.snowflake.com/s/article/How-to-generate-log-file-on-Snowflake-connectors

7. What is your Snowflake account identifier, if any? (Optional)
   gqa33272

[goetas]

ok, seems a PDO thing... https://www.php.net/manual/en/mysqlinfo.concepts.buffering.php

is there a chance to offer such feature for snowflake PDO.... since it mainly contains large datasets

[AV-GregMayes]

Also running into this issue. With MySQL you can do $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); to make it use unbuffered queries. Would be great to have an equivalent in Snowflake, if it doesn't already eixst.

[goetas]

With postgres , it is possible to use FETCH FORWARD 5 FROM cursor_name;... but in snowflake FETCH can not be used outside of a stored procedure (and it fetches always one row)

[sfc-gh-dszmolka]

hi - apologies for leaving this unattended for so long, we're changing that going forward.

we'll consider this enhancement request and i'll keep this thread posted on the progress, if any.
In the meantime, you can consider using LIMIT .. OFFSET .. as a workaround and 'paginate' through very large query results in multiple iterations to lower memory usage.

[goetas]

yea, that is what we are doing, but is a query takes a large time to complete, running it multiple times with limit offset makes it worse

[android-rewstar]

My problem (#410) still persists if even if I paginate with LIMIT and OFFSET
The query works fine and I get results but after a few requests it fails on this line (not on the execute line)...
$db = new PDO($connectionstring, $username, "");

With this error.
0 => PDOException: SQLSTATE[08001] [240012] curl_easy_perform() failed: Out of memory in
[filepath and name].php:179
Stack trace:
#0 [filepath and name].php(179): PDO->__construct()

[shanedale]

My problem (#410) still persists if even if I paginate with LIMIT and OFFSET The query works fine and I get results but after a few requests it fails on this line (not on the execute line)... $db = new PDO($connectionstring, $username, "");

With this error. 0 => PDOException: SQLSTATE[08001] [240012] curl_easy_perform() failed: Out of memory in [filepath and name].php:179 Stack trace: #0 [filepath and name].php(179): PDO->__construct()

my problem appears to be the same at the creation of PDO

Running a script that does multiple retrievals using this code:

Running a script that does multiple retrievals using this code:

function getFeedData($semester, $account, $user, $pass) {
    try {
        echo 'Memory usage before snowflake pdo creation: ' . memory_get_usage() . "\n";
        $pdo = new PDO("snowflake:account=" . $account, $user, $pass);
        $query = 'SELECT * FROM SCS_ANALYTICS.S3.SCS_GRADES WHERE "COURSE SEMESTER" = \'' . $semester . '\'';

        echo 'Memory usage before snowflake retrieval: ' . memory_get_usage() . "\n";
        $stmt = $pdo->prepare($query);

        $stmt->execute();
        $records = $stmt->fetchAll(PDO::FETCH_ASSOC);
        echo 'Memory usage after snowflake retrieval: ' . memory_get_usage() . " number of records: " . sizeof($records) . "\n";

        return $records;
    } catch (PDOException $e) {
        error_log($e->getMessage());
        echo "Snowflake Connection failed: " . $e->getMessage() . "\n";
    } finally {
        if ($stmt) {
            $stmt = null;
        }
        if ($pdo) {
            $pdo = null;
        }
    }
}


Output:
Memory usage before snowflake pdo creation: 16379784
Memory usage before snowflake retrieval: 16380304
Memory usage after snowflake retrieval: 36798616 number of records: 16488
Memory usage after creating an array: 21027928 bytes for semester: M18  numrecs: 16488
Memory usage before snowflake pdo creation: 21026112
Snowflake Connection failed: SQLSTATE[08001] [240012] curl_easy_perform() failed: Out of memory


Log file: 
2025-01-16 11:00:37.123 INFO  PDO   snowflake_driver.c  649: Snowflake PHP PDO Driver: 8.3.14-3.0.3
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  655: user: xxxx
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  658: password: ****
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  662: host: (null)
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  666: port: 443
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  671: account: xxxxx
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  676: region: (null)
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  681: database: (null)
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  686: schema: (null)
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  691: warehouse: (null)
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  695: role: (null)
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  700: protocol: https
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  705: insecureMode: (null)
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  718: timezone: (null)
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  720: autocommit: 1
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  729: application: (null)
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  738: authenticator: (null)
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  747: priv_key_file: (null)
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  756: priv_key_file_pwd: ****
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  765: proxy: (NULL)
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  774: no_proxy: (null)
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  781: disablequerycontextcache: false
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  789: includeretryreason: true
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  798: logintimeout: 300
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  807: maxhttpretries: 7
2025-01-16 11:00:37.124 DEBUG PDO   snowflake_driver.c  816: retryimeout: 300
2025-01-16 11:00:37.124 INFO  C     client.c          841: Snowflake C/C++ API: 1.1.0, OS: Linux, OS Version: 5.15.0-124-generic
2025-01-16 11:00:37.124 INFO  C     client.c          489: Connecting to GLOBAL Snowflake domain
2025-01-16 11:00:37.124 DEBUG C     client.c          518: application name: PDO
2025-01-16 11:00:37.124 DEBUG C     client.c          519: application version: 8.3.14-3.0.3
2025-01-16 11:00:37.124 DEBUG C     client.c          520: authenticator: snowflake
2025-01-16 11:00:37.124 DEBUG C     client.c          521: user: xxxxxx
2025-01-16 11:00:37.124 DEBUG C     client.c          522: password: ****
2025-01-16 11:00:37.124 DEBUG C     client.c          531: host: xxxx.snowflakecomputing.com
2025-01-16 11:00:37.124 DEBUG C     client.c          532: port: 443
2025-01-16 11:00:37.124 DEBUG C     client.c          533: account: xxxxx
2025-01-16 11:00:37.124 DEBUG C     client.c          534: region: (null)
2025-01-16 11:00:37.124 DEBUG C     client.c          535: database: (null)
2025-01-16 11:00:37.124 DEBUG C     client.c          536: schema: (null)
2025-01-16 11:00:37.124 DEBUG C     client.c          537: warehouse: (null)
2025-01-16 11:00:37.124 DEBUG C     client.c          538: role: (null)
2025-01-16 11:00:37.124 DEBUG C     client.c          539: protocol: https
2025-01-16 11:00:37.124 DEBUG C     client.c          540: autocommit: true
2025-01-16 11:00:37.124 DEBUG C     client.c          541: insecure_mode: false
2025-01-16 11:00:37.124 DEBUG C     client.c          542: ocsp_fail_open: false
2025-01-16 11:00:37.124 DEBUG C     client.c          543: timezone: (null)
2025-01-16 11:00:37.125 DEBUG C     client.c          544: login_timeout: 300
2025-01-16 11:00:37.125 DEBUG C     client.c          545: network_timeout: 0
2025-01-16 11:00:37.125 DEBUG C     client.c          546: retry_timeout: 300
2025-01-16 11:00:37.125 DEBUG C     client.c          547: retry_count: 7
2025-01-16 11:00:37.125 DEBUG C     client.c          548: qcc_disable: false
2025-01-16 11:00:37.125 DEBUG C     client.c          549: include_retry_reason: true
2025-01-16 11:00:37.125 DEBUG C     connection.c      696: URL: https://xxxxx.snowflakecomputing.com:443/session/v1/login-request?request_id=66f03c9e-540f-4955-b92d-e4482556f8d3&&&&&request_guid=00000000-0000-0000-0000-000000000000
2025-01-16 11:00:37.162 ERROR C     http_perform.c    403: curl_easy_perform() failed: Out of memory
2025-01-16 11:00:37.163 ERROR C     client.c          970: No response
2025-01-16 11:00:37.163 DEBUG PDO   snowflake_driver.c   45: file=/home/dales/pdo_snowflake/snowflake_driver.c line=821
2025-01-16 11:00:37.163 ERROR PDO   snowflake_driver.c   52: connection error
2025-01-16 11:00:37.163 ERROR PDO   snowflake_driver.c   56: error code: 240012
2025-01-16 11:00:37.163 ERROR PDO   snowflake_driver.c   71: sqlstate: 08001, msg: curl_easy_perform() failed: Out of memory
2025-01-16 11:00:37.163 ERROR PDO   snowflake_driver.c   74: Failed to allocate DBH

[sfc-gh-dszmolka]

for this issue where the result set is retrieved in batches of subsets, but still OOMs, let's track it in #410 until it is proven it is not a separate bug

so we can keep this #318 tracking enhancement for unbuffered query support