Thread: Performance nightmare with dspam (urgent)
We've seen PostgreSQL performance as a dspam database be simply stellar on some machines with absolutely no tuning to the postgres.conf, and no statistics target altering. Some months ago, I moved my domains from a crusty old generic PIII 733 to a brand new Athlon 3000+ server that I was leasing. The load was very high, and it was all PostgreSQL. I cried and screamed on #postgresql for hours, and eventually it was discovered that the following command fixed everything and suddenly performance was lightning fast again: alter table "dspam_token_data" alter "token" set statistics 200; analyze; We had set up about 200 domains on a SuperMicro P4 2.4GHz server, and it was working great too (without the above tweak!), but then the motherboard started having issues and the machine would lock up every few weeks. So we moved everything to a brand new SuperMicro P4 3.0GHz server last week, and now performance is simply appalling. Whereas before the load average was something around 0.02, it's now regularly at 4 (all postgres), and there's hundreds of messages in the queue waiting. Lots of people are complaining about slow mail delivery, and I've been up for 2 days trying to fix this with no success. Originally, the problem was a lot worse, but I spent a lot of time tuning the postgresql.conf, and changed the statistics target shown above, and this made things okay (by okay I mean that it's okay at night, but during the day several hundred messages will regularly be waiting for delivery). I found this response to my original post, and tried every single suggestion in it, which has not helped: http://archives.postgresql.org/pgsql-performance/2004-11/msg00416.php I'm sorry to come begging for help, but this is a MAJOR problem with no logical explanation, and is almost certainly the fault of PostgreSQL, because the database and contents have been identical across all the hosts, and some work beautifully with no tuning whatsoever; so I don't feel I'm wrong in placing blame... All machines run Gentoo Linux. All have the same package versions. Disk I/O doesn't seem to be related - the 733MHz server had a 33MB/s IDE drive, the 2.4GHz server had a RAID 5 with 3 ultra320 drives: neither of those required any tuning. The new 3.0GHz has a mirror raid with 2 ultra320 drives, and the 3000+ that tuning fixed had an ultra160 disk not in a RAID. I really like PostgreSQL, and really don't want to use MySQL for dspam, but if I can't get this worked out ASAP I'm going to have to change for the sake of our customers. Any help is GREATLY appreciated! I'm online on instant messengers (contact IDs shown below), monitoring my email, and will be on #postgresql on Freenode. Cheers, -- Casey Allen Shobe | http://casey.shobe.info cshobe@seattleserver.com | cell 425-443-4653 AIM & Yahoo: SomeLinuxGuy | ICQ: 1494523 SeattleServer.com, Inc. | http://www.seattleserver.com
On Wednesday 01 June 2005 20:19, Casey Allen Shobe wrote: > We've seen PostgreSQL performance as a dspam database be simply stellar on > some machines with absolutely no tuning to the postgres.conf, and no > statistics target altering. Wow. That took a phenomenally long time to post. I asked on IRC, and they said it is "normal" for the PG lists to bee so horribly slow. What gives? I think you guys really need to stop using majordomo, but I'll avoid blaming that for the time being. Maybe a good time for the performance crew to look at the mailing list software instead of just PG. > We had set up about 200 domains on a SuperMicro P4 2.4GHz server, and it was > working great too (without the above tweak!), but then the motherboard > started having issues and the machine would lock up every few weeks. So we > moved everything to a brand new SuperMicro P4 3.0GHz server last week, and > now performance is simply appalling. Well, we actually added about 10 more domains right around the time of the move, not thinking anything of it. Turns out that simply set the disk usage over the threshhold of what the drive could handle. At least, that's the best guess of the situation - I don't really know whether to believe that because the old machine had a 3-disk RAID5 so it should have been half the speed of the new machine. However, analyzing the statements showed that they were all using index scans as they should, and no amount of tuning managed to reduce the I/O to an acceptable level. After lots of tuning, we moved pg_xlog onto a separate disk, and switched dspam from TEFT to TOE mode (which reduces the number of inserts). By doing this, the immediate problem was alleviated. Indeed the suggestion in link in my previous email to add an extra index was a BAD idea, since it increased the amount of work that had to be done per write, and didn't help anything. Long-term, whenever we hit the I/O limit again, it looks like we really don't have much of a solution except to throw more hardware (mainly lots of disks in RAID0's) at the problem. :( Fortunately, with the above two changes I/O usage on the PG data disk is a quarter of what it was, so theoretically we should be able to quadruple the number of users on current hardware. Our plan forward is to increase the number of disks in the two redundant mail servers, so that each has a single ultra320 disk for O/S and pg_xlog, and a 3-disk RAID0 for the data. This should triple our current capacity. The general opinion of the way dspam uses the database among people I've talked to on #postgresql is not very good, but of course the dspam folk blame PostgreSQL and say to use MySQL if you want reasonable performance. Makes it real fun to be a DSpam+PostgreSQL user when limits are reached, since everyone denies responsibility. Fortunately, PostgreSQL people are pretty helpful even if they think the client software sucks. :) Cheers, -- Casey Allen Shobe | http://casey.shobe.info cshobe@seattleserver.com | cell 425-443-4653 AIM & Yahoo: SomeLinuxGuy | ICQ: 1494523 SeattleServer.com, Inc. | http://www.seattleserver.com
Casey Allen Shobe wrote: > On Wednesday 01 June 2005 20:19, Casey Allen Shobe wrote: > ... > Long-term, whenever we hit the I/O limit again, it looks like we really don't > have much of a solution except to throw more hardware (mainly lots of disks > in RAID0's) at the problem. :( Fortunately, with the above two changes I/O > usage on the PG data disk is a quarter of what it was, so theoretically we > should be able to quadruple the number of users on current hardware. > Be very careful in this situation. If any disks in a RAID0 fails, the entire raid is lost. You *really* want a RAID10. It takes more drives, but then if anything dies you don't lose everything. If you are running RAID0 and you *really* want performance, and aren't concerned about safety (at all), you could also set fsync=false. That should also speed things up. But you are really risking corruption/data loss on your system. > Our plan forward is to increase the number of disks in the two redundant mail > servers, so that each has a single ultra320 disk for O/S and pg_xlog, and a > 3-disk RAID0 for the data. This should triple our current capacity. I don't know if you can do it, but it would be nice to see this be 1 RAID1 for OS, 1 RAID10 for pg_xlog, and another RAID10 for data. That is the recommended performance layout. It takes quite a few drives (minimum of 10). But it means your data is safe, and your performance should be very good. > > The general opinion of the way dspam uses the database among people I've > talked to on #postgresql is not very good, but of course the dspam folk blame > PostgreSQL and say to use MySQL if you want reasonable performance. Makes it > real fun to be a DSpam+PostgreSQL user when limits are reached, since > everyone denies responsibility. Fortunately, PostgreSQL people are pretty > helpful even if they think the client software sucks. :) > I can't say how dspam uses the database. But they certainly could make assumptions about how certain actions are done by the db, which are not quite true with postgres. (For instance MySQL can use an index to return information, because Postgres supports transactions, it cannot, because even though a row is in the index, it may not be visible to the current transaction.) They also might be doing stuff like "select max(row)" instead of "select row ORDER BY row DESC LIMIT 1". In postgres the former will be a sequential scan, the latter will be an index scan. Though I wonder about "select max(row) ORDER BY row DESC LIMIT 1". to me, that should still return the right answer, but I'm not sure. > Cheers, Good luck, John =:->
Attachment
> PostgreSQL and say to use MySQL if you want reasonable performance.
If you want MySQL performance and reliability with postgres, simply run
it with fsync deactivated ;)
I'd suggest a controller with battery backed up cache to get rid of the 1
commit = 1 seek boundary.
> Makes it
> real fun to be a DSpam+PostgreSQL user when limits are reached, since
> everyone denies responsibility. Fortunately, PostgreSQL people are
> pretty
> helpful even if they think the client software sucks. :)
>
> Cheers,
On Mon, Jun 06, 2005 at 10:08:23AM -0500, John A Meinel wrote: >I don't know if you can do it, but it would be nice to see this be 1 >RAID1 for OS, 1 RAID10 for pg_xlog, That's probably overkill--it's a relatively small sequential-write partition with really small writes; I don't see how pg_xlog would benefit from raid10 as opposed to raid1. Mike Stone
On Monday 06 June 2005 15:08, John A Meinel wrote: > Be very careful in this situation. If any disks in a RAID0 fails, the > entire raid is lost. You *really* want a RAID10. It takes more drives, > but then if anything dies you don't lose everything. We have redundancy at the machine level using DRBD, so this is not a concern. > I don't know if you can do it, but it would be nice to see this be 1 > RAID1 for OS, 1 RAID10 for pg_xlog, and another RAID10 for data. That is > the recommended performance layout. It takes quite a few drives (minimum > of 10). But it means your data is safe, and your performance should be > very good. The current servers have 4 drive bays, and we can't even afford to fill them all right now...we just invested what amounts to "quite a lot" on our budget for these 2 servers, so replacing them is not an option at all right now. I think the most cost-effective road forward is to add 2 more drives to each of the existing servers (which currently have 2 each). Cheers, -- Casey Allen Shobe | http://casey.shobe.info cshobe@seattleserver.com | cell 425-443-4653 AIM & Yahoo: SomeLinuxGuy | ICQ: 1494523 SeattleServer.com, Inc. | http://www.seattleserver.com
Michael Stone wrote: > On Mon, Jun 06, 2005 at 10:08:23AM -0500, John A Meinel wrote: > >> I don't know if you can do it, but it would be nice to see this be 1 >> RAID1 for OS, 1 RAID10 for pg_xlog, > > > That's probably overkill--it's a relatively small sequential-write > partition with really small writes; I don't see how pg_xlog would > benefit from raid10 as opposed to raid1. > Mike Stone > pg_xlog benefits from being super fast. Because it has to be fully synced before the rest of the data can be committed. Yes they are small, but if you can make it fast, you eliminate that overhead. It also benefits from having it's own spindle, because you eliminate the seek time. (Since it is always appending) Anyway, my point is that pg_xlog isn't necessarily tiny. Many people seem to set it as high as 100-200, and each one is 16MB. But one other thing to consider is to make pg_xlog on a battery backed ramdisk. Because it really *can* use the extra speed. I can't say that a ramdisk is more cost effective than faster db disks. But if you aren't using many checkpoint_segments, it seems like you could get a 1GB ramdisk, and probably have a pretty good performance boost. (I have not tested this personally, though). Since he is using the default settings (mostly) for dspam, he could probably get away with something like a 256MB ramdisk. The only prices I could find with a few minutes of googleing was: http://www.cenatek.com/store/category.cfm?Category=15 Which is $1.6k for 2GB. But there is also a product that is being developed, which claims $60 for the PCI card, you supply the memory. It has 4 DDR slots http://www.engadget.com/entry/1234000227045399/ And you can get a 128MB SDRAM ECC module for around $22 http://www.newegg.com/Product/Product.asp?Item=N82E16820998004 So that would put the total cost of a 512MB battery backed ramdisk at $60 + 4*22 = $150. That certainly seems less than what you would pay for the same speed in hard-drives. Unfortunately the Giga-byte iRam seems to just be in the demo stage. But if they aren't lying in the press releases, it would certainly be something to keep an eye on. John =:->
Attachment
On Mon, Jun 06, 2005 at 10:52:09AM -0500, John A Meinel wrote: >pg_xlog benefits from being super fast. Because it has to be fully >synced before the rest of the data can be committed. Yes they are small, >but if you can make it fast, you eliminate that overhead. It also >benefits from having it's own spindle, because you eliminate the seek >time. (Since it is always appending) Eliminating the seeks is definately a win. >Anyway, my point is that pg_xlog isn't necessarily tiny. Many people >seem to set it as high as 100-200, and each one is 16MB. It's not the size of the xlog, it's the size of the write. Unless you're writing out a stripe size of data at once you're only effectively writing to one disk pair at a time anyway. (Things change if you have a big NVRAM cache to aggregate the writes, but you'd need a *lot* of transaction activity to exceed the 50MB/s or so you could get from the single raid1 pair in that scenario.) Mike Stone
On Thu, 2 Jun 2005 06:19 am, Casey Allen Shobe wrote: > I found this response to my original post, and tried every single suggestion > in it, which has not helped: > > http://archives.postgresql.org/pgsql-performance/2004-11/msg00416.php > > I'm sorry to come begging for help, but this is a MAJOR problem with no > logical explanation, and is almost certainly the fault of PostgreSQL, because > the database and contents have been identical across all the hosts, and some > work beautifully with no tuning whatsoever; so I don't feel I'm wrong in > placing blame... I would personally strongly suggest turing on logging on the PG server for about an hour, sifting through the runtimes for the queries and finding which ones are taking all the time. I'd then run explain analyze and see what is happening. I have heard you could get much better performance by rewriting some of the dspam queries to use PG features. But I've never used dspam, so I can't verify that. But a quick look through the dspam pg driver source... /* Declare Cursor */ #ifdef VIRTUAL_USERS strcpy (query, "DECLARE dscursor CURSOR FOR SELECT DISTINCT username FROM dspam_virtual_uids"); #else strcpy (query, "DECLARE dscursor CURSOR FOR SELECT DISTINCT uid FROM dspam_stats"); #endif If that's run often, it probably won't give the best performance, but that's a guess. Again I'd suggest turning up the logging. > > All machines run Gentoo Linux. All have the same package versions. Disk I/O > doesn't seem to be related - the 733MHz server had a 33MB/s IDE drive, the > 2.4GHz server had a RAID 5 with 3 ultra320 drives: neither of those required > any tuning. The new 3.0GHz has a mirror raid with 2 ultra320 drives, and the > 3000+ that tuning fixed had an ultra160 disk not in a RAID. > > I really like PostgreSQL, and really don't want to use MySQL for dspam, but if > I can't get this worked out ASAP I'm going to have to change for the sake of > our customers. Any help is GREATLY appreciated! Again I'd suggest turning up the logging. > > I'm online on instant messengers (contact IDs shown below), monitoring my > email, and will be on #postgresql on Freenode. > > Cheers,