Re: snort, acid and postgres

> -----Original Message-----
> From: Jeremy Hefner [mailto:jeremy(at)meer(dot)net]
> Sent: Monday, November 17, 2003 6:59 PM
> To: Dann Corbit
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] snort, acid and postgres
>
>
> Well, it usually takes atleast 15-20 minutes to get results
> back on a database that has many alerts in it. The system
> itself is dual pentium 4 1 GHZ with 1 GByte of RAM. I have
> been talking to a few people and they mentioned trying to
> update the memory space for which postgres uses by tweaking
> freebsd and also postrgesql.conf itself? I have done some
> vacuuming on the database itself. I guess I just need to
> optimize the speed of the system as much as possible.

I doubt very much if that is the real core of the problem.

Probably, you need to examine:
1. Your queries. (especially the slow ones if you can identify them)
2. Your database schema.

I am guessing a judicious choice of index will give you orders of
magnitude speedup.

The information you have provided cannot be used for anything more than
a guess. So I might be way off base.

Do you know the SQL for the queries that are taking the longest time?

> Jeremy
>
> Dann Corbit wrote:
> >
> > > -----Original Message-----
> > > From: Jeremy Hefner [mailto:jeremy(at)meer(dot)net]
> > > Sent: Monday, November 17, 2003 12:15 PM
> > > To: pgsql-general(at)postgresql(dot)org
> > > Subject: [GENERAL] snort, acid and postgres
> > >
> > >
> > > Ok, so here is my problem. I am running snort with ACID
> as the query
> > > interface and FreeBSD with Postgresql 7.2 as the back end
> database
> > > system.
> >
> > What kind of hardware is the FreeBSD OS running on? How
> much memory?
> > What sort of disk subsystem?
> >
> > > The problem I am encountering is
> > > that it takes forever for acid to query the database and delete
> > > alerts.
> >
> > How long is "forever"? That seems a bit vague.
> >
> > > Also, there is no way to have more than one
> > > person query the database without having it crawl.
> >
> > There are PostgreSQL database systems with thousands of
> simultaneous
> > users. Perhaps you can clarify your question a bit.
> >
> > > Is there
> > > anyone out there that has experience tweaking postgres so that it
> > > performs faster in this setup? The database is out of the
> box with
> > > no tweaks to it.
> >
> > Probably, some additional information would be helpful.
> >
> > If you know the queries that you are sending, try an analyze to see
> > what sort of plan is used.
> >
> > Have you done any vacuum operations on your database?
>