Best Practices for Using Multiple Filesystems on MySQL Database Servers

When mounting a volume on *NIX , the best practice is to create a subdirectory withing the mounted filesystem to host the actual files. This is due to the fact that every filesystem has a lost+found directory in the base (root) directory of that filesystem, normally owned by the root user. This can create issues with non-root commands and processes.

We recently saw the impact of this at a customer site when trying to use the tpm update command:

[tungsten@db1 tungsten-clustering-6.1.4-44]$ ./tools/tpm update
db1|ERROR: Initializing new object in package update failed:
find: ‘/opt/continuent/lost+found’: Permission denied

This issue happened because a filesystem was mounted directly on /opt/continuent

The best practice is to have a subdirectory off the main root directory to hold mount points, and mount filesystems onto those subdirectories, and then use additional subdirectories under the mount point directories for application use.

For example, let's say we have the following i/o streams that should each have thier own filesystem for the best performance:

• Root volume
• MySQL Data
• MySQL Binary Logs
• Tungsten THL
• MySQL General Log and any other logging

To illustrate the best practice for the above, please look at the following structure:

/volumes/data/mysql
/volumes/binlogs/mysql
/volumes/logging/mysql
/volumes/tungsten/thl

What we see above is a 3-level structure starting with /volumes, which is a simple directory holding second-level subdirectories data, binlogs, logging and tungsten. When you mount filesystems upon those subdirectories, they are called mount points. After mounting, the lost+found directories will become visible. Next, cd to the mount point directories and create the third-level directories for the applications to use.

This structure has the additional benefit of preventing MySQL from writing to the root directory in the event the filesystem is not mounted, because the third-level subdirectory that exists on the filesystem will not be visible. This will stop MySQL from launching at all, making sure all the proper disk dependencies are in place.

The best practice procedure would be to do something like:

mkdir -p /volumes/data
mount {resource} /volumes/data
cd /volumes/data
mkdir continuent
cd /opt/
ln -s /volumes/data/continuent

And in the INI, set install-directory=/volumes/data/continuent

In summary, one gets the best performance by using multiple filesystems. Mount and use them properly to avoid the pitfalls of having non-root processes interact with the lost+found directory at the root of every filesystem.