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.
Best Answer
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:
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.