[Snort-users] snort - MYSQL performance + packet dropped?

Alex Butcher, ISC/ISYS Alex.Butcher at ...11254...
Wed Mar 2 02:05:33 EST 2005

--On 02 March 2005 09:54 +1300 Nyuk Loong Kiw <Kiw at ...13015...> wrote:

> I am not sure if this has been asked before.

Yup, but maybe not all at once. Searching the mailing list archives is 
usually useful <http://sourceforge.net/mailarchive/forum.php?forum_id=3972>.

> How can i tell if my snort box is dropping packets or not?? I checked
> both the interface on the snort box itself as well as the switch port
> that's plugged in to and i hardly see any errors at all... does it mean i
> can pretty safely assume none of the packets are dropped?? Is there any
> better way of finding this out?

Enable the perfmonitor preprocessor, and, optionally, use pmgraph.pl 
<http://people.su.se/~andreaso/perfmon-graph/> to graph the output.

> Second question is, I have setup snort + MYSQL + BASE + snortreport etc
> on a PII box with 512MB ram (just my play box). It seems to be doing it's
> job fine until i plug it in to a switch segment (with about 20 pcs
> attached to it) and have all signatures turned on. I am having serious
> performance problem with the MYSQL that eveytime when i try to view the
> report via the snortreport interface or usnig BASE to look at alerts etc,
> it can take as long as 2-3 minutes before i will get the full page
> loaded.

That can be normal.

> I have tried stopping snort while doing the query via the php
> page and it doesn't make any difference whether snort was logging to the
> database at the same time or not. While diong the query doing a top shows
> me that mysqld is using all the CPU. Is this normal?

Yup, MySQL is executing the query, so it will be using CPU extensively.

> Is there anything i can do to increase MYSQL's performance? (eg is there
> any day to day maintenance task that i am suppose to do daily to keep the
> DB happy?) Or am i using a box that's not up to spec and the only way to
> fix is to put in a better hardware??

All of the above. :-)

1) Keep your alert database small, either by deleting alerts that you've 
investigated, or by having a cron job that clears alerts older than a 
certain age. I use a modified version of a script that can be found at 

2) Run OPTIMIZE TABLE on all the tables periodically. Again, I do this from 
a cron job every few hours.

3) Run Snort on a separate machine from the database. Use a spool processor 
(e.g. barnyard, mudpit or FLoP - I use FLoP) to decouple the two so Snort 
won't drop packets if the database server gets clogged. Ideally, run BASE 
on a third separate machine too.

4) Tune MySQL according to the amount of memory you have. In /etc/my.cnf:

set-variable = key_buffer_size=128M
set-variable = table_cache=512
set-variable = sort_buffer=8M
set-variable = record_buffer=2M

Those values are recommended elsewhere for a 512M machine. 
Increase/decrease them in proportion to the amount of memory you have 
fitted. MySQL loves memory.

5) Put MySQL's database on a device that's tuned for the job (e.g. ext2 
rather than ext3 or some other journalled fs, mounted with noatime).

6) Throw money and hardware at the problem. :-)

7) I'm also considering using PostgreSQL instead of MySQL. Historically, 
MySQL was reported as having better performance with ACID (BASE's 
predecessor), but I don't know how true that is any longer.

> What's the best way of logging from snort to MYSQL at the moment??

IMHO, FLoP, but each to their own. They all have pros and cons. Logging 
directly from Snort is definitely the worst approach. I've been considering 
whether a batch approach might be a better idea than attempting to do it in 
real-time, though.

> Currently i have got snort logging directly to mysql, i am aware that i
> can get snort to log to some sort of log file (binary?) and get barnyard
> to read from log and export to MYSQL, is this how people normally do it
> in a production environment (to improve performance?).


> Sorry to ask so many Q at the same time, i am pretty new to snort and am
> getting pretty excited about what SNORT is capable of .. :)
> Thanks
> Kiw

Best Regards,
Alex Butcher: Security & Integrity, Personal Computer Systems Group
Information Systems and Computing             GPG Key ID: F9B27DC9
GPG Fingerprint: D62A DD83 A0B8 D174 49C4 2849 832D 6C72 F9B2 7DC9

More information about the Snort-users mailing list