Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Well, it has to have one. –  ConcernedOfTunbridgeWells Apr 26 '12 at 17:27
add comment

3 Answers

up vote 19 down vote accepted

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

share|improve this answer
    
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 '12 at 17:49
    
Thanks for the information about PostgreSQL. I will check the book. ;) –  Zenklys Apr 26 '12 at 17:51
1  
Good stuff from pages 21-23 –  RolandoMySQLDBA Apr 26 '12 at 17:56
    
I can see the wisdom of your second paragraph when compared to the pages 21-23. –  RolandoMySQLDBA Apr 26 '12 at 18:00
add comment
  • 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)

share|improve this answer
    
Thanks, for the answer. SSD's are a good choice then? Over CPU power? –  Zenklys Apr 26 '12 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 '12 at 13:43
    
20- 30 gb maximum. 10 to 1 read/write ratio, only small data, OLTP. –  Zenklys Apr 26 '12 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 '12 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 '12 at 0:24
show 1 more comment

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.

share|improve this answer
2  
Great answer. I'm going to check all those links, thanks. –  Zenklys Apr 26 '12 at 14:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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