{"id":1941,"date":"2013-11-01T12:27:44","date_gmt":"2013-11-01T12:27:44","guid":{"rendered":"http:\/\/bicortex.com\/?p=1941"},"modified":"2018-07-23T08:21:06","modified_gmt":"2018-07-23T08:21:06","slug":"amazon-redshift-review-data-warehouse-in-the-cloud-is-here-part-2","status":"publish","type":"post","link":"http:\/\/bicortex.com\/bicortex\/amazon-redshift-review-data-warehouse-in-the-cloud-is-here-part-2\/","title":{"rendered":"Amazon Redshift Review – Data Warehouse in the Cloud Is Here, Part 2"},"content":{"rendered":"

Background And Environment Specs Recap<\/h3>\n

In the FIRST<\/b><\/a> part of this series I briefly explored the background of Amazon\u2019s Redshift data warehouse offering as well as outlined the environments and tools used for managing test datasets. I also recreated TICKIT database schema on all instances provisioned for testing and preloaded them with dummy data. In this post I would like to explore the performance findings from running a bunch of sample SQL queries to find out how they behave under load. Final post outlining how to connect and use data stored in Redshift can be found HERE<\/b><\/a>.<\/p>\n

As mentioned in my FIRST<\/b><\/a> post, all datasets which populated TICKIT sample database were quite small so I needed to synthetically expand one of the tables in order to increase data volumes to make the whole exercise viable. Using nothing but SQL I \u2018stretched out\u2019 my Sales fact table to around 50,000,000 records which is hardly enough for any database to break a sweat, but given the free storage limit on Amazon S3 is only 5 GB (you can also use DynamoDB as a file storage) and that my home internet bandwidth is still stuck on ADSL2 I had to get a little creative with the SQL queries rather than try to push for higher data volumes. Testing methodology was quite simple here \u2013 using mocked-up SQL SELECT statements, for each query executed I recorded execution time on all environments and plucked them into a graph for comparison. None of those statements represent any business function so I did not focus on any specific output or result; the idea was to simply make them run and wait until completion, taking note of how long it took from beginning to end. Each time the query was executed, the instance got restarted to ensure cold cache and full system resources available to database engine. I also tried to make my SQL as generic as possible. By default, SQL is not a very verbose language, with a not a lot of dichotomies between different vendors. Most of the core syntax principles apply to both PG\/SQL and Microsoft T-SQL alike. However, there are some small distinctions which can get \u2018lost in translation\u2019 therefore I tried to keep the queries are as generic and universal as possible.<\/p>\n

As far as physical infrastructure composition, the image below contains a quick recap of the testing environment used for this evaluation. Initially, I planned to test my queries on 3 different Redshift deployments \u2013 a single and a multi-node Redshift XL instance as well as a single Redshift 8XL instance, however, after casually running a few queries and seeing the performance level Redshift is capable of I decided to scale back and run only with Redshift XL class in a single and multi-node configurations.<\/p>\n

\"Test_Env_Specs_ART\"<\/a>Graphing the results, for simplicity sake, I assigned the following monikers to each environment:<\/p>\n