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 FileConfigurator operator 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 FileConfigurator operator 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

   1. Use the Executor operator to restart MySQL allowing it to load the new configuration file produced in the previous step.

   2. Optionally, use the Executor operator to verify that the application is up and running and has finished any initialization logic.

3. Test the performance of the application

   1. Use any of the workflow operators to perform a performance test against the application.

4. Prepare test results

   1. Use any of the workflow operators to organize test results so that they can be imported into Akamas using the supported telemetry providers (see also section here below).

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

name: OptimizeMySQL
tasks:

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

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

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

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

Telemetry providers

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

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

provider: prometheus
config:
  address: mysql.mydomain.com
  port: 9090
  job: mysql_exporter

Examples

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