Tuesday, 24 September 2013

A Guide to Organising MySQL Files and Directories

I've been a Professional Database Administrator since 2001. In this time, I had the opportunity to manage Oracle, PostgreSQL, MySQL, and MS SQL Server databases. From all those, I believe Oracle was the only one with documented best practices about how to organise your files and directories.

While not crucial for performance in small deployments, having a properly organised database file-system architecture can play a big part on your performance.

In this article I will explain my preferred database file-systems for MySQL databases, how to configure your MySQL for this purpose, and what benefits you can crop from it. I will assume here you're using some sort of Linux system, because this is, at least from my point of view, the most common case. I believe extrapolating this organisation to other Operating Systems is a simple and straightforward exercise.

Base Directory

Everything starts by choosing a base directory for the database files. The Filesystem Hierarchy Standard (FHS) dictates Application State information should be stored under /var/lib/mysql. I strongly recommend you to comply to this standard, as it makes your life easy and is also the default supported for most Linux Distributions.

I classify the database files into six categories: Data Files, InnoDB Journal Files, Binary Logs, Relay Logs, Temporary Files, and Log Files.

Run-Time Files

Complying wiht the FHS and with the Policies of most of the modern Linux distributions, you should deploy your MySQL Socket file (used to establish Inter-Process-Communication (IPC) -based connections to your MySQL Server at /var/run/mysqld/mysqld.sock. I recommend you set the configuration variable socket to /var/run/mysqld/mysql.sock in your MySQL configuration file for this effect.

Also in compliance with the aforementioned standards, I recommend you deploy your Process-ID file in the same directory, by setting pid-file to /var/run/mysqld/mysqld.pid to your configuration file.

Data Files

Data files are the files that contain your data. MySQL usually keep this files in per-database-schema directories. This is actually how MySQL implements the notion of database-schemas: the first-level directories under your database datadir are considered to be all schema names.

The InnoDB System Tablespace (also known as ibdata1 file) also belongs to this category (mostly because it makes little sense to split it away).

The Data Files location is governed by a two options in MySQL: datadir, and innodb_file_per_table

Hum. Well, that's actually a white-lie. You can also set your InnoDB System Tablespace Location with innodb_data_home_dir and innodb_data_file_path although if left unset those variables will by default place the InnoDB System Tablespace file in MySQL's datadir.

Nowadays, running a MySQL database without innodb_file_per_table to "ON" is a must. If you're not keeping your tablespaces separated, I strongly recommend you to do so.

I recommend setting datadir to /var/lib/mysql/data, and arranging for this directory to be mounted on a separated RAID set from where your Operating System is, if possible. Due to the random nature of the writes to the Data Files, this is probably the place where you can use SSD disks with better performance gains.

InnoDB Journal Files

InnoDB by default creates two fixed-size journal files named ib_logfile0 and ib_logfile1. You can change how many journal files will be used by setting innodb_log_files_in_group, although I never witnessed a situation where it would be beneficial to have a different number from the default.

InnoDB Journal Files are automatically created by the InnoDB Storage Engine upon startup, and will be placed in the directory defined by innodb_log_group_home_dir (or by default in the same directory as the datadir).

This is also an excellent location where to place the InnoDB Double-Write file, where InnoDB will write data pages before copying them to their correct positions in the data files, in order to avoid the risk of corrupting data files with half-written pages. To place the InnoDB Double-Write file here, I recommend setting innodb_doublewrite_file to /var/lib/mysql/journal/ib_doublewrite.

I recommend setting innodb_log_group_home_dir to /var/lib/mysql/journal, and arranging for this directory to be mounted on a separated set of RAID disks, if possible controlled by a different RAID controller than where your Data Files are, and also separated from your Operating System disks. Due to the sequential nature of the writes to the InnoDB Journal Files, a good set of small SAS disks and a good sized RAID Controller write-cache can provide you with the best performance gains here.

MySQL Binary Logs

MySQL Replication relays on Binary Logs, a sequential record of all the transactions applied to this MySQL instance. Binary Log recording can be enabled and disabled with the log-bin option. This option also takes one argument, which is the "prefix" we should add to the name of the individual binary log files. I recommend setting this variable to /var/lib/mysql/binary/binlog. Due to the sequential nature of the writes to the binary log files, this directory can share a RAID set with the Journal Files.

Together with the Binary Log files, MySQL maintains an index file with the known Binary Log files. This file location defaults to the log-bin prefix. It can be changed by setting the variable log-bin-index. For completeness, I recommend setting this variable to /var/lib/mysql/binary/binlog.index.

MySQL Relay Logs

The MySQL Replication I/O (Server) Thread will copy Binary Logs from the master database as (local) Relay Log files, which will be in turn read sequentially by the Replication SQL (Server) Thread and applied to the local database. The I/O Thread will use the file name prefix configured in relay-log to create the files in disk. I recommend setting this option to /var/lib/mysql/relay/relay-bin. Due to the sequential nature of reads and writes to this file, they can share the same RAID set as the Journal and Binary log files without much trouble. If you're really concerned about replication performance, you can separate this files in a different RAID set, and even a different RAID Controller.

Together with the Relay Logs files, MySQL maintains an index file with the known Relay Logs downloaded from the master. This file location defaults to the relay-log prefix. It can be changed by setting the variable relay-log-index. For completeness, I recommend setting this variable to /var/lib/mysql/relay/relay-log.index.

MySQL also stores replication state (position, master file name, etc) in a file named Relay Log Info File. This file location also defaults to the location of the relay logs. Also for completeness, I recommend explicitly setting the location of this file using the variable relay-log-info-file to /var/lib/mysql/relay/relay-log.info.

MySQL Temporary Directory

Although a lot of System Engineers set this configuration variable to point to the Operating System temporary directory. I recommend against this. MySQL Temporary Files are also part of the Application State, and accordingly to the FHS they belong under /var/lib/mysql/. Besides, MySQL Temporary Files might quite quickly fill up a typical /tmp directory.

Also, depending on your distribution settings, there is the risk that your temporary directory will be cleaned upon restart, which might destroy state MySQL requires for replication (MySQL Replication requires that it's temporary files survive system restarts, in order to be able to continue replicating LOAD DATA INFILE statements).

You can estimate the size requirements of your Temporary Directory with a simple calculation:

tmpdir size = (server threads) * (row length + sizeof(row pointer)) * (matched rows) * 2

You might want to adjust this formula to take into consideration more than one temporary table per thread.

To set the location MySQL will use for temporary files, use the tmpdir option. I recommend setting your temporary directory location to /var/lib/mysql/tmp/.

Due to the nature of disk accesses to the temporary directory, it can probably share the same RAID controller with your Data Files.

MySQL Log Files

Last but not least important, MySQL produces at the very least error logs while servicing requests. Ideally, you should also log Slow Queries (which is a great and valuable source of information about how your database is performing).

MySQL Error Log

MySQL log error messages either to console or to a log file in the disk. If you're running MySQL as a service for a professional application, I recommend using a log file. Set the location of the log file with log-error. I recommend logging to /var/lib/mysql/log/${HOSTNAME}-err.log.

By default only errors are logged. I recommend setting log-warnings to 2 so MySQL will log all the information it can. More logs usually means less debugging time.

MySQL Slow Query Log

MySQL logs queries that run for longer than the time set in the long_query_time configuration variable. Those queries are written (along with some statistics) to a log file on the disk.

I recommend setting the location of this file with slow-query-log-file to /var/lib/mysql/log/${HOSTNAME}-slow.log.

Due to the sequential nature of both the Slow Query Log file and Error Log file writes, those can safely share the same RAID controller and disk-set with the Journal, Relay Logs, and Binary Log files. Another good alternatives is to share the RAID set with the Operating System. I personally like the first option. There is usually no need to use SSD disks for this files, as SAS disks usually perform better for sequential writes.

Backup Directory

This is not actually required to run MySQL, and won't change the performance of your service. It is more of a convenience for your Database Administrator, and I personally consider it good practice to have disk space and a standardized location where to place data and log files while the DBA is backing up, restoring, or just servicing the MySQL instance.

I create and use /var/lib/mysql/backup for that. Due to the random nature of the reads and writes here, this directory can share the same RAID Controller and RAID Disk set as your Data Files (ideally you should have it on a completely separated Raid Controller and Disk set, but one can only hope for so many disk controllers on a host).


RAID
Disk Set
Mount Point Contents Configuration Variables
Operating System /var/run/mysqld/ Process-ID file, IPC Socket pid-file
socket
RAID Set #1 /var/lib/mysql/data MySQL Data Files datadir
innodb_data_file_path
innodb_data_home_dir
innodb_file_per_table
RAID Set #2 /var/lib/mysql/journal InnoDB Journal Files
InnoDB Double-Write File
datadir
innodb_log_group_home_dir
innodb_log_files_in_group
innodb_doublewrite_file
RAID Set #2 /var/lib/mysql/binary MySQL Binary Logs log-bin
log-bin-index
RAID Set #2 /var/lib/mysql/relay MySQL Relay Logs relay-log
relay-log-index
relay-log-info-file
RAID Set #1 /var/lib/mysql/tmp Temporary MySQL Files tmpdir
Operating System /var/lib/mysql/log MySQL Error, Slow Query Logs log-error
log-warnings
slow-query-log-file
RAID Set #3 /var/lib/mysql/backup DBA-owned data while database is in maintenance. N/A

No comments:

Post a Comment