AWS EBS Performance

Here is a very good presentation on Amazon EBS performance from reInvent in 2013:
http://www.slideshare.net/AmazonWebServices/ebs-webinarfinal

Oracle Database Huge Pages

Oracle DBAs will use Oracle Hug pages to increase Oracle database performance : http://docs.oracle.com/cd/E11882_01/server.112/e10839/appi_vlm.htm#UNXAR394

The only requirement for huge-pages (2MB pages) is that you run a HVM instance. However, all the Oracle AMIs are PVM.

For now to use huge pages with Oracle on EC2,  the best option would be to go with SUSE HVM AMI and then install the Oracle Database on the EC2 instance. 

Redshift : optimizing query performance with compression, distribution key and sort key

Encoding/compression

In order to determine the correct compression, first issue these commands to clean up dead space and analyze the data in the table:

vacuum orders;

analyze orders;

Then issue this command:

analyze compression orders;

Then create a table that matches the results from the analyze compression statement:

CREATE TABLE orders (

  o_orderkey int8 NOT NULL ENCODE MOSTLY32 PRIMARY KEY       ,

  o_custkey int8 NOT NULL ENCODE MOSTLY32 DISTKEY REFERENCES customer_v3(c_custkey),

  o_orderstatus char(1) NOT NULL ENCODE RUNLENGTH            ,

  o_totalprice numeric(12,2) NOT NULL ENCODE MOSTLY32        ,

  o_orderdate date NOT NULL ENCODE BYTEDICT SORTKEY          ,

  o_orderpriority char(15) NOT NULL ENCODE BYTEDICT          ,

  o_clerk char(15) NOT NULL ENCODE RAW                       ,

  o_shippriority int4 NOT NULL ENCODE RUNLENGTH              ,

  o_comment varchar(79) NOT NULL ENCODE TEXT255

);

Distributing data

Partition data using a distribution key. This allows data to be spread out on a cluster to maximize the parallelization potential of the queries. To help queries run fast, the distribution key should be a value that will be used in regularly joined tables. This allows Redshift to co-locate the data of these different entities, reducing IO and network exchanges.

Redshift also uses a specific sort column to know in advance what values of a column are in a given block, and to skip reading that entire block if the values it contains don’t fall into the range of a query. Using columns that are used in filters (i.e. where clauses) helps execution.

Compression depends directly on the data as it is stored on disk, and storage is modified by distribution and sort options.  Therefore, if you change sort or distribution key or create a new table that has the same data but different distribution and sort keys you will need to rerun the vacuum, analyze ad analyze compression statements.

AWS Storage Tiering for Enterprise Workloads

This a replay of the AWS reInvent session that covers storage tiering options for running Oracle Enterprise workloads such as Oracle Databases on AWS:

http://www.youtube.com/watch?v=i5Il80bhQCI

Slides:
http://www.slideshare.net/tomlaszewski/aws-storage-tiering-for-enterprise-workloads

AWS Database reference implementation

This reference implementation provides the architecture and associated CloudFormation templates for a standard, enterprise class, large enterprise class and high performance Oracle 11g configuration on AWS EC2:

http://media.amazonwebservices.com/AWS_RDBMS_Oracle_11g_on_EC2_Reference_Architecture.pdf

Oracle Database on AWS white paper

This white paper is a great resource for Oracle DBAs and developers:

http://media.amazonwebservices.com/AWS_RDBMS_Oracle.pdf

AWS EBS PIOPS : block size and IOPS

Having spent more time in the database world than in the web development world, I am accustomed to measuring (database) performance/through put in terms of IOPS or TPS.  The web/video/image world like to use MB/sec.  Why I am saying this? Because it relates to the conversation about getting a certain level of PIOPS (based upon a 16 KB block) on AWS EBS and how this effects MB/sec.  MB/sec, I am beginning to understand, and maybe move to the 'dark side', is the ultimate measure of disk 'performance'.  

Example: A 2000 Provisioned IOPS volume can handle:

•2000 16KB read/write per second, or 1000 32KB read/write per second, or 500 64KB read/write per second 

•You will get consistent 32 MB/sec throughput (with 16KB or higher IOs)

•Perform an index creation action and sends I/O of 32K, IOPS becomes 1000, you still get 32MB/sec throughput

•On best effort, you may get up to 40 MB/sec throughput 

So, you may be better off using a 64 KB block size but your PIOPS will show up as lower but your MB/sec could be better.

Hadoop Cluster and Amazon EMR

Accenture calculated the total cost of ownership of a bare-metal Hadoop cluster and derived the capacity of nine different cloud-based Hadoop clusters at the matched TCO. The performance of each option was then compared by running three real-world Hadoop applications.  The full report is here:

http://www.accenture.com/us-en/Pages/insight-hadoop-deployment-comparison.aspx

There report compares the performance of both a bare-metal Hadoop cluster and Amazon ElasticMapReduce (Amazon EMR).

Data warehousing load times

Some times when people are moving to AWS for their databases they are looking for load times in the range of 2 TB per hour (this is 650+ MB/second).  

1. Green Plum : http://www.emc.com/collateral/hardware/data-sheet/h7419-greenplum-dca-ds.pdf : 10 TB to 60 TB per hour claimed in this document.

2. Exadata : http://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/173705.pdf  Data Load Rate: Up to 12 TB/hour

Numbers listed in this book:  http://books.google.com/books?id=Whx9nldv8UEC&pg=PA102&lpg=PA102&dq=oracle+database+loads+terabytes+per+hour&source=bl&ots=VEXelUIhd2&sig=9LW4T0PgottDF2kpgvUobd2FonQ&hl=en&sa=X&ei=PHjyUcPKMuKbygHuyIDgBg&ved=0CHkQ6AEwBw#v=onepage&q=oracle%20database%20loads%20terabytes%20per%20hour&f=false

Of course,  Greenplum and Exadata are significant monetary investments.

Paraccel claims to achieve 2 TB an hour: http://www.paraccel.com/resources/Analyst-Reports/TDWI_Checklist-Report_Analytic-Databases-for-Big-Data.pdf

OpenWorld Oracle on AWS sessions

Here are the two sessions focused on Oracle on AWS at Oracle OpenWorld:

https://oracleus.activeevents.com/2013/connect/search.ww?eventRef=openworld#loadSearch-event=null&searchPhrase=Amazon&searchType=session&tc=0&sortBy=titleSort&p=&i(11180)=20800

I will be co-presenting this session: Best Practices for Running Oracle Database Instances on Amazon Web Services.

MySQL Session at MySQL Connect

Here are the two MySQL on AWS session an MySQL Connect in September:

https://oracleus.activeevents.com/2013/connect/search.ww?eventRef=mysqlconnect#loadSearch-event=null&searchPhrase=aws&searchType=session&tc=0&sortBy=&p=&i(11180)=20802

I will be co-presenting the Best Practices for Deploying MySQL on Amazon Web Services session.

Redshift Query performance

The items that impact performance of the queries against Redshift are:

1. Node type : This is one of two options which are one of the Redshift option types: extra large node (XL)  or an eight extra large node (8XL).

2. Number of nodes : The number of nodes you choose depends on the size of your dataset and your desired query performance. Amazon Redshift distributes and executes queries in parallel across all nodes, you can increase query performance by adding nodes to your data cluster.  You can monitor query performance in the Amazon Redshift Console and with Amazon Cloud Watch metrics.

3. Sort Key : Keep in mind that not all queries can be optimized by sort key.   There is only one sort key for each table.   The Redshift query optimizer uses sort order when it determines optimal query plans.  If you do frequent range or equality filtering on one column, make this column the sort key.  If you frequently join a table, specify the join column as both the sort key and the distribution key.  More details here : http://docs.aws.amazon.com/redshift/latest/dg/c_best-practices-sort-key.html

4. Distribution key :  There is one distribution key per table.  If the table has a foreign key or another column that is frequently used as a join key, consider making that column the distribution key. In making this choice, take pairs of joined tables into account. You might get better results when you specify the joining columns as the distribution keys and the sort keys on both tables. This enables the query optimizer to select a faster merge join instead of a hash join when executing the query.  If not many joins, then use the column in the group by clause.  More on distribution keys here: http://docs.aws.amazon.com/redshift/latest/dg/c_choosing_dist_sort.html

Keep in mind you want to have even distribution across nodes. You can issue the select from svv_diskuage to find out the distribution.
5. Column compression : This has an impact on query performance : http://docs.aws.amazon.com/redshift/latest/dg/t_Compressing_data_on_disk.html

6. Run queries in memory : Redshift supports the ability to run queries entirely from memory. This can obviously impact performance.  More details here: http://docs.aws.amazon.com/redshift/latest/dg/c_troubleshooting_query_performance.html

7. Look at the query plan : More details can be found here : http://docs.aws.amazon.com/redshift/latest/dg/c-query-planning.html

8. Look at disk space usage : More details can be found here : http://docs.aws.amazon.com/redshift/latest/dg/c_managing_disk_space.html

9.  Workload manager setting : By default, a cluster is configured with one queue that can run five queries concurrently.  The workload management (WLM) details found here: http://docs.aws.amazon.com/redshift/latest/dg/cm-c-implementing-workload-management.html

Documentation: http://docs.aws.amazon.com/redshift/latest/dg/c_redshift_system_overview.html
Video: http://www.youtube.com/watch?v=6hk0KvjrvfoBlog: http://aws.typepad.com/aws/2012/11/amazon-redshift-the-new-aws-data-warehouse.html

Oracle Database on ephemeral drives

Using EC2 ephemeral storage (either disk or SSD) is a way to achieve higher IO throughput.
You could use the design pattern Redshift uses (these use the HS1.* instances which have similar storage characteristics to the hi1.4xlarge instances) - "the first line of defense consists of two replicated copies of your data, spread out over up to 24 drives on different nodes within your data warehouse cluster".  This includes:

  1.  All data written to a node in your cluster is automatically replicated to other nodes within the cluster
  2.  All data is continuously backed up to Amazon S3

Oracle on SSD as it is recommendation to get highest level of IO when running Oracle on EC2.

AWS storage performance

A common question is "How would the client compare the performance of storage on Amazon vs. local data center? "  Well here are some numbers for AWS storage:

a.   Instance storage Disk : Equivalent to local disk for a server

b.   Instance storage SSD

                                                             i.     ~120,000 random read IOPS (4 KB blocks)

                                                            ii.     ~10,000-85,000 random write IOPS (4 KB blocks)

c.    EBS Standard (100 IOPS)

                                                             i.     Reads typically <20ms writes typically <10ms

                                                            ii.     Best effort to 10’s of MB/sec

d.   EBS PIOPS (4K IOPS),

                                                             i.     On best effort, you may get up to 40 MB/sec throughput for 2K PIOPS disk

e.   S3 : loading :http://cloudconclave.blogspot.com/2013/05/aws-s3-load-throughput.html

f.     Glacier : Long time archiving. Takes 3-5 hours to retrieve.

g.   Storage Gateway : On premise to AWS S3 ‘replication’.  So, will depend on the pipe (internet, AWS DirectConnect) from on premise to AWS.

AWS S3 load throughput

Here is the S3 performance tips and tricks public blog entry with some good information:

http://aws.typepad.com/aws/2012/03/amazon-s3-performance-tips-tricks-seattle-hiring-event.html

The key learning is that S3 does continue to scale very well and there are customers running >100k TPS. However, S3 does not automatically create a partition scheme quickly nor can they use an API to manage this or use CloudWatch to monitor. 

·     

SSD for Oracle Databases

Here are a couple of articles/web site with the pros and cons of running your Oracle database on SSD:

http://www.pythian.com/blog/de-confusing-ssd-for-oracle-databases/
http://www.slideshare.net/gwenshap/ssd-collab13

If your Oracle database is less than 2 TB you could run it on the  hi1.4xlarge instance type.  This instance type has  2 SSD-based volumes each with 1024 GB of instance storage.  More on AWS instance types here: http://aws.amazon.com/ec2/instance-types/

You could also just use Oracle RDS on AWS.  With PIOPS for RDS, you can get up to 25K IOPS on your Oracle database. 
Instagram uses SSD : http://m.cio.com/article/716829/SSDs_Boost_Instagram_39_s_Speed_on_Amazon_EC2

EC2 EBS Optimized Instances

Because Oracle databases are typically terabytes in size and required 1000's of IOPS, you will typically use EBS PIOPS volumes with EBS-optimized EC2 instances.   This leads the often asked question of "What does EBS optimized EC2 instances really mean?"  The short answer is that storage pipe to the EBS (PIOPS) volume is dedicated and does not compete with network traffic like it does in a non EBS-optimized instance.  A more detailed answer can be found here:
http://perspectives.mvdirona.com/2012/08/01/EBSProvisionedIOPSOptimizedInstanceTypes.aspx

On a related note, additional instance types now offered EBS Optimized functionality: http://aws.amazon.com/about-aws/whats-new/2013/03/19/announcing-ebs-optimized-support-for-additional-instance-types/

Cloudwatch and benchmarking

Question: Can Cloudwatch provide enough metrics for an Oracle DB benchmarks? What are other tools that can be used?

Response: Cloud really does not provide much in terms of Oracle DB metrics.  Oracle AWR is the best option.  There is also a new OEM AWS plug for AWS:

http://www.oracle.com/technetwork/oem/grid-control/downloads/oem-aws-plugin-1852739.html

Of course, there is the performance pack that is part of OEM.

AWS Provisioned I/Os

Provisioned IOPS volumes are designed to deliver within 10% of the provisioned IOPS performance 99.9% of the time. Therefore, PIOPS are very good choice when running RDBMS systems like Oracle on EC2 or RDS.