1 of 1

Optimizing an Oracle Database server instance

In this example, we are going to tune the initialization parameters of an Oracle Database server instance in order to maximize its throughput while stressed by a load generator.

Environment setup

Environment

For the purpose of this experiment we are going to use two dedicated machines:

We assume to be working with Linux hosts

Prometheus and exporters

Install the OracleDB Prometheus exporter

The OracleDB Prometheus exporter publishes as metrics the results of the queries defined in the configuration file. In our case, we’ll use it to extract valuable performance metrics from Oracle’s Dynamic Performance (V$) Views.

The exporter will publish the metrics on port 9161.

Here’s the example metrics file used to run the exporter:

Install and configure Prometheus

In order to configure the OracleDB exporter you can add the following snippet to the configuration file:

Optimization setup

System

In order to model the system composed of the tuned database and the workload generator we need two different components:

For the tpcc component, we’ll need first to define some custom metrics and a new component-type. The following is the definition of the metrics (tpcc-metrics.yaml):

The following is the definition of the new component-type (tpcc-ctype.yaml):

We can then create the new component type running the commands:

As a next step, we can proceed then with the definition of our system (system.yaml):

Here’s the definition of our oracle component (oracle.yaml):

Here’s the definition of the tpcc component (tpcc.yaml):

name: tpcc
description: OLTP TPC-C load benchmarck
componentType: TPC-C Benchmarck
properties:
  hostname: oltpbench.mycompany.com
  username: ubuntu
  sshPort: 22
  key: rsa_key_file

We can create the system by running:

akamas create system system.yaml

We can then create the components by running:

akamas create component oracle.yaml 'oracle system'
akamas create component tpcc.yaml 'oracle system'

Telemetry

Prometheus

provider: Prometheus
config:
  address: prometheus
  port: 9090

We can now create the telemetry instance and attach it to our system by running:

akamas create telemetry-instance prom.yaml 'oracle system'

CSV

provider: csv
config:
  address: oltpbench.mycompany.com
  port: 22
  username: ubuntu
  protocol: scp
  authType: key
  auth: rsa_key_file

  remoteFilePattern: /home/ubuntu/oltpbench/results/output.csv
  componentColumn: component
  timestampColumn: ts
  timestampFormat: yyyy-MM-dd HH:mm:ss

metrics:
  - metric: throughput
    datasourceMetric: throughput
    staticLabels: {}

  - metric: resp_time
    datasourceMetric: avg_lat
    staticLabels: {}

  - metric: resp_time_min
    datasourceMetric: min_lat
    staticLabels: {}

  - metric: resp_time90th
    datasourceMetric: 90th_lat
    staticLabels: {}

  - metric: resp_time_max
    datasourceMetric: max_lat
    staticLabels: {}

We can create the telemetry instance and attach it to our system by running:

akamas create telemetry-instance csv.yaml 'oracle system'

Workflow

Remove previous executions' data

name: Clean results
operator: Executor
arguments:
  command: rm -f ~/oltpbench/results/*
  component: tpcc

Configure the Oracle instance

name: Update parameters
operator: OracleConfigurator
arguments:
  component: oracle

Restart the instance

name: Restart Oracle container
operator: Executor
arguments:
  command: docker restart oraxe
  component: oracle

Run the workload

name: Execute load test
operator: Executor
arguments:
  command: cd ~/oltpbench ; ./oltpbenchmark --bench tpcc --config tpcc_conf.xml --execute=true -s 5 --output out
  component: tpcc

Prepare test results

name: Parse TPC-C results
operator: Executor
arguments:
  command: cd ~/oltpbench ; ./tpcc_parse_csv.sh
  component: tpcc

Where tpcc_parse_csv.sh is the following script:

#!/bin/bash

OUTFILE=output.csv

COMP_NAME=tpcc

BASETS=`tail -n+2 results/out.csv | head -n1 | cut -d',' -f3`
echo 'component,ts,throughput,avg_lat,min_lat,90th_lat,max_lat' > $OUTFILE
awk -F, "BEGIN{OFS=\",\"} NR>1 {\$1=strftime(\"%F %T\", ${BASETS}+\$1); print \"${COMP_NAME}\",\$0}" < results/out.res | cut -d',' -f1-5,9,12 >> $OUTFILE

Complete workflow

By putting together all the tasks defined above we come up with the following workflow definition (workflow.yaml):

name: oracle workflow
tasks:
  - name: Clean results
    operator: Executor
    arguments:
      command: rm -f ~/oltpbench/results/*
      component: tpcc

  - name: Update parameters
    operator: OracleConfigurator
    arguments:
      component: oracle

  - name: Restart Oracle container
    operator: Executor
    arguments:
      command: docker restart oraxe
      component: oracle

  - name: Execute load test
    operator: Executor
    arguments:
      command: cd ~/oltpbench ; ./oltpbenchmark --bench tpcc --config tpcc_conf.xml --execute=true -s 5 --output out
      component: tpcc

  - name: Parse TPC-C results
    operator: Executor
    arguments:
      command: cd ~/oltpbench ; ./tpcc_parse_csv.sh
      component: tpcc

We can create the workflow by running:

akamas create workflow workflow.yaml

Study

The objective of this study is to maximize the transaction throughput while stressed by the TPC-C load generator, and to achieve this goal the study will tune the size of the most important areas of the Oracle instance.

Goal

Here’s the definition of the goal for our study, which is to maximize the tpcc.throughput metric:

goal:
  objective: maximize
  function:
    formula: tpcc.throughput

Windowing

We define a window in order to consider only the data points after the ramp-up time of the load test:

windowing:
  type: trim
  trim: [4m, 1m]
  task: Execute load test

Parameters to optimize

For this study, we are trying to achieve our goal by tuning the size of several areas in the memory of the database instance. In particular, we will tune the overall size of the Program Global Area (containing the work area of the active sessions) and the size of the components of the Shared Global Area.

The domains are configured to explore, for each parameter, the values around the default values.

parametersSelection:
  - name: oracle.pga_aggregate_target
    domain: [1128, 4512]
  - name: oracle.db_cache_size
    domain: [512, 6144]
  - name: oracle.java_pool_size
    domain: [1, 1024]
  - name: oracle.large_pool_size
    domain: [1, 256]
  - name: oracle.log_buffer
    domain: [2, 256]
  - name: oracle.shared_pool_size
    domain: [128, 1024]
  - name: oracle.streams_pool_size
    domain: [1, 1024]

Constraints

The following constraint allows the study to explore different size configurations without exceeding the maximum overall memory available for the instance:

parameterConstraints:
  - name: Cap total memory to 10G
    formula: oracle.db_cache_size + oracle.java_pool_size + oracle.large_pool_size + oracle.log_buffer + oracle.shared_pool_size + oracle.streams_pool_size + oracle.pga_aggregate_target < 10240

Steps

We are going to add to our study two steps:

A baseline step, in which we configure the default values for the memory parameters as discovered from previous manual executions.
An optimization step, where we perform 200 experiments to search the set of parameters that best satisfies our goal.

The baseline step contains some additional parameters (oracle.memory_target, oracle.sga_target) that are required by Oracle in order to disable the automatic management of the SGA components.

Here’s what these steps look like:

steps:
  - name: baseline
    type: baseline
    values:
      oracle.pga_aggregate_target: 1128
      oracle.db_cache_size: 2496
      oracle.java_pool_size: 16
      oracle.large_pool_size: 16
      oracle.log_buffer: 13
      oracle.shared_pool_size: 640
      oracle.streams_pool_size: 0
      oracle.memory_target: 0
      oracle.sga_target: 0

  - name: optimization
    type: optimize
    numberOfExperiments: 200
    maxFailedExperiments: 200

Complete study

Here’s the study definition (study.yaml) for optimizing the Oracle instance:

name: Oracle: tune memory
description: Tune memory minimizing reponse
system: oracle system
workflow: oracle workflow

goal:
  objective: maximize
  function:
    formula: throughput
    variables:
      throughput:
        metric: tpcc.throughput

windowing:
  type: trim
  trim: [4m, 1m]
  task: Execute load test

parametersSelection:
  - name: oracle.pga_aggregate_target
    domain: [1128, 4512]
  - name: oracle.db_cache_size
    domain: [1024, 6144]
  - name: oracle.java_pool_size
    domain: [1, 1024]
  - name: oracle.large_pool_size
    domain: [1, 256]
  - name: oracle.log_buffer
    domain: [2, 256]
  - name: oracle.shared_pool_size
    domain: [128, 1024]
  - name: oracle.streams_pool_size
    domain: [1, 1024]

parameterConstraints:
  - name: Cap total memory to 10G
    formula: oracle.db_cache_size + oracle.java_pool_size + oracle.large_pool_size + oracle.log_buffer + oracle.shared_pool_size + oracle.streams_pool_size + oracle.pga_aggregate_target < 10240

steps:
  - name: baseline
    type: baseline
    values:
      oracle.pga_aggregate_target: 1128
      oracle.db_cache_size: 2496
      oracle.java_pool_size: 16
      oracle.large_pool_size: 16
      oracle.log_buffer: 13
      oracle.shared_pool_size: 640
      oracle.streams_pool_size: 0
      oracle.memory_target: 0
      oracle.sga_target: 0

  - name: optimization
    type: optimize
    numberOfExperiments: 200
    maxFailedExperiments: 200

You can create the study by running:

akamas create study study.yaml

You can then start it by running:

akamas start study 'Oracle: tune memory'