# Optimizing a MySQL server database running OLTPBench

In this example study, we are going to optimize a MySQL instance by setting the performance goal of maximizing the throughput of operations towards the database.

As regards workload generation, in this example we are going to use [OLTPBench](https://github.com/oltpbenchmark/oltpbench), a popular open-source benchmarking suite for databases. OLTPBench supports several benchmarks, in this example we will be using Synthetic Resource Stresser.

To import the results of the benchmark into Akamas, we are going to use a custom script to convert its output to a CSV file that can be parsed by the [CSV provider](https://docs.akamas.io/akamas-docs/3.6/integrating/integrating-telemetry-providers/csv-provider).

## Environment Setup

In order to run the OLTP Benchmark suite against a MySQL installation, you need to first install and configure the two software. In the following, we will assume that both MySQL and OLTP will run on the same machine, to obtain more significant results in terms of performance you might want to run them on separate hosts.

### MySQL Installation

To install MySQL please follow the official documentation. In the following, we will make a few assumptions on the location of the configuration files, the user running the server, and the location of the datafiles. These assumptions are based on a default installation of MySQL on an Ubuntu instance performed via apt.

* **Datafile location**: `/var/lib/mysql`
* **Configuration file**: `/etc/mysql/conf.d/mysql.cnf`
* **MySQL user**: mysql
* **MySQL root user password:** root

This is a template for the configuration file `mysql.cnf.template`

```
[mysqld]

innodb_buffer_pool_size     = 134217728
innodb_thread_sleep_delay   = 10000
innodb_flush_method         = fsync
innodb_log_file_size        = 50331648
innodb_thread_concurrency   = 0
innodb_max_dirty_pages_pct  = 10.00000
innodb_read_ahead_threshold = 56
```

If your installation of MySQL has different default values for these parameters please update the provided scripts accordingly.

### OLTP Installation <a href="#oltp-installation" id="oltp-installation"></a>

To install OLTP you can download a pre-built version [here](https://akamas.s3.us-east-2.amazonaws.com/oltp.tgz) or build it from the [official repository](https://github.com/oltpbenchmark/oltpbench/wiki). In the following, we will assume that OLTP is installed in the `/home/ubuntu/oltp` folder.

To verify your installation of OLTP and initialize the database you can download the following set of scripts and place them in the `/home/ubuntu/scripts` folder. Move in the folder and run the `init-db.sh` script.

This is the `init-db.sh` script:

```bash
#!/bin/bash
set -e

cd "$(dirname "$0")"
cd ../oltp
mysql -u root -proot -e "CREATE DATABASE resourcestresser"
./oltpbenchmark --bench resourcestresser --config scripts/resourcestresser.xml --create=true --load=true

sleep 5
sudo systemctl stop mysql

#Create the backup
echo "Backing up the database"
sudo rm -rf /tmp/backup
sudo mkdir /tmp/backup
sudo rsync -r --progress /var/lib/mysql /tmp/backup/
sleep 2

sudo systemctl start mysql
sudo systemctl status mysql
```

This script will:

* connect to your MySQL installation
* create a `resourcestresser` database for the test
* run the OLTP data generation phase to populate the database
* backup the initialized database under `/tmp/backup`

The `resourcestresser.xml` file contains the workload for the application. The default setting is quite small and should be used for testing purposes. You can then modify the test to suit your benchmarking needs.

## Optimization Setup

Here is a step-by-step explanation of all the required configurations for this example.

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

In this example, we are interested in optimizing MySQL settings and measuring the peak throughput measured using OLTPBench. Hence, we are going to create two components:

* A `mysql` component which represents the MySQL instance, including all the configuration parameters
* An `OLTP` component which represents OLTPBench and contains the custom metrics reported by the benchmark

#### The OLTP component <a href="#the-oltp-component" id="the-oltp-component"></a>

MySQL is a widespread technology and Akamas provides a specific Optimization Pack to support its optimization. OLTP, on the other hand, is a benchmark application and is not yet supported by a specific optimization pack. To use it in our study, we will need to define its metrics first. This operation can be done once and the created component type can be used across many systems.

First, build a `metrics.yaml`file with the following content:

```yaml
metrics:
  - name: throughput
    description: The throughput of the database
    unit: tps

  - name: response_time_avg
    description: The average response time of the database
    unit: milliseconds

  - name: response_time_min
    description: The minimum response time of the database
    unit: milliseconds

  - name: response_time_25th
    description: The response time 25th percentile of the database
    unit: milliseconds

  - name: response_time_median
    description: The response time median of the database
    unit: milliseconds

  - name: response_time_75th
    description: The response time 75th percentile of the database
    unit: milliseconds

  - name: response_time_90th
    description: The response time 90th percentile of the database
    unit: milliseconds

  - name: response_time_95th
    description: The response time 95th percentile of the database
    unit: milliseconds

  - name: response_time_99th
    description: The response time 99th percentile of the database
    unit: milliseconds

  - name: response_time_max
    description: The maximum response time of the database
    unit: milliseconds

  - name: duration
    description: The duration of the task (load or benchmark execution)
    unit: seconds
```

You can now create the metrics by issuing the following command:

```bash
akamas create metrics metrics.yaml
```

Finally, create a file named `resourcestresser.yaml` with the following definition of the component:

```yaml
name: ResourceStresser
description: >
  ResourceStresser benchmark from OLTPBench for database systems. It is a
  purely synthetic benchmark that can create isolated contention on the system
  resources. Each of the benchmark’s transactions imposes some load on three
  specific resources: CPU, disk I/O, and locks.
parameters: []
metrics:
  - name: throughput
  - name: response_time_avg
  - name: response_time_max
  - name: response_time_min
  - name: response_time_25th
  - name: response_time_median
  - name: response_time_75th
  - name: response_time_90th
  - name: response_time_95th
  - name: response_time_99th
  - name: duration
```

You can now create the metrics by issuing the following command:

```bash
akamas create component-type resourcestresser.yaml
```

#### **Model the system** <a href="#model-the-system" id="model-the-system"></a>

Here’s the definition of our system (`system.yaml`):

```yaml
name: MySQL-ResourceStresser
description: A system for evaluating MySQL with OLTP Benchmark
```

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

```yaml
name: mysql
description: MySQL
componentType: MySQL 8.0
properties:
    hostname: gibbo.dev.akamas.io
    sshPort: "22"
    username: ubuntu
    sourcePath: /home/ubuntu/scripts/my.cnf.template
    targetPath: /home/ubuntu/scripts/my.cnf
    prometheus:
      instance: gibbo
      job: mysql_exporter
    key: |
            -----BEGIN RSA PRIVATE KEY-----
            ...
            -----END RSA PRIVATE KEY-----

```

Here’s the definition of our `OLTP` component (`oltp.yaml`):

```yaml
name: OLTP
description: OLTP Benchmark for database systems
componentType: ResourceStresser
```

We can create the system by running:

```bash
akamas create system system.yaml
```

We can then create the components by running:

```bash
akamas create component mysql.yaml MySQL-ResourceStresser
akamas create component oltp.yaml MySQL-ResourceStresser
```

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

A workflow for optimizing MySQL can be structured into 6 tasks:

1. Reset OLTPBench data
2. Configure MySQL
3. Restart MySQL
4. Launch the benchmark
5. Parse the benchmark results

Here below you can find the scripts that codify these tasks.

This is the `restart-mysql.sh` script:

```bash
#!/usr/bin/env bash
set -e

cd "$(dirname "$0")"

#Stop the DB
echo "Stopping MySQL"
sudo systemctl stop mysql &> /dev/null
#sudo systemctl status mysql

#Apply Configuration
echo "Copying the configuration"
sudo cp my.cnf /etc/mysql/conf.d/mysql.cnf

#Drop data
echo "Dropping the data"
sudo rm -rf /var/lib/mysql
#Create the backup
# sudo rsync -r --progress /var/lib/mysql /tmp/backup/

#Restore the backup data
echo "Restoring the DB"
sudo rsync -r --progress /tmp/backup/mysql /var/lib/
sudo chown -R mysql: /var/lib/mysql

sync; sudo sh -c "echo 3 > /proc/sys/vm/drop_caches"; sync

#Restart DB
echo "Restarting the database"
sudo systemctl start mysql &> /dev/null
#sudo systemctl status mysql
sleep 2
```

This is the `clean_bench.sh` script:

```bash
#!/usr/bin/env bash
set -e
cd "$(dirname "$0")"

if ! test -d results || [[ -z "$(ls -A results)" ]]; then
    echo "First iteration"
    mkdir -p results
    exit 0
fi

rm -rf results
mkdir -p results

```

This is the `run_test.sh` script:

```bash
#!/bin/bash
set -e

cd "$(dirname "$0")"

HOST="--mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root"
sysbench oltp_read_only --tables=10 --table_size=1000000 --threads=100 $HOST --time=60 --max-requests=0 --report-interval=1 --rand-type=uniform --db-driver=mysql --mysql-db=sbtest --mysql-ssl=off run | tee -a results/res.txt
```

This file `parse_csv.sh` script:

```bash
#!/bin/bash
set -e
cd "$(dirname "$0")"
OUTFILE=$(pwd)/results/output.csv
INFILE=$(pwd)/results/res.txt
COMPONENT=Sysbench
epoch_now=$(date +"%s")
num_samples=$(grep -c "thds" ${INFILE})
epoch_start=$(($epoch_now - $num_samples))
cat $INFILE | while read line
do
        ts_sysbench=$(echo $line | cut -d' ' -f2)
        # CSV header
        [ "$ts_sysbench" == "started!" ] && echo "component,ts,throughput,response_time_95pct" > ${OUTFILE} && continue
        # CSV body
        tps=$(echo $line | cut -d' ' -f7)
        lat_95pct=$(echo $line | cut -d' ' -f14)
        # skip unless it's a metric line
        echo $line | grep -q "thds" || continue
        ts_seconds=$(echo $ts_sysbench | sed 's/s//')
        epoch_current=$(($epoch_start + $ts_seconds))
        ts=$(date -d @$(($epoch_current)) "+%Y-%m-%d %H:%M:%S")
        echo "${COMPONENT},$ts,$tps,$lat_95pct" >> ${OUTFILE}
done
```

Here is the complete Akamas workflow for this example (`workflow.yaml`):

```yaml
name: MySQL-ResourceStresser
tasks:

  - name: Reset OLTP data
    operator: Executor
    arguments:
      command: "bash /home/ubuntu/scripts/clean_bench.sh"
      component: mysql

  - name: Configure MySQL
    operator: FileConfigurator
    arguments:
      component: mysql

  - name: Restart MySQL
    operator: Executor
    arguments:
      command: "/home/ubuntu/scripts/restart-mysql.sh"
      component: mysql

  - name: test
    operator: Executor
    arguments:
      command: "cd /home/ubuntu/oltp && ./oltpbenchmark --bench resourcestresser --config /home/ubuntu/scripts/resourcestresser.xml --execute=true -s 5 --output out"
      component: mysql

  - name: Parse csv results
    operator: Executor
    arguments:
      command: "bash /home/ubuntu/scripts/parse_csv.sh"
      component: mysql

```

You can create the workflow by running:

```bash
akamas create workflow workflow.yaml
```

### Telemetry <a href="#telemetry" id="telemetry"></a>

We are going to use Akamas telemetry capability to import the metrics related to OLTPBench benchmark results, in particular the throughput of operations. To achieve this we can leverage the Akamas [CSV provider](https://docs.akamas.io/akamas-docs/3.6/integrating/integrating-telemetry-providers/csv-provider), which extracts metrics from CSV files. The CSV file is the one produced in the last task of the workflow of the study.

This telemetry provider can be installed by running:

```bash
akamas install telemetry-provider telemetry/providers/csv.yaml
```

To start using the provider, we need to define a telemetry instance (`csv.yaml`):

```yaml
provider: csv
config:
  protocol: scp
  address: gibbo.dev.akamas.io
  username: ubuntu
  authType: key
  auth: |
    -----BEGIN RSA PRIVATE KEY-----
    ...
    -----END RSA PRIVATE KEY-----
  remoteFilePattern: /home/ubuntu/output.csv
  csvFormat: horizontal
  componentColumn: component
  timestampColumn: ts
  timestampFormat: yyyy-MM-dd HH:mm:ss

metrics:
- metric: throughput
  datasourceMetric: throughput
- metric: response_time_avg
  datasourceMetric: response_time_avg
- metric: response_time_max
  datasourceMetric: response_time_max
- metric: response_time_min
  datasourceMetric: response_time_min
- metric: response_time_25th
  datasourceMetric: response_time_25th
- metric: response_time_median
  datasourceMetric: response_time_median
- metric: response_time_75th
  datasourceMetric: response_time_75th
- metric: response_time_90th
  datasourceMetric: response_time_90th
- metric: response_time_95th
  datasourceMetric: response_time_95th
- metric: response_time_99th
  datasourceMetric: response_time_99th

```

Please make sure the telemetry configuration is correct for your environment (e.g. hostname, username, key, file paths, etc.).

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

```bash
akamas create telemetry-instance csv.yaml MySQL-ResourceStresser
```

### Study <a href="#study" id="study"></a>

In this example, we are going to leverage Akamas AI-driven optimization capabilities to maximize MySQL database query throughput, as measured by the OLTPBench benchmark.

Here is the Akamas study definition (`study.yaml`):

```yaml
name: MySQL Tuning
description: Tuning of mysql-8 with OLTPBenchmark using benchmark resourcestresser
system: MySQL-ResourceStresser
workflow: MySQL-ResourceStresser

goal:
  objective: maximize
  function:
    formula: max(1, OLTP.throughput)
  constraints: []

windowing:
  task: test
  type: trim
  trim: [1m, 1m]

parametersSelection:
  # postgres
  - name: mysql.mysql_innodb_buffer_pool_size
    domain: [5242880, 10485760]
  - name: mysql.mysql_innodb_thread_sleep_delay
  - name: mysql.mysql_innodb_flush_method
  - name: mysql.mysql_innodb_log_file_size
  - name: mysql.mysql_innodb_thread_concurrency
    domain: [0, 4]
  - name: mysql.mysql_innodb_max_dirty_pages_pct
  - name: mysql.mysql_innodb_read_ahead_threshold

trialAggregation: AVG
numberOfTrials: 1

steps:
  - name: baseline
    type: baseline
    renderParameters: ["mysql.*"]

  - name: optimize
    type: optimize
    optimizer: AKAMAS
    numberOfExperiments: 200
    maxFailedExperiments: 200
    renderParameters: ["mysql.*"]
```

You can create the study by running:

```bash
akamas create study study.yaml
```

You can then start it by running:

```bash
akamas start study "MySql Tuning"
```

You can now follow the study progress using the UI and explore the results using the Analysis and Metrics tabs.