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
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:
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.
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 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
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.yaml
file with the following content:
You can now create the metrics by issuing the following command:
Finally, create a file named resourcestresser.yaml
with the following definition of the component:
You can now create the metrics by issuing the following command:
Here’s the definition of our system (system.yaml
):
Here’s the definition of our mysql
component (mysql.yaml
):
Here’s the definition of our OLTP
component (oltp.yaml
):
We can create the system by running:
We can then create the components by running:
A workflow for optimizing MySQL can be structured into 6 tasks:
Reset OLTPBench data
Configure MySQL
Restart MySQL
Launch the benchmark
Parse the benchmark results
Here below you can find the scripts that codify these tasks.
This is the restart-mysql.sh
script:
This is the clean_bench.sh
script:
This is the run_test.sh
script:
This file parse_csv.sh
script:
Here is the complete Akamas workflow for this example (workflow.yaml
):
You can create the workflow by running:
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:
To start using the provider, we need to define a telemetry instance (csv.yaml
):
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:
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
):
You can create the study by running:
You can then start it by running:
You can now follow the study progress using the UI and explore the results using the Analysis and Metrics tabs.