[Snort-devel] Composite keys in Snort DB schema

Kreimendahl, Chad J Chad.Kreimendahl at ...1167...
Mon Aug 23 13:44:04 EDT 2004

There's talk I hear often about the database writes taking too much
overhead... but I've yet to experience it.

and beyond that...

I've redesigned the structure for writing performance purposes, but only
from the perspective of Oracle.   This involves the merging of some
tables (such as all the iphdr related ones (tcp/udp/icmp)...).  This
makes queries which typically join 6 tables only have to join 3.   Also,
inserts using sequences aren't vulnerable to things such as colliding
snorts... (like when there are two snorts with the same config running
on the same interface).   Though that does mean you'll end up with
double the data, it does prevent the errors associated with holding the
counter in the application.   I've also found that taking it down to one
single event_id and using that to link everything prevents massive I/O
in oracle in relation to full table scans when doing large-ass queries.

Though I know that the merger of all the iphdr tables together goes
against it, the rest of this is basic database design...   If you or
anyone else can provide some time to me, I'd like to get this new design
pushed out, along with the output modules. 

-----Original Message-----
From: Christian Robottom Reis [mailto:kiko at ...2474...] 
Sent: Thursday, August 19, 2004 10:01 AM
To: snort-devel at lists.sourceforge.net
Cc: venere at ...2613...; Erlison
Subject: [Snort-devel] Composite keys in Snort DB schema

Hello there,

    I've been taking a look at Snort's DB schema (using the recently
updated diagram Chris provided) and a question has popped up. Is there a
reason why we use a composite key (cid and sid) for the tables beyond
wanting to have sequential cids for each sensor? It seems it complicates
things somewhat since we need to spread out these keys over all the
tables in the event-specific snort tables. 

Wouldn't it be simpler -- white still fully functional -- to have a
single globally unique sequential event ID?

Take care,
Christian Robottom Reis | http://async.com.br/~kiko/ | [+55 16] 3361

SF.Net email is sponsored by Shop4tech.com-Lowest price on Blank Media
100pk Sonic DVD-R 4x for only $29 -100pk Sonic DVD+R for only $33
Save 50% off Retail on Ink & Toner - Free Shipping and Free Gift.
Snort-devel mailing list
Snort-devel at lists.sourceforge.net

More information about the Snort-devel mailing list