Im thinking of moving mysql DB onto its own server. Should I invest into 15k rpm SAS drives, over standard SATA ones? My dataset is only 450mb... grows at about 40mb/month. The server would have 4GB of ram, and would also run an instance of memcached.
6 Answers
it's not about the size, but the speed you require. SAS are lot faster. However, because the dataset is ~0.5GB and you have 4GB and the grow rate is so slow, you might as well just load everything into memory (mysql cache) and not worry about the hard disk speed.
-
1
Such a small dataset should work fine out of memory. But if you want to invest in a performance disk, get a solid state disk. That can outperform any SAS disk out there.
Looking at your working set, type of disks is the least of your concern unless you plan to read/wrote thousands of time per second. Put more ran and use that memcached.
I would buy SAS 15k disks... They are still faster than SSD when a lot of read/write occurs (especially writes). You could also configure ram-disk as the database is small enough, but you must remember to leave space in memory also for joins/sort buffers and temporary tables (also you could consider ram-disk for those temporary tables that mysql creates on disk). In fact - everything depends on how this database is used. If it mostly read: better would be to configure larger query cache memory than putting whole database or temporary tables in ram-disks as this speeds up running same queries. If inserts/updates dominate - good SAS disk or ram-disk will be better.
We host huge web-server with mysql on SAS 15k disks and it performs very well, so I could recommend them!
You still need discs to write your commit log - in this case it's the performance of small random writes which matters. Getting a battery backed RAID controller is essential as it makes small writes vastly faster (the controller does them in the background; if the power fails then it keeps the data in its cache until power is restored)
This is, assuming you require durability.
The file system cache will keep your whole working set in memory at that size for reads. I would measure your read and write IOPS, and assume they are all random. Use iostat on Linux ("w/s" field), or perfmon on Windows (Physical Disk writes/sec)
A SATA disk can do about 60-70 random write IOPS, 15K disks can do 150+. Your RAID level affects this, too. RAID 1 will perform the same as 1 disk. RAID 10 will perform the same as num_disks/2 for writes. RAID-5 will perform the same as num_disks/4 unless there is a hardware controller with battery-backed cache.
If multiple SATA spindles can't handle your workload, I would look at some of the server-class SSDs, such as the Intel X-25E (2nd generation). They will blow away any 15K disk by a factor of 10 or more, even for random writes.
At current disk prices, it's often cheaper to add IOPS by getting more 7.2K SATA spindles than buying 15K disks. Even when you add in extra chassis and power costs. Plus you get all that capacity for free (use it for snapshots or whatever). If you need more, go SSD.
