[Snort-devel] last_cid in new database scheme v106

Kreimendahl, Chad J Chad.Kreimendahl at ...1167...
Thu Sep 19 09:14:04 EDT 2002

I think the goal was to separate some of the code that can optimize
different databases... And only use one set where something is
somewhat-globally true for databases snort people use.  For people who
only use MySQL... They would have their own optimizations (such as
auto_increment and the like), while Oracle and others could use
sequences.  As such... This would allow for greater performance across
all systems, while still maintaining a common data structure.

We could also consider things like turning off autocommit, and doing
commits on a timed basis.  This way, a big 100hits/sec slam wouldn't
bring the DB to its knees (as quickly).  Though, may be problematic if
snort crashes (lost data?).

(more comments inline)

-----Original Message-----
From: Ian Macdonald [mailto:secsnortdev at ...1490...] 
Sent: Saturday, September 14, 2002 9:28 PM
To: ian.willis at ...1523...
Cc: snort-devel at lists.sourceforge.net
Subject: Re: [Snort-devel] last_cid in new database scheme v106

----- Original Message -----
From: <ian.willis at ...1523...>
To: "Ian Macdonald" <secsnortdev at ...1490...>
Cc: <snort-devel at lists.sourceforge.net>
Sent: Friday, September 13, 2002 10:07 PM
Subject: Re: [Snort-devel] last_cid in new database scheme v106

> Some possible problems that relate to making optimisation such as you
> mention
> 1 Some of the optimisations that you are making are not universal
> solutions, they are database dependant. I believe that Oracle properly
> configured doesn't have any real gains from some of the changes that
> believe will speed up queries.

- Perhaps you would be kind enough to list the things that you don't
- will make a difference?

Some of the benefits we could see:
Less selects made to pull info, when there are other means of doing so.
Smaller primary key, means less db time to validate.
Faster inserts with commits on a timed basis.
Use of optimized functions built into libs for different DBs (OCI's
prepare, <loop> bind, execute </loop> stuff for example)

> 2 Proper indexing should reduce query time by log(n) unless a complete
> table scan is needed.

- Agreed, but what would you rather do, insert a value into a character
- that has been indexed or into a int column that is indexed, or a int
- that isn't indexed at all? For speed I would choose the last option,
if I am
- doing any joins against the column I would choose an indexed int as a
- resort I would choose a varchar. I would only suggest using non
- tables for dataload part done by snort. All tables that are used by
- front end reporting system should be indexed.

Would be nice for people that use/create applications to access the
databases, that we had some optimized indexes created for different
frequently used columns.  Sig_id/signature, ip_src/dst... Etc...  And
possibly have a setup for db sizes as well (small/med/large) with some
comments about records...  I know some databases you don't have to worry
about setting up proper index sizes... Though in others it makes a great
difference in future performance, since often times indexes are all
stored in the same tablespace and growth can cause fragmentation and
thus slowness.

> 3 I have worked in oraganisation where we have denormalized data
> structures to gain performance only to have the very changes that we
> with the best intentions restricting future innovations, our crystal
> tended to be very clouded.

- Noted. What would you recommend instead? and why

> 4 When doing bulk loads from files the integrity checking that the
> database normally does may not apply, again this is implementation
> dependant.

- Yes, agreed, I was just putting an idea out there that may make
- that support this feature the prefered databases for high volume

Wouldn't turning off autocommit on most databases fix this problem?
(and raise a few others).

More information about the Snort-devel mailing list