Resources

Get Started

Product Tour

Dec 18, 2023

Automate OtterTune Database Knob Configurations for Terraform with GitHub Actions

Priyank Bhandia

OtterTune uses AI to optimize Amazon (AWS) RDS PostgreSQL and MySQL databases. OtterTune also provides fleet-level health checks and monitoring to ease database administration. It can tune databases automatically by directly updating database parameter groups with optimized knob values.

For users who use Terraform to manage their AWS resources, making direct updates to parameter groups is no bueno. OtterTune’s automatic parameter group changes are external to the Terraform state, so when tf apply is run, Terraform will overwrite them to restore the state. To solve this problem, we show how automatic knob tuning can be achieved with Terraform by using OtterTune’s API in a GitHub Actions workflow.

We will first fetch recommended knob values from Ottertune’s API. Then we will update the database parameter group’s Terraform resource. Changes from the updated resource will be applied through Terraform and committed to the repository so they do not get overwritten. All these steps will be scheduled to run automatically to apply optimized values periodically.

The full code we will be walking through in this blog can be found on Github.

Prerequisites

An OtterTune account

After signing up, you will be walked through setting up OtterTune’s agent to collect your database metrics and AI-optimized recommendations become available typically on the second day after setup. New recommendations are generated every 24 hours. For this tutorial, you should note yourAPI Key.

Familiarity with Terraform

Terraform is an infrastructure as code tool that lets you build, change, and version infrastructure safely and efficiently. If you do not use Terraform to manage your AWS resources, you can listen to some otter tunes and let OtterTune tune your databases automatically.

If you are new to using Terraform, you should work through their excellent tutorials. We recommend setting Terraform up locally for testing.

A quick refresher of some commands we will use in this tutorial:

tf init: The terraform init command initializes a working directory containing Terraform configuration files. This is the first command that should be run after writing a new Terraform configuration or cloning an existing one from version control. It is safe to run this command multiple times.

tf apply: The terraform apply command executes the actions proposed in a Terraform plan.

tf import: The terraform import command imports existing resources into Terraform.

We recommend having AWS Command Line Interface (CLI)set up locally for testing.

Let’s look at the Terraform configurations and features of the OtterTune API we will use before building the automatic update script.

Terraform configurations

For this tutorial, we assume that you have the database resource defined in main.tf and its associated parameter group defined in db_params.tf, as shown below.

If you have Terraform and AWS CLI set up correctly in your local system, running tf init followed by tf apply will create a “terraform-blog-example” database with “terraform_blog_example” DB parameter group hosted by AWS.

main.tf:

# Note: This file is for tutorial purposes. Adjust the options for production use cases, especially for security and reliability.

# Provider information
provider "aws" {
  region = "us-east-2" # replace with your desired region
}


# Set environment variable `TF_VAR_db_password="your_secure_password"`
variable "db_password" {}

import {
  to = aws_db_instance.terraform_blog_example
  id = "terraform-blog-example"
}

# AWS RDS PostgreSQL instance creation using Terraform
resource "aws_db_instance" "terraform_blog_example" {

  # Instance specifications

  identifier                   = "terraform-blog-example"                           # The unique identifier for the RDS instance
  engine                       = "postgres"                                         # The database engine
  engine_version               = "15"                                               # The version of the database engine
  instance_class               = "db.t3.micro"                                      # Determines the machine type used for the DB instance
  allocated_storage            = "10"                                               # The size of the storage (in GB) allocated to the DB instance
  username                     = "postgres"                                         # Master username for the DB instance
  password                     = var.db_password                                    # Master password for the DB instance
  parameter_group_name         = aws_db_parameter_group.terraform_blog_example.name # The DB parameter group to associate with the DB instance
  performance_insights_enabled = true                                               # Enables RDS Performance Insights for the DB instance

  # Tutorial specific settings (not recommended in production environments)

  publicly_accessible     = true  # Whether the DB instance is accessible from the internet
  backup_retention_period = "0"   # The number of days to retain backups ('0' disables backups)
  skip_final_snapshot     = true  # Whether to skip creation of final snapshot before the DB instance is deleted
  multi_az                = false # Whether to replicate the DB instance across multiple AZs for high availability

  # Optional settings

  # Uncomment and set these if you are using a custom VPC or need specific network settings
  # db_subnet_group_name = "your_db_subnet_group" # The DB subnet group name to associate with this instance
  # vpc_security_group_ids = ["sg-xxxxxxxx"]      # List of VPC security group IDs to associate with this instance

  # Add additional settings here as necessary

db_params.tf:

# AWS RDS PostgreSQL Parameter Group Configuration

import {
  to = aws_db_parameter_group.terraform_blog_example
  id = "terraform-blog-example-pg"
}

resource "aws_db_parameter_group" "terraform_blog_example" {
  name   = "terraform-blog-example-pg" # Name of the DB parameter group
  family = "postgres15"                # Database family (PostgreSQL 15.x in this case)

  # Below are OtterTune's recommended knobs for tuning RDS PostgreSQL 15 performance.
  # They are initially set to their default values when the RDS instance is created.
  # After completing the steps in the blog article, these DB parameter group settings
  # will be updated daily with OtterTune's latest knob recommendations.

  parameter {
    name  = "autovacuum_vacuum_cost_delay"
    value = "2"
  }
  parameter {
    name  = "autovacuum_vacuum_scale_factor"
    value = "0.1"
  }
  parameter {
    name  = "autovacuum_vacuum_threshold"
    value = "50"
  }
  parameter {
    name  = "bgwriter_delay"
    value = "200"
  }
  parameter {
    name  = "bgwriter_lru_maxpages"
    value = "100"
  }
  parameter {
    name  = "bgwriter_lru_multiplier"
    value = "2"
  }
  parameter {
    name  = "checkpoint_completion_target"
    value = "0.9"
  }
  parameter {
    name  = "default_statistics_target"
    value = "100"
  }
  parameter {
    name  = "effective_cache_size"
    value = "47626"
  }
  parameter {
    name  = "effective_io_concurrency"
    value = "1"
  }
  parameter {
    name  = "max_wal_size"
    value = "2048"
  }
  parameter {
    name  = "random_page_cost"
    value = "4"
  }
  parameter {
    name  = "temp_buffers"
    value = "1024"
  }
  parameter {
    name  = "wal_writer_delay"
    value = "200"
  }
  parameter {
    name  = "work_mem"
    value = "4096"

The OtterTune API

This section shows what some of the responses of the OtterTune API look like – feel free to skim through if you are already familiar with what OtterTune does. Available endpoints are listed in this Postman collection.

The Get all databases endpoint lists all your databases connected to OtterTune. It also shows the id assigned by OtterTune to your database.

curl --location 'https://service.ottertune.com/api/databases' \
--header 'Accept: application/json' \
--header 'OT-API-KEY: d47dcad9-3bdf-sample-key'

{
    "count": 1,
    "next": null,
    "previous": null,
    "results": [
        {
            "id": 9208,
            "dbIdentifier": "example-backend-test-postgres-15",
            "score": 91.75,
            "dbSystem": "PostgreSQL",
            "region": "us-east-2",
            "dbSystemVersion": "15",
            "isAgentInitialized": true,
            "isAgentConnected": true,
            "isEnabled": true,
            "updatedTime": "2023-12-14T03:00:16.471439Z",
            "isAuroraCluster": false,
            "createdTime": "2023-12-14T02:50:42.524505Z",
            "awsAccountId": "407946696963",
            "dbParameterGroup": "example-backend-test-postgres-15",
            "dbClusterParameterGroup": null,
            "dbClusterIdentifier": null,
            "hardware": {
                "instanceType": "db.t3.large",
                "cpu": 2,
                "memoryMb": 8192.0,
                "minCapacity": null,
                "maxCapacity": null,
                "storageType": "gp2",
                "storageIops": null,
                "storageSizeGb": 30
            },
            "tuningLevel": "instance",
            "tuningMode": "Manual Review",
            "dbClusterRole": null,
            "numCreatedRecommendations": 5
        },
    ]
}

We can use this id to lookup available recommendations in the Get all recommendations endpoint:

curl --location 'https://service.ottertune.com/api/databases/9208/recommendations' \
--header 'Accept: application/json' \
--header 'OT-API-KEY: d47dcad9-3bdf-sample-key'

{
    "count": 23,
    "next": null,
    "previous": null,
    "results": [
        {
            "id": 2058015,
            "name": "Set autovacuum_vacuum_cost_delay",
            "description": "Machine learning recommended value. It's the vacuum cost delay in milliseconds, for autovacuum.",
            "createdTime": "2023-12-14T03:01:31.346525Z",
            "knobInitialValue": 2,
            "knobFinalValue": 11,
            "action": "aws rds modify-db-parameter-group --db-parameter-group-name example-backend-test-postgres-15 --parameters \"ParameterName='autovacuum_vacuum_cost_delay',ParameterValue=11,ApplyMethod=immediate\";",
            "type": "knob",
            "applyUpdatedTime": null,
            "applyRequestTime": null,
            "status": "created",
            "databaseId": 9208,
            "dbIdentifier": "example-backend-test-postgres-15",
            "region": "us-east-2",
            "dbSystem": "PostgreSQL",
            "dbSystemVersion": "15",
            "awsAccountId": "407946696963",
            "knobName": "autovacuum_vacuum_cost_delay",
            "knobType": "integer",
            "knobUnit": "ms"
        },
        {
            "id": 2058016,
            "name": "Set autovacuum_vacuum_cost_limit",
            "description": "Machine learning recommended value. It's the vacuum cost amount available before napping, for autovacuum.",
            "createdTime": "2023-12-14T03:01:31.409932Z",
            "knobInitialValue": 200,
            "knobFinalValue": 279,
            "action": "aws rds modify-db-parameter-group --db-parameter-group-name example-backend-test-postgres-15 --parameters \"ParameterName='autovacuum_vacuum_cost_limit',ParameterValue=279,ApplyMethod=immediate\";",
            "type": "knob",
            "applyUpdatedTime": null,
            "applyRequestTime": null,
            "status": "created",
            "databaseId": 9208,
            "dbIdentifier": "example-backend-test-postgres-15",
            "region": "us-east-2",
            "dbSystem": "PostgreSQL",
            "dbSystemVersion": "15",
            "awsAccountId": "407946696963",
            "knobName": "autovacuum_vacuum_cost_limit",
            "knobType": "integer",
            "knobUnit": null
        },
        ...
    ]
}

These results can be further filtered to return only created knob recommendations, by adding ?type=knob&status=created filters.

curl --location 'https://service.ottertune.com/api/databases/9208/recommendations?type=knob&status=created' \
--header 'Accept: application/json' \
--header 'OT-API-KEY: d47dcad9-3bdf-sample-key'

{
    "count": 18,
    "next": null,
    "previous": null,
    "results": [
        {
            "id": 2058015,
            "name": "Set autovacuum_vacuum_cost_delay",
            "description": "Machine learning recommended value. It's the vacuum cost delay in milliseconds, for autovacuum.",
            "createdTime": "2023-12-14T03:01:31.346525Z",
            "knobInitialValue": 2,
            "knobFinalValue": 11,
            "action": "aws rds modify-db-parameter-group --db-parameter-group-name example-backend-test-postgres-15 --parameters \"ParameterName='autovacuum_vacuum_cost_delay',ParameterValue=11,ApplyMethod=immediate\";",
            "type": "knob",
            "applyUpdatedTime": null,
            "applyRequestTime": null,
            "status": "created",
            "databaseId": 9208,
            "dbIdentifier": "example-backend-test-postgres-15",
            "region": "us-east-2",
            "dbSystem": "PostgreSQL",
            "dbSystemVersion": "15",
            "awsAccountId": "407946696963",
            "knobName": "autovacuum_vacuum_cost_delay",
            "knobType": "integer",
            "knobUnit": "ms"
        },
      ...
    ]
}

This response contains the names of the parameters that OtterTune wants you to change, as well as the values that they need to be changed to (see the knobName and knobFinalValue fields).

Now let’s write a script that uses these endpoints to edit our Terraform configuration!

Putting it together

The following script calls the OtterTune API and extracts the AI-recommended parameter values. It then modifies the Terraform configuration file with new parameter values.

update_parameters.py:

import requests
import json
import os
import re
import sys


# Your OtterTune API key
ottertune_api_key = os.environ['OT_API_KEY']

# Your database identifier
db_identifier = os.environ['DB_IDENTIFIER']

# Base URL for the OtterTune API
base_url = 'https://service.ottertune.com/api'

# Make a call to retrieve the database ID
databases_url = f'{base_url}/databases'
headers = {'OT-API-KEY': ottertune_api_key}

# Replace with your config file name
terraform_file_path = 'db_params.tf'

# Regex patterns to match parameter 'name' and 'value' lines
name_pattern = re.compile(r'name\s*=\s*"?([^"\n]+)"?')
value_pattern = re.compile(r'value\s*=\s*"?([^"\n]+)"?')

try:
    response = requests.get(databases_url, headers=headers)
    response.raise_for_status()
    database_data = response.json()

    # Find the database id for the given dbIdentifier
    database_id = None
    for database in database_data['results']:
        if database['dbIdentifier'] == db_identifier:
            database_id = database['id']
            break

    if database_id is not None:
        recommendations_url = f'{base_url}/databases/{database_id}/recommendations'

        # Make a call to retrieve knob recommendations
        params = {'status': 'created', 'type': 'knob'}
        response = requests.get(recommendations_url, headers=headers, params=params)
        response.raise_for_status()
        recommendations_data = response.json()

        # Extract knob recommendations and store in a list of tuples
        knob_recommendations = {}
        for recommendation in recommendations_data['results']:
            knob_name = recommendation['knobName']
            knob_final_value = recommendation['knobFinalValue']
            knob_recommendations[knob_name] = knob_final_value

        print(f"KNOB_RECOMMENDATIONS = {json.dumps(knob_recommendations, indent=4, default=str)}\n")

        # We have some new knob values from OtterTune which we need to update
        # in our terraform file
        if len(knob_recommendations) > 0:
            with open(terraform_file_path, 'r') as file:
                terraform_config = file.read()

            config_lines = terraform_config.splitlines()
            new_config_lines = []

            # Iterate over the lines and update parameter values if needed
            i = 0
            while i < len(config_lines):
                line = config_lines[i]

                # Check if the line contains the parameter definition
                # parameter definitions look like:
                # parameter {
                #  name = "param_name"
                #  value = "param_value"
                # }
                # Note: This script will only update those parameters that are present
                # in the parameter configuration file, other recommendations will be ignored
                if line.strip().startswith("parameter {"):
                    # Extract the parameter name and value
                    param_name = None
                    param_value = None
                    while not line.strip().startswith("}"):
                        name_match = name_pattern.search(line)
                        if name_match:
                            param_name = name_match.group(1)

                        value_match = value_pattern.search(line)
                        if value_match:
                            param_value = value_match.group(1)

                        line = config_lines[i]
                        i += 1

                    # the parameter in the terraform configuration has a new recommended value
                    if param_name in knob_recommendations:
                        # Replace the parameter value with the new value
                        param_value = knob_recommendations[param_name]
                        new_config_lines.append(
                            f'  parameter {{\n    name  = "{param_name}"\n    value = "{param_value}"\n  }}')
                    else:
                        # Keep the original parameter value
                        new_config_lines.append(
                            f'  parameter {{\n    name  = "{param_name}"\n    value = "{param_value}"\n  }}')
                else:
                    # Add non-parameter lines as is
                    new_config_lines.append(line)
                    i += 1

            # Join the modified configuration lines
            modified_terraform_config = "\n".join(new_config_lines)

            # Write the modified Terraform configuration back to the file
            with open(terraform_file_path, 'w') as file:
                file.write(modified_terraform_config)

            print(f"Terraform configuration file ({terraform_file_path}) has been updated with knob recommendations.")
        else:
            print("No knob recommendations found for this database.")
    else:
        print(f'Error: Database with dbIdentifier "{db_identifier}" not found.')
        sys.exit(1)

except requests.exceptions.RequestException as e:
    print(f'Error making HTTP request: {e}')
    sys.exit(1)
except Exception as e:
    print(f'An error occurred: {e}')
    sys.exit(1)

Now, we can set up this script to run once a day using Github Actions. For that, you need to:

Create a repository access token. The workflow needs a token with permissions to write to the repository in order to push the changes to the configuration file. To do so, create an access token with “repo” scope, following the steps here. This will allow the workflow to commit OtterTune’s recommended changes to your repository.
Create Repository Secrets: In Github, go to your Repository → Settings → Secrets and variables → Actions. Select New repository secret and configure the following secrets:
1. REPO_ACCESS_TOKEN: Your repo access token created in step 1.
2. AWS_ACCESS_KEY_ID.
3. AWS_SECRET_ACCESS_KEY: Read more about AWS access key Id and secret access key here. These are needed to allow Terraform access to your AWS in the workflow.
4. OT_API_KEY: Your OtterTune API key. This will be used by the workflow to access OtterTune’s API and fetch knob recommendations.
5. TF_VAR_db_password: Your DB instance’s password. This is needed to allow Terraform access to your database in the workflow.
Create a .github/workflows directory in your repo if it doesn’t exist already.
Create a new YAML file, e.g., daily_update.yml, in the .github/workflows directory. This YAML file will define the workflow to run your script, commit changes, and apply Terraform. Here’s an example workflow configuration:

daily_update.yml:

name: Daily Parameter Update

on:
  schedule:
    - cron: '0 0 * * *' # Schedule the workflow to run daily at midnight UTC
  workflow_dispatch:

jobs:
  update_and_commit:
    runs-on: ubuntu-latest

    steps:
      - name: Checkout code
        uses: actions/checkout@v2

      - name: Set up Python
        uses: actions/setup-python@v2
        with:
          python-version: 3.x

      - name: Install Python Packages
        run: |
          pip install -r requirements.txt

      - name: Run Python script
        run: |
          python update_parameters.py
        env:
          DB_IDENTIFIER: "terraform-blog-example"
          OT_API_KEY: ${{ secrets.OT_API_KEY }}

      - name: Set up Terraform
        uses: hashicorp/setup-terraform@v1
        with:
          terraform_version: 1.6.5 # Replace with your desired Terraform version

      - name: Initialize Terraform
        run: terraform init

      - name: Apply Terraform changes
        # CAUTION: -auto-approve may override changes to the infra
        # that are not saved in the terraform files 
        run: terraform apply -auto-approve
        env:
          TF_VAR_db_password: ${{ secrets.DB_PASSWORD }}
          AWS_ACCESS_KEY_ID: ${{ secrets.AWS_ACCESS_KEY_ID }}
          AWS_SECRET_ACCESS_KEY: ${{ secrets.AWS_SECRET_ACCESS_KEY }}

      - name: Check if there are any changes
        id: verify_diff
        run: |
          git diff --quiet . || echo "changed=true" >> $GITHUB_OUTPUT

      - name: Commit changes
        if: steps.verify_diff.outputs.changed == 'true'
        run: |
          git status
          git config user.email "name@mail.com"  # Github email
          git config user.name "user"  # Github username
          git add db_params.tf
          git commit -m "AI knob updates"
          git push
        env:
          GITHUB_TOKEN: ${{ secrets.REPO_ACCESS_TOKEN }}  # GitHub access token

And there you go! Now, your database configuration will be updated daily based on OtterTune’s recommendations and you should see an improvement in your database performance, be it throughput or latency. Use your AI-driven database for fun and profit :)

Try OtterTune for free. Start using AI to optimize your PostgreSQL or MySQL databases running on Amazon RDS or Aurora.

Get Started

Latest blogs

Dec 18, 2023