Optimizing a MySQL server database running Sysbench
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 Sysbench, a popular open-source benchmarking suite.
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 Sysbench 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 Sysbench will run on the same machine, to obtain more significant results in terms of performance you might want to run them on separate hosts.
A set of scripts is provided to support all the setup steps.
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.
- 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]
socket=/tmp/mysql.sock
ssl=0
innodb_buffer_pool_size = ${mysql.mysql_innodb_buffer_pool_size}
innodb_thread_sleep_delay = ${mysql.mysql_innodb_thread_sleep_delay}
innodb_flush_method = ${mysql.mysql_innodb_flush_method}
innodb_log_file_size = ${mysql.mysql_innodb_log_file_size}
innodb_thread_concurrency = ${mysql.mysql_innodb_thread_concurrency}
innodb_max_dirty_pages_pct = ${mysql.mysql_innodb_max_dirty_pages_pct}
innodb_read_ahead_threshold = ${mysql.mysql_innodb_read_ahead_threshold}
If your installation of MySQL has different default values for these parameters please update the provided scripts accordingly.
To install Sysbench on an ubuntu machine run the following command
sudo apt install sysbench
To verify your installation of Sysbench and initialize the database you can use the scripts provided here below and place them in the
/home/ubuntu/scripts folder. Move in the folder, make sure MySQL is already running, and run the init-db.sh script.This is the
init-db.sh script:1
#!/bin/bash
2
set -e
3
cd "$(dirname "$0")"
4
mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS sbtest"
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=300 --max-requests=0 --report-interval=1 --rand-type=uniform --db-driver=mysql --mysql-db=sbtest --mysql-ssl=off prepare| tee -a res.warmup.ro.txt
8
9
#sleep 5
10
#sudo systemctl stop mysql
11
#
12
##Create the backup
13
#echo "Backing up the database"
14
#sudo rm -rf /tmp/backup
15
#sudo mkdir /tmp/backup
16
#sudo rsync -r --progress /var/lib/mysql /tmp/backup/
17
#sleep 2
18
#
19
#sudo systemctl start mysql
20
#sudo systemctl status mysql
This script will:
- connect to your MySQL installation
- create a
sbtestdatabase for the test - run the Sysbench data generation phase to populate the database
The
init-db.sh script contains some information on the amount of data to generate. The default setting is quite small and should be used for testing purposes. You can then modify the test to suit your benchmarking needs. If you update the script please also update the run_benchmark.sh script accordingly.Here follow a step by step explanation of all the required configuration for this example. You can find attached a zip file that contains all of the YAML files for your convenience.
In this example, we are interested in optimizing MySQL settings and measuring the peak throughput measured using Sysbench. Hence, we are going to create two components:
- A
mysqlcomponent which represents the MySQL instance, including all the configuration parameters - A
Sysbenchcomponent which represents Sysbench 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. Sysbench, 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_95th
12
description: The response time 95th percentile of the database
13
unit: milliseconds
14
15
- name: duration
16
description: The duration of the task (load or benchmark execution)
17
unit: seconds
You can now create the metrics by issuing the following command:
akamas create metrics metrics.yaml
Finally, create a file named
sysbench.yaml with the following definition of the component:1
name: Sysbench
2
description: >
3
Sysbench benchmark. It is a purely synthetic benchmark that can create isolated contention on system resources. Each of the benchmark’s transaction imposes some load on three specific resources: CPU, disk I/O, and locks. It is also used to simulate a database workload.
4
parameters: []
5
metrics:
6
- name: throughput
7
- name: response_time_avg
8
- name: response_time_95th
9
- name: duration
You can now create the component by issuing the following command:
akamas create component-type sysbench.yaml
Here’s the definition of our system (
system.yaml):1
name: MySQL-Sysbench
2
description: A system for optimizing MySQL with Sysbench
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-----
Please make sure the component properties are correct for your environment (e.g. hostname, username, key, file paths, etc.).
Here’s the definition of our
Sysbench component (sysbench.yaml):1
name: Sysbench
2
description: Sysbench Benchmark for database systems
3
componentType: Sysbench
We can create the system by running:
akamas create system system.yaml
We can then create the components by running the following commands:
akamas create component mysql.yaml MySQL-Sysbench
akamas create component sysbench.yaml MySQL-Sysbench
A workflow for optimizing MySQL can be structured in 6 tasks:
- 1.Reset Sysbench 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
sync; sudo sh -c "echo 3 > /proc/sys/vm/drop_caches"; sync
16
17
#Restart DB
18
echo "Restarting the database"
19
sudo systemctl start mysql &> /dev/null
20
#sudo systemctl status mysql
21
sleep 2
This is the
clean_bench.sh script:1
#!/usr/bin/env bash
2
set -e
3
cd "$(dirname "$0")"
4
5
if ! test -d results || [[ -z "$(ls -A results)" ]]; then
6
echo "First iteration"
7
mkdir -p results
8
exit 0
9
fi
10
11
rm -rf results
12
mkdir -p results
13
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-Sysbench
2
tasks:
3
4
- name: Reset Sysbench 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: "bash /home/ubuntu/scripts/restart-mysql.sh"
19
component: mysql
20
21
- name: test
22
operator: Executor
23
arguments:
24
command: "bash /home/ubuntu/scripts/run_test.sh"
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
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 Sysbench benchmark results, in particular, the transaction throughput and latency. 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 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/scripts/results/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_95th
21
datasourceMetric: response_time_95pct
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-Sysbench
In this example, we are going to leverage Akamas AI-driven optimization capabilities to maximize MySQL database transaction throughput, as measured by the Sysbench benchmark.
Here is the Akamas study definition (
study.yaml):1
name: MySQL Sysbench Tuning
2
description: Tuning of mysql-8 with Sysbench benchmark
3
system: MySQL-Sysbench
4
workflow: MySQL-Sysbench
5
6
goal:
7
objective: maximize
8
function:
9
formula: Sysbench.throughput
10
constraints: []
11
12
# Akamas score automatically trim 1m of warm-up and 1m of tear-down
13
windowing:
14
task: test
15
type: trim
16
trim: [1m, 1m]
17
18
# We optimize some common MySQL parameters
19
parametersSelection:
20
- name: mysql.mysql_innodb_buffer_pool_size
21
domain: [5242880, 10485760]
22
- name: mysql.mysql_innodb_thread_sleep_delay
23
domain: [1,3000]
24
- name: mysql.mysql_innodb_flush_method
25
- name: mysql.mysql_innodb_log_file_size
26
- name: mysql.mysql_innodb_thread_concurrency
27
domain: [0, 4]
28
- name: mysql.mysql_innodb_max_dirty_pages_pct
29
- name: mysql.mysql_innodb_read_ahead_threshold
30
31
# The metrics we are interested in
32
metricsSelection:
33
- Sysbench.throughput
34
- Sysbench.response_time_95th
35
36
# Each experiment can run multiple trials to evaluate stability
37
numberOfTrials: 1
38
39
steps:
40
# We first run a baseline experiment with default values
41
- name: baseline
42
type: baseline
43
renderParameters: ["mysql.*"]
44
45
# We then optimize for 200 experiments
46
- name: optimize
47
type: optimize
48
optimizer: AKAMAS
49
numberOfExperiments: 200
50
maxFailedExperiments: 200
51
renderParameters: ["mysql.*"]
You may need to update some parameter domains based on your environment (e.g. InnoDB buffer pool size maximum value depends on your server available memory)
You can create the study by running:
akamas create study study.yaml
You can then start it by running:
akamas start study "MySQL Sysbench Tuning"
You can now follow the study progress using the UI and explore the results using the Analysis and Metrics tabs.