[Snort-users] Speeding up mysql

Hutchinson, Andrew Andrew.Hutchinson at ...3639...
Fri Oct 19 11:04:11 EDT 2001


I noted the same issue on the mysql database - it can be solved by
adding an index as described, OR by rewriting the queries so that they
reference the compound index rather than adding an additional index.  I
normally try to keep indicies to a minimum, because it significantly
affects the speed of database writes.  Our environment has a high volume
of alerts from our sensors, and as such the writing speed is critical.

To solve in indexing issue, just be sure to always use the compound
index (inthe correct order) when writing queries.  For example:

"SELECT event.sid,event.cid,event.timestamp,iphdr.ip_src,iphdr.ip_dst 
FROM event,iphdr,signature 
WHERE signature.sig_id=$sigid 
AND signature.sig_id=event.signature 
AND event.sid=iphdr.sid 
AND event.cid=iphdr.cid
AND event.timestamp > DATE_SUB(NOW(), interval $days day)
ORDER BY event.timestamp DESC;");

If you always join on BOTH sid and cid, you can avoid the performance
hit of the additional index altogether while speeding up queries by
several orders of magnitude.

Hope this helps,

Andrew

>  -----Original Message-----
> From: 	snort-users-admin at lists.sourceforge.net@VANDERBILT   On
> Behalf Of quentyn at ...3871...
> Sent:	Friday, October 19, 2001 12:24 PM
> To:	Kevin Brown; snort-users at lists.sourceforge.net
> Subject:	Re: [Snort-users] Speeding up mysql
> 
> > Kevin Brown wrote:
> >
> > What schema are you using?  What version of Mysql?  I'm using schema
> > 104, Mysql 3.23.43 and according to the mysql manual (looking at
> > mysql.com) it says that indexes are automatically made on Primary
> > keynames.  So doing a "show index from <tbl_name>" shows that each
> of
> > the tables that contain a CID column are indexed already.  Same
> thing
> > with signature in the event table and sig_id in the signature table.
> >
> > > -----Original Message-----
> > > From: quentyn at ...3871... [mailto:quentyn at ...3871...]
> > > Sent: Friday, October 19, 2001 09:42
> > > To: snort-users at lists.sourceforge.net
> > > Subject: [Snort-users] Speeding up mysql
> > >
> 
> it is the way mysql indexing works
> 
> these are answers from 2 different people
> 
> 
> the primary key covers 2 fields, however the join refers to the 2ndary
> field and in the current indexing structure of MYSQL it would appear
> that this prevents the index being used, hence the queries performing
> a
> table scan (which takes ages). By adding a non unique index on the 2nd
> field you see a performance increase
> 
> 
> in multi column indexes mysql doesn't no use the index if you haven't
> referenced the 1st column of the index.
> 
> 
> or something or other.....
> 
> 
> Q
> 
> 
> --
> #####################
> Quentyn Taylor
> Sysadmin - Fotango
> #####################
> "The way NT mounts filesystems is something I'd expect to find in a
> barnyard or on a
> stock-breeding farm."
>    Mike Andrews, ASR
> 
> _______________________________________________
> Snort-users mailing list
> Snort-users at 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




More information about the Snort-users mailing list