Re: [Sguil-users] Auto Categorization, UPDATE statements, MyISAM Table Locks and Query Queue

[John C. <joh...@me...>]

Eoin Miller,

I've reviewed the code surrounding these related Sguil functions:
BYEventRcvd -> EventRcvd -> AutoCat.  Sguil does inserts events into the
database initially (in proc BYEventRcvd) and then later updates the
status (in proc AutoCat). 

Prior to autocat being introduced, all events entered the Sguil system
uncategorized. Each time an event is categorized, presumably by an
analyst, a record is added to the history table indicating the status
change along with the analyst's userid, etc...  This is how Sguil
maintains the historical actions performed by analysts with regards to
the classification of events.   The goal of the AutoCat feature was to
categorized known events 'automatically', while maintaining the original
flow within Sguil, i.e. 1) insert the event, 2) evaluate the event, 3)
update the event and history.

After reviewing the code I can agree that to update the status prior to
inserting the event would eliminate some extra work for Sguil.
However, in order to accomplish that and to maintain integrity of the
original logic the following changes are required:

    1)  Change proc AutoCat to return the status on match
    2)  Change proc AutoCat to require a second argument to enable
previous behavior to update events and perform history inserts
    3)  Change proc BYEventRcvd to call proc AutoCat, modify the status
(if matched), then insert the event into the database.
    4)  Change proc BYEventRcvd to insert history, if status was
'modified' based on the results from AutoCat.
    5)  Change proc BYEventRcvd to call proc EventRcvd, only if AutoCat
did not match, so clients can see the event
    6)  Change proc EventRcvd to pass a second argument 'update' to
AutoCat which allows previous behavior for other agents (i.e. Pads,
GenericEvent).

These changes allow AutoCat be called by BYEventRcvd without performing
updates and history inserts, thereby allowing BYEventRcvd to perform
these actions on initial insert.  These changes also allows AutoCat to
behave in the previous manner when called by EventRcvd (i.e. to process
events received from SguilPadsLib.tcl and SguilGenericEvent.tcl) which
will perform the update on matches.

I've attached three patches to accomplish these changes. If these
changes work as expected, then SguilPadsLib.tcl and
SguilGenericEvent.tcl could be changed to work the same.

cd /usr/local/bin/server/lib
patch < ~/SguildSensorAgentComms.tcl.20091125.BYEventRcvd.patch.txt
patch < ~/SguildEvent.tcl.20091125.EventRcvd.patch.txt
patch < ~/SguildAutoCat.tcl.20091125.AutoCat.patch.txt

Please test and reply,
-John Curry



Eoin Miller wrote:
> We have been reviewing the Sguil database and attempting to optimize our 
> configuration further for higher performance. Honestly, this 
> architecture is pretty amazing and I have learned a ton more about MySQL 
> in the last few days just trying to figure out what was going on. We 
> rely heavily on the auto categorization feature within Sguil as 
> otherwise, it would become unusable. However, we noticed a ton of UPDATE 
> statements being issued after alerts are already imported.
>
> proc UpdateDBStatus { sensorName date sid cid timestamp uid status } {
>     global MAIN_DB_SOCKETID
>     set tmpDate [clock format [clock scan $date] -gmt true -format "%Y%m%d"]
>     set tableName "event_${sensorName}_$tmpDate"
>     set updateString\
>      "UPDATE `$tableName` SET status=$status, 
> last_modified='$timestamp', last_uid='$uid' WHERE sid=$sid AND cid=$cid"
>     InfoMessage $updateString
>     set execResults [mysqlexec $MAIN_DB_SOCKETID $updateString]
> }
>
> Now, we know UPDATE's causes a lock and that the MyISAM storage engine 
> only supports table level locking. So, while an UPDATE is happening, no 
> one else can perform a SELECT by default. What I ended up discovering 
> though is that UPDATE is given priority over SELECT in the query queue. 
> So as more and more INSERT/UPDATE statements pile up from sensors, the 
> pending SELECT's should keep getting pushed to the bottom of the stack. 
> You should be able to include LOW_PRIORITY within the UPDATE statement, 
> or you can add low-priority-updates to your /etc/mysql/my.cnf file 
> (which we have done). However, adding this to the UPDATE code would make 
> the application more responsive to end users regardless of how the MySQL 
> server configuration is done.
>
> Or
>
> Why must UPDATE statements be applied for the alerts after they have 
> already been put into the database? If automatic categorization is 
> occurring, shouldn't the status column be set on the initial INSERT? 
> This should greatly reduce the number of queries, and table locks while 
> helping the server remain more responsive to the end users.
>
> -- Eoin
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day 
> trial. Simplify your report design, integration and deployment - and focus on 
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> _______________________________________________
> Sguil-users mailing list
> Sgu...@li...
> https://lists.sourceforge.net/lists/listinfo/sguil-users
>