RE: Acid and Mysql with Snort

["Hutchinson, Andrew" <Andrew.Hutchinson () Vanderbilt edu>]

Two things for you to check from the ACID faq:

http://www.andrew.cmu.edu/~rdanyliw/snort/acid_faq.html#faq_b9

(B-10) MySQL optimizations 

1. Compact the tables
After numerous delete operations, "holes" will occur in the native files
used to store the tables decreasing the speed of the all queries. The
following shell script will examine all the MySQL tables and compact
them. 
for table in `echo show tables|mysql snort|tail +2` 
do 
   echo optimize  table $table|mysql snort 
done 

2. Creating indexes
Some of the required indexes are not created in initial MySQL creation
script. The following indexes can be added to significantly improve
performance: 
tcphdr.tcp_sport 
tcphdr.tcp_dport 
acid_ag_alert.ag_sid + acid_ag_alert.ag_cid 



Based on what you're seeing, I would suspect that adding the indices
listed in step 2 is the key for you. MySQL is plenty fast - you just
need to have the proper indexing set up.  If you need a good MySQL
reference, pick up a copy of Paul DuBois' book, which is currently the
bible for MySQL.  O'Reilly also recently released a reference by Monty
and the MySQL AB team, but I haven't read it yet and thus cannot
comment.

Hope this helps,

Andrew


Andrew Hutchinson
Vanderbilt University Medical Center
Informatics / NCS / Network Security
andrew.hutchinson () vanderbilt edu

-----Original Message-----
From: Hall, Duane [mailto:Duane.Hall () hastings-ent com] 
Sent: Thursday, July 11, 2002 2:52 PM
To: Snort Userslist
Subject: [Snort-users] Acid and Mysql with Snort


I have a speed issue with ACID.  To give a little background:  I was
using snort to capture packets for the Internet team to help diagnose an
issue.  The only problem is they started stress testing without telling
me.  So between 8:00am and lunch, snort and Mysql logged about 2.5
million of these packets.  I am proud to say it didn't loose a single
packet.  Now my problem.  Mysql and ACID are slow.  It takes upwards of
2 - 5 minutes to run a query.  Are there any performance tuning scripts
available for Mysql and the snort database. For now these logged packets
aren't needed, so I am removing them from the database.  My question is
what If the database ever has this issue again.  

Duane
  
Duane Hall
Security Administrator
Hastings Entertainment


-----BEGIN GEEK CODE BLOCK-----
Version: 3.12
GIT d+ s:- a- C+ UL++ P+ L++ E--- W++ N++ o K- w--- 
O- M-- V-- PS PE Y PGP t++ 5 X R- tv+ b+ DI++ D+ 
G e+ h---- r+++ y++++ 
------END GEEK CODE BLOCK------


-------------------------------------------------------
This sf.net email is sponsored by:ThinkGeek
PC Mods, Computing goodies, cases & more http://thinkgeek.com/sf
_______________________________________________
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=ort-users


-------------------------------------------------------
This sf.net email is sponsored by:ThinkGeek
Gadgets, caffeine, t-shirts, fun stuff.
http://thinkgeek.com/sf
_______________________________________________
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