4 ms·

This seems really slow for queries especially when taking into account all the computing power being thrown at it: Over 6 billion rows (not huge by modern stan

This seems really slow for queries especially when taking into account all the computing power being thrown at it: Over 6 billion rows (not huge by modern standards), a relatively common aggregation query with 4 basic aggregates (2 sum, 2 avg), one where, and two group by clauses, over 1 table (no joins) takes about 4.25 minutes (254.650 seconds). On some column databases on good hardware with a single machine you can probably get a couple seconds, probably faster.

makmanalp 10y ago
So this is a good question - the answer is that you're comparing apples to oranges. This is all about in-situ data processing, which basically means that you can run reasonably efficient, optimized queries on just regular old files that you have lying around, without having to do anything special. This is as opposed to a situation where you've spent a ton of time, effort and expense actually ingesting the data into whatever data system you're running (in this case redshift). So it's usually said that these systems are good for ad-hoc queries, i.e. one-off queries on files and datasets that you want to explore more, but it doesn't make sense to invest the hours upon hours of waiting and storage / cpu resources to bring them into your database just to make a few queries.
- jnordwick 10y ago
  So these ingested files are JSON or something semi-structured? I saw how the table was created, so maybe is was doing the schema-less key-value pair thing on table creation and running queries against that? I'm sure this is all in the docs somewhere, but while I have a lot of big db experience, I have about 2 hours of AWS.
  - sunsu 10y ago
    The files are anything separated by line. Log files, CSV exports, anything. No specific format required.
    - jnordwick 10y ago
      So this is like a distributed awk, reparsing on every query?
  - makmanalp 10y ago
    So in this example the image says "lineitem_1t_parq" which makes me think that's probably parquet files (optimized and perhaps compressed columnar file format). But it does the same stuff with CSVs too, perhaps a bit less efficiently. JSON is also definitely possible, but a bit more tricky because of the inherently nested structure and more complex format. Take a look at this for the details on a similar idea: http://stratos.seas.harvard.edu/files/stratos/files/nodb-cacm.pdf http://stratos.seas.harvard.edu/files/stratos/files/nodb-cac...
    - kakoni 10y ago
      That paper discusses PostgresRAW, which can be found here; https://github.com/HBPMedical/PostgresRAW https://github.com/HBPMedical/PostgresRAW I wonder if there are any similar projects using psql as an engine?
      - makmanalp 10y ago
        I think it's pretty common - it's open source, nice and modular codebase, reasonably advanced execution engine with stuff like MVCC. But also redshift itself, citus, hadapt and greenplum are examples that come to my mind.
afpx 10y ago
This is an interesting problem space. More and more companies are dealing with much more data than they've ever had to process. Off the top of my head, I know a handful of people whose companies are dealing with 10s of trillions of rows of data. But, they don't have the staff on hand to help them analyze and develop custom indices to improve analysts' query time. So, something like this is much better (and cheaper) than nothing (and, yes, aws makes a killing). There's gonna be a lot of money to be made by the smart people who help crack this problem.
aub3bhat 10y ago
On a dataset where all values are 0, the answer can be found in 10 nanoseconds, if not faster. What's your point?
ah- 10y ago
I wonder where the bottleneck was. Right below that result they have the performance plot that shows how it used up almost no CPU or network bandwidth. I'd expect S3 to be faster than this? FWIW, I've been playing with parquet and Kudu lately and on a reasonably sized single machine you can run similar queries on similar scale data in <5 seconds.
- awgupta 10y ago
  The constraint for Redshift Spectrum queries tends to be between S3 and Spectrum. The graphs Jeff shows are in the Redshift cluster itself which, for the sample query he is running, is lightly loaded. We are working on upgrading our performance dashboards...
teej 10y ago
Just for comparison's sake, I recreated Jeff's example queries on my in-house table that's ~8B rows. COUNT(*) takes 6s and the SELECT with 2 groups, 2 sums, and 2 averages takes 52s. The downside is that the table takes 600GB of disk space compressed which is a major chunk of our cluster. But here's the kicker - I would never query this table for data I needed in under 5 min so my current performance is meaningless. Given our access patterns for this table, I'm going to investigate using Redshift Spectrum for it. It seems like a huge win for us.
- jnordwick 10y ago
  What are you using?
  - teej 10y ago
    Sorry, this is just vanilla redshift.
- andrioni 10y ago
  >The downside is that the table takes 600GB of disk space compressed which is a major chunk of our cluster. And that it probably takes one hour or more to fully reload it if you ever need to.

makmanalp 10y ago

So this is a good question - the answer is that you're comparing apples to oranges. This is all about in-situ data processing, which basically means that you can run reasonably efficient, optimized queries on just regular old files that you have lying around, without having to do anything special. This is as opposed to a situation where you've spent a ton of time, effort and expense actually ingesting the data into whatever data system you're running (in this case redshift). So it's usually said that these systems are good for ad-hoc queries, i.e. one-off queries on files and datasets that you want to explore more, but it doesn't make sense to invest the hours upon hours of waiting and storage / cpu resources to bring them into your database just to make a few queries.

jnordwick 10y ago
So these ingested files are JSON or something semi-structured? I saw how the table was created, so maybe is was doing the schema-less key-value pair thing on table creation and running queries against that? I'm sure this is all in the docs somewhere, but while I have a lot of big db experience, I have about 2 hours of AWS.
- sunsu 10y ago
  The files are anything separated by line. Log files, CSV exports, anything. No specific format required.
  - jnordwick 10y ago
    So this is like a distributed awk, reparsing on every query?
- makmanalp 10y ago
  So in this example the image says "lineitem_1t_parq" which makes me think that's probably parquet files (optimized and perhaps compressed columnar file format). But it does the same stuff with CSVs too, perhaps a bit less efficiently. JSON is also definitely possible, but a bit more tricky because of the inherently nested structure and more complex format. Take a look at this for the details on a similar idea: http://stratos.seas.harvard.edu/files/stratos/files/nodb-cacm.pdf http://stratos.seas.harvard.edu/files/stratos/files/nodb-cac...
  - kakoni 10y ago
    That paper discusses PostgresRAW, which can be found here; https://github.com/HBPMedical/PostgresRAW https://github.com/HBPMedical/PostgresRAW I wonder if there are any similar projects using psql as an engine?
    - makmanalp 10y ago
      I think it's pretty common - it's open source, nice and modular codebase, reasonably advanced execution engine with stuff like MVCC. But also redshift itself, citus, hadapt and greenplum are examples that come to my mind.

afpx 10y ago

This is an interesting problem space. More and more companies are dealing with much more data than they've ever had to process. Off the top of my head, I know a handful of people whose companies are dealing with 10s of trillions of rows of data. But, they don't have the staff on hand to help them analyze and develop custom indices to improve analysts' query time. So, something like this is much better (and cheaper) than nothing (and, yes, aws makes a killing). There's gonna be a lot of money to be made by the smart people who help crack this problem.

aub3bhat 10y ago

On a dataset where all values are 0, the answer can be found in 10 nanoseconds, if not faster. What's your point?

ah- 10y ago

I wonder where the bottleneck was. Right below that result they have the performance plot that shows how it used up almost no CPU or network bandwidth. I'd expect S3 to be faster than this? FWIW, I've been playing with parquet and Kudu lately and on a reasonably sized single machine you can run similar queries on similar scale data in <5 seconds.

awgupta 10y ago
The constraint for Redshift Spectrum queries tends to be between S3 and Spectrum. The graphs Jeff shows are in the Redshift cluster itself which, for the sample query he is running, is lightly loaded. We are working on upgrading our performance dashboards...

teej 10y ago

Just for comparison's sake, I recreated Jeff's example queries on my in-house table that's ~8B rows. COUNT(*) takes 6s and the SELECT with 2 groups, 2 sums, and 2 averages takes 52s. The downside is that the table takes 600GB of disk space compressed which is a major chunk of our cluster. But here's the kicker - I would never query this table for data I needed in under 5 min so my current performance is meaningless. Given our access patterns for this table, I'm going to investigate using Redshift Spectrum for it. It seems like a huge win for us.

jnordwick 10y ago
What are you using?
- teej 10y ago
  Sorry, this is just vanilla redshift.
andrioni 10y ago
>The downside is that the table takes 600GB of disk space compressed which is a major chunk of our cluster. And that it probably takes one hour or more to fully reload it if you ever need to.