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