5 ms·

I’ve been using Redshift fairly heavily for the past couple years. I haven’t had the time to do comparisons between BigTable, unfortunately. I’ve been too strap

by afpx 9y ago

I’ve been using Redshift fairly heavily for the past couple years. I haven’t had the time to do comparisons between BigTable, unfortunately. I’ve been too strapped for time. And, we have only just started experimenting with Snowflake recently. I would like to try out BigTable, but experimenting with it has required too much work. I would have to move my entire data set (Several Petabytes) over to Google. Redshift has been fine. But, with Reshift, we have two big issues: 1) Loading data takes too long. Even with efficient copy operations from S3, it takes too long to import data at scale. 2) Ineffient queries frequently overflow to disk and consume the entire SSD. I’ve tried to train my data science team on how to avoid inefficient queries, but it’s been impossible. So, I’m really looking forward to seeing the results from part 2!

wjossey 9y ago
I'd really recommend taking a look at BigQuery, as I think it could address at least the inefficient query trigger extreme load. It'd be great to have you report back your experiences as well. The GCP podcast team just did a good interview recently with some team members that you might find useful: https://www.gcppodcast.com/post/episode-94-big-query-under-the-hood-with-tino-tereshko-and-jordan-tigani/ https://www.gcppodcast.com/post/episode-94-big-query-under-t...
EwanToo 9y ago
For the Redshift data loading issue, look at the new Redshift Spectrum features (if it's available in your region). It can now load/query data directly from s3
ddorian43 9y ago
Bigtable is oltp. You meant BigQuery ?
- afpx 9y ago
  Yeah - sorry. Not familiar enough with the Google cloud to get my product names right.
mattbillenstein 9y ago
Have you thought about using EMR to generate rollups of the S3 data and then putting that into Redshift?
- afpx 9y ago
  We do that, for the most part. It definitely helps.
mulmen 9y ago
As far as training your users on efficient queries do you leverage WLM queues to push the worst queries into a longer running queue? It sounds like you are dealing with a lot of data. You say you are using SSDs but also that you have petabytes of data. The max size for a Redshift cluster with SSDs (dc1.8xlarge) is 326TB. Do you only keep a subset of your data in Redshift? How much data are you loading on a daily basis? Another comment suggested using EMR to create the rollups, have you looked at using a second cluster of dc2.8xlarge instances for rollups? Have you looked at Spectrum? I'd love to hear your experiences and solutions because it sounds like you're pushing Redshift to it's limits.
scapecast 9y ago
Lars here, co-founder of a start-up that specializes in optimizing Amazon Redshift performance. We work with a lot of Fivetran customers who we help maximize their load speeds. My hunch is that your data science team can solve both issues by using the Workload Management (WLM) feature in Redshift. Redshift operates in a queueing model. As a user, you can define queues for your different workloads, and then assign them a specific concurrency / memory configuration. The default configuration for Redshift is one queue with a concurrency of 5. If you run more than 5 concurrent queries, then your queries wait in the queue. That's when the "takes too long" goes into effect. The available amount of memory is distributed evenly across each concurrency slot. Say that you have a total of 1GB, then with a default configuration, each of the 5 concurrency slot gets 200MB memory. If you run a query that needs more than 200MB, then it falls back to disk. Disk-based queries are really bad for the cluster, because they consume a lot of I/O which slows down the entire cluster, not just a specific queue. You can fix both by configuring Redshift specific to your workloads. As a general set-up, we recommend defining 4 queues (load, transform, ad_hoc, catch_all) to isolate your workloads from each other. ------------- 1) data loading takes too long Create a dedicated "load" queue in the WLM that is only responsible for loading data into Redshift. These are all COPY statements. By separating your data loads from everything else, you make sure that your data loads are protected from e.g. some big ad-hoc queries that some not-so-skilled SQL user writes. The next trick is to figure out how many concurrent slots you need for all your loads. 2) queries frequently overflow to disk I assume this is true for some large aggregations / roll-ups you're running, or some massive ad-hoc queries. By giving them their own queue, with sufficient memory, you start eliminating the volume of disk-based queries. -------- The problem is that it's not straightforward to figure out how to set the right concurrent / memory configuration. The information is hidden in the log files (which Redshift deletes on a rolling basis), and sifting through those log files requires writing really complex queries. That just put more load on the cluster... However, if you do get it right, you can run blazing fast data loads and queries. We struggled with this problem ourselves at our previous company, and so with https://www.intermix.io https://www.intermix.io we built a service to solve that problem. If you ping me at lars at intermix dot io - I can give you an extended free trial. I'll pay you dinner (in SF, or maybe in Vegas at Reinvent), if we can't solve both issues for you. How is that?
- 9y ago

by afpx 9y ago

wjossey 9y ago

I'd really recommend taking a look at BigQuery, as I think it could address at least the inefficient query trigger extreme load. It'd be great to have you report back your experiences as well. The GCP podcast team just did a good interview recently with some team members that you might find useful: https://www.gcppodcast.com/post/episode-94-big-query-under-the-hood-with-tino-tereshko-and-jordan-tigani/ https://www.gcppodcast.com/post/episode-94-big-query-under-t...

EwanToo 9y ago

For the Redshift data loading issue, look at the new Redshift Spectrum features (if it's available in your region). It can now load/query data directly from s3

ddorian43 9y ago

Bigtable is oltp. You meant BigQuery ?

afpx 9y ago
Yeah - sorry. Not familiar enough with the Google cloud to get my product names right.

mattbillenstein 9y ago

Have you thought about using EMR to generate rollups of the S3 data and then putting that into Redshift?

afpx 9y ago
We do that, for the most part. It definitely helps.

mulmen 9y ago

As far as training your users on efficient queries do you leverage WLM queues to push the worst queries into a longer running queue? It sounds like you are dealing with a lot of data. You say you are using SSDs but also that you have petabytes of data. The max size for a Redshift cluster with SSDs (dc1.8xlarge) is 326TB. Do you only keep a subset of your data in Redshift? How much data are you loading on a daily basis? Another comment suggested using EMR to create the rollups, have you looked at using a second cluster of dc2.8xlarge instances for rollups? Have you looked at Spectrum? I'd love to hear your experiences and solutions because it sounds like you're pushing Redshift to it's limits.

scapecast 9y ago

Lars here, co-founder of a start-up that specializes in optimizing Amazon Redshift performance. We work with a lot of Fivetran customers who we help maximize their load speeds. My hunch is that your data science team can solve both issues by using the Workload Management (WLM) feature in Redshift. Redshift operates in a queueing model. As a user, you can define queues for your different workloads, and then assign them a specific concurrency / memory configuration. The default configuration for Redshift is one queue with a concurrency of 5. If you run more than 5 concurrent queries, then your queries wait in the queue. That's when the "takes too long" goes into effect. The available amount of memory is distributed evenly across each concurrency slot. Say that you have a total of 1GB, then with a default configuration, each of the 5 concurrency slot gets 200MB memory. If you run a query that needs more than 200MB, then it falls back to disk. Disk-based queries are really bad for the cluster, because they consume a lot of I/O which slows down the entire cluster, not just a specific queue. You can fix both by configuring Redshift specific to your workloads. As a general set-up, we recommend defining 4 queues (load, transform, ad_hoc, catch_all) to isolate your workloads from each other. ------------- 1) data loading takes too long Create a dedicated "load" queue in the WLM that is only responsible for loading data into Redshift. These are all COPY statements. By separating your data loads from everything else, you make sure that your data loads are protected from e.g. some big ad-hoc queries that some not-so-skilled SQL user writes. The next trick is to figure out how many concurrent slots you need for all your loads. 2) queries frequently overflow to disk I assume this is true for some large aggregations / roll-ups you're running, or some massive ad-hoc queries. By giving them their own queue, with sufficient memory, you start eliminating the volume of disk-based queries. -------- The problem is that it's not straightforward to figure out how to set the right concurrent / memory configuration. The information is hidden in the log files (which Redshift deletes on a rolling basis), and sifting through those log files requires writing really complex queries. That just put more load on the cluster... However, if you do get it right, you can run blazing fast data loads and queries. We struggled with this problem ourselves at our previous company, and so with https://www.intermix.io https://www.intermix.io we built a service to solve that problem. If you ping me at lars at intermix dot io - I can give you an extended free trial. I'll pay you dinner (in SF, or maybe in Vegas at Reinvent), if we can't solve both issues for you. How is that?

9y ago