Optimizing MySQL Configuration

on

Introduction

MySQL is one of the most used databases in conjunction with PHP. Making sure that your MySQL databases are running at it’s best is one of the most important jobs you have to consider whenever your web applications are growing bigger.

In this series of 3 standalone articles, we will have a look at how we can optimize our MySQL installation. We will take a look at which optimizations we can perform on our database, on our MySQL configuration and how we can find potential problems when MySQL is not performing well. We will be using mostly tools from the Percona Toolkit to work with our database. This article focuses on improving our configuration.

How to change the MySQL configuration

MySQL keeps its configuration stored in the my.cnf file. In general, you will find the config file at /etc/mysql/my.cnf.

When you change the configuration file, you will need to restart your MySQL server to reload the changes.

However, if you feel like you want to do the changes on runtime, you can use the SET GLOBAL and SET SESSION query. Do note that not all config variables are available to be set on runtime and the changes are not persistent. Please consult the following list to see if the variable can be changed: Dynamic system variables

Find config improvements

Percona Toolkit released a tool called pt-variable-advisor. With this tool, you can analyze your current MySQL configuration and receive feedback. pt-variable-advisor will check your config, based on a given set of rules, defined by Percona. To see the complete list of rules, you can consult their help section.

Let’s run the tool on a clean MySQL installation and see what kind of feedback we receive.

# WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# WARN innodb_log_file_size: The InnoDB log file size is set to its default value, which is not usable on production systems.

# NOTE log_warnings-2: Log_warnings must be set greater than 1 to log unusual events such as aborted connections.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE max_connect_errors: max_connect_errors should probably be set as large as your platform allows.

# WARN slave_net_timeout: This variable is set too high.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# NOTE innodb_flush_method: Most production database servers that use InnoDB should set innodb_flush_method to O_DIRECT to avoid double-buffering, unless the I/O system is very low performance.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.

Based on this output, we can start improving our configuration. For a detailed explanation of every variable, you can check the MySQL documentation for more information.

MySQLTuner

Another tool, which is not part of Percona Toolkit, is MySQLTuner. This tool will analyze the performance of your MySQL server and suggest changes. In general, you should only run this tool when your MySQL server has already been running for a couple of days. After you changed the configuration, you should wait another couple of days and run it again.

Let’s run ./mysqltuner.pl and see how our current MySQL configuration is performing.

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.35-0ubuntu0.12.04.2-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 941M (Tables: 399)
[--] Data in InnoDB tables: 2G (Tables: 891)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 913

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4d 1h 41m 1s (64K q [0.183 qps], 791 conn, TX: 119M, RX: 71M)
[--] Reads / Writes: 71% / 29%
[--] Total buffers: 192.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 607.2M (10% of installed RAM)
[OK] Slow queries: 4% (3K/64K)
[OK] Highest usage of available connections: 3% (6/151)
[!!] Key buffer size / total MyISAM indexes: 16.0M/309.5M
[!!] Key buffer hit rate: 86.9% (14M cached / 1M reads)
[OK] Query cache efficiency: 58.9% (31K cached / 53K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 808 sorts)
[OK] Temporary tables created on disk: 6% (99 on disk / 1K total)
[OK] Thread cache hit rate: 99% (6 created / 791 connections)
[!!] Table cache hit rate: 6% (400 open / 5K opened)
[OK] Open file limit used: 3% (695/20K)
[OK] Table locks acquired immediately: 99% (47K immediate / 47K locks)
[!!] InnoDB data size / buffer pool: 2.0G/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    key_buffer_size (> 309.5M)
    table_cache (> 400)
    innodb_buffer_pool_size (>= 2G)

At first we will see the test results which have been checked by MySQLTuner. Based on those test results, MySQLTuner will give you suggestions on how you can improve your MySQL configuration. Do take into account it’s always a trial and error if everything works out correctly. Also, don’t forget to rerun this script a couple of days later, to see if things have been improved or that more improvement is needed.

Comparing your config across multiple servers

If you are working with multiple MySQL servers, you might want to make sure all configurations are the same. Checking it manually can be a time consuming task. Lucky for us, Percona created a tool called pt-config-diff.

Basically it takes 2 configuration files and compares them. Take the following 2 examples into consideration.

[mysqld]
log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes = 1
[mysqld]
log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes = 0

If we now run pt-config-diff /server1/etc/mysql/my.cnf /server2/etc/mysql/my.cnf you will get a similar output like this:

1 config difference
Variable                  /server1/etc/mysql/my.cnf /server2/etc/mysql/my.cnf
========================= =========== ====
log_queries_not_using_... 1           0

The example used is of course very simple. However, if you optimized your MySQL server by setting a lot of different variables, this tool can come in handy to make sure no differences are present on your servers.

Conclusion

Not only is it important to keep your own database in good shape, it’s also important to configure your MySQL in the correct way. Learning each variable and seeing what it actually does can be a time consuming task. Luckily, we have some tools within our reach which can help us handle these daunting tasks.

Are you using any tools to optimize your MySQL configuration? If so, what kind of tools are you using? I would love to hear from you in the comments below.

Leave a Reply

Your email address will not be published. Required fields are marked *