[prev in list] [next in list] [prev in thread] [next in thread] 

List:       dbi-users
Subject:    Re: Tail Module + DBI Module, can\'t keep up!
From:       Bruce Johnson <johnson () pharmacy ! arizona ! edu>
Date:       2011-09-21 19:52:20
Message-ID: DEB70B4C-9F5D-4DC4-B30B-8C92B6F59239 () pharmacy ! arizona ! edu
[Download RAW message or body]


On Sep 21, 2011, at 8:55 AM, Curtis Leach wrote:

> Here's a way that might speed things up for you considerably by
> eliminating a DB hit.
> 

> > 4. Perl script from (3) constantly reads in the /var/log files using
> > the Tail module.  It does the following:
> > 
> > a. When a connection is opened, it INSERTs into the
> > sonicwall_connections table
> > 
> > b. When a connection is closed, it SELECTs from the
> > sonicwall_connection table the last record id that matches the
> > protocol, source_ip, source_port, and destination_port
> > 
> > c. If a record exists matching this criteria, it UPDATEs that
> > record's close_dt column with the time the connection was
> > closed
> > 
> > d. If a record does not exist, then in our case this means that
> > the connection was denied due to firewall rules, and we
> > instead INSERT into a different table, sonicwall_denied
> [...]

If the slowdown is truly this step of inserting data into the DB, how about just \
inserting the unchecked log entry into the database and doing all the ruke matching \
post insert, or doing the matching as views into the raw table; manage all the above \
business rules in the database.

If you're just looking for open and close records, for example:

Perl script watches the log

if a log entry matches either OPEN or CLOSE dump it to the raw log table, which is \
indexed on protocol, source_ip, source_port, and destination_port.

Then with the data in the database, you can do the queries needed to find the open \
and close of a connection and easily find rows with a close without an open to find \
the sonicwall-denied entries. (this, in fact was exactly how we used to manage the \
same type of information about a Cisco terminal server, looking for abnormally \
dropped connections and identifying who was connected when to what IP address)

Basically you just need to process the log entries into row inserts without doing any \
other queries to the database, which will be about as fast as you can manage, \
especially if you do block commits. Then if it's still not fast enough to keep up, \
you need to look elsewhere for speed improvements

Quit making Perl do the work of Mysql, in other words.


-- 
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs


[prev in list] [next in list] [prev in thread] [next in thread]