What is the optimal OS partition layout for a database server?
I’ve seen so many different configurations for OS partitions of recent time, none to my satisfaction.
Historically, in Unix days 20 years agao, long before RAID and SAN’s all my experiences were for strongly defined partitions.
That is separate partitions for the OS /, /boot, /tmp, /usr, /var and then seperate partitions for effectively application and data with /home and /opt
Today what is optimal for an OS configuration on a database server.
I’m seeking the input and experiences of the community. I’m making the assumption of at least RAID 1 or better for all disks.
It’s obvious that the database partition must be separate, and given snapshot capabilities both the data and binary logs should be specified on the same partition for consistency.
It’s also obvious the /tmp filesystem should not be with the / file system. You never want anything stupid that is using the /tmp filesystem to affect your operational system.
I’ll make the following assumptions.
- Is a production database server
- You are not installing new software often, therefore /usr should remain relatively static.
- You have correctly configured MySQL not to place an data in /var.
This leaves /boot, /usr and /var for the OS. Do these require separate partitions? I would like to see it but do people care. With the amount of disk space available does a large amount bypass the need?
Any comments would be appreciated.
We currently do it in the following fashion:
/
/boot
/var
/tmp
/mymadeupdirectory/logs/mysql
/mymadeupdirectory/mysql
Each being a seperate partition, this makes it so that the innodb (which we use) tablespace is on a seperate disk and the iblog files are on seperate disks. The operation system itself is on a different disk as well.
Optionally you could make it so every ibdata file is on a different disk.
My 5 cents
Ivo
1) I like RAID 5 – that seems to cover most uses;
2) Yes, database partition is separate;
3) I like to have logging separate, although it is not a definite. If you would like to have a binary and general log, or if your database has a lot of binary logging (millions of database operations daily) then I would definitely recommend a separate partition;
4) /tmp is separate, although it must be large enough to handle your temporary disk needs! We have had databases go down because there was not enough space in /tmp! (I thought 1G was enough) Our temporary (pun intended) workthrough on this (until we could have time to allocate /tmp partition space) was to set the temp folder inside our database! Yikes!
5) Our /boot, /usr, and /var directories are in the same partition, as my main concerns lie with disk I/O, and thus are the impetus behind my points above – database, logging and temp space are the top three I/O uses. As for recovery of a “box gone bad”, we have a database backups and a spare machine to use for that purpose.
I think you should specify whether the application is serving an OLTP or OLAP (or mixed) workload, as often that will affect how one goes about developing a strategy in this regard, no?
Good point!
My list above is based more on an OLTP model, as our setup tends toward that, although we are setting up a dedicated reporting server before Christmas to give us an angle on the OLAP side. I also should have added that because of our high availability requirement with our current setup (OLTP-related), we also have Linux-HA alongside our master server to help with uptime.
You want to have /boot as a separate partition, alone for security reasons (unmounted all the time, only root-mountable).
For /var nearly the same as for /tmp is true as you don’t want your root partition to run out of disk space because of some logfiles that didn’t logrotate or suddenly started to grow like crazy…
I like /usr as separate partition, but to be honest right now I can’t say why. More some historic reasons I assume.
We use RAID1 for OS and RAID 5 for the database related partitions. However, we’re going to change this to RAID 10 with lots of small disks. Much better performance, which was actually cheered upon by a MySQL employee during a meeting we had.
Databases usually do write short bursts of data which is less than optimal on RAID-5, because parity is always calculated for the whole stripe size…