Recommend server upgrade

[Xon]

I don't know about the performance of mySql, but what about moving to a more robust database? I'd wager an oracle license is no more expensive than a new CPU.

While MySQL isnt that great as far as databases go, Oracle is not a cheap alternative since they pay per core for license .

PostgreSQL is apparently quite good, but with more features it is harder to manage.

[Pu-239]

Perhaps this mod will be useful?
http://www.phpbb.com/community/viewtopi ... 6&t=531915

Phrase search would be extremely useful in addition to performance.

Nevermind, it appears InnoDB which you're probably using already doesn't support full text search http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html

++ on Postgres, which does have a fulltext search if you use tsearch2

http://www.lowlevel.cz/log/pivot/entry.php?id=101 - Migration instructions

You'd have to mod the mod to use postgres's search though, AND migrate to postgres, might be too much work and probably more cost effective (time/money wise) to throw more hardware at it for the time being.

There is a phpbb3 mod here http://area51.phpbb.com/phpBB/viewtopic.php?f=4&t=28707

[Braedley]

I would definitely agree on the socket AM2. AM3 CPUs will be backwards compatible with socket AM2, so it's relatively inexpensive to upgrade your CPU when you need to, and then your mobo a year or two later.

This is my plan for next summer, or a little later in the year as the case may be (along with a new GPU, possibly but not likely SLi'ed with my current one), which makes it easier to swap out my mobo and add a HD two years from now.

[phongn]

I would definitely agree on the socket AM2. AM3 CPUs will be backwards compatible with socket AM2, so it's relatively inexpensive to upgrade your CPU when you need to, and then your mobo a year or two later.

Did you post this in the wrong thread? Mike just wants an interim upgrade for his S939 machine.

[The Kernel]

It's the random-write performance that I'm worried about, coupled with the parity cost if Mike has to go software RAID (which I'm certain he would). Don't forget that not only is that machine a server, but it'll face interactive loads as well, further compounding the issue.

In fact, I've never heard anyone seriously advocate RAID 5 for database loads until you brought it up. It's usually RAID 1 or RAID 10 that I hear bandied around. I like RAID 5 (I have a 1TB soft-RAID 5 configuration) but not for databases.

I don't know much about the database loads involved in a BBS, but I would assume that random read performance would be the most critical factor rather than random writes. After all, isn't most load on a BBS users making read queries as they browse through the forums?

And for the hardware vs. software RAID issue, hardware SATA controllers are pretty cheap these days. I'm not sure how much of the CPU is offloaded for a hardware RAID controller, but the low end Promise cards tend to be pretty good.

[phongn]

I don't know much about the database loads involved in a BBS, but I would assume that random read performance would be the most critical factor rather than random writes. After all, isn't most load on a BBS users making read queries as they browse through the forums?

True enough, but if the write performance is disproportionately slow, it block the database non-trivially.

And for the hardware vs. software RAID issue, hardware SATA controllers are pretty cheap these days. I'm not sure how much of the CPU is offloaded for a hardware RAID controller, but the low end Promise cards tend to be pretty good.

Quite a bit of CPU time is offloaded for a "true" hardware RAID controller. I'm not sure if the low-end Promise cards count as one - the drivers, IIRC, are doing all the work there via CPU time. Their higher-end cards, certainly, do 'real' work, but I've usually preferred 3ware for that kind of work.

EDIT: Don't forget that if Mike is forced to use a PCI RAID card, he's going to get a bottleneck from the PCI bus' bandwidth limitations.

[The Kernel]

True enough, but if the write performance is disproportionately slow, it block the database non-trivially.

I think the random read performance benefits would outweigh the hit in write performance, and the overhead on writes is largely exaggerated.

Link 1

Link 2

The above might shed a little more light on this.

Quite a bit of CPU time is offloaded for a "true" hardware RAID controller. I'm not sure if the low-end Promise cards count as one - the drivers, IIRC, are doing all the work there via CPU time. Their higher-end cards, certainly, do 'real' work, but I've usually preferred 3ware for that kind of work.

EDIT: Don't forget that if Mike is forced to use a PCI RAID card, he's going to get a bottleneck from the PCI bus' bandwidth limitations.

I would never use a PCI SATA RAID controller, the PCI-E cards are plentiful and affordable and won't overload the bus.

The Promise SuperTrak EX4350 is a good example of just such a card, and it is also a hardware RAID solution with 64MB of dedicated memory and an onboard RISC processor for parity calcs.

EDIT: Of course this assumes Mike has PCI-E slots on his motherboard. PCI-E x4 of course being the most ideal and a lot of mobos have them, but I'm not sure about his S939 board.

EDIT2: Mike's Asus A8N-E APPEARS to have a PCI-E x4 slot if my eyes are correct.

[The Kernel]

Aha! Pay dirt. I've found an article exactly on point where the mySQL devs themselves recommend a RAID 5 array. Also this article might be extremely useful for general performance tuning and hardware selection. They go into lengthy detail on what upgrades provide the best benefits. Worth a read.

mySQL Server Performance Tuning

[Braedley]

I would definitely agree on the socket AM2. AM3 CPUs will be backwards compatible with socket AM2, so it's relatively inexpensive to upgrade your CPU when you need to, and then your mobo a year or two later.

Did you post this in the wrong thread? Mike just wants an interim upgrade for his S939 machine.

Yeah, except it's going to be hard to find a S939 X2 chip out there, as most X2 chips being produced now are AM2. Also, swapping out the mobo now gives a better upgrade path in the future.

When I first read Mike's post, I saw the AMD X2 and immediately thought he was going with AM2, and if I had read it a little more carefully, I would still suggest AM2 because of the additional upgradeability. But since going with AM2 would also require Mike to replace the RAM, finding a S939 chip would probably do for the time being.

[phongn]

Aha! Pay dirt. I've found an article exactly on point where the mySQL devs themselves recommend a RAID 5 array. Also this article might be extremely useful for general performance tuning and hardware selection. They go into lengthy detail on what upgrades provide the best benefits. Worth a read.

The article notes "RAID 5 is the most commonly used RAID implementation. When funds are tight, and redundancy is clearly more important than performance, it's the best compromise available," and later also writes "RAID 10 is the only way to get the highest performance on your database server without sacrificing redundancy. If you have the budget to justify it, you won't be disappointed."

Most of the earlier sources I were looking at tended to be Oracle-centric, and those probably involve write-heavy loads.

As for Promise, I've just not heard very good things about them, even their high-end hardware-accelerated products. Anecdotal, I know, but when I look around everyone and their mother seemed to recommend ponying up the cash for 3ware (and ideally getting the battery module for it). It seemed to be mostly an issue of driver and management software maturity.

[The Kernel]

The article notes "RAID 5 is the most commonly used RAID implementation. When funds are tight, and redundancy is clearly more important than performance, it's the best compromise available," and later also writes "RAID 10 is the only way to get the highest performance on your database server without sacrificing redundancy. If you have the budget to justify it, you won't be disappointed."

Yes, but I think it's clear that RAID 5 is beneficial to performance over nothing. RAID 10 is great, but it has more cost associated with it.

Most of the earlier sources I were looking at tended to be Oracle-centric, and those probably involve write-heavy loads.

One of the links above talks about Oracle 10g specifically. As long as you have a hardware card, the overhead involved in writes is not a big issue.

As for Promise, I've just not heard very good things about them, even their high-end hardware-accelerated products. Anecdotal, I know, but when I look around everyone and their mother seemed to recommend ponying up the cash for 3ware (and ideally getting the battery module for it). It seemed to be mostly an issue of driver and management software maturity.

I've heard mostly good things about Promise for SMB systems, although there are other choices at the same price point from other vendors besides 3ware.

3ware doesn't actually make a good fit for this project as they don't have a good RAID5 card with PCI-E x4 support and 4 SATA ports. Their lineup is a little thin on the low end stuff.

[The Kernel]

Mike, one other thing occurs to me. Have you thought about using a solid state drive to improve you performance? The technology is new and a tad expensive, but it might be just the ticket if you space requirements are low enough.

The chief benefit of an SSD is going to be read times, not pure data throughput. However for a lot of random reads this could be a godsend for your performance issues.

[InnocentBystander]

It must be fairly large if he has gigabytes of Indexes.

[Ace Pace]

Mike, one other thing occurs to me. Have you thought about using a solid state drive to improve you performance? The technology is new and a tad expensive, but it might be just the ticket if you space requirements are low enough.

The chief benefit of an SSD is going to be read times, not pure data throughput. However for a lot of random reads this could be a godsend for your performance issues.

SSDs are not a valid replacement for conventional hard disks yet.

[The Kernel]

Mike, one other thing occurs to me. Have you thought about using a solid state drive to improve you performance? The technology is new and a tad expensive, but it might be just the ticket if you space requirements are low enough.

The chief benefit of an SSD is going to be read times, not pure data throughput. However for a lot of random reads this could be a godsend for your performance issues.

SSDs are not a valid replacement for conventional hard disks yet.

Those are desktop benchmarks, not server workloads. For random read access SSDs should provide some pretty significant benefits.

EDIT: Some reading about SSDs in database/enterprise scenarios:

Link 1

Link 2

Link 3

SSDs are not THAT expensive and if you just keep the database on it (with the OS on the Raptors) you should be fine.

[Darth Wong]

The RAID idea is interesting, but I wanted to keep my expenditures as low as possible, so I just went with the CPU/RAM upgrades. Adding another Raptor or two and a RAID controller (not to mention the aggravation of backing up and then restoring entire drives) would bloat up my hardware budget.

So, here's the new spec:

CPU: AMD Athlon64 3800+ X2 dual-core
MB: Asus A8N-E
RAM: 4GB DDR400
HD: two WD 10krpm Raptor 74GB SATA drives

MySQL is currently using the my-huge.cnf example configuration file, but with the keybuffer expanded to a monstrous 1.5GB, and the max number of connections increased to 200.

[Chris OFarrell]

Could just be me, but since the server came back online it does appear to be loading new pages significantly faster then it was before.

[Ace Pace]

Could just be me, but since the server came back online it does appear to be loading new pages significantly faster then it was before.

You might be limited by your net, I'm not expecting to see any speed boosts because I know alot of the time spent loading new pages is just due to crappy internet connections from crappy countries.

[Chris OFarrell]

...Except for the slight fact that my internet connection hasn't changed...

[Xon]

Adding another Raptor or two and a RAID controller (not to mention the aggravation of backing up and then restoring entire drives) would bloat up my hardware budget.

You are actually better off getting one of the +500gb drives than a Raptor. The dramatically increased data densities give them awesome preformance, and they are cheaper

[Pu-239]

...Except for the slight fact that my internet connection hasn't changed...

Well, searches don't seem faster for me, and that's not limited to the internet connection. Though the benefit of upgrade is that it won't slow down therest of the board.

[Darth Wong]

I wouldn't expect the board to become lightning fast. I'm just hoping to avoid some of those really long delays and server timeouts that have been getting increasingly common lately. I've seen cases where the board became completely unresponsive for minutes at a time. More multi-processing capability and RAM should cut down on those incidents.

[Ace Pace]

Have you considered looking into Kernels link for possibly improving preformance through MySql tweaking?

[Darth Wong]

Have you considered looking into Kernels link for possibly improving preformance through MySql tweaking?

I've been meaning to look into it more when I have time. Maybe I'll post my my.cnf file here so people can comment on it.

[Scottish Ninja]

It seems to have done some good; I'm getting better load times as well.

And I just tried the search function - it's just about lightning fast now. That always used to aggravate me, so I'll give this upgrade a hearty thumbs up! (I'd give it some money but I don't have any.)