1 of 1

Guidelines for optimizing Oracle RDS

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

Optimization setup

System setup

Workflow setup

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 the 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