# Guidelines for optimizing Oracle RDS

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

## Optimization setup

### System setup <a href="#system-setup" id="system-setup"></a>

Every RDS instance fetches the initialization parameters from the definition of the [DB parameter group](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html) it is bound to. A best practice is to create a dedicated copy of the baseline group for the target database, to avoid impacting any other database that may share the same configuration object.

### Workflow setup <a href="#workflow-setup" id="workflow-setup"></a>

DB parameter groups must be configured through the dedicated [Amazon RDS API interface](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_Operations.html). 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](https://aws.amazon.com/cli/). The following snippets show an example of tuning an instance with id `oracletest`, bound to the configuration group named `test-oracle`:

```yaml
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:

```yaml
#!/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:

```bash
#!/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
```