Darth Wong wrote:I'm toying with the idea of getting a new server this summer, once my tax refund comes back and I actually have some cash.
I'm not really familiar with MySQL, but here's my two cents:
A powerful CPU can help
if the database does a lot of computation or complex queries (complex joins, etc). An example is Business Intelligence database because there's a lot of aggregation.
But I don't think it is the case with a PHPBB database. Moreoever, you are correct that I/O (and RAM, if I may add) is always the first bottleneck in your database. In fact, it's always the case in any kind of database.
So put the CPU upgrade as the
last priority after you upgrade the storage and RAM. In fact, unless your current CPU is very obsolete, I don't think you need to upgrade the CPU at all.
Darth Wong wrote:[*]2GB of RAM
Yup, bigger RAM always help because it enables you to increase the buffer cache size,
but hard drive I/O is always unavoidable no matter how large the RAM you have, so put this as
second priority after you upgrade your storage.
Darth Wong wrote:[*]A Western Digital 10k Raptor 74GB SATA hard drive (the exact layout of partitions and the possible need for a second slower, larger HD is something I haven't really planned either, but this is about the cheapest route to a decent server HD solution that I can think of).
Storage upgrade is always your first priority, but small, multiple hard drives is more important than a single, large hard drive; especially when the large hard drive is slower like you said.
Why? Because with multiple physical hard drives, you can spread your database files in multiple physical locations, so it would speed up I/O operation (simultaneous reading/writing) and reduce I/O bottlenecks.
The following is a storage separation scheme in order of priority.
(1) Redologs. Redologs *should* be separated from database files. This should be your first priority in separating the storage, because redologs always constantly write to the hard drive, so it will cause contention.
The good news; redologs are not usually large (unless in Micro$$oft SQL when redologs can bloat beyond proportion), so you just need a small (but fast) hard drive on this one.
Other things need to be separated in this priority are rollback segments (to "undo" transactions) and temporary segments (for sorting), but does MySQL have the equivalents of such things? And if it does, how much the importance of them in a PHPBB database? I guess you don't have to worry too much about rollback and temporary segments.
(2) Index. After you separate the redologs, this should be the next priority if your budget still allows it. Separating index files from table datafiles allow simultaneous reading of the both. This is particularly important in retrieving data using index. Furthermore, an index rebuild won't tax your system so much that way.
Note: you can ignore no (3) and no (4) for your immediate upgrade, but you may want to make notes of them if someday you plan to move the database to a larger server. For example, if you plan to host them on a large, dedicated database server, the following can be your "wish list" to the hosting party.
(3) Table partitioning. If you have *very* large table, you can partition it accross separate hard drive to speed up I/O. In practice, it's almost unpractical to put each table partition on a different hard drive (particularly when you have more than a thousand partitions like I did with my recent costumer), but
the good news is table partitioning still help search performance even though they're placed on the same physical hard drive. Why? Because instead of skimming through a very large file to find (or update) a particular record, the I/O operation just have to "browse" through a much smaller file.
(4) Large, frequently joined tables. If you have more than one large tables frequently joined together, it's advisable to separate them in separate hard drives. Normally I would put this on higher priority, but I don't think this is a critical thing in a PHPBB database.
Darth Wong wrote:[*]Don't really care about the graphics as long as it will handle DVDs and let me play Chromium.[/list]
Wait, do you use the database server for other things as well? Then a huge RAM is a must!
Darth Wong wrote:Anyone have any input they can offer? It's been a while since I closely followed the hardware scene, and I'm a bit out of touch. I want to solve some of the board performance issues without breaking the bank; it's not as if I'm made of money.
So this should be your order of priority:
(1) Hard drives. Try to use multiple hard drives as possible as your budget allows. Small, multiple hard drives is more important than a single large one. I don't know how many hard drives you plan to buy, but
at least the redologs and the datafiles should be separated.
(2) RAM. More RAM never hurts. In fact, huge RAM can save your day (to some extent) if you cannot afford multiple hard drives. In fact,
if multiple hard drives is out of question, then RAM should be your highest priority, because you can have larger buffer cache to compensate the I/O bottleneck.
(3) CPU. Only put this on last priority. CPU bottleneck rarely happens in a database, and I don't think it's the case with your database. Do you *really* need a CPU upgrade? Only upgrade your CPU if the answer is absolutely yes.
)
).
).
