The variety and number of ways that people test performance on Snowflake incorrectly continues to amaze me. I don’t think that testing the performance of a query in isolation is difficult, but it requires a very scientific approach. We need to isolate our variables at a minimum and often we need to develop educated hypothoses based on data and test them thoroughly.
I’ve already addressed what I think about benchmarking, but it’s worth another article to talk about what good query performance testing looks like. I’m not going to be covering testing the impact of concurrency or load testing in this article – that is a topic for another time.
Isolation
The most common mistake I see people making when testing the isolated performance of an individual query is that they run it on a shared warehouse. Put another way, sometimes people look at the performance of an indivdual run of a query on a shared warehouse and take that one timing as the defining run for performance testing, even when other queries are running concurrently.
Don’t get me wrong – Snowflake virtual warehouses are excellent at handling concurrent workloads. At the same time, Snowflake tries to make every query use all of the resources available. This means that even two queries running at the same time can have a performance impact on each other. It is important to also do load/concurrency testing, but that should be wholly separate from isolated testing of individual queries. Both are critical types of testing.
My preference is to have a warehouse that is dedicated for testing that is spun up only when needed. It is even better if each user has a warehouse that they can start up or resize for testing purposes. Personally, I tend to have warehouses of each size available that I can grab and use for testing that have no other workload. An idle warehouse consumes no credits, so this isn’t expensive.
Data Volume
I have been working on different SQL systems for more than 20 years. I’ve rarely seen performance scale exactly linearly with data volume. This makes it very hard to do performance tests with a subset or set of “representative” data and extrapolate results to larger data sets. There are some mistakes in SQL or in data modeling that only become apparent with significant data volumes. Many systems have a cliff – once you reach some unclear threshold, SQL performance falls off of a cliff for a variety of reasons. This is to simply say that expecting fully linear scaling of performance as data grows without attention to changing resource needs or changing data model bottlenecks is not reasonable.
Time and time again, I’ve seen a modest increase in data volume or a change in data distribution cause a sudden performance problem when “nothing changed”. Something is always changing, you just didn’t expect it was enough of a change to cause performance to fall off a cliff. This has been a pattern for every SQL system I have ever worked with. The reasons performance can suddenly go bad due to an increase in data volume include:
- A change in the execution plan
- A threshold for memory being surpassed
- A threshold for CPU utilization being surpassed
These have been true for every platform I have worked with, and Snowflake is no exception. Having just a little bit more data can push Snowflake to choose a hash-partitioned hash join instead of the generally faster broadcast join. Just a little bit more data can push over a memory threshold or a local cache threshold and cause a need for spilling that didn’t happen before.
Data Model
With small data sets for analytical workloads, a lot of sins committed through data modeling can be hidden. As data grows, mistakes in data modeling come to light, often through poor performance. There isn’t a single data model that is perfect for Snowflake. For any data set there are probably thousands of ways to model the data, and hundreds of them are probably just fine. But there are some data modeling patterns – often the extremes – that tend to be more problematc.
Don’t get me wrong, I spent at least 15 years of my career specializing in OLTP databases in strict 3rd or 4th-normal form. I love a good normalized data set. And yet, Snowflake often performs best with some level of denormalization. Finding the sweet spot for denormalization is an exercise in experimentation in itself. I’ve also seen the other end of the spectrum where hundreds of attributes are stored in a single JSON column. This is also not ideal. Minimally extracting columns that are frequently used in joins, clustering keys, filtering predicates, and aggregates tends to be a better choice.
Metrics
The next important thing to consider is what metrics to use to compare queries, compare different runs of the same query, or to establish a baseline. End to end execution time can be a useful one, but does not isolate the query – it includes network time and sometimes application processing time. It is a valuable metric for integration testing, but not for isolated query performance testing.
I’m a fan of using metrics that are available in ACCOUNT_USAGE.QUERY_HISTORY. These metrics truly isolate the time spent in Snowflake. TOTAL_ELAPSED_TIME is an excellent metric to use for comparison and baselining. It can also be useful to look at metrics such as BYTES_SCANNED, BYTES_WRITTEN, the ROWS_* metrics, PARTITIONS_TOTAL, and PARTITIONS_SCANNED. These metrics may be especially useful when evaluating clustering or deciding if Gen2 warehouses make sense for a MERGE, UPDATE, or DELETE operation.
Measurement
In any database system, but most especially in a cloud data platform, a single execution of a query can be influenced by so many different things. A metric value for a single execution should not be considered definitive. Generally you’ll want to do multiple measurements at different times of day, preferably on different days, and then use a statistically viable aggregation method to combine those together to get a single number. Often the average/mean is a great way to do this. However, it often makes sense to also use a measure of variability in conjuction with the average/mean. This could be variance, standard deviation, or just looking at the P95 value to understand worst-case scenarios.
It is important to exclude warehouse startup time (often milliseconds, but can be larger). Another important factor to either include, exclude, or make an intentional mix is the impact of a warm virtual warehouse data cache. My preference here is often to have one cold start execution for every three to four executions, but it is up to you on what makes sense for your workload. If you plan to run the query on a warehouse that is up most of the time, then excluding the first execution time after the warehouse is started can make a lot of sense.
When baselining or testing the performance of individual queries, I turn the use of the result set cache off using ALTER session set USE_CACHED_RESULT = FALSE. This makes sense for the testing of individual query performance, but using cached results can be an important aspect of the performance of a full workload of queries, particularly for a dashboard workload.
What to Do with the Results of Individual Query Performance Testing
Simply recording the details of query performance as a baseline is a perfectly good reason for doing testing of individual queries.
More often, when I am testing individual query performance, I’m changing one thing at a time to see what I can do to improve performance. Things I might be testing in isolation include:
- SQL syntax changes
- Changes to clustering
- Different warehouse sizes or types
- Data model changes
- Periodic re-establishment of baseline performance
- Understanding query performance without concurrency
Sometimes the results of testing are very informative and lead to improvements in performance, and other times they present further mysteries to explore. No matter what use results are put to, they are useful only if a reasonable methodology is used.
