This blog entry is on a step that many DBAs either skip, ignore, or do not know about. My last entry was on some install tricks and this time I want to cover where to put the various pieces of a MySQL server if you are looking to maximize performance. George Trujillo covered this for Oracle DBAs transitioning to MySQL but generally you do not see a lot of best practices information on this subject.
1. Pick a spot on a drive for the server software. It does not matter if it is /opt/mysql, /usr/local/mysql, or some variant.
2. Pick a spot on another drive that is on another disk controller for the log files.
3. Pick a spot on yet another drive that is on yet another disk for the data. Or get different spots for the various schemas you have to support — accounting, manufacturing, shipping all on different drives and controllers.
The more you separate the various pieces, the less bottle necks in the hardware will arise. With the data, programs, logs, and everything else on the system on one disk it is very easy get to the point where waiting for the disk to complete a prior read/write to complete. Given a preference, splitting the pieces to different controllers would be the best. But sometimes you get stuck with having to use one controller (and it will take away some potential performance).
The biggest points of contention will be log files, especially if you are using replication, and the data. So if you can only get two separated drives/controllers, please do split these two up.
But what if you are corporately mandated to use a SAN? Well, SANs are very useful. But if you are trying to eek that last 15% out of a server, then you need to get off that SAN. Virtual environments have a similar problem in that visualization itself if not going to be as fast as running the same code on the real hardware. If you are required to be on the corporate SAN check with the SAN administrators to see if there is some way to cut down on contention.
RAID? RAID 1, 5 or 10? Yes. Please use RAID and go with the level you are most comfortable with. Paranoia unfortunately pays off over time with disk drives. And a good RAID does not mean you do not have to make backups.
And if you are just starting out with a single instance on an old laptop or desktop in order to learn MySQL, then you are not concerned with performance – yet – and you can get by on a single disk. But please starting looking at the variable that point to where various items are stored. Hint SHOW VARIABLES LIKE ‘%DIR%’;