Automate Database Configuration Check

Many system administrators commonly overlook the importance of ongoing database configuration tuning. Configuration options are often being configured or tuned once, during the installation stage, and being left out until some unwanted events occur to the database service. Only then, one would put more attention to re-visit the configuration options and tune up the limits, thresholds, buffers, caches, etc, in the urge to restore the database service again.

Our focus in this blog post is to automate the database configuration check and validation process. This is an important process because configuration options are always changing across major versions. An unchanged config file could potentially have deprecated options that are no longer supported by the newer server version, which commonly causes some major issues to the upgraded server.

Configuration Management Tools

Puppet, Ansible, Chef and SaltStack are most commonly used by DevOps for configuration management and automation. Configuration management allows users to document the environment, improve efficiency, manageability and reproducibility, and an integral part of continuous integration and deployment. Most of the configuration management tools provide a catalog of modules and repositories for others to contribute, simplifying the learning curve for the community user to adapt to the technology.

Although configuration management tools are mostly used to automate deployment and installation, we can also perform configuration checks and enforcement in a centralized push-out approach. Each of these tools has its own way of templating a configuration file. As for Puppet, the template file commonly suffixed with “.erb” and inside it, we can define the configuration options together with pre-formulated values. 

The following example shows a template file for MySQL configuration:

[mysqld]
thread_concurrency = <%= processorcount.to_i * 2 %>
# Replication
log-bin            = /var/lib/mysql/mysql-bin.log
log-bin-index      = /var/lib/mysql/mysql-bin.index
binlog_format      = mixed
server-id         = <%= @mysql_server_id or 1 %>

# InnoDB
innodb_buffer_pool_size = <%= (memorysizeinbytes.to_i / 2 / 1024 / 1024).to_i -%>M
innodb_log_file_size    = <%= ((memorysizeinbytes.to_i / 2 / 1024 / 1024) * 0.25).to_i -%>M

As shown above, the configuration value can be a fixed value or dynamically calculated. Therefore, the end result can be different according to the target host’s hardware specification with other predefined variables. In the Puppet definition file, we can push our configuration template like this:

# Apply our custom template
file { '/etc/mysql/conf.d/my-custom-config.cnf':
  ensure  => file,
  content => template('mysql/my-custom-config.cnf.erb')
}

Other than templating, we can also push the configuration values directly from the definition file. The following is an example of Puppet definition for MariaDB 10.5 configuration using Puppet MySQL module:

# MariaDB configuration
class {'::mysql::server':
  package_name     => 'mariadb-server',
  service_name     => 'mariadb',
  root_password    => 't5[sb^D[+rt8bBYu',
  manage_config_file => true,
  override_options => {
    mysqld => {
      'bind_address' => '127.0.0.1',
      'max_connections' => '500',
      'log_error' => '/var/log/mysql/mariadb.log',
      'pid_file'  => '/var/run/mysqld/mysqld.pid',
    },
    mysqld_safe => {
      'log_error' => '/var/log/mysql/mariadb.log',
    },
  }
}

The above example shows that we used manage_config_file => true with override_options to structure our configuration lines which later will be pushed out by Puppet. Any modification to the manifest file will only reflect the content of the target MySQL configuration file. This module will neither load the configuration into runtime nor restart the MySQL service after pushing the changes into the configuration file. It’s the SysAdmin’s responsibility to restart the service to activate the changes.

For Puppet and Chef, check the output of the agent log to see if the configuration options are corrected. For Ansible, simply look at the debugging output to see if the congratulations are successfully updated. Using configuration management tools can help you automating configuration checks and enforce a centralized configuration approach.

MySQL Shell

A sanity check is important before performing any upgrade. MySQL Shell has a very cool feature that is intended to run a series of tests to verify if your existing installation is safe to upgrade to MySQL 8.0, called Upgrade Checker Utility. You can save a huge amount of time when preparing for an upgrade. A major upgrade, especially to MySQL 8.0, introduces and deprecates many configuration options and therefore has a big risk for incompatibility after the upgrade. 

This tool is specifically designed for MySQL (Percona Server included), especially when you want to perform a major upgrade from MySQL 5.7 to MySQL 8.0. To invoke this utility, connect with MySQL Shell, and as root user, specify the credentials, target version and the configuration file:

$ mysqlsh
mysql> util.checkForServerUpgrade('root@localhost:3306', {"password":"p4ssw0rd", "targetVersion":"8.0.11", "configPath":"/etc/my.cnf"})

At the bottom of the report, you will get the key summary:

Errors:   7
Warnings: 36
Notices:  0

7 errors were found. Please correct these issues before upgrading to avoid compatibility issues.

Focus on fixing up all the errors first, because this is going to be causing major problems after the upgrade if no action is taken. Take a look back at the generated report and find all issues with “Error:” wording inline, for example:

15) Removed system variables

  Error: Following system variables that were detected as being used will be
    removed. Please update your system to not rely on them before the upgrade.
  More information: https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed

  log_builtin_as_identified_by_password - is set and will be removed
  show_compatibility_56 - is set and will be removed

Once all the errors are fixed, try to reduce the warnings whichever possible. The warnings mostly will not affect the reliability of the MySQL server, but can potentially degrade the performance or changed behavior than what they used to. For example, take a look at the following warnings:

13) System variables with new default values

  Warning: Following system variables that are not defined in your
    configuration file will have new default values. Please review if you rely on
    their current values and if so define them before performing upgrade.
  More information:
    https://mysqlserverteam.com/new-defaults-in-mysql-8-0/

  back_log - default value will change
  character_set_server - default value will change from latin1 to utf8mb4
  collation_server - default value will change from latin1_swedish_ci to
    utf8mb4_0900_ai_ci
  event_scheduler - default value will change from OFF to ON
  explicit_defaults_for_timestamp - default value will change from OFF to ON
  innodb_autoinc_lock_mode - default value will change from 1 (consecutive) to
    2 (interleaved)
  innodb_flush_method - default value will change from NULL to fsync (Unix),
    unbuffered (Windows)
  innodb_flush_neighbors - default value will change from 1 (enable) to 0
    (disable)
  innodb_max_dirty_pages_pct - default value will change from 75 (%)  90 (%)
  innodb_max_dirty_pages_pct_lwm - default value will change from_0 (%) to 10
    (%)
  innodb_undo_log_truncate - default value will change from OFF to ON
  innodb_undo_tablespaces - default value will change from 0 to 2
  log_error_verbosity - default value will change from 3 (Notes) to 2 (Warning)
  max_allowed_packet - default value will change from 4194304 (4MB) to 67108864
    (64MB)
  max_error_count - default value will change from 64 to 1024
  optimizer_trace_max_mem_size - default value will change from 16KB to 1MB
  performance_schema_consumer_events_transactions_current - default value will
    change from OFF to ON
  performance_schema_consumer_events_transactions_history - default value will
    change from OFF to ON
  slave_rows_search_algorithms - default value will change from 'INDEX_SCAN,
    TABLE_SCAN' to 'INDEX_SCAN, HASH_SCAN'
  table_open_cache - default value will change from 2000 to 4000
  transaction_write_set_extraction - default value will change from OFF to
    XXHASH64

Upgrade Checker Utility provides a critical overview of what to expect and averts us from a huge surprise after the upgrade. 

ClusterControl Advisors

ClusterControl has a number of internal mini-program called Advisors, where you write a small program that lives and runs within the structure of the ClusterControl objects. You can think of it as a scheduled function that executes a script created in Developer Studio and produces a result containing status, advice and justification. This allows users to easily extend the functionality of ClusterControl by creating custom advisors that can run on-demand or on a schedule.

The following screenshot shows an example of InnoDB Advisors called innodb_log_file_size check, after being activated and scheduled inside ClusterControl:

The above result can be found under ClusterControl -> Performance -> Advisors. For every Advisor, it shows the status of the advisor, database instance, justification and advice. There is also information about the schedule and the last execution time. The advisor can also be executed on-demand by clicking on the “Compile and Run” button under the Developer Studio.

The above advisors containing the following code, written using ClusterControl Domain-Specific Language (DSL) which is pretty similar to JavaScript:

#include "common/mysql_helper.js"
#include "cmon/graph.h"

var DESCRIPTION="This advisor calculates the InnoDB log growth per hour and"
" compares it with the innodb_log_file_size configured on the host and"
" notifies you if the InnoDB log growth is higher than what is configured, which is important to avoid IO spikes during flushing.";
var TITLE="Innodb_log_file_size check";
var MINUTES = 20;


function main()
{
    var hosts     = cluster::mySqlNodes();
    var advisorMap = {};
    for (idx = 0; idx < hosts.size(); ++idx)
    {
        host        = hosts[idx];
        map         = host.toMap();
        connected     = map["connected"];
        var advice = new CmonAdvice();
        print("   ");
        print(host);
        print("==========================");
        if (!connected)
        {
            print("Not connected");
            continue;
        }
        if (checkPrecond(host))
        {
            var configured_logfile_sz = host.sqlSystemVariable("innodb_log_file_size");
            var configured_logfile_grps = host.sqlSystemVariable("innodb_log_files_in_group");
            if (configured_logfile_sz.isError() || configured_logfile_grps.isError())
            {
                justification = "";
                msg = "Not enough data to calculate";
                advice.setTitle(TITLE);
                advice.setJustification("");
                advice.setAdvice(msg);
                advice.setHost(host);
                advice.setSeverity(Ok);
                advisorMap[idx]= advice;
                continue;
            }
            var endTime   = CmonDateTime::currentDateTime();
            var startTime = endTime - MINUTES * 60 /*seconds*/;
            var stats     = host.sqlStats(startTime, endTime);
            var array     = stats.toArray("created,interval,INNODB_LSN_CURRENT");

            if(array[2,0] === #N/A  || array[2,0] == "")
            {
                /* Not all vendors have INNODB_LSN_CURRENT*/
                advice.setTitle(TITLE);
                advice.setJustification("INNODB_LSN_CURRENT does not exists in"
                                        " this MySQL release.");
                advice.setAdvice("Nothing to do.");
                advice.setHost(host);
                advice.setSeverity(Ok);
                advisorMap[idx]= advice;
                continue;
            }
            var firstLSN = array[2,0].toULongLong();
            var latestLSN = array[2,array.columns()-1].toULongLong();
            var intervalSecs = endTime.toULongLong() - startTime.toULongLong();
            var logGrowthPerHourMB = ceiling((latestLSN - firstLSN) * 3600 / 1024/1024 / intervalSecs / configured_logfile_grps);
            var logConfiguredMB =  configured_logfile_sz/1024/1024;
            if (logGrowthPerHourMB > logConfiguredMB)
            {
                justification = "Innodb is producing " + logGrowthPerHourMB + "MB/hour, and it greater than"
                " the configured innodb log file size " + logConfiguredMB + "MB."
                " You should set innodb_log_file_size to a value greater than " +
                    logGrowthPerHourMB + "MB. To change"
                " it you must stop the MySQL Server and remove the existing ib_logfileX,"
                " and start the server again. Check the MySQL reference manual for max/min values. "
                "https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_log_file_size";
                msg = "You are recommended to increase the innodb_log_file_size to avoid i/o spikes"
                " during flushing.";
                advice.setSeverity(Warning);
            }
            else
            {
                justification = "Innodb_log_file_size is set to " + logConfiguredMB +
                    "MB and is greater than the log produced per hour: " +
                    logGrowthPerHourMB + "MB.";
                msg = "Innodb_log_file_size is sized sufficiently.";
                advice.setSeverity(Ok);
            }
        }
        else
        {
            justification = "Server uptime and load is too low.";
            msg = "Not enough data to calculate";
            advice.setSeverity(0);
        }
        advice.setHost(host);
        advice.setTitle(TITLE);
        advice.setJustification(justification);
        advice.setAdvice(msg);
        advisorMap[idx]= advice;
        print(advice.toString("%E"));
    }
    return advisorMap;
}

ClusterControl provides an out-of-the-box integrated development environment (IDE) called Developer Studio (accessible under Manage -> Developer Studio) to write, compile, save, debug and schedule the Advisor:

With Developer Studio and Advisors, users have no limit in extending ClusterControl’s monitoring and management functionalities. It is literally the perfect tool to automate the configuration check for all your open-source database software like MySQL, MariaDB, PostgreSQL and MongoDB, as well as the load balancers like HAProxy, ProxySQL, MaxScale and PgBouncer. You may even write an Advisor to make use of the MySQL Shell Upgrade Checker Utility, as shown in the previous chapter.

Final Thoughts

Configuration check and tuning are important parts of the DBA and SysAdmin routine to ensure critical systems like database and reverse proxies are always relevant and optimal as your workloads grow.