Sweet potato select MySQL server hardware configuration

5 minute DBA-database server hardware selection describes some ways to help the database select hardware, which is actually biased towards MySQL...

Simply put, it is of little significance to have a CPU larger than 8. You do not need to buy 4*4 Core or 4*6 Core because MySQL cannot be used at present.

The larger the memory, the better the memory, the much cheaper It is now. If I/O bound is faulty, it is usually the most cost-effective method to directly add the memory to 32 GB or 64 GB in addition to Program Rewriting.

The faster the hard disk is, the better the raid 10 with hardware (with a battery) is than the raid 10 with the software. However, this mainly depends on the requirement. If it is a CPU bound application, might the SATA hard disk be enough?

Ensure that you use gigabitethernet for NICs, preferably bounding/trunking. In addition to adding thoughput, it can also be used as a redundant link. Then the operating system must select 64 bits, otherwise it will be limited by the memory addressable space.

5 minute DBA-database server hardware selectionposted on
March 10,200 9
By matthew yonkovit

So you need to purchase a new database server, and you really don't know where to start ..

Because their maybe different recommendations for different OS's I am going to stick with Linux with these recommendations. I want to say right off the bat here... Choosing the right hardware shocould probably not be a five minute task. I think you really need
To spend time reviewing your application, it's access patterns, the IO capacity, etc. you just can't do this in five minutes generally. but if your under the gun and asked me to spec something out today... Here are some general guidelines.

As of March 10th 2009 here is what I wowould recommend (this are going to change every few months potentially with new versions of MySQL & new hardware:

CPU:
Currently the scalability of InnoDB beyond 8 cores is limited, in fact with the current (unpatched) releases you may see a performance regression with more then 8 cores. we wrote about this before. if you have the money go with the fastest CPU's available.
Avoid servers with lots of cores and a lower speeds.

Memory:
The more memory generally the better. here I wocould shoot for an amount of memory that will allow 100% of your hot data to be in memory. if you do not know how much data is going to be hot, a good guess in my experience is 10-20% of the total database size,
But that's going to vary from application to application. if you can't figure out the total host data, more is better or a 20% rule of thumb is better then nothing. minimally plan for 2-4 GB of memory to be allocated to the OS. typically I want at least 4 GB
Of memory for the OS, and if I have a really active database I want more (8 GB + ). when I say 4 gb for the OS, this will also be used for the MySQL per Thread buffers/thread stack, etc. so if you have a 24 GB database and all 24 GB is hot or going to be used
Frequently I may put in 32 GB of memory in my system and allocate 24 GB to InnoDB and reserve the other 8 GB for the OS and other items.

Disk:
Disk is going to be a toughy in 5 minutes. you really have two concerns here, disk capacity & Io capacity (think Io's per second ). disk capacity is generally pretty easy, I need xgb of disk space... Most people can estimate this no problems. Keep in mind
Raid you will lose some raw disk capacity (I. e. 4 160 GB drives in a raid 10 setup will deliver ~ 320 GB of usable space). a 5 minute answer to Io capacity is a bit more of a challenge. One of the descisions yo will need to make if you can get by
Internal disk or you need a SAN. just the other day
Peter Z wrote about when to choose a San... Give it a read. assuming your making a demo-in 5 mintes I am going to make an assumption that your going to look for internal disk. purchasing the correct San in my opinion requires a lot of thought. so let
Assume you want to use internal disk. first if all of your data fits into memory, it lessons your disk requirements a bit but it will not eliminate disk io (your still going to read and write to disk, but your reads shoshould be lessened ). I strongly urge that
You keep the database and OS completely separate. so that's 2 disks for the OS (mirrored disks shocould be fine ). for the database I typically recommend raid10 for the data (RAID 5 maybe OK for database with fewer writes, I. e. data Warehouses) which means
A minimum of 4 disks for your database. so thats 6 disks to start. you can then scale up from there as you need more, without more analysis your going to be guessing here. in terms of the type of drives, SAS or SCSI in either 10 K or 15 K speeds
Are pretty standard. I wowould avoid SATA drives that are <10 k rpm. Of course this can all change, because it really depends on your needs and requirements... As I said tough!

Disk controller:
Cache on your disk controller is important and the more the better in most cases. Make sure your controller has a battery backup, otherwise its really useless.

Network cards:
Purchase at least two network cards for your system, both full 1gbe. typically I wowould bond these two into together to give some redundancy. also avoid dropping your new server into a 100 Mb/s network as well.

A quick note on the OS... Make sure you install a 64 bit OS!