This installment of the Drupal-friendly server series covers the process of setting up MySQL to work flawlessly with Drupal. Previous article of the series described the nuances of tuning nginx web server, now is the time to deal with the database. The OS of choice for us is Debian.

Installing MySQL

Installing MySQL 5.5 or 5.6

First off, we need to update the package list:

apt-get update

Now, to install MySQL 5.5 we run the following command:

apt-get install mysql-server-5.5

If we need MySQL 5.6, we run this:

apt-get install mysql-server-5.6

Installing MySQL 5.7

To install MySQL 5.7, we need to add a new repository, https://dev.mysql.com/downloads/repo/apt/. To do that, click Download, copy the link to the “No thanks, just start my download” field and download the .deb:

wget https://repo.mysql.com//mysql-apt-config_0.8.7-1_all.deb
dpkg -i mysql-apt-config_0.8.7-1_all.deb

Choose mysql-5.7 in Apply and click ENTER. Next, update the package list:

apt-get update

Install the mysql-server package that contains MySQL 5.7.

apt-get install mysql-server

Setting up MySQL

When you need to change the MySQL settings, you go to the config file at /etc/mysql/my.cnf

Setting up logging

There are 4 main log files in MySQL:

• Error Log, gathering all errors occurring in MySQL operations;
• Binary Log, one that contains all DB changing commands, used for replication and restoration in case of corruption;
• General Query Log, the log that records all queries;
• Slow Query Log, this is where all “slow” queries are stored.

Error Log

This log contains all errors and warnings registered when the server works. Error log is the log to dig into when the system malfunctions or fails. In Debian, the default settings dictate recording all errors to syslog, or you can have them registered in a separate log file:

log_error=/var/log/mysql/mysql_error.log

Binary Log

This log collects all commands that change the DB. It is used when you need to replicate the DB or restore it after a failure.

This is how you set up the binary log:

log_bin                 = /var/log/mysql/mysql-bin.log # path to log file
expire_logs_days        = 5 # entries expiration term, days
max_binlog_size         = 500M # max file size

General Query Log

This log contains all SQL queries and connections data.

This is how you set up the general query log:

general_log_file        = /var/log/mysql/mysql.log # path to log file
general_log             = 1 # logging on

Slow Queries Log

slow_query_log          = /var/log/mysql/mysql-slow.log # path to slow queries log settings file
long_query_time         = 1 # min query execution time (seconds) that sets normal queries from slow queries

MySQL parameters

bind-address — ip address for the listening port. For safety reasons, it is best to put 127.0.0.1 here if you do not use external connections with the server.

max_allowed_packet — max allowed size of a packet that can be transmitted in a single query. The default value is 4 MB. For Drupal, we recommend increasing it to 128 MB. If after that the system tells you that the packet is too large, decrease the value gradually.

max_connections — max number of parallel connections to the server. If you face the “Too many connections” error, increase the value. The default value is 151.

max_join_size — sets the limit for the number of lines read/searched by a query. This one allows weeding out queries trying to read millions of lines.

open_files_limit - number of files the OS lets use mysqld. If you encounter the "Too many open files" error, you need to increase the value. In Unix, this value cannot be greater than ulimit -n .

thread_cache_size — the number of cached threads. After processing a query, the server does not  close the thread but puts it into cache instead if the limit set by this parameter is not exceeded. The default value is 0, you can increase it to 8 or even 16. If the threads_created variable grows, you can increase the thread_cache_size even more.

Queries Cache

query_cache_limit — this is the max size of a query that can be cached. For Drupal, a good value of this parameter is 16 MB.

query_cache_size — size of the queries cache. Put 0 here to switch caching off altogether. To find out the optimal value of this parameter, you need to monitor Qcache_lowmem_prunes. When its value increases just slightly, you have query_cache_size correct. Also, remember that the larger the cache the greater the load on the server. For Drupal, 128 MB is a good choice.

query_cache_type — (OFF, DEMAND, ON). OFF switches caching off, DEMAND means the query gets cached only when it contains SQL_CACHE directive, ON switches caching on.

query_cache_wlock_invalidate — sets cache usage by the query in case the table the query tries to reach is not readable.

Slow Queries

slow_query_log — this is the path to the slow queries log file. The value is the full path (e.g. /var/log/slow_queries).

long_query_time — a slow query is a query that takes longer to execute than the value set for long_query_time (in seconds).

Timeouts

interactive_timeout — a period of time during which MySQL server waits for activity from the interactive connection (that uses the CLIENT_INTERACTIVE flag) before closing it. The value is set in seconds. For example, this parameter is used when working through MySQL console client. If the server sees no queries for the set period of time, it closes the connection.

net_read_timeout — time interval during which MySQL waits for the data to come before closing the connection. Set in seconds.

net_write_timeout — again, the limit of time MySQL stays alert for incoming data before closing the connection. Set in seconds.

wait_timeout — general timeout, the period of time during which MySQL expects any activity. When it expires, the connection is closed. Set in seconds.

Buffers

key_buffer_size — this is the size of the buffer allocated for indexes and open to all threads. This parameter affects performance. The default value is 8 MB; we recommend to make it around 15-30% of the RAM available. Also, it makes sense to have this value set below the joint size of all .MYI files.

table_open_cache — number of cached open tables. Please remember that each entry in this cache uses a system handle, which sometimes calls for increasing the handles limits. The opened_tables variable allows monitoring the number of tables opened bypassing cache.

tmp_table_size — memory limit for temp tables.

All values are set in bytes.

InnoDB Parameters

innodb_buffer_pool_size — memory given to InnoDB for index and data storage. You can increase it to match the joint size of all InnoDB tables or 80% of RAM, whichever is smaller.

innodb_flush_log_at_trx_commit — the values here are 0, 1, 2. 0 means the log hits the disk once a second, 1 makes flushing an event accompanying a transaction, 2 means the records are made when a transaction occurs, but nothing is flushed to disk (let the OS take care of storing the data). The default is 1, the most reliable but also the slowest value. We recommend changing it to 2, which adds a low risk of losing the data in case the OS crushes, but you only lose some seconds worth of data, depending on the OS’ tuning. 0 is the fastest option, but the risks here are higher, since you can lose data in case the any of the two, OS or MySQL server, crushes.

innodb_log_file_size — max log file size. When the log hits this value, InnoDB creates a new log file. For Drupal, we recommend 64Мб.

innodb_file_per_table - switch it on if you want InnoDB to store tables’ data in separate files instead of just one, which is the default setting. Separate files do not speed things up but offer a number of other advantages:
- you get more space when you delete tables, whereas a single file cannot grow smaller;
- with innodb_file_per_table on, you can use the compressed format of tables.

innodb_buffer_pool_instances - by default, InnoDB uses only one instance for Buffer Pool. However, MySQL allows having more of them, and in some cases InnoDB works with them much more efficiently. It makes sense to have a number of instances in case innodb_buffer_pool_size exceeds 2GB. Make each instance 1GB or more. And don’t forget that innodb_buffer_pool_size is the joint size of the pool.

Tools for MySQL optimization

• Percona Toolkit
• MySQLTuner
• Drupal module Performance Monitor