search
Ctrlk

Optimizing an Oracle Database for an e-commerce service

In this example study, we will tune the initialization parameters of an Oracle Database server instance to minimize the memory required for running KonaKartarrow-up-right, a popular Java e-commerce service, without significantly impacting the responsiveness of the whole system.

We’ll use Apache JMeterarrow-up-right to stress the system for the test, while we will leverage the Oracle Prometheus exporterarrow-up-right to extract the metrics.

hashtag
Environment Setup

hashtag
Environment

For this study, we will use three dedicated machines:

  • oradb.mycompany.com, hosting an Oracle Database 19c instance

  • konakart.mycompany.com, running the KonaKart Community Edition service

  • akamas.mycompany.com, which generates the workload using JMeter and will host the OracleDB Prometheus exporterarrow-up-right instance

Refer to the following links to install and configure KonaKart Community Edition:

For this use case, we provisioned the database on a VM on Oracle Cloudarrow-up-right, which allows us to easily provision licensed instances on demand.

hashtag
Prometheus and exporters

hashtag
Install the OracleDB Prometheus exporter

Through the OracleDB Prometheus exporter, we can publish as metrics the results of the arbitrary 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.

We can spin up the exporter using the official Docker imagearrow-up-right using the following command, where cust-metrics.toml is our custom metrics file:

The exporter will publish the metrics on the specified port 9161.

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

hashtag
Install and configure Prometheus

Using the following snippet we configure Prometheus to fetch metrics from:

  • the JMeter exporter exposing the load-generator stats

  • the OracleDB exporter monitoring the database

For a complete guide on how to configure and manage Prometheus refer to the official documentationarrow-up-right.

hashtag
Setup the load-generator

The load generator runs containerized on the akamas.mycomopany.com instance using the attached Konakart_optimizePerf.jmx configuration file.

The provided run_test.sh wraps the command to execute the test, and requires as an argument the URL of the target KonaKart instance.

hashtag
Optimization Setup

hashtag
System

Our modeled system includes the following components:

  • The oracle component that models the Oracle Database instance on oradb.mycompany.com, whose parameters are the targets of our optimization

  • The webapp component that models the KonaKart service running on konakart.mycompany.com, providing the performance metrics used to validate the system’s SLOs

The first step is defining the system (system.yaml):

Here’s the definition of our oracle component (oracle.yaml), including the parameters needed to connect to the database instances and the filters to fetch metrics from Prometheus.

Notice: to update the init parameter the user requires the ALTER SYSTEM privilege.

Here’s the definition of the konakart component (konakart.yaml), containing the filters to fetch the metrics from Prometheus:

We can create the system by running the following command:

We can then create the components by running the following commands:

hashtag
Telemetry

Since we are using Prometheus to extract the database metrics we can leverage the Prometheus provider, which already includes the queries needed for the Oracle and JMetric queries for the metrics we need. To use the Prometheus provider we need to define a telemetry instance (prom.yaml):

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

hashtag
Workflow

This section outlines the steps performed during the execution of the experiments.

hashtag
Stop KonaKart

Using an Executor operator we run a command to stop the KonaKart instance using the script provided with the installation, then check the service is not running anymore with a custom script:

Attached you can find the referenced script check_konakart_stop.sh:

hashtag
Configure the Oracle instance

Using the OracleConfigurator operator to update the Oracle initialization parameters with the new configuration. Then with the Executor operator, we run some custom scripts to restart the database instance to apply the new parameters and check for a successful startup. Additionally, in case of a failed startup, the script of the last task restores a backup of the default configuration file (spfile), restarts the database, and returns an error code to notify Akamas that the tested configuration is invalid:

Attached you can find the referenced script check_db.sh:

and restart_db.sh:

hashtag
Restart the KonaKart instance

We then define the Executor operator tasks that restart the KonaKart service and check it is running correctly:

Attached you can find the referenced script:

hashtag
Run the workload

Finally, we define a task that uses the Executor operator to run the JMeter load test against the KonaKart instance:

hashtag
Complete workflow

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

We can create the workflow by running:

hashtag
Study

This study aims to minimize the memory allocated for the Oracle database while under a simulated load of the typical traffic, without impacting the SLOs.

This section provides a step-by-step description of the study definition.

hashtag
Goal

Here’s the definition of the goal for our study, which is to minimize the memory allocated by Oracle to the SGA and PGA memory areas. The constraints ensure that any tested configuration that does not operate within the defined SLOs is flagged as not valid. In particular, the following are required:

  • the peak error rate must not exceed 5 errors per second

  • the transaction throughput must not decrease more than 10% with respect to the baseline

  • the response time must not increase more than 20% with respect to the baseline

hashtag
Windowing

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

hashtag
Parameters to optimize

For this study, we are trying to optimize the size of the two main memory areas, meaning the Program Global Area and the Shared Global Area.

Given our goal, we set the domains of the parameters to explore only sizes smaller than the baseline.

The following constraint prevents Akamas from exploring configurations that we already know Oracle won’t validate:

hashtag
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.

Here’s what these steps look like:

hashtag
Complete study

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

You can create the study by running:

You can then start it by running:

Last updated

Was this helpful?