OpenSnowcat-rdb-loader and constant warnings about potential CPU starvation

[toni-loisko]

Hello,

We're currently running OpenSnowcat-rdb-loader for loading data into Snowflake from S3 in our staging environment. Since no jar files nor official container image are shipped for this one we built it ourselves with sbt snowflakeLoader/docker:stage and docker buildx build --platform linux/arm64 -t snowcat-rdb-loader:latest . (for whatever reason sbt snowflakeLoader/docker:publishLocal did not produce working container image for our environment so extra steps it is then).

With OpenSnowcat-rdb-loader we're seeing massive amounts of warnings like this:

[WARNING] Your app's responsiveness to a new asynchronous event (such as a new connection, an upstream response, or a timer) was in excess of 100 milliseconds. Your CPU is probably starving. Consider increasing the granularity of your delays or adding more cedes. This may also be a sign that you are unintentionally running blocking I/O operations (such as File or InetAddress) without the blocking combinator.

And on the Snowflake side we're seeing following message, which I've been told happens between bulk ingests (in one instance the number of messages was counted to be 86):
alter session /* JDBC:SnowflakeConnectionV1.setAutoCommit*/ set autocommit= true|false

Running environment is AWS EKS and instances themselves are of ARM Graviton 2, 3 and 4 (I think specifically c6gd, c7gd and c8gd). Rdb-loader gets following env variable: JAVA_TOOL_OPTIONS: "-Xms256m -Xmx2g -XX:+UseSerialGC -XX:ActiveProcessorCount=1 --add-opens=java.base/java.nio=ALL-UNNAMED" (ALL-UNNAMED was added with OpenSnowcat-rdb-loader because it wouldn't start otherwise, it wasn't necessary when I was testing it from my workstation, but as soon as we pushed image into ECR and EKS application would crash on start which I found strange as this should be needed for jdk16 and later if I read correctly and container image is set with jdk11 I think it was).

Config file being:

{
  "region": "<region>",
  "messageQueue": "<message_queue>",
  "storage": {
    "type": "snowflake",
    "snowflakeRegion": "<region>",
    "username": "<loader_user>",
    "password": {
      "ec2ParameterStore": {
        "parameterName": "<loader_pass_location>"
      }
    },
    "privateKey": {
      "key": {
        "ec2ParameterStore": {
          "parameterName": "<loader_key_location>
        }
      },
      "keyType": "RSA",
    },
    "role": "<role>",
    "account": "<snowflake_account>",
    "warehouse": "SNOWPLOW_WAREHOUSE",
    "schema": "ATOMIC",
    "database": "<snowflake_database>",
    "maxError": "1",
    "jdbcHost": "<snowflake_host>",
    "transformedStage": {
      "name": "SNOWPLOW_RDB_LOADER_STAGING",
      "location": "s3:/<bucket>/transformed/"
    }
  }
}

I'm pretty sure I was informed that data ingestion is still actually happening and data team can see data in Snowflake, but I'll get verification for that later, likely tomorrow.

So I'm wondering if the warnings and set autocommit spam is something to worry about? Is this just more vocal about things versus old rdb-loader? We saw none of that before we started testing OpenSnowcat-rdb-loader. Looking at the capacity allocation both CPU and memory should be fine, neither is exactly starved or even close and I didn't see anything strange IO wise either on the actual hosts.

[joaolcorreia]

@toni-loisko we are just doing minor updates on the RDB loader but let me investigate how we built our images. We are migrating everything from opensnowcat-rdb-loader to our Snowflake Streaming Loader and over time the opensnowcat-rdb-loader will be discontinued.

We are simplifying how data is loaded, and need to maintain the project sustainable, so we have created a comercial loader that support Snowflake, Elastic 9, Redshift, Azure, BigQuery, etc. I would be happy to chat. I should post more insights on the build today.

[joaolcorreia]

@toni-loisko we have a similar setup and also see the same message. I've raise this to the person who maintains rdb loader and I'll see what he says. BTW are you using OpenSnowcat at nebenan.de? We would love to display your logo on opensnowcat.io!

[lgirndt]

Hi @joaolcorreia Thanks for coming back to this. I'll share my findings when I was debugging the issue with @toni-loisko. We could boil down our case to this: It looks like the polling on SQS is done synchronously. Increasing cats.effect.cpu.starvation.check.threshold=0.7 at least gets rid of the warning. As fas as we understand the setup, the wait timeout on SQS should not lead to any problems. But we are happy to hear the maintainer's opinion as well.

WRT to showing our logo: We would be happy if you do so.

[joaolcorreia]

@lgirndt @toni-loisko I would like to chat with you!. We don't maintain the RDB loader, we are discontinuing it ... I would be happy to chat with you offline! My emai if you can reach out directly or feel free to pick up a time on my calendar!