More RAM or More Cores for a MySQL Database Server?

Question

Here's the scenario, which I'd love your expert advice on:

I've got about 2GB of database currently, maybe double that in a year. I want the optimum server performance for the dedicated DB server I'm going to order, which will be used as the backend for a fairly heavy traffic site running WordPress, forum, and mediawiki. Most of the database traffic should be read-only.

So the question is, do I really need more than 4GB of RAM? And should I go for 8 cores or just 4? Is one more important than the other?

[edit] Just as a followup, ended up getting a good deal on an 8-core server with 8 GB of RAM, so went with that. Glad to know that I'll have lots of room to grow.

Solution 1

interesting question. Please try to define the bottleneck of your current application first. From your description and some practical knowledge, I would guess that you have four possible bottlenecks:

• nic
• ram
• hd throughput
• cpu (speed)
• cpu (cores)

Now ram would not be an issue, because, since you db is only 2G, you could buffer not only all keys and indizes, but actually the entire db in ram having 2G, if you size (MyISAM-) key_buffer and/or innodb_buffer_pool_cache accordingly! You would probably be fine even with ram < dbsize, because usually not all parts of a db are used at the same time (ymmv).

Of course ram is also used for memory tables, sorting and ordering and some join operations, so you should look at the complexity of queries your database does, and whether it returns very large resultsets. I do not know, but I believe neither wordpress nor mediawiki do really complex operations there. So just get a moderate amount of ram.

HD is the usual bottleneck for any large database, but yours can be cached in ram anyway, and you say that you have mostly read operations, so I'd say: for a normal large database, the rule of thumb would be: hd throughput is a main bottleneck, so: 1. buy hds, and 2. do not necessarily buy the fastest ones, but buy many of them. In you case, I'd say: its all cached anyway.

As for cores: MySQL can indeed take advantage of many cores, but it mainly needs them for complex calculations, procedural programs and sort and merge operations. Siple queries like "Select * from table" or even select * from table where..." will not benefit much from more cores. Many connections will gain minor benefit. My guess is you should prefer a faster processor over many cores.

I believe you should check for the nic as the main bottleneck, and think about a second (Third, fourth...) nic, depending on the amount of traffic on your primary interface.

So, to sum it all up, I'd spend my money on (in that order): - more than one nic (if that is indeed a bottleneck) - a fast processor - 2 - 4 cores - 2-4G ram with the option to plug in 8G later (cheaper than cores, anyway) - best posible disk subsystem (you do not need much now, but it will help you expand later)

Cheers, Nik.

Solution 2

As nikb states it's important to understand your application but in lieu of knowing this I'd suggest the following rule-of-thumb.

Unless only a part of your DB is read from or written to routinely then no amount of extra cores will match the performance benefits of having all of your DBs and ancillary data (indexes etc) in memory. Extra cores will be useful if you have lots of concurrent clients hitting your or you do a lot of background work such as reporting or indexing.

If I were you I'd pick a fairly recent Intel 55xx-series based dual-socket server and just buy one 4-core, hyperthreaded, processor with 3 x 4GB DDR memory modules (don't let someone sell you 2/4/8 etc, 55xx's use 3,6,9 etc ok). That way not only can you very quickly and easily add a second identical processor in the future but as those chips are plug compatible with the upcoming 8-core chips you could always just swap out the one you initially get.

Hope this is of some help.

Comments

[simplename]

Don't forget that you'll need 64 bit OS and application binaries to make the most of the 4GB RAM.

[The How-To Geek]

That's useful to know, didn't realize the 55xx used RAM in sets of 3.

[Chopper3]

Yeah, triple-channel, stupidly quick :)

[Adam Gordon Bell]

Remember the more disks one buys, the more backup-and-recovery strategies need to be applied for each disk.

[Helvick]

Yep super quick but absolutely must be set up in triples (or 6x for dual socket). And build it by preference with fewer DIMM's (3x2GB rather than 6x1GB, 3x4GB rather than 6x2GB or 12x1GB). Lower DIMM counts support faster DIMM speeds and the difference can be up to 30%. The difference between a very badly configured Xeon 55xx RAM (e.g. 4GB as 4x1GB DIMM's running at 800Mhz using only 2 channels) vs well set up (3GB as 3x1GB DIMM's running at 1333Mhz) is severe -the theoretical max bandwidth goes from ~12GB/sec to ~30GB/sec.