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. 

AWS high memory instance type : Good for Oracle Databases on AWS

The r3.8xlarage has 244 GiB of memory, 32 vCPUs, and 2 - 320 GB SSD drives. The cost is $2.80 per hour for on-demand instances.  More details here:

http://aws.amazon.com/about-aws/whats-new/2014/04/10/r3-announcing-the-next-generation-of-amazon-ec2-memory-optimized-instances/

Prior to this you had to use the i2.8xlarge instance type which also has 244 GiB of memory and 32 vCPUs. But also has 8 - 800 GB SSD drives which you will most likely not use for your Oracle Database.  The cost of i2.8xlage is $6.82 per hour for on-demand instances.  The other highest memory option is the cr1.8xlarge with 32 vCPUs and 244 GIB with 2 -120 GB SSD drives.  The cost of this option is $3.50 per hour for on-demand instances.

The R3 instances include the following features:

• Intel Xeon E5-2670 v2 "Ivy Bridge" Processors

• Hardware Virtualization (HVM) only

• SSD-backed instance storage, including TRIM support

• Enhanced Networking with lower latency, low jitter, and high packet-per-second performance

One caveat here that Oracle has not released any HVM-based instance types.  There are undocumented methods to create an HVM AMI from a PVM AMI but this new AMI may not be supported by Oracle.

HVM and PVM : Oracle AMIs

AWS AMIs are either Para Virtual Machine (PVM) or Hardware Virtual Machine (HVM) images. All of the AMIs from Oracle are currently only PVM-based. Therefore, they will not run on cluster compute or i2 instance types.

More information on HVM-based instances types:
http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/using_cluster_computing.html#concepts_cluster_compute_hvmAMI
http://aws.amazon.com/ec2/faqs/#Does_use_of_Cluster_Compute_Instances_differ_from_other_Amazon_EC2_instance_types

Currently, the new i2 instance types are HVM only:
http://aws.amazon.com/about-aws/whats-new/2013/12/19/announcing-the-next-generation-of-amazon-ec2-high-i/o-instance/

More on HVM and PVM can be found here:
http://serverfault.com/questions/222010/difference-between-xen-pv-xen-kvm-and-hvm

If you are looking to run Oracle solutions on Oracle Enterprise Linux and require more then 68 GB of memory, you would have to run on hs1.8xlarge (117 GiB) as all other instance types with more then 68 GB are HVM. See more here: http://aws.amazon.com/amazon-linux-ami/instance-type-matrix/

SAP HANA on AWS

There are two offerings for running SAP HANA on AWS

1. AWS Marketplace : https://aws.amazon.com/marketplace/pp/B009KA3CRY. This runs on the Cluster Compute 8XL (cc2.8xlarge) instance type with 60.5 GB of memory. This includes the SAP HANA One licenses. 

2. SAP Cloud Appliance Library (CAL) : http://marketplace.saphana.com/New/SAP-HANA-%3A-infrastructure-subscription-by-AWS/p/1871. This uses a AWS CloudFormation template to deploy a cr1.8xlarge cluster compute instance (244 GB of memory and 240 GB of SSD instance storage) with Amazon PIOPS volumes.

Oracle Database 12c features for AWS

Here are some Oracle 12c features which may be of most interest to customers running Oracle Database on AWS:

-Multi tenant database : http://www.oracle.com/technetwork/database/multitenant/overview/index.html
-Active Data Guard far sync : http://docs.oracle.com/cd/E16655_01/server.121/e17640/create_fs.htm#SBYDB5416
-in memory column database : http://www.oracle.com/us/corporate/press/2020717

CloudWatch custom metric examples

Creating custom metric requires some custom coding or scripting using the AWS Command Line Interface or SDK.   As the name implies these metric are custom so some custom coding / scripting is required to be done.  Here are some places to start that will provide examples of creating custom metric:
CloudWatch custom metric for MySQL using PHP:
http://aws.typepad.com/aws/2011/05/how-to-use-the-new-cloudwatch-user-defined-metrics-feature-with-php-1.html

Linux script sample custom CloudWatch metric for memory, disk space, and swap space:
http://aws.amazon.com/code/8720044071969977

More examples using the CloudWatch Command Interface to create custom metric:
http://blogs.clogeny.com/custom-metrics-in-amazon-cloudwatch/

Sample code for instance memory reporting:
#!/bin/bash export AWS_CLOUDWATCH_HOME=/home/ec2-user/CloudWatch-1.0.12.1 export AWS_CREDENTIAL_FILE=$AWS_CLOUDWATCH_HOME/credentials export AWS_CLOUDWATCH_URL=https://monitoring.amazonaws.com export PATH=$AWS_CLOUDWATCH_HOME/bin:$PATH export JAVA_HOME=/usr/lib/jvm/jre # get ec2 instance id instanceid=`wget -q -O - http://169.254.169.254/latest/meta-data/instance-id` memtotal=`free -m | grep 'Mem' | tr -s ' ' | cut -d ' ' -f 2` memfree=`free -m | grep 'buffers/cache' | tr -s ' ' | cut -d ' ' -f 4` let "memused=100-memfree*100/memtotal" mon-put-data --metric-name "FreeMemoryMBytes" --namespace "System/Linux" --dimensions "InstanceId=$instanceid" --value "$memfree" --unit "Megabytes" mon-put-data --metric-name "UsedMemoryPercent" --namespace "System/Linux" --dimensions "InstanceId=$instanceid" --value "$memused" --unit "Percent"

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

EC2 instance comparison

http://www.ec2instances.info