34

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.

Improve this question
1

3 Answers 3

Reset to default
31

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.)

Improve this answer
4
  • Hey, I have the book. Is there any particular page, section or chapter you are reffering to ??? (BTW +1 for PostgreSQL perspective)
    – RolandoMySQLDBA
    Apr 26, 2012 at 17:49
  • Thanks for the information about PostgreSQL. I will check the book. ;)
    – Zenklys
    Apr 26, 2012 at 17:51
  • 1
    Good stuff from pages 21-23
    – RolandoMySQLDBA
    Apr 26, 2012 at 17:56
  • I can see the wisdom of your second paragraph when compared to the pages 21-23.
    – RolandoMySQLDBA
    Apr 26, 2012 at 18:00
23

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:

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

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.

Improve this answer
1
  • 2
    Great answer. I'm going to check all those links, thanks.
    – Zenklys
    Apr 26, 2012 at 14:16
6
  • 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)

Improve this answer
5
  • Thanks, for the answer. SSD's are a good choice then? Over CPU power?
    – Zenklys
    Apr 26, 2012 at 13:37
  • @Zenklys: hard to say. What size of database do you have? Write volume? Read load? OLTP or OLAP? etc
    – gbn
    Apr 26, 2012 at 13:43
  • 20- 30 gb maximum. 10 to 1 read/write ratio, only small data, OLTP.
    – Zenklys
    Apr 26, 2012 at 13:46
  • 2
    @Zenklys: In which case, it doesn't really matter. Just buy RAM especially for MySQL so as much data is cached as possible
    – gbn
    Apr 26, 2012 at 13:59
  • 3
    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.
    – dbenhur
    Apr 28, 2012 at 0:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.