MySQL has hundreds of settings that can be configured. Version 5.7 ships with many improvements in default configuration values and requires far fewer changes. In this recipe, we will look at some of the most important parameters for tuning MySQL performance.
Getting ready
You will need access to a root account or an account with sudo privileges.
You will need access to a root account on the MySQL server.
How to do it…
Follow these steps to improve MySQL configuration:
First, create a backup of the original configuration file:
$ cd /etc/mysql/mysql.conf.d
$ sudo cp mysqld.cnf mysqld.cnf.bkp
Now open my.cnf for changes:
$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Adjust the following settings for your InnoDB tables:
innodb_buffer_pool_size = 512M # around 70% of total ram
innodb_log_file_size = 64M
innodb_file_per_table = 1
innodb_log_buffer_size = 4M
If you are using MyISAM tables, set the key buffer size:
key_buffer_size = 64M
Enable the slow query log:
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log
long_query_time = 2
Disable the query cache:
query_cache_size = 0
Set the maximum connections as per your requirements:
max_connections = 300
Increase the temporary table size:
tmp_table_size = 32M
Increase max_allowed_packet to increase the maximum packet size:
max_allowed_packet = 32M
Enable binary logging for easy recovery and replication:
log_bin = /var/log/mysql/mysql-bin.log
Additionally, you can use mysqltuner.pl, which gives general recommendations about the MySQL best practices:
$ wget http://mysqltuner.pl/ -O mysqltuner.pl
$ perl mysqltuner.pl
How it works…
The preceding example shows some important settings for MySQL performance tuning. Ensure that you change one setting at a time and assess its results. There is no silver bullet that works for all, and similarly, some of these settings may or may not work for you. Secondly, most settings can be changed at runtime with a SET statement. You can test settings in runtime and easily reverse them if they do not work as expected. Once you are sure that settings work as expected, you can move them to the configuration file.
The following are details on the preceding settings:
innodb_buffer_pool_size: the size of the cache where InnoDB data and indexes are cached. The larger the buffer pool, the more data can be cached in it. You can set this to around 70% of available physical memory as MySQL uses extra memory beyond this buffer. It is assumed that MySQL is the only service running on server.
log_file_size: the size of the redo logs. These logs are helpful in faster writes and crash recovery.
innodb_file_per_table: This determines whether to use shared table space or separate files for each table. MySQL 5.7 defaults this setting to ON.
key_buffer_size: determines the key buffer for MyISAM tables.
slow_query_log and long_query_time enable slow query logging and set slow query time respectively. Slow query logging can be useful for identifying repeated slow queries.
Query_cache_size caches the result of a query. It is identified as a bottleneck for concurrent queries and MySQL 5.6 disables it by default.
max_connections sets the number of maximum concurrent connections allowed. Set this value as per your application's requirements. Higher values may result in higher memory consumption and an unresponsive server. Use connection pooling in the application if possible.
max_allowed_packet sets the size of the packet size that MySQL can send at a time. Increase this value if your server runs queries with large result sets. mysqld set it to 16M and mysqldump set it to 24M. You can also set this as a command-line parameter.
log_bin enables binary logging, which can be used for replication and also for crash recovery. Make sure that you set proper rotation values to avoid large dump files.
There’s more…
MySQL performance tuning primer script: This script takes information from show status and show variables statements. It gives recommendations for various settings such as slow query log, max connections, query cache, key buffers, and many others. This shell script is available at http://day32.com/MySQL .
You can download and use this script as follows:
$ wget http://day32.com/MySQL/tuning-primer.sh
$ sh tuning-primer.sh
Percona configuration wizard
Percona systems provide a developer-friendly, web-based configuration wizard to create a configuration file for your MySQL server. The wizard is available at http://tools.percona.com
MySQL table compression
Depending on the type of data, you can opt for compressed tables. Compression is useful for tables with long textual contents and read-intensive workloads. Data and indexes are stored in a compressed format, resulting in reduced I/O and a smaller database size, though it needs more CPU cycles to compress and uncompress data. To enable compression, you need an InnoDB storage engine with innodb_file_per_table enabled and the file format set to Barracuda. Check MySQL documents for more details on InnoDB compression at https://dev.mysql.com/doc/innodb/1.1/en/innodb-compression.html .
See also
MySQL tuner script at https://github.com/major/MySQLTuner-perl
MySQL docs at https://dev.mysql.com/doc/refman/5.7/en/optimization.html
InnoDB table compression at https://dev.mysql.com/doc/refman/5.7/en/innodb-table-compression.html
