Re: Problems with base and postgresql

[Bamm Visscher <bamm.visscher () gmail com>]

Another problem with large IDS DBs in general (but especially
noticeable in postgresql or mysql with InnoDB) is archiving or
managing the data. At some time, you are going to reach the point at
which A) you have no more room on your HD to store the data or B) the
DB has become too large and performance has drawn to a standstill. 
When you reach this point, you'll probably want to archive the oldest
data to make room for new data and you should expect this to become a
regular task.  Removing a couple of million rows from a DB that has
10+ million rows will take time, especially doing the required tasks
to reclaim the used space (vacuum anlayze and vacuum full or
mysqlcheck -a -o type stuff).

Anyway, I just thought I'd throw that out there, since many get well
into their deployment before this particular DB problem whacks them
out of the blue. It's always best to be prepared.  We are using MERGE
tables and what we refer to as  PMPDB (Poor Mans Partioned DB) as our
solution.  SInce we log sancp data (flow data) to the DB, it grows
much faster than the alert tables. Users report decent performance
with 100+ million rows in their DB and deleting old data to make room
for new data is as simple as dropping the appropriate tables and
rebuilding the MERGE table.

Bammkkkk

On Apr 5, 2005 1:54 PM, Michael Stone <mstone+snort () mathom us> wrote:
> On Tue, Apr 05, 2005 at 11:51:09AM -0700, Aaron Glenn wrote:
> > On Apr 4, 2005 7:52 AM, Michael Stone <mstone+snort () mathom us> wrote:
> > > I'd suggest not doing this, it'll really hurt if your system ever
> > > crashes. Base is really not tuned for postgres right now--if you want
> > > decent performance you're better off just running mysql.
> >
> > What, specifically, needs "tuning" ?
>
> For example, there are a lot of instances of count(*) in the code. mysql
> shortcuts that operation by keeping a counter, but in postgres that
> triggers a sequential scan of the table--which can take a while for a
> large table. There are also some spots where queries could be rewritten
> to be more efficient by throwing in some joins instead of doing multiple
> queries and other such items. I've been poking at the code to speed
> things up, but now I'll have to reintegrate with the new release before
> sending up a patch & I still need to go back and test that mysql still
> works after the changes.
>
> Mike Stone

-- 
sguil - The Analyst Console for NSM
http://sguil.sf.net


-------------------------------------------------------
SF email is sponsored by - The IT Product Guide
Read honest & candid reviews on hundreds of IT Products from real users.
Discover which products truly live up to the hype. Start reading now.
http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
_______________________________________________
Snort-users mailing list
Snort-users () lists sourceforge net
Go to this URL to change user options or unsubscribe:
https://lists.sourceforge.net/lists/listinfo/snort-users
Snort-users list archive:
http://www.geocrawler.com/redir-sf.php3?list=snort-users