[Snort-devel] last_cid in new database scheme v106

Kreimendahl, Chad J Chad.Kreimendahl at ...1167...
Mon Sep 23 07:36:04 EDT 2002

Not to toot the non-free databases out there... But we had a similar
problem when we first attempted to use MySQL.  We already had another
IDS in house, and we were testing snort with mysql and demarc.  Well...
After about 100,000 records, response times went beyond our browser's
timeouts.  All of this just to load a very simple query.   I personally
believe that for people in a non-corp environment that mysql is the way
to go, so long as you clean up the data when it gets excessive.

You may consider using a different database, I'm sure you'll find you
get much superior performance with large amounts of data.  In our
current setup, there's no difference in performance between 100 events
and 1,000,000.  The only trick I've had to employ is the occasional
rebuilding of indexes, since they get fragmented when they grow.  Of
course, if you build giant index spaces initially, this may never be an

-----Original Message-----
From: Christian Mock [mailto:cm at ...1206...] 
Sent: Monday, September 23, 2002 6:09 AM
To: snort-devel at lists.sourceforge.net
Subject: Re: [Snort-devel] last_cid in new database scheme v106

[This is a late answer, I've been to busy implementing what I'll 
describe lately...]

On Fri, Sep 13, 2002 at 08:19:34PM -0400, Ian Macdonald wrote:
> I would love to see a different database structure, as long as we keep
> existing spo for compatibility. For databases that support views it
> also be possible to build one that maps back to the original db

We're doing a managed IDS service based on snort and started out with
and a few local hacks to it. However, when the number of alerts stored
grew, performance degraded terribly (when we got to the point when
the DB couldn't be cached in RAM anymore).

We decided to completely ditch the snort DB schema and ACID and build
own. The system is proprietary, but the important points I think should
be considered are:

- simpler table structure: the snort DB schema is normalized, which
  IMO complicates the building of queries and doesn't help performance
  (at least not with postgresql, we tried mysql too, wasn't a big
  win). We ended with a a pair of event and packet tables, where the
  table has an eid (event id) which is a serial, sensor_id, timestamp,
  signature ID, source and destination IP addresses and ports (which 
  duplicate as ICMP type/code), IP protocol and an incident ID 
  (as we assign alerts to incidents); the packet table only has the 
  eid and the packet data.

  This brings a simple table structure that has a fixed and small
  record size for the "event" table and variable size for the seldomly-
  accessed packet table.

- build a "multi-stage" table "pipeline". There's a pair of input tables
  where a simple perl script imports the data from unified log files,
  "active" tables for stuff that's to-be-examined, and "archive" tables
  for stuff that is already assigned to incidents. This means the most
  accessed tables, "active", stay small and fast.

- build a helper table that has statistics on the events on some
  key columns, like IP addresses, ports, signatures. I recognize this
  may make you database people cringe, but it allows the type of queries
  that were extremely slow with ACID and a lot of data (i.e. "show me
  the top N signatures in the last N hours) very fast. The helper table
  is updated when adding/moving/deleting alerts by means of a perl
  function -- we tried to use triggers in postgres, but performance
  was terrible.

- Note that we didn't rewrite spo_database, as the idea of directly
  into the DB didn't seem too smart to me anyways -- we're writing
  alert files, transfer those and import them, thereby achieving more
  resilience in case of DB downtime, network outages and similiar

- As we decided to go with postgresql (for the transaction support), 
  we're using non-portable features like the 'inet' (ip address)
  data type, referential integrity etc.

The new system seems to work OK so far, the response times in the
interface are short, and it seems to hold up to floods of alerts and 
scale well.



Christian Mock                          Wiedner Hauptstrasse 15
Senior Security Engineer                1040 Wien
CoreTEC IT Security Solutions GmbH      +43-1-5037273

More information about the Snort-devel mailing list