Brandon,
This is probably more of a DB issue than DBI. I would google for
general DB tuning strategies for mysql and high insert scenarios. I bet
your unit of work is too small and you DB can't keep up with the inserts.
Can you switch to using transactions an only commit every N lines or if N
seconds has elapsed? Are you using bind variables to make sure your
queries have an execution plan and aren't being reparsed every time? Do
you have extra indexes on the table you don't need?
Eric Bambach | Discover
Senior Assoc. Programmer, Warehouse Infrastructure and Tools
2500 Lake Cook Road, Riverwoods IL 60015
P: 224.405.2896 [email protected]
Brandon Phelps <[email protected]>
09/15/2011 08:46 AM
To
<[email protected]>
cc
Subject
Tail Module + DBI Module, can't keep up!
Hello!
I have a bit of a problem with a script and am hoping someone will have an
idea as to how I can mitigate the problem. Basically I have a syslog
server that receives messages from various hosts and logs them to
/var/log/ files. One of these remote hosts is a firewall which is sending
all of it's access information and logging it to
/var/log/loghosts/firewall.log. We need a web gui for this host that
allows management to see recently downloaded files, denied URLs, etc. So
I wrote a perl script which uses the Tail module to constantly tail this
file, parsing each line and inserting into a mysql database using the DBI
module. My problem here is that the logs are coming in extremely fast and
while the script does work, it progressively gets further and further
behind. It's currently Sep 15, 10AM yet the script is still dealing with
lines from Sep 14 at 8am. This is obviously due to the fact that the Tail
module, and more likely the DBI module simply can't INSERT/UPDATE the
database as fast as the logs are coming in.
Has anyone ever dealt with an issue like this and have any idea how I
might rework the script and/or application to avoid this delay, which will
obviously get worse and worse as time goes on?
Thanks,
Brandon
Please consider the environment before printing this email.
