Optimizing MySQL Database

When optimizing a MySQL instance, typically the goal is one of the following:

• Throughput optimization: increasing the capacity of a MySQL deployment to serve clients

• Cost optimization: decreasing the size of a MySQL deployment while guaranteeing the same service level

Please refer to the MySQL optimization pack for the list of component types, parameters, metrics, and constraints.

Workflows

Applying parameters

Usually, MySQL parameters are configured by writing them in the MySQL configuration file, typically called my.cnf, and located under /etc/mysql/ on most Linux systems.

In order to preserve the original config file intact, it is best practice to use additional configuration files, located in /etc/mysql/conf.d to override the default parameters. These files are automatically read by MySQL.

FileConfigurator and Executor operator

You can leverage the by creating a template file on a remote host that contains some scripts to configure MySQL with placeholders that will be replaced with the values of parameters tuned by Akamas. When all the placeholders in FileConfigurator get replaced, the operator can be used to actually execute the script to configure and restart the database

A typical workflow

A typical workflow to optimize a MySQL deployment can be structured in three parts:

1. Configure MySQL

   1. Use the to specify an input and an output template file. The input template file is used to specify how to interpolate MySQL parameters into a configuration file, and the output file is used to contain the result of the interpolation.

2. Restart MySQL

Finally, when running performance experiments on databases is common practice to do some cleanup tasks at the end of the test to restore the database's initial condition to avoid impacting subsequent tests.

Here’s an example of a typical workflow for MySQL, which uses the OLTP Resourcestresser benchmark to run performance tests

Telemetry providers

Akamas can access MySQL metrics using the This provider can be leveraged to query MySQL metrics collected by a Prometheus instance via the .

Here’s an example of a telemetry providers instance that uses Prometheus to extract all the MySQL metrics defined in this optimization pack:

Examples

This and this describe an example of how to leverage the MySQL optimization pack.