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 the workload generation, in this example we are going to use 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.
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.
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.
To install OLTP you can download a pre-built version here or build it from the official repository. 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:1
#!/bin/bash
2
set -e
3
4
5
cd "$(dirname "$0")"
6
cd ../oltp
7
mysql -u root -proot -e "CREATE DATABASE resourcestresser"
8
./oltpbenchmark --bench resourcestresser --config scripts/resourcestresser.xml --create=true --load=true
9
10
sleep 5
11
sudo systemctl stop mysql
12
13
#Create the backup
14
echo "Backing up the database"
15
sudo rm -rf /tmp/backup
16
sudo mkdir /tmp/backup
17
sudo rsync -r --progress /var/lib/mysql /tmp/backup/
18
sleep 2
19
20
sudo systemctl start mysql
21
sudo systemctl status mysql
This script will:
- connect to your MySQL installation
- create a
resourcestresserdatabase 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.Here follow a step-by-step explanation of all the required configurations for this example.
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
mysqlcomponent which represents the MySQL instance, including all the configuration parameters - An
OLTPcomponent which represents OLTPBench and contains the custom metrics reported by the benchmark
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. In order 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.yamlfile with the following content:1
---
2
metrics:
3
- name: throughput
4
description: The throughput of the database
5
unit: tps
6
7
- name: response_time_avg
8
description: The average response time of the database
9
unit: milliseconds
10
11
- name: response_time_min
12
description: The minimum response time of the database
13
unit: milliseconds
14
15
- name: response_time_25th
16
description: The response time 25th percentile of the database
17
unit: milliseconds
18
19
- name: response_time_median
20
description: The response time median of the database
21
unit: milliseconds
22
23
- name: response_time_75th
24
description: The response time 75th percentile of the database
25
unit: milliseconds
26
27
- name: response_time_90th
28
description: The response time 90th percentile of the database
29
unit: milliseconds
30
31
- name: response_time_95th
32
description: The response time 95th percentile of the database
33
unit: milliseconds
34
35
- name: response_time_99th
36
description: The response time 99th percentile of the database
37
unit: milliseconds
38
39
- name: response_time_max
40
description: The maximum response time of the database
41
unit: milliseconds
42
43
- name: duration
44
description: The duration of the task (load or benchmark execution)
45
unit: seconds
You can now create the metrics by issuing the following command:
akamas create metrics metrics.yaml
Finally, create a file named
resourcestresser.yaml with the following definition of the component:1
name: ResourceStresser
2
description: >
3
ResourceStresser benchmark from OLTPBench for database systems. It is a
4
purely synthetic benchmark that can create isolated contention on the system
5
resources. Each of the benchmark’s transactions imposes some load on three
6
specific resources: CPU, disk I/O, and locks.
7
parameters: []
8
metrics:
9
- name: throughput
10
- name: response_time_avg
11
- name: response_time_max
12
- name: response_time_min
13
- name: response_time_25th
14
- name: response_time_median
15
- name: response_time_75th
16
- name: response_time_90th
17
- name: response_time_95th
18
- name: response_time_99th
19
- name: duration
You can now create the metrics by issuing the following command:
akamas create component-type resourcestresser.yaml
Here’s the definition of our system (
system.yaml):1
name: MySQL-ResourceStresser
2
description: A system for evaluating MySQL with OLTP Benchmark
Here’s the definition of our
mysql component (mysql.yaml):1
name: mysql
2
description: MySQL
3
componentType: MySQL 8.0
4
properties:
5
hostname: gibbo.dev.akamas.io
6
sshPort: "22"
7
username: ubuntu
8
sourcePath: /home/ubuntu/scripts/my.cnf.template
9
targetPath: /home/ubuntu/scripts/my.cnf
10
prometheus:
11
instance: gibbo
12
job: mysql_exporter
13
key: |
14
-----BEGIN RSA PRIVATE KEY-----
15
16
-----END RSA PRIVATE KEY-----
17
Here’s the definition of our
OLTP component (oltp.yaml):1
name: OLTP
2
description: OLTP Benchmark for database systems
3
componentType: ResourceStresser
We can create the system by running:
akamas create system system.yaml
We can then create the components by running:
akamas create component mysql.yaml MySQL-ResourceStresser
akamas create component oltp.yaml MySQL-ResourceStresser
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:1
#!/usr/bin/env bash
2
set -e
3
4
cd "$(dirname "$0")"
5
6
#Stop the DB
7
echo "Stopping MySQL"
8
sudo systemctl stop mysql &> /dev/null
9
#sudo systemctl status mysql
10
11
#Apply Configuration
12
echo "Copying the configuration"
13
sudo cp my.cnf /etc/mysql/conf.d/mysql.cnf
14
15
#Drop data
16
echo "Dropping the data"
17
sudo rm -rf /var/lib/mysql
18
#Create the backup
19
# sudo rsync -r --progress /var/lib/mysql /tmp/backup/
20
21
#Restore the backup data
22
echo "Restoring the DB"
23
sudo rsync -r --progress /tmp/backup/mysql /var/lib/
24
sudo chown -R mysql: /var/lib/mysql
25
26
sync; sudo sh -c "echo 3 > /proc/sys/vm/drop_caches"; sync
27
28
#Restart DB
29
echo "Restarting the database"
30
sudo systemctl start mysql &> /dev/null
31
#sudo systemctl status mysql
32
sleep 2
This is the
clean_bench.sh script:1
#!/usr/bin/env bash
2
set -e
3
cd "$(dirname "$0")"
4
5
6
if ! test -d results || [[ -z "$(ls -A results)" ]]; then
7
echo "First iteration"
8
mkdir -p results
9
exit 0
10
fi
11
12
rm -rf results
13
mkdir -p results
14
This is the
run_test.sh script:1
#!/bin/bash
2
set -e
3
4
cd "$(dirname "$0")"
5
6
HOST="--mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root"
7
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:1
#!/bin/bash
2
set -e
3
cd "$(dirname "$0")"
4
OUTFILE=$(pwd)/results/output.csv
5
INFILE=$(pwd)/results/res.txt
6
COMPONENT=Sysbench
7
epoch_now=$(date +"%s")
8
num_samples=$(grep -c "thds" ${INFILE})
9
epoch_start=$(($epoch_now - $num_samples))
10
cat $INFILE | while read line
11
do
12
ts_sysbench=$(echo $line | cut -d' ' -f2)
13
# CSV header
14
[ "$ts_sysbench" == "started!" ] && echo "component,ts,throughput,response_time_95pct" > ${OUTFILE} && continue
15
# CSV body
16
tps=$(echo $line | cut -d' ' -f7)
17
lat_95pct=$(echo $line | cut -d' ' -f14)
18
# skip unless it's a metric line
19
echo $line | grep -q "thds" || continue
20
ts_seconds=$(echo $ts_sysbench | sed 's/s//')
21
epoch_current=$(($epoch_start + $ts_seconds))
22
ts=$(date -d @$(($epoch_current)) "+%Y-%m-%d %H:%M:%S")
23
echo "${COMPONENT},$ts,$tps,$lat_95pct" >> ${OUTFILE}
24
done
Here is the complete Akamas workflow for this example (
workflow.yaml):1
name: MySQL-ResourceStresser
2
tasks:
3
4
- name: Reset OLTP data
5
operator: Executor
6
arguments:
7
command: "bash /home/ubuntu/scripts/clean_bench.sh"
8
component: mysql
9
10
- name: Configure MySQL
11
operator: FileConfigurator
12
arguments:
13
component: mysql
14
15
- name: Restart MySQL
16
operator: Executor
17
arguments:
18
command: "/home/ubuntu/scripts/restart-mysql.sh"
19
component: mysql
20
21
- name: test
22
operator: Executor
23
arguments:
24
command: "cd /home/ubuntu/oltp && ./oltpbenchmark --bench resourcestresser --config /home/ubuntu/scripts/resourcestresser.xml --execute=true -s 5 --output out"
25
component: mysql
26
27
- name: Parse csv results
28
operator: Executor
29
arguments:
30
command: "bash /home/ubuntu/scripts/parse_csv.sh"
31
component: mysql
32
You can create the workflow by running:
akamas create workflow workflow.yaml
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, 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:
akamas install telemetry-provider telemetry/providers/csv.yaml
To start using the provider, we need to define a telemetry instance (
csv.yaml):1
provider: csv
2
config:
3
protocol: scp
4
address: gibbo.dev.akamas.io
5
username: ubuntu
6
authType: key
7
auth: |
8
-----BEGIN RSA PRIVATE KEY-----
9
10
-----END RSA PRIVATE KEY-----
11
remoteFilePattern: /home/ubuntu/output.csv
12
csvFormat: horizontal
13
componentColumn: component
14
timestampColumn: ts
15
timestampFormat: yyyy-MM-dd HH:mm:ss
16
17
metrics:
18
- metric: throughput
19
datasourceMetric: throughput
20
- metric: response_time_avg
21
datasourceMetric: response_time_avg
22
- metric: response_time_max
23
datasourceMetric: response_time_max
24
- metric: response_time_min
25
datasourceMetric: response_time_min
26
- metric: response_time_25th
27
datasourceMetric: response_time_25th
28
- metric: response_time_median
29
datasourceMetric: response_time_median
30
- metric: response_time_75th
31
datasourceMetric: response_time_75th
32
- metric: response_time_90th
33
datasourceMetric: response_time_90th
34
- metric: response_time_95th
35
datasourceMetric: response_time_95th
36
- metric: response_time_99th
37
datasourceMetric: response_time_99th
38
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:
akamas create telemetry-instance csv.yaml MySQL-ResourceStresser
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):1
name: MySQL Tuning
2
description: Tuning of mysql-8 with OLTPBenchmark using benchmark resourcestresser
3
system: MySQL-ResourceStresser
4
workflow: MySQL-ResourceStresser
5
6
goal:
7
objective: maximize
8
function:
9
formula: max(1, OLTP.throughput)
10
constraints: []
11
12
windowing:
13
task: test
14
type: trim
15
trim: [1m, 1m]
16
17
18
parametersSelection:
19
# postgres
20
- name: mysql.mysql_innodb_buffer_pool_size
21
domain: [5242880, 10485760]
22
- name: mysql.mysql_innodb_thread_sleep_delay
23
- name: mysql.mysql_innodb_flush_method
24
- name: mysql.mysql_innodb_log_file_size
25
- name: mysql.mysql_innodb_thread_concurrency
26
domain: [0, 4]
27
- name: mysql.mysql_innodb_max_dirty_pages_pct
28
- name: mysql.mysql_innodb_read_ahead_threshold
29
30
31
trialAggregation: AVG
32
numberOfTrials: 1
33
34
steps:
35
- name: baseline
36
type: baseline
37
renderParameters: ["mysql.*"]
38
39
- name: optimize
40
type: optimize
41
optimizer: AKAMAS
42
numberOfExperiments: 200
43
maxFailedExperiments: 200
44
renderParameters: ["mysql.*"]
You can create the study by running:
akamas create study study.yaml
You can then start it by running:
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.