GenerationIP

Type : System

After reading this articile, you could have a better view on hardware choice for a mysql DATABASE server and how to customize the File System Partition.

The most important parametters for your Database Server is to improve your I/O. More your I/O is important more the response time of queries are short.

Hard Drive :

The I/O is one of the most important parametter when you have a DATABASE Server. More your I/O is important more you Database server can answere shortly, for that you have to chose the best hard disk and topologie for your server.

Avg SATA I/O : 90MO/S R/W - seek time ~7MS

Avg SAS 10rpm I/O : 140MO/S - seek time ~4-5MS

Avg SAS 15rpm I/O : 160MO/S - seek time ~3-4MS

Avg SSD  I/O : 200MO/S R/W - seek time <0.5MS

Reference Site for Hard Drive benchmark.

Now with those informations, you can define witch Hard drive is better for your server. But a good hard drive is not enought to create a good storage solution for your database server.

RAID

With raid technologies, you can increased throughput and secured your data. My recommandation is to use a RAID 5 or RAID 50 (don't forget to add spare).

RAID info here

For your hardware choice, you can chosed your server with a big RAID backplane or take a SAN, more you can add disk in your raid solution more you throughput could be important. If you take a SAN, you have to provide the best link between the SAN and the Server, Fiber CHANNEL is a good solution but a little bit expensive.

Leader in SAN solution :

NETAPP

EMC

Partition :

Partition :

If you want to have the best reponse time under linux or windows, you have to split your system with different partitions :

1. partition on first controler for the OS
2. partition on second controler for logs
3. partition with the most important throughput on a dedicate controler for mysql data.

But you can add more partitions if you want but in this case you must have a SAN solution and create a partition on the SAN for Each DATABASE or add a SAN for each DATABASE if you have enough budget.

Example Under Linux :

/ ->  /dev/sda (RAID 1) -> SAS

/var/log -> /dev/sdb (RAID 1) -> SATA

/var/lib/mysql  -> /dev/sdc (RAID 5 or higher) -> SAN, SAS or SSD

File System :

File System choice could be a important parametter under LINUX.

FS Comparaison on Wikipedia

Test of different FS on Linux-MAG :

Transactions /seconde for different size of file.

Figure 1: Postmark Results for Small File Size

Figure 2: Postmark Results for Large File Size

Specifications :

EXT2 Old but efficient FS for Linux

EXT3 Base FS for Linux

EXT4 Future FS for Linux

ReiserFS

XFS Very Good FS

btrfs Very interesting FS

nilfs Impressive FS

Conclusion :

Take the Best Hard Divre if you can : SSD

Take a SAN Solution If you can based on SSD or SAS drives.

Define your partitionning to improve your throughput.

Don't forget to take the best FS for your DATABASE SIZE