PostgreSQL + IDS load/latency help

From: "Loki" <loki(at)fatelabs(dot)com>
To: focus-ids(at)securityfocus(dot)com, pgsql-general(at)postgresql(dot)org, pgsql-admin(at)postgresql(dot)org
Subject: PostgreSQL + IDS load/latency help
Date: 2003-03-27 15:12:09
Message-ID: web-1582843@ackind.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-general

Lists:

We have a PostgreSQL database full of over 500,000 events.
When the database has a small number of events, to
retrieve a specific event stored in that db takes a mere
few seconds.

However, as the database grew in size to where it is now,
it takes over 15-20 seconds to get information back from a
query to that database. We have tried everything,
including vacuum, which someone else recommended to me.

I noticed when running top(exec) on the system that the
PostgreSQL process eventually eats up ALL available RAM
(half a gig of RAM) and forces the system into scratch
space. When rebooting the machine the query time is
greatly improved back down to a reasonable query time,
(obviously because it hasnt eaten up all the memory yet.)

Am I correct in saying that 500,000 events shouldn't be a
problem? Is there anyone out there with more than 500,000
events in their DB than what we currently have?

Can anyone recommend anything to try or do to rectify the
situation. 15-20 seconds to get the information queried
from the database is unacceptable and can not work. Please
advise.

* We are running Snort 1.9.1 storing events to a custom
PostgreSQL database. Dumping the database and creating a
new one every so often is also not an option. Can anyone
provide assistance? Please advise.

For those needing more details on our setup, etc. I am not
currently able to log into the system. Please email me and
I can provide you more details offline.

Sincerely,

Eric Hines
Internet Warfare and Intelligence
Fate Research Labs
http://www.fatelabs.com

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Gavin M. Roy 2003-03-27 16:23:57 Re: PostgreSQL + IDS load/latency help
Previous Message Rajesh Kumar Mallah 2003-03-27 14:02:01 Re: contrib/dbsize

Browse pgsql-general by date

  From Date Subject
Next Message Peter Nixon 2003-03-27 15:34:57 hex to decimal and back again
Previous Message Richard Huxton 2003-03-27 15:07:44 Possible bug in date(timestamptz)?