Re: optimizing MySQL for Snort

[Chris Adams <chris () improbable org>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thursday, December 6, 2001, at 01:12 , Hutchinson, Andrew wrote:
> I made the same mistake.  Changing the query will drop the time to a 
> minute fraction of what it was.  The other alternative is to add an 
> index, but then you'll have redundant indicies and that will also 
> impact insert speed.

Alternately, creating indexes can lead to a considerable performance 
improvement - I used the following SQL statements to get orders of 
magnitude improvements in snortreport (this is now create_indexes.sql in 
snortreport-1.1) at the expense of slightly lower insert performance 
(this wasn't an issue for us; if it matters to you, you should probably 
consider using barnyard so that database activity doesn't affect snort).

- -- These 4 make an enormous difference as they improve several of the 
joins used in *every* query in alerts.php
CREATE INDEX ip_cid ON iphdr (cid);
CREATE INDEX udp_cid ON udphdr (cid);
CREATE INDEX tcp_cid ON tcphdr (cid);
CREATE INDEX icmp_cid ON icmphdr (cid);

- -- More improvements by using cid indexes:
CREATE INDEX event_cid ON event (cid);
CREATE INDEX data_cid ON data (cid);
        
- -- This one makes the two alert using queries using an index instead of 
a scan.
CREATE INDEX time_sig ON event (timestamp, signature, cid);

Chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (Darwin)
Comment: For info see http://www.gnupg.org

iEYEARECAAYFAjwP8+sACgkQpwjw3VnGunQzSACcCySFa1riOecbEVxdVo9gHc0l
jZMAoOBRN0g8hNfsLVXWLFcYU1s5P3cH
=0ejB
-----END PGP SIGNATURE-----


_______________________________________________
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