From: | "Gavin M(dot) Roy" <gmr(at)justsportsusa(dot)com> |
---|---|
To: | Loki <loki(at)fatelabs(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: PostgreSQL + IDS load/latency help |
Date: | 2003-03-27 16:23:57 |
Message-ID: | 3E83259D.8090909@justsportsusa.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-general |
Have you used "explain" on your queries to make sure they are not using
seq scan? I've got tens of millions of records in my master database
and get 1 second response times with the right indexes created.
Gavin
Loki wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Dennis Gearon | 2003-03-27 16:49:19 | Re: [GENERAL] PostgreSQL + IDS load/latency help |
Previous Message | Loki | 2003-03-27 15:12:09 | PostgreSQL + IDS load/latency help |
From | Date | Subject | |
---|---|---|---|
Next Message | Dennis Gearon | 2003-03-27 16:49:19 | Re: [GENERAL] PostgreSQL + IDS load/latency help |
Previous Message | Tad Marko | 2003-03-27 16:07:28 | Re: Earth distance |