Thread: snort, acid and postgres

snort, acid and postgres

From
Jeremy Hefner
Date:
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. The problem I am encountering is that it takes forever for acid
to query the database and delete alerts. Also, there is no way to have
more than one person query the database without having it crawl.  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.



thanks,

Jeremy

Re: snort, acid and postgres

From
"Dann Corbit"
Date:
> -----Original Message-----
> From: Jeremy Hefner [mailto:jeremy@meer.net]
> Sent: Monday, November 17, 2003 12:15 PM
> To: pgsql-general@postgresql.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?

Re: snort, acid and postgres

From
Jeremy Hefner
Date:
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.


Jeremy

Dann Corbit wrote:
>
> > -----Original Message-----
> > From: Jeremy Hefner [mailto:jeremy@meer.net]
> > Sent: Monday, November 17, 2003 12:15 PM
> > To: pgsql-general@postgresql.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?

Re: snort, acid and postgres

From
"Dann Corbit"
Date:
> -----Original Message-----
> From: Jeremy Hefner [mailto:jeremy@meer.net]
> Sent: Monday, November 17, 2003 6:59 PM
> To: Dann Corbit
> Cc: pgsql-general@postgresql.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@meer.net]
> > > Sent: Monday, November 17, 2003 12:15 PM
> > > To: pgsql-general@postgresql.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?
>

Re: snort, acid and postgres

From
Mark Kirkwood
Date:
There are some web pages that provide specific hints for tuning the
snort + ACID combination, e.g:

http://www.andrew.cmu.edu/~rdanyliw/snort/acid_faq.html#faq_c9

Having said that, it is worth collecting the information Dann suggested,
as folk on this list can probably give you database tuning tips that the
standard FAQs may not contain.

regards

Mark
Jeremy Hefner wrote:

>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. The problem I am encountering is that it takes forever for acid
>to query the database and delete alerts. Also, there is no way to have
>more than one person query the database without having it crawl.  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.
>
>