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.
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.
Follow these steps to improve MySQL configuration:
$ cd /etc/mysql/mysql.conf.d $ sudo cp mysqld.cnf mysqld.cnf.bkp
my.cnf
for changes:$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
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
key_buffer_size = 64M
slow_query_log = 1 slow_query_log_file = /var/lib/mysql/mysql-slow.log long_query_time = 2
query_cache_size = 0
max_connections = 300
tmp_table_size = 32M
max_allowed_packet
to increase the maximum packet size:max_allowed_packet = 32M
log_bin = /var/log/mysql/mysql-bin.log
mysqltuner.pl
, which gives general recommendations about the MySQL best practices:$ wget http://mysqltuner.pl/ -O mysqltuner.pl $ perl mysqltuner.pl
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.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 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
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.