Akamas Docs
3.1.2
3.1.2
  • How to use this documentation
  • Getting started with Akamas
    • Introduction to Akamas
    • Licensing
    • Deployment
      • Cloud Hosting
    • Security
    • Maintenance & Support (M&S) Services
      • Customer Support Services
      • Support levels for Customer Support Services
      • Support levels for software versions
      • Support levels with Akamas 3.1
  • Installing Akamas
    • Akamas Architecture
    • Prerequisites
      • Hardware Requirements
      • Software Requirements
      • Network requirements
    • Install Akamas dependencies
    • Install the Akamas Server
      • Online installation mode
        • Online installation behind a Proxy server
      • Offline installation mode
      • Changing UI Ports
      • Setup HTTPS configuration
    • Install the Akamas CLI
      • Setup the Akamas CLI
      • Verify the Akamas CLI
      • Initialize Akamas CLI
      • Change CLI configuration
    • Verify the Akamas Server
    • Install the Akamas license
    • Manage anonymous data collection
    • Install an Akamas Workstation
    • Troubleshoot install issues
    • Manage the Akamas Server
      • Akamas logs
      • Audit logs
      • Install upgrades and patches
      • Monitor the Akamas Server
      • Backup & Recover of the Akamas Server
  • Using Akamas
    • General optimization process and methodology
    • Preparing optimization studies
      • Modeling systems
      • Modeling components
        • Creating custom optimization packs
        • Managing optimization packs
      • Creating telemetry instances
      • Creating automation workflows
        • Creating workflows for offline studies
        • Performing load testing to support optimization activities
        • Creating workflows for live optimizations
      • Creating optimization studies
        • Defining optimization goal & constraints
        • Defining windowing policies
        • Defining KPIs
        • Defining parameters & metrics
        • Defining workloads
        • Defining optimization steps
        • Setting safety policies
    • Running optimization studies
      • Before running optimization studies
      • Analyzing results of offline optimization studies
        • Optimization Insights
      • Analyzing results of live optimization studies
      • Before applying optimization results
    • Guidelines for choosing optimization parameters
      • Guidelines for JVM (OpenJ9)
      • Guidelines for JVM layer (OpenJDK)
      • Guidelines for Oracle Database
      • Guidelines for PostgreSQL
    • Guidelines for defining optimization studies
      • Optimizing Linux
      • Optimizing Java OpenJDK
      • Optimizing OpenJ9
      • Optimizing Web Applications
      • Optimizing Kubernetes
      • Optimizing Spark
      • Optimizing Oracle Database
      • Optimizing MongoDB
      • Optimizing MySQL Database
      • Optimizing PostgreSQL
  • Integrating Akamas
    • Integrating Telemetry Providers
      • CSV provider
        • Install CSV provider
        • Create CSV provider instances
      • Dynatrace provider
        • Install Dynatrace provider
        • Create Dynatrace provider instances
      • Prometheus provider
        • Install Prometheus provider
        • Create Prometheus provider instances
        • CloudWatch Exporter
        • OracleDB Exporter
      • Spark History Server provider
        • Install Spark History Server provider
        • Create Spark History Server provider instances
      • NeoLoadWeb provider
        • Setup NeoLoadWeb telemetry provider
        • Create NeoLoadWeb provider instances
      • LoadRunner Professional provider
        • Install LoadRunner Professional provider
        • Create LoadRunner Professional provider instances
      • LoadRunner Enterprise provider
        • Install LoadRunner Enterprise provider
        • Create LoadRunner Enterprise provider instances
      • AWS provider
        • Install AWS provider
        • Create AWS provider instances
    • Integrating Configuration Management
    • Integrating Value Stream Delivery
    • Integrating Load Testing
      • Integrating NeoLoad
      • Integrating Load Runner Professional
      • Integrating LoadRunner Enterprise
  • Akamas Reference
    • Glossary
      • System
      • Component
      • Metric
      • Parameter
      • Component Type
      • Workflow
      • Telemetry Provider
      • Telemetry Instance
      • Optimization Pack
      • Goals & Constraints
      • KPI
      • Optimization Study
      • Offline Optimization Study
      • Live Optimization Study
      • Workspace
    • Construct templates
      • System template
      • Component template
      • Parameter template
      • Metric template
      • Component Types template
      • Telemetry Provider template
      • Telemetry Instance template
      • Workflows template
      • Study template
        • Goal & Constraints
        • Windowing policy
          • Trim windowing
          • Stability windowing
        • Parameter selection
        • Metric selection
        • Workload selection
        • KPIs
        • Steps
          • Baseline step
          • Bootstrap step
          • Preset step
          • Optimize step
        • Parameter rendering
    • Workflow Operators
      • General operator arguments
      • Executor Operator
      • FileConfigurator Operator
      • LinuxConfigurator Operator
      • WindowsExecutor Operator
      • WindowsFileConfigurator Operator
      • Sleep Operator
      • OracleExecutor Operator
      • OracleConfigurator Operator
      • SparkSSHSubmit Operator
      • SparkSubmit Operator
      • SparkLivy Operator
      • NeoLoadWeb Operator
      • LoadRunner Operator
      • LoadRunnerEnteprise Operator
    • Telemetry metric mapping
      • Dynatrace metrics mapping
      • Prometheus metrics mapping
      • NeoLoadWeb metrics mapping
      • Spark History Server metrics mapping
      • LoadRunner metrics mapping
    • Optimization Packs
      • Linux optimization pack
        • Amazon Linux
        • Amazon Linux 2
        • Amazon Linux 2022
        • CentOS 7
        • CentOS 8
        • RHEL 7
        • RHEL 8
        • Ubuntu 16.04
        • Ubuntu 18.04
        • Ubuntu 20.04
      • DotNet optimization pack
        • DotNet Core 3.1
      • Java-OpenJDK optimization pack
        • Java OpenJDK 8
        • Java OpenJDK 11
      • OpenJ9 optimization pack
        • IBM J9 VM 6
        • IBM J9 VM 8
        • Eclipse Open J9 11
      • NodeJS optimization pack
        • NodeJS
      • GO optimization pack
        • GO 1
      • Web Application optimization pack
        • Web Application
      • Docker optimization pack
        • Container
      • Kubernetes optimization pack
        • Kubernetes Pod
        • Kubernetes Container
        • Kubernetes Workload
        • Kubernetes Namespace
        • Kubernetes Cluster
      • WebSphere optimization pack
        • WebSphere 8.5
        • WebSphere Liberty ND
      • AWS optimization pack
        • EC2
        • Lambda
      • PostgreSQL optimization pack
        • PostgreSQL 11
        • PostgreSQL 12
      • Cassandra optimization pack
        • Cassandra
      • MySQL Database optimization pack
        • MySQL 8.0
      • Oracle Database optimization pack
        • Oracle Database 12c
        • Oracle Database 18c
        • Oracle Database 19c
        • RDS Oracle Database 11g
        • RDS Oracle Database 12c
      • MongoDB optimization pack
        • MongoDB 4
        • MongoDB 5
      • Elasticsearch optimization pack
        • Elasticsearch 6
      • Spark optimization pack
        • Spark Application 2.2.0
        • Spark Application 2.3.0
        • Spark Application 2.4.0
    • Command Line commands
      • Administration commands
      • User and Workspace management commands
      • Authentication commands
      • Resource management commands
      • Optimizer options commands
  • Knowledge Base
    • Setting up a Konakart environment for testing Akamas
    • Modeling a sample Java-based e-commerce application (Konakart)
    • Optimizing a web application
    • Optimizing a sample Java OpenJ9 application
    • Optimizing a sample Java OpenJDK application
    • Optimizing a sample Linux system
    • Optimizing a MongoDB server instance
    • Optimizing a Kubernetes application
    • Leveraging Ansible to automate AWS instance management
    • Guidelines for optimizing AWS EC2 instances
    • Optimizing a sample application running on AWS
    • Optimizing a Spark application
    • Optimizing an Oracle Database server instance
    • Optimizing an Oracle Database for an e-commerce service
    • Guidelines for optimizing Oracle RDS
    • Optimizing a MySQL server database running Sysbench
    • Optimizing a MySQL server database running OLTPBench
    • Optimizing a live K8s deployment
    • Optimizing a live full-stack deployment (K8s + JVM)
  • Akamas Free Trial
Powered by GitBook
On this page
  • Workflows
  • A typical workflow
  • Telemetry providers
  • Examples

Was this helpful?

Export as PDF
  1. Using Akamas
  2. Guidelines for defining optimization studies

Optimizing MySQL Database

Last updated 1 year ago

Was this helpful?

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

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

    2. Optionally, use the 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 to perform a performance test against the application.

  4. Prepare test results

    1. Use any of the to organize test results so that they can be imported into Akamas using the supported (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

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

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

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

MySQL optimization pack
FileConfigurator operator
FileConfigurator operator
Executor operator
Executor operator
workflow operators
workflow operators
telemetry providers
Prometheus provider.
MySql Prometheus exporter
page
page