More RAM or More Cores for a MySQL Database Server?

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.