Guidelines for optimizing Oracle RDS
This page provides a list of best practices when optimizing an Oracle RDS with Akamas.
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.
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:1
name: tune RDS Oracle
2
tasks:
3
- name: Generate Oracle configuration
4
operator: FileConfigurator
5
arguments:
6
sourcePath: oracle/rdsscripts/oraconf.template
7
targetPath: oracle/oraconf
8
component: oracle
9
10
- name: Update conf
11
operator: Executor
12
arguments:
13
command: bash ~/oracle/rdsscripts/rds_update.sh test-oracle ~/oracle/
14
component: oracle
15
16
- name: Reboot Oracle
17
operator: Executor
18
arguments:
19
command: bash ~/oracle/rdsscripts/rds_reboot.sh oracletest
20
component: oracle
21
22
# rest of the workflow...
Where the following is an example of the configuration template
oraconf.template:1
pga_aggregate_target ${oracle.pga_aggregate_target}
2
pga_aggregate_limit ${oracle.pga_aggregate_target}
3
db_cache_size ${oracle.db_cache_size}
4
java_pool_size ${oracle.java_pool_size}
5
large_pool_size ${oracle.large_pool_size}
6
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:1
#!/bin/bash
2
3
set -euo pipefail
4
5
GROUP_NAME=$1
6
TMPFLD=$2
7
8
TS=`date +'%y%m%d%H%M%S'`
9
10
cd ${TMPFLD}
11
12
cp oraconf conf.$TS
13
14
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"}'
15
16
echo Applying params: ; awk "${AWK_CODE}" oraconf
17
18
aws rds modify-db-parameter-group \
19
--db-parameter-group-name ${GROUP_NAME} \
20
--parameters `awk "${AWK_CODE}" oraconf`
21
22
# dump full new conf
23
aws rds describe-db-parameters \
24
--db-parameter-group-name ${GROUP_NAME} | jq -c '.Parameters[] | {ParameterName, ParameterValue}' > full_pars_dump.$TS.jsonl
25
26
# if configuration changed wrt last one (ie: this is the first trial of the new experiment) wait for update propagation
27
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:1
#!/bin/bash
2
3
set -u
4
5
INST_ID=$1
6
7
DELAY_SEC=30
8
RETRIES=60
9
10
aws rds reboot-db-instance --db-instance-identifier $INST_ID | jq -c '.DBInstance | {DBInstanceIdentifier, Engine, DBInstanceStatus}'
11
12
echo "Waiting for ${INST_ID}"
13
14
for i in `seq $RETRIES`; do
15
sleep $DELAY_SEC
16
status=`aws rds describe-db-instances --db-instance-identifier $INST_ID | jq -r '.DBInstances[].DBInstanceStatus'`
17
echo "${INST_ID}: ${status}"
18
[ "${status}" = 'available' ] && exit 0
19
[ "${status}" = 'incompatible-parameters' ] && exit 255
20
done
21
22
exit 255