On 3/22/06, Steve Shockley <[EMAIL PROTECTED]> wrote: > 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.
How many days do you keep around? What's the query? What does EXPLAIN ANALYSE tell you? > 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'm not intimately familiar with the Xeon SMP cache contention issue, but I do recall it being discussed quite thoroughly on the pgsql mailing lists. I don't know if it affects your setup, but it might be worth doing some cursory investigation. > 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. I highly doubt it's a hardware issue. > 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? I'm inclined to say you'll gain much more from query optimization than turning OS knobs. aaron.glenn
