Re: Pushing raw tcpdump data into database is extremely slow

[Edwin Eefting <edwin () bit nl>]

On Wed, 21 Nov 2001 16:54:20 +0100 Thomas Novin <thnov () thalamus se> wrote:

> Hi all.
>
> At first I tried to log our network traffic directly into a MySQL database 
> but found that snort dropped ~ 75% of the packets. Instead I used tcpdump 
> to log to a file, push the file over to the mysql server and then, using 
> snort -r, inserting the data into the database.
>
> The problem is, over a ~ 5 minute period the tcpdump logfile had grown to 
> be approx 50 MB of size and had 770k lines. I gave up with the snort -r 
> after letting it run for 25 minutes. Snort had then inserted 330k lines 
> into the database. I think you can all see the problem here, there is no 
> way the database will keep up with my traffic.
>
> The database server is a quite powerful machine, dual PIII 933 MHz, 1 GB 
> RAM, Seagate U160 SCSI. I see however that the CPU load is no more than ~ 
> 20% (varies between 0 and 50) and there was still 350 MB mem left. When i 
> logged directly to the database the machine used CPU 1 100% and CPU2 ~ 15% 
> and all of the memory.
>
> Anyone got an idea how I should speed up the process of getting the data 
> into the database? My configs are:
>
> Machine 1 (logger):
> tcpdump -i fxp0 -n -w file
>
> Machine 2 (database):
> Snort 1.8.1-RELEASE
> FreeBSD 4.3-SECURITY
> MySQL 3.2.23 compiled with linuxthreads
> Optimized kernel
> Optimized conf for mysql
>
> snort -r snort_eag.log -l /mnt/data1/logs/ -c /usr/local/etc/snort.conf
> log tcp any any -> any any (msg:"tcp";)
> log udp any any -> any any (msg:"udp";)
> log icmp any any -> any any (msg:"icmp";)

Am i wrong, or are you trying to log ALL the traffic on your network into
the database??? (e.g.  log tcp any any -> any any (msg:"tcp";)

Snort wasn't created for this, perhaps you should use something else or
stick with tcpdump. (maybe you need something like they use for Echelon
:-)

Maybe I don't understand you, or you don't understand snort. That's also
possible. ;-)

Goodluck
Edwin Eefting


> output database: log, mysql, dbname=snort user=xxx host=localhost
> password=xxx detail=fast
>
> Any help would be appreciated.
>
> Regards,
>
> Thomas
>
>
> --
> Thomas Novin · thnov () thalamus se · http://xyz.pp.se/~thnov/pgp_thalamus.asc
> System Engineer · Thalamus Networks AB · http://www.thalamus.se
> V: +46 (0)431 445400 · F: +46 (0)431 445410 · GSM: +46 (0)704 280382
>
>
> _______________________________________________
> 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


--                            __________________
                             /\ ___/          
Edwin Eefting               /- \ _/  Business Internet Trends BV
                           /--- \/           __________________


_______________________________________________
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