Redshift Performance and Cost
nerds.airbnb.com
nerds.airbnb.com
For a basic overview: http://en.wikipedia.org/wiki/Paraccel
As for the rest of the article, it feels like a basic Data Warehousing 101 re-discovered. It should have been titled "Analytics: Back To The Future" :-)
How much time and money would have been saved learning Database Theory/SQL/Data Warehousing/Dimensional Modeling instead of cramming everything into an unstructured data-store?
Too bad Redshift can't handle JSON files: Converting everything will be annoying.
I remember it well, because I was trying to explain why having tens of gigabytes of indexes wouldn't help them much if they only had 16Gb of RAM.
In terms of group-by performance, it depends a lot on the kind of data and how it's stored. For example, taking a sum on a columnar store is quite amenable to parallel solutions and a lot of databases will do that way.
SQL Server can
Oracle can
Postgres can
Even MySQL can (!)
The limitations are almost always in the hardware, not the software.
If you're looking at column based systems, you can look at Greenplum (does both row and column-based storage), InfiniDB (MySQL based), and all sorts of expensive but very fast appliance options like Netezza, Teradata, etc.
And even for moderate data sizes (10+ GB per table), row store DBs tend to become painful. This is especially true when you need to support ad-hoc reporting queries, since the usual technique of matching your schema, indexes, and queries won't be effective any more. With true ad-hoc reporting, your only hope becomes lots of shallow indices rather than ones tuned to a particular query.
http://en.wikipedia.org/wiki/Dimensional_modeling http://www.amazon.com/Data-Warehouse-Toolkit-Complete-Dimens...
Redshift is indeed a solid product but all these comparisons against Hive are surprising, as that's not the right tool in the first place. Infobright, greenplum, aster, vertica, etc are the products which Redshift seeks to disrupt.
At my university, standard normalisation was taught in the "databases" course. OLAP was mentioned as part of the "advanced databases" course.
The database course at that time blew about half its time on building PHP applications to talk to the database. I hate to second guess my professors, but I can't help but feel that a more productive use of the time would have been to teach normalised OLTP in the first half, and dimensionally modelled OLAP in the second half. Better yet, to divide them into two courses and spend some time talking about database history ("here's why network and hierarchical databases sucked") and maybe some introduction to how query planners work.
Edit: actually, maybe not Date. It's up to you. It's good, but it's controversial because he's not a fan of SQL and so he uses his own language.
The one I used in uni was Ramakrishnan & Gehrke's Database Management. It was OK but there's a certain amount of at-the-time trendy bullshit that to me detracts from a focus on relational databases for their own sake.
Edit 2: and Joe Celko's SQL for Smarties contains good oil on the relational paradigm.
It's a bit old, but still pretty good.
One thing to be aware about with both is the lack of any support for wide tables - Infobright inherits MySQL's limit of 65,535 bytes per row (and UTF8 means 3 bytes per char); with Redshift you can stored wider rows but you can't query them (http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE...). Obviously not a deal breaker, but it locks you firmly in the densely populated rows, relational mindset.
This aside, we're super-excited about Redshift!
Btw, do you have more experience to share? e.g. with infobright, how many events can be processed per second? what would be the "ETL latency"? can infobright handle 10TB of data easily, any caveat besides the row limit? Thanks.
It's so exhausting to hear how much smarter you are and if we just educated ourselves we would realise the error of our ways. People who choose the technologies aren't stupid or masochistic. They understand their use case and the fact is that there are plenty of situations where SQL is suboptimal.
Sometimes with technologies going through the Gartner Hype Cycle people choose the incorrect one, because of the buzz, the glamour around it.
NoSQL is most definately in vouge, quite rightly, too many people often use heavy RBDMS when they are not required.
But too many people perhaps are too quick to dismiss the regular database without actually understanding it.
Any suggestion to avoid hype of technology, question your use cases fully is in my mind a good suggestion.
This weekend I loaded 2 billion rows from S3 both ways:
- From a single gzipped object: 4 hours 42 minutes
- From 2000 gzipped slices of 1M rows each: 17 minutes
(Loading from gzipped files is considerably faster, in addition to saving S3 charges.)
The article notes that choice of distribution key is critical. I'd add that choice of sort key is equally important. In my testing, a better sort key improved compression from 1.5:1 to 4:1, and also made common queries 5x faster.
Unfortunately, you only get one dist key and one sort key per table, so less common queries could get slower.
The OP's cluster is a 16-node hs1.xlarge cluster (has 3 spindles per node). There's actually a more powerful node-type hs1.8xlarge which has 24 spindles on each node. More info: http://aws.amazon.com/redshift/pricing/
So it's not fair to compare Redshift performance to your Vertica cluster unless the hardware is similar.