RE: Acid and Mysql with Snort

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

Mike,

You likely have to create the indices of these columns.  The way to
check if the indices are already there are with the SHOW INDEX command.
For instance, to check the tcphdr table, you would run:

mysql> show index from tcphdr;
+--------+------------+-----------+--------------+-------------+--------
---+-------------+----------+--------+---------+
| Table  | Non_unique | Key_name  | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Comment |
+--------+------------+-----------+--------------+-------------+--------
---+-------------+----------+--------+---------+
| tcphdr |          0 | PRIMARY   |            1 | sid         | A
|        NULL |     NULL | NULL   |         |
| tcphdr |          0 | PRIMARY   |            2 | cid         | A
|     2543146 |     NULL | NULL   |         |
| tcphdr |          1 | tcp_sport |            1 | tcp_sport   | A
|        NULL |     NULL | NULL   |         |
| tcphdr |          1 | tcp_dport |            1 | tcp_dport   | A
|        NULL |     NULL | NULL   |         |
| tcphdr |          1 | tcp_flags |            1 | tcp_flags   | A
|        NULL |     NULL | NULL   |         |
+--------+------------+-----------+--------------+-------------+--------
---+-------------+----------+--------+---------+
5 rows in set (0.00 sec)

You can see that in this case, the tcphdr.tcp_sport index is in line 3,
and the tcphdr.tcp_dport is in line 4.

If you need to create the index, you can run:

CREATE INDEX idx_tcp_sport ON tcphdr(tcp_sport);

To create a compound index, you would do this:

CREATE INDEX idx_cpd_sid_cid ON acid_ag_alert(ag_sid,ag_cid);

If you want to take a closer look at the table structures, you can use
the DESCRIBE command, and pass it the table name:

mysql> DESCRIBE tcphdr;
+-----------+----------------------+------+-----+---------+-------+
| Field     | Type                 | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+-------+
| sid       | int(10) unsigned     |      | PRI | 0       |       |
| cid       | int(10) unsigned     |      | PRI | 0       |       |
| tcp_sport | smallint(5) unsigned |      | MUL | 0       |       |
| tcp_dport | smallint(5) unsigned |      | MUL | 0       |       |
| tcp_seq   | int(10) unsigned     | YES  |     | NULL    |       |
| tcp_ack   | int(10) unsigned     | YES  |     | NULL    |       |
| tcp_off   | tinyint(3) unsigned  | YES  |     | NULL    |       |
| tcp_res   | tinyint(3) unsigned  | YES  |     | NULL    |       |
| tcp_flags | tinyint(3) unsigned  |      | MUL | 0       |       |
| tcp_win   | smallint(5) unsigned | YES  |     | NULL    |       |
| tcp_csum  | smallint(5) unsigned | YES  |     | NULL    |       |
| tcp_urp   | smallint(5) unsigned | YES  |     | NULL    |       |
+-----------+----------------------+------+-----+---------+-------+
12 rows in set (0.02 sec)

If you want more info on digging around in your MySQL db's, get "MySQL"
by Paul DuBois.

Hope this answers your questions,

Andrew

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

-----Original Message-----
From: Pacheco, Michael F. [mailto:MPacheco () elcom com] 
Sent: Tuesday, July 16, 2002 2:37 PM
To: Hutchinson, Andrew; Hall, Duane; Snort Userslist
Subject: RE: [Snort-users] Acid and Mysql with Snort


Hi all,

Pardon the jump in, I got good information from the FAQ on MySQL tuning
for ACID - I just can not seem to find the exact contents (columns,
relationships etc...) of the indexes mentioned:

> tcphdr.tcp_sport
> tcphdr.tcp_dport 
> acid_ag_alert.ag_sid + acid_ag_alert.ag_cid 

Can anybody give me a pointer?

Thanks, Cheers

Mike
-----Original Message-----
From: Hutchinson, Andrew [mailto:Andrew.Hutchinson () Vanderbilt edu]
Sent: Friday, July 12, 2002 9:25 AM
To: Hall, Duane; Snort Userslist
Subject: RE: [Snort-users] Acid and Mysql with Snort


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


-------------------------------------------------------
This sf.net email is sponsored by:ThinkGeek
Welcome to geek heaven.
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