RE: Snort and SQL performance

[Kevin Brown <Kevin.M.Brown () asu edu>]

Well I found a solution to get around the INNER JOIN in the SQL calls to
mysql (since they take forever to do, even with just a weeks worth of
information).  The solution was to find the first CID that occurred for that
time period and the last CID to occur for that period.  If I have this
right, then the three tables I'm trying to get information from are linked
to the events table via the columns sid and cid in each one (tcphdr, udphdr,
icmphdr).  So I then do a count on each of the three tables and then only
have to do an inner join between event and signature to get the count of
portscan alerts that went off.

SELECT count(cid) from tcphdr where cid >= $first_event and cid <=
$last_event
select count(cid) from udphdr where cid >= $first_event and cid <=
$last_event
select count(cid) from icmphdr where cid >= $first_event and cid <=
$last_event
SELECT count(event.cid) from event inner join signature on signature.sig_id
= event.signature where signature.sig_name like 'spp_portscan%' AND
event.timestamp < '$today' AND event.timestamp >= DATE_SUB('$today',
INTERVAL 7 DAY)

These queries go very fast (takes less than a minute for all 4, even with
100,000 records for the week).

> -----Original Message-----
> From: Fraser Hugh [mailto:hugh_fraser () dofasco ca]
> Sent: Wednesday, September 05, 2001 12:13
> To: 'Kevin Brown'; 'snort-users () lists sourceforge net'
> Subject: RE: [Snort-users] Snort and SQL performance
>
>
> Postgres's transaction overhead seems to make a considerable 
> difference to
> ACID, which issues a lot of queries building its pages. The 
> DB itself isn't
> slow... I've written some perl scripts to generate stats that 
> quickly churn
> through endless Code Red alerts, but they're based upon 
> queries written to
> use the DB efficiently, something that's more difficult to do 
> from a generic
> user query web page. Even with fsync turned off, deleting 
> 40000 Code Red
> alerts through ACID is painful, but very quick in Perl. I suspect some
> optimization in ACID would bring Postgres and MySQL closer  
> in performance.
>
> In my environment, I wanted to merge Snort and ACID with DCL 
> (a free trouble
> ticket tracking system) to manage the audit trail that we 
> generate when
> there's a security alert. A bit of plpgsql to add some 
> triggers to the snort
> database , a couple of tables to identify alerts we're interested in
> tracking, and I've extended Snort to cut trouble tickets in 
> DCL without
> actually changing Snort or any rules. Eventually, I'll get around to a
> custom output plugin, but triggers in Postgres seemed much simpler. My
> solution for the performance issue has been to use scheduled 
> perl scripts to
> do daily pruning of old events to keep the size down.
>
> > -----Original Message-----
> > From:       Kevin Brown [SMTP:Kevin.M.Brown () asu edu]
> > Sent:       Wednesday, September 05, 2001 1:01 PM
> > To: 'snort-users () lists sourceforge net'
> > Subject:    [Snort-users] Snort and SQL performance
> >
> > Currently running Version 1.8.1-RELEASE (Build 74) on a 
> Netra T1 AC200
> > (500MHz Sparc IIe, 1GB RAM, 2x 18GB 10k RPM SCSI drives) and have it
> > logging
> > to a remote SQL database, MySQL 3.23.40 running on a Quad 
> PII450 Xeon, 2GB
> > RAM, 40GB Hard drive space devoted to the database.  I have 
> noticed that
> > as
> > the database gets larger in size, the performance of snort 
> begins to slip.
> > During the summer when few to no students were present, 
> snort was clocking
> > along at 70% of the CPU.  The semester has begun and 
> network usage has
> > risen, yet snort has slowly gone down from 70% to 45%.  The 
> only thing
> > that
> > has changed on the network is that the SQL Database has grown from 0
> > records
> > on 8-13-01 to 2,632,460 records as of an hour and a half 
> ago, listening on
> > a
> > total of 200Mb of bandwidth to various ISPs that service 
> this campus.  I
> > have also tried this with PostgreSQL, but while the insert 
> performance may
> > have been better the ACID performance for viewing the data 
> was an order of
> > magnitude, or more, worse (e.g. 100s for postgres vs. 10s 
> for mysql).
> > To me this suggests that there may still be some tuning 
> options, either in
> > mysql or in the spo_database plugin to improve the speed of 
> the inserts to
> > see if that keeps snort chugging along happily.
> >
> > END OF LINE...
> >
> > Begin Geek Code;
> $_='while(read+STDIN,$_,2048){$a=29;$b=73;$c=142;$t=255;@t=map
> {$_%16or$t^=
> > $c
> > ^=(
> $m=(11,10,116,100,11,122,20,100)[$_/16%8])&110;$t^=(72,@z=(64,
> 72,$a^=12*($
> > _%
> > 16
> -2?0:$m&17)),$b^=$_%64?12:0,@z)[$_%8]}(16..271);if((@a=unx"C*"
> ,$_)[20]&48)
> > {$
> > h
> =5;$_=unxb24,join"",@b=map{xB8,unxb8,chr($_^$a[--$h+84])}@ARGV
> ;s/...$/1$&/
> > ;$
> d=unxV,xb25,$_;$e=256|(ord$b[4])<<9|ord$b[3];$d=$d>>8^($f=$t&(
> $d>>12^$d>>4
> > ^
> $d^$d/8))<<17,$e=$e>>8^($t&($g=($q=$e>>14&7^$e)^$q*8^$q<<6))<<
> 9,$_=$t[$_]^
> >
> (($h>>=8)+=$f+(~$g&$t))for@a[128..$#a]}print+x"C*",@a}';s/x/pa
> ck+/g;eval
> > _______________________________________________
> > 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

_______________________________________________
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