Guidelines for optimizing Oracle RDS

This page provides a list of best practices when optimizing an Oracle RDS with Akamas.

Optimization setup

System setup

Every RDS instance fetches the initialization parameters from the definition of the DB parameter group it is bound to. A best practice is to create a dedicated copy of the baseline group for the target database, in order to avoid impacting any other database that may share the same configuration object.

Workflow setup

DB parameter groups must be configured through the the the dedicated Amazon RDS API interface. A simple way to implement this step in the Akamas workflow is to save the tested configuration in a configuration file and submit it through a custom executor leveraging the AWS Command Line Interface. The following snippets show an example tuning an instance with id oracletest, bound to configuration group named test-oracle:

name: tune RDS Oracle
tasks:
  - name: Generate Oracle configuration
    operator: FileConfigurator
    arguments:
      sourcePath: oracle/rdsscripts/oraconf.template
      targetPath: oracle/oraconf
      component: oracle

  - name: Update conf
    operator: Executor
    arguments:
      command: bash ~/oracle/rdsscripts/rds_update.sh test-oracle ~/oracle/
      component: oracle

  - name: Reboot Oracle
    operator: Executor
    arguments:
      command: bash ~/oracle/rdsscripts/rds_reboot.sh oracletest
      component: oracle

# rest of the workflow...

Where the following is an example of the configuration template oraconf.template:

pga_aggregate_target	${oracle.pga_aggregate_target}
pga_aggregate_limit	${oracle.pga_aggregate_target}
db_cache_size	${oracle.db_cache_size}
java_pool_size	${oracle.java_pool_size}
large_pool_size	${oracle.large_pool_size}
log_buffer	${oracle.log_buffer}

The following script rds_update.sh updates the configuration. It requires the name of the target DB parameter group and path of the temporary folder containing the generated configuration:

#!/bin/bash

set -euo pipefail

GROUP_NAME=$1
TMPFLD=$2

TS=`date +'%y%m%d%H%M%S'`

cd ${TMPFLD}

cp oraconf conf.$TS

AWK_CODE='{n=$2} $2~/[0-9]+m$/ {gsub(/m$/,"",n);n=n*1024*1024} $2~/[0-9]+k$/ {gsub(/k$/,"",n);n=n*1024} {print "ParameterName="$1",ParameterValue="n",ApplyMethod=pending-reboot"}'

echo Applying params: ; awk "${AWK_CODE}" oraconf

aws rds modify-db-parameter-group \
  --db-parameter-group-name ${GROUP_NAME} \
  --parameters `awk "${AWK_CODE}" oraconf`

# dump full new conf
aws rds describe-db-parameters \
  --db-parameter-group-name ${GROUP_NAME} | jq -c '.Parameters[] | {ParameterName, ParameterValue}' > full_pars_dump.$TS.jsonl

# if configuration changed wrt last one (ie: this is the first trial of the new experiment) wait for update propagation
diff -q `ls conf\.* | tail -n2` || (echo 'Configuration changed. Waiting for propagation.' && sleep 420 )

The following script rds_reboot.sh restarts the RDS instance with the provided id:

#!/bin/bash

set -u

INST_ID=$1

DELAY_SEC=30
RETRIES=60

aws rds reboot-db-instance --db-instance-identifier $INST_ID | jq -c '.DBInstance | {DBInstanceIdentifier, Engine, DBInstanceStatus}'

echo "Waiting for ${INST_ID}"

for i in `seq $RETRIES`; do
    sleep $DELAY_SEC
    status=`aws rds describe-db-instances --db-instance-identifier $INST_ID | jq -r '.DBInstances[].DBInstanceStatus'`
    echo "${INST_ID}: ${status}"
    [ "${status}" = 'available' ] && exit 0
    [ "${status}" = 'incompatible-parameters' ] && exit 255
done

exit 255