Any resources for troubleshooting performance problems? We're seeing absurd delays in our ETL process (several hours to write one row in an audit table) and are looking at all sources of bottlenecks. We already know increasing the compute power in our Snowflake warehouse hasn't helped, and are wondering about memory and swapping in our Matillion instance.
2 Community Answers
David Lipowitz —
Thanks for the question. This sounds like a locking or transactional issue to me. Are you by chance handling transactions manually in your jobs? I’ve seen a number of cases where this can cause unexpected locking on the data warehouse side. One suggestions would be to try the SHOW LOCKS command from the Snowflake console during the long-running, single-record insert you describe. Details on this command can be found here: SHOW LOCKS.
Can you please let us know if that command lets you diagnose the underlying issue?
Sorry I've not gotten back to this. Our consulting team discovered that we were not getting enough threads to keep up with all the concurrency in our job, and we fixed the problem by upgrading our EC2 instance size. Perhaps Matillion could update documentation about instance sizing - it revolves heavily around the number of people developing and doesn't point out the implications of thread count.