Optimizing MySQL performance

Optimizing MySQL performance – configuration

By OK - February 25, 2017

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 mysqldumpset 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.

Search This Blog

Linux, UNIX