Thread: 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. 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
> -----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?
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?
> -----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? >
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. > >