Snowflake - Poor performance on clustered table

0
votes

I have a quite large table (1.5TB, 18 billion records) that holds IoT type of data. In general, it has EVENT_TYPE (VARCHAR), EVENT_TIME (TIMESTAMP), and some columns storing event data. There are 12 event types in total with varying record distribution (from 5-10 million to 1-5 billion).

Most of the queries are filtering on specific event type and date range. Also, there are views created pointing to the data for specific event type. These are usually queried by date range.

There is clustered index created on EVENT_TYPE, CAST(EVENT_TIME AS DATE). I thought it would be most suitable for this type of workload. However, the performance is not very good.

For example, query like SELECT COUNT(1) FROM <event table> WHERE EVENT_TIME >= '2020-01-01' AND EVENT_TYPE = '<some type>' runs for 30 seconds on XL warehouse. I would expect better performance, as it 's using indexed columns. Also, it is scanning 25% of the partitions (42,786 out of 174,201), while it covers only 0.5% of total data.

I suspect that clustered index is not optimal. Here are initial results from SYSTEM$CLUSTERING_INFORMATION:

{
  "cluster_by_keys" : "LINEAR(EVENT_TYPE, CAST(EVENT_TIME AS DATE))",
  "total_partition_count" : 175792,
  "total_constant_partition_count" : 44575,
  "average_overlaps" : 97273.7777,
  "average_depth" : 93801.4483,
  "partition_depth_histogram" : {
    "00000" : 0,
    "00001" : 44536,
    "00002" : 0,
    "00003" : 0,
    "00004" : 0,
    "00005" : 0,
    "00006" : 0,
    "00007" : 0,
    "00008" : 0,
    "00009" : 0,
    "00010" : 0,
    "00011" : 0,
    "00012" : 0,
    "00013" : 0,
    "00014" : 0,
    "00015" : 0,
    "00016" : 0,
    "131072" : 130790,
    "65536" : 466
  }
}

I have tried re-clustering the table, but it did not improve much:

{
  "cluster_by_keys" : "LINEAR(EVENT_TYPE, CAST(EVENT_TIME AS DATE))",
  "total_partition_count" : 173905,
  "total_constant_partition_count" : 55880,
  "average_overlaps" : 78938.3633,
  "average_depth" : 74663.1889,
  "partition_depth_histogram" : {
    "00000" : 0,
    "00001" : 55829,
    "00002" : 0,
    "00003" : 0,
    "00004" : 0,
    "00005" : 0,
    "00006" : 0,
    "00007" : 0,
    "00008" : 0,
    "00009" : 0,
    "00010" : 0,
    "00011" : 0,
    "00012" : 0,
    "00013" : 0,
    "00014" : 0,
    "00015" : 0,
    "00016" : 0,
    "04096" : 5,
    "08192" : 7,
    "131072" : 117196,
    "16384" : 15,
    "32768" : 529,
    "65536" : 324
  }
}

Looking into results above I see a couple of issues: 1. average_overlaps and average_depth appears to high 2. The histogram is skewed to the end. I would expect more or less even distribution

Any ideas on what might be wrong, or how to improve it?

Thanks, Rimvis

3
snowflake-cloud-data-platform
Are you sure that your auto-clustering service is turned on? When you say that you tried to recluster the table, what statement did you run? Clustering can take time and doesn't occur by you simply adding the cluster keys to the table. Also, if this table is being added to in the order of the event_time AND event_time is often in your filter, I would recommend having that as your first key in your cluster, because the data will already be well clustered by that. – Mike Walton
Auto-clustering if not turned on. If fact, it is not available for my account. I'm getting "Unsupported feature 'Auto recluster'" when trying to enable it. Instead I was running this command: alter table <table name> recluster It's a bit strange that it works, because according to Snowflake documentation "As of May, 2020, manual reclustering has been deprecated for all accounts." docs.snowflake.com/en/user-guide/tables-clustering-manual.html What is also interesting, that average_overlaps and average_depth keeps decreasing when I run RECLUSTER. – Rimvydas Gurskis
Also, I was thinking about putting EVENT_TIME first, as the data comes in t hat order more or less. But then again, according to SF recommendations : As a general rule, Snowflake recommends ordering the columns from lowest cardinality to highest cardinality. docs.snowflake.com/en/user-guide/… As EVENT_TYPE is almost ALWAYS used in filtering, I thought it would better candidate for leading column – Rimvydas Gurskis

3 Answers

1
votes

TLDR: Try using hash(event_type) instead of event_type itself when defining your clustering keys.

I had the exact same issue, and our Snowflake sales engineer told us that it is caused by Snowflake using only the first six characters of a varchar field for determining the value of its clustering key.

In other words, all of the following values would be considered identical from the perspective of Snowflake's clustering operation:

event_1
event_2
event_3

In my case the clustering was showing a similar distribution as yours: good average depth, good overlap, a large percent of constant partitions, but a very strange tail in the partition depth histogram.

{
  "cluster_by_keys" : "LINEAR(\n  received_date,\n  event_type\n)",
  "total_partition_count" : 4214,
  "total_constant_partition_count" : 4129,
  "average_overlaps" : 1.0152,
  "average_depth" : 1.7243,
  "partition_depth_histogram" : {
    "00000" : 0,
    "00001" : 4119,
    "00002" : 3,
    "00003" : 4,
    "00004" : 0,
    "00005" : 0,
    "00006" : 0,
    "00007" : 0,
    "00008" : 0,
    "00009" : 0,
    "00010" : 0,
    "00011" : 0,
    "00012" : 0,
    "00013" : 0,
    "00014" : 3,
    "00015" : 0,
    "00016" : 0,
    "00032" : 14,
    "00064" : 71
  }
}

To fix this, I experimented by using a clustering key on the hash(event_type) rather than the event type itself. Notice how this improves all the metrics as well as fixes the tail on the histogram of clustering depth.

{
  "cluster_by_keys" : "LINEAR(\n  received_date, hash(event_type)\n)",
  "total_partition_count" : 2028,
  "total_constant_partition_count" : 1653,
  "average_overlaps" : 0.357,
  "average_depth" : 1.2002,
  "partition_depth_histogram" : {
    "00000" : 0,
    "00001" : 1643,
    "00002" : 367,
    "00003" : 15,
    "00004" : 3,
    "00005" : 0,
    "00006" : 0,
    "00007" : 0,
    "00008" : 0,
    "00009" : 0,
    "00010" : 0,
    "00011" : 0,
    "00012" : 0,
    "00013" : 0,
    "00014" : 0,
    "00015" : 0,
    "00016" : 0
  }
}

It's worth noting that the total number of partitions has decreased on the better clustered table. So some queries that were only pulling a few partitions may actually be slower, as they need to load and scan a larger sized partition.

But my guess is you'll have better performance by clustering on the hash(event) type over the event_type itself for most workloads.

0
votes

Regarding your overlaps and depth, that is actually what you want. You want those numbers to be as close to 1 as possible.

I recommend a few things for you:

1) Contact support and find out why auto-clustering isn't enabled

2) Recreate that table using an INSERT WITH OVERWRITE SELECT * FROM table ORDER BY event_time, event_type using the largest warehouse you can make (so it doesn't spill). This will save you money when auto-clustering finally starts for you.

3) Create your cluster on event_time::DATE, event_type, instead of the other way around.

I know the documentation specifies the other way around for those keys, but since you are loading the data every day by that date, this will naturally order your data by date, so you'll have far less auto-clustering costs to keep things nice and neat. And if you don't have enough data to benefit from the additional key on event_type, then you can just drop it anyway. Meaning, if the amount of data stored for a single day of data is only a few micro-partitions of data, then the additional key won't help very much, anyway.

0
votes

Since you're not using auto-clustering, you should continuously run alter table <table_name> recluster; until the average_depth of the table drops below a certain threshold.

For our large tables (more then 50 billion rows) our average_depth threshold is 10. I am sharing the clustering information of one of our tables that is similar to yours.

{
  "cluster_by_keys" : "LINEAR(CAST(DWH_TIME AS DATE), N, S)",
  "total_partition_count" : 2035920,
  "total_constant_partition_count" : 0,
  "average_overlaps" : 16.1197,
  "average_depth" : 9.9825,
  "partition_depth_histogram" : {
    "00000" : 0,
    "00001" : 62,
    "00002" : 1521,
    "00003" : 10949,
    "00004" : 29620,
    "00005" : 63038,
    "00006" : 113138,
    "00007" : 169567,
    "00008" : 232884,
    "00009" : 286370,
    "00010" : 304991,
    "00011" : 272397,
    "00012" : 215381,
    "00013" : 149515,
    "00014" : 92013,
    "00015" : 50387,
    "00016" : 22727,
    "00032" : 15506,
    "00064" : 4127,
    "00128" : 1727
  }
}