Snort mailing list archives
RE: Snort and SQL performance
From: Kevin Brown <Kevin.M.Brown () asu edu>
Date: Thu, 06 Sep 2001 10:32:16 -0700
Well, I tried running a query of the MySQL database to retrieve some information for a report. The query is basically what the ACID main page looks like, but with a date restriction having been added to just show the number and type of alerts for the previous month. In Postgres this query took, maybe, half an hour to complete, but with MySQL it was at 1400 mintutes and counting on just the first of 4 querys. I asked for assistance from the SQL guru here at work to see if there was a way to improve the performance of the query, but no luck. "SELECT count(*) FROM tcphdr INNER JOIN event ON event.cid = tcphdr.cid WHERE MONTH(event.timestamp) = '$month'"; "SELECT count(*) FROM udphdr INNER JOIN event ON event.cid = udphdr.cid WHERE MONTH(event.timestamp) = '$month'"; "SELECT count(*) FROM icmphdr INNER JOIN event ON event.cid = icmphdr.cid WHERE MONTH(event.timestamp) = '$month'"; "SELECT count(*) FROM event INNER JOIN signature ON signature.sig_id = event.signature WHERE signature.sig_name LIKE 'spp_portscan%' AND MONTH(event.timestamp) = '$month'"; $month is set by calling the linux date command and then subtracting 1 to find out lasts months number.
-----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 aNetra 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 QuadPII450 Xeon, 2GBRAM, 40GB Hard drive space devoted to the database. I havenoticed thatas the database gets larger in size, the performance of snortbegins to slip.During the summer when few to no students were present,snort was clockingalong at 70% of the CPU. The semester has begun andnetwork usage hasrisen, yet snort has slowly gone down from 70% to 45%. Theonly thingthat 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 halfago, listening ona total of 200Mb of bandwidth to various ISPs that servicethis campus. Ihave also tried this with PostgreSQL, but while the insertperformance mayhave been better the ACID performance for viewing the datawas an order ofmagnitude, or more, worse (e.g. 100s for postgres vs. 10sfor mysql).To me this suggests that there may still be some tuningoptions, either inmysql or in the spo_database plugin to improve the speed ofthe inserts tosee 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
Current thread:
- Snort and SQL performance Kevin Brown (Sep 05)
- <Possible follow-ups>
- RE: Snort and SQL performance Fraser Hugh (Sep 05)
- RE: Snort and SQL performance Kevin Brown (Sep 06)
- RE: Snort and SQL performance Kevin Brown (Sep 06)
- RE: Snort and SQL performance Kevin Brown (Sep 28)