* 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. Jim
