Jim Razmus wrote: > * Steve Shockley <[EMAIL PROTECTED]> [060322 21:35]: > > I've currently got a server running syslog-ng (1.6.9) with > > PostgreSQL (8.1.1) on a 3.9 snapshot from March 1. The setup has > > been working well for a while, but I've recently been told to have > > it accept syslog for a couple of anti-spam appliance devices, and > > they generate between 1-2 million syslog entries per day, and that > > seems to kill PostgreSQL's performance. Any query which needs to > > iterate the whole database will take 10-15 minutes after a day or > > so, and that's no good for a web front-end. > > > > I'm running it on a Compaq DL580, which is a quad Xeon 700, 2gb RAM, > > Compaq 3200 controller (cac) with 32mb cache RAM, and four 36gb 10k > > drives in RAID5. > > > > I can also move the system to a Compaq ML530, which is a dual Xeon > > 1000, 2gb RAM, Compaq 5300 controller (ciss) with 128mb cache, and > > four 36gb 10k drives plus 10 18gb 10k drives. Sure, that's better, > > but I'm not sure it's enough better. > > > > When I'm accepting syslog from the mail devices, a postgres process > > holds steady using 5-10% CPU, but the load average hovers between 1 > > and 2. > > > > I don't normally turn knobs, but the few knobs I've turned have > > been: > > > > kern.seminfo.semmni=256 > > kern.seminfo.semmns=2048 > > kern.shminfo.shmmax=201326592 > > > > set bufcachepct to 30 via config > > > > fsync=off (ick, I'd like to turn this back on) > > shared_buffers=16384 > > > > autovacuum is turned on. I vacuum analyze the database nightly, > > doing > > so doesn't seem to effect performance. > > > > Is there anything else I can do to improve performance on this > > hardware? It seems that I'm not using very much memory (around > > 512mb of 2gb), what's the best way to tell PostgreSQL to use more > > memory, in a way that will actually make a difference? > > A good data model for starters. You may want to match the access > paths used by common queries with a table and index structure that > supports them. > > Unless your running Teradata databases, your not going to iterate over > record sets like that quickly unless you get into unreasonable > hardware. You could check out solid state disks, etc. But really a > good data model goes a long way. > > Hire or consult a good DBA. Your unlikely to beat this problem with > just hardware.
I strongly agree with Jim's comments. Also, OpenBSD performance notwithstanding, you'll get better performance out of RAID10 for your DBs than RAID5.
