MySql recommended hardware

Our company servers are currently hosted on a VPS and we've decided to move into dedicated servers.

When choosing the best hardware for a DB server, what should we invest more resources:
better CPU (more cores)? or more RAM?

Where is the best ROI?

Any suggestions?

You should invest in both.

As for MySQL, if you are using InnoDB as your default storage engine, you must upgrade to MySQL 5.5 in order to use more cores. Here are some of my past posts on this subject

• Multi cores and MySQL Performance

• Possible to make MySQL use more than one core?

• About single threaded versus multithreaded databases performance

• 16 Cores 12 GB RAM server MySql Configuration - my.cnf

As a point of reference, I have a client at my employer's web hosting company that has three DB Servers in Circular Replication. Each DB server has the following:

• MySQL 5.5.9

• 192 GB RAM

• 162 GB InnoDB Buffer Pool

• dual hexacore (that's right, 12 CPUs)

• 1.8TB Disk Volume

• 978 multitenant databases

• 892 GB of InnoDB Data

• innodb_file_per_table enabled

They have had this setup for a whole year. I personally configured my.cnf for multicore engagement. All 12 CPUs are in use. The InnoDB Buffer Pool is full to capacity. There have been no complaints from the client on the DB Performance since this layout was setup for them.

Tuning InnoDB is very important because if you don't, MySQL 5.5 will perform worse than MySQL 5.1. In some cases, it can be worse than MySQL 4.1.

• Query runs a long time in some newer MySQL versions

• Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)

As for RAM, tuning connection usage, the number of connections and InnoDB caches is the most important thing

• Cache MySQL database in memory

• What are the main differences between InnoDB and MyISAM?

• How do I properly perform a MySQL bake-off?

StackExchange Database Administrators Q#15030, answer score: 12