RE: Snort and SQL performance

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

> Don't know much about ACID model but 
> How many row counts your "event" table ?

mysql> select count(cid) from event;
+------------+
| count(cid) |
+------------+
|    2363943 |
+------------+

That is after I deleted over 200,000 alerts.

> Is there an index on event.timestamp ?

Looks like there is an index of timestamp in the event table.
mysql> show index from event;  displays an index column of time referencing
timestamp.

> If not you could create one on timestamp 
> Instead of using $month as a month number convert le 1rst day 
> of the month and the 1rst day of previous month to timestamp.
> suppress the MONTH function fron the left part of the where 
> and use greater and lesser operators.
>
> WHERE event.timestamp >= $first_day_of_the_previous_month
> AND   event.timestamp <  $first_day_of_the_current_month

Tested performance by doing: 
        "select count(timestamp) from event where timestamp < '2001-09-01'
and timestamp >= '2001-08-01';"
and:
        "select count(timestamp) from event where MONTH(timestamp) = 8;"

Both queries took just over 10s to complete and came back with the same
answer.  I suspect that the real slow down is in doing the INNER JOIN of
event with icmphdr, tcphdr, udphdr and signature.

> Be sure the columns {tcphdr,udphde,icmphdr}.cid are indexed.

the cid columns are all indexed.

> Maybe counting a fix character instead of * will improve a 
> little bit more. 
> SELECT COUNT('x') 
> it avoids an access to the data page. ( I'm not sure how 
> mySQL process count(*) ).
>
>
> By substracting 1 to month you'll encounter a pb on January 
> month 0 do not exists !

Yep, my php script takes that into account.

$month = exec("/bin/date +%m");
$year = exec("/bin/date +%Y");

if ($month > 1) {
        $month = $month-1;
        }
else {
        $month = 12;
        $year = $year-1;
        }

though I don't use the year in the query (yet) since the database only has
records dating back to Aug 13.  Prior to that we were using Postgres, but
management was not happy with the speed of ACID.

http://acidlab.sourceforge.net/perf/acid_perf.html

> > 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.
> -- 
>   ________________________________________
>  |_  | Jean Baptiste Lallement
>   / /  ZENI Corporation    http://zeni.fr
>  |___| Tel: 0 803 003 111

_______________________________________________
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