Is the CPU performance relevant for a database server?

Question

This is a purely theoretical question. Let's say I have an application deployed on multiple servers.

1. A load balancer,
2. Multiple/scalable applications servers
3. A (single) database server (for the moment)

On the two first parts, I do know what to look for. But what about the database server? What kind of hardware should I look for?

• Is CPU frequency relevant for a database server?
• Are multiple core CPUs relevant?
• Is RAM more important than CPU?

PS: Supposing the chosen database is MySQL or PostgreSQL.

• ConcernedOfTunbridgeWells: Well, it has to have one.

Answer 1

For PostgreSQL, CPU power can be very relevant, especially if a fairly high percentage of the active working set of your data fits in RAM. Most of the databases I've worked with have had CPU power as the main bottleneck most of the time. (I just checked vmstat on a server hosting web sites with millions of hits per day hosting over 5TB of database space, and I never saw more than 2% disk wait time, but saw a peak of 12% user CPU time.)

Since PostgreSQL is process-based, any single process can only run as fast as one core, but in a mix like like we have on the server mentioned above, with a high volume of small requests, total CPU across all cores is most important. For the same total CPU power, PostgreSQL will generally do better with fewer, faster cores than many, slower cores.

Up to the point where a high percentage of your active data set is cached, adding RAM will typically show more bang for the buck than adding cores. After you've got sufficient caching, the benefit of additional RAM goes down and you're better off boosting CPU power.

For more details on this topic as it pertains to PostgreSQL, I don't think there is a better source than PostgreSQL 9.0 High Performance by Greg Smith. (Full disclosure, I was a technical reviewer for the book, but get no financial benefit based on sales.)

• RolandoMySQLDBA: Hey, I have the book. Is there any particular page, section or chapter you are reffering to ??? (BTW +1 for PostgreSQL perspective)
• Zenklys: Thanks for the information about PostgreSQL. I will check the book. ;)
• RolandoMySQLDBA: Good stuff from pages 21-23
• RolandoMySQLDBA: I can see the wisdom of your second paragraph when compared to the pages 21-23.

Answer 2

Strictly from a MySQL perspective, that's a very loaded question

CPU frequency relevant for a database server?

While faster CPU and motherboard are great, other bottlenecks can get in the way. Such bottlenecks include:

• Disk I/O
• Connection Maximums
• Network Latency
• Query Performance Per Connection

Every little advantage helps, but I have to say No because CPU speed, in itself, does not improve on the aforementioned bottlenecks. After all, what good can a Formula One RaceCar do wearing an open parachute or with an 800 pound gorilla at the wheel ?

Are multiple core CPUs relevant?

That depends entirely on which version of MySQL you are running. MySQL 5.1 InnoDB Plugin, MySQL 5.5, and Percona Server's XtraDB all have settings YOU MUST PROPERLY CONFIGURE to get InnoDB to access all cores. The real incentive for doing this stems from the fact that some older versions of MySQL LEFT UNCONFIGURED are faster than newer versions as I discussed in my past posts:

• How do I properly perform a MySQL bake-off?
• Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
• Query runs a long time in some newer MySQL versions
• Multi cores and MySQL Performance

Therefore, if you are not willing to configure InnoDB for accessing all CPUs, having multiple cores buys you absolutely nothing.

Is RAM more important than CPU?

Oh, yes indeed. Memory configuration for MySQL entails setting up

• buffers for InnoDB and MyISAM
• per-connection options for sorts, joins, read, and temp tables
• maximum connection limits
• bulk insert operation
• query caching

Requesting too little or too much of any combination of these things and MySQL come back to bite you. A faster CPU with MySQL improperly configured for RAM just makes MySQL bite you faster.

• Zenklys: Great answer. I'm going to check all those links, thanks.

Answer 3

• No
• No
• Yes

In simple terms, you need RAM and IO performance (latency + read speed + write speed) for databases.

The choice of 4 or 6 cores or 2.5 GHz vs 3 GHz is not really relevant (I assume you aren't having to choose between a P3-450 with 32 GB RAM or the latest Xeon with 1GB RAM).

If you're CPU bound, then you have other problems (poor design, poor indexes, swapping, non-dedicated server etc)

• Zenklys: Thanks, for the answer. SSD's are a good choice then? Over CPU power?
• gbn: @Zenklys: hard to say. What size of database do you have? Write volume? Read load? OLTP or OLAP? etc
• Zenklys: 20- 30 gb maximum. 10 to 1 read/write ratio, only small data, OLTP.
• gbn: @Zenklys: In which case, it doesn't really matter. Just buy RAM especially for MySQL so as much data is cached as possible
• dbenhur: Not sure why this is the accepted answer. It's simplistic as it doesn't consider the application, work load, or data set size. @kgrittn gave a better answer grounded in real world experience and a better grasp of theory of operation for Postgres.