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 DirectoryEverything 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.
/var/run/mysqld/mysqld.sock. I recommend you set the configuration variable socket to
/var/run/mysqld/mysql.sockin your MySQL configuration file for this effect.
/var/run/mysqld/mysqld.pidto your configuration file.
ibdata1file) also belongs to this category (mostly because it makes little sense to split it away).
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
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.
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, 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
/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.
MySQL Relay Logs
/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.
MySQL Temporary Directory
/var/lib/mysql/. Besides, MySQL Temporary Files might quite quickly fill up a typical
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 INFILEstatements).
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
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
MySQL Error Log
2so MySQL will log all the information it can. More logs usually means less debugging time.
MySQL Slow Query Log
/var/lib/mysql/backupfor 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).
|Mount Point||Contents||Configuration Variables|
||Process-ID file, IPC Socket||pid-file
|RAID Set #1||
||MySQL Data Files||datadir
|RAID Set #2||
||InnoDB Journal Files
InnoDB Double-Write File
|RAID Set #2||
||MySQL Binary Logs||log-bin
|RAID Set #2||
||MySQL Relay Logs||relay-log
|RAID Set #1||
||Temporary MySQL Files||tmpdir|
||MySQL Error, Slow Query Logs||log-error
|RAID Set #3||
||DBA-owned data while database is in maintenance.||N/A|