Here's a way that might speed things up for you considerably by eliminating a DB hit.
I'm assuming that your query only returns 1 row and that you are not stepping through them all & just selecting the last one returned. If you are stepping through them, modify your select to only return 1 row. I'd assume you'd only have 1 record at a time with close_dt NULL. Then combine steps (b) & (C) and just do the update. If it updates 0 records do (d). This will eliminate a trip to the database. I believe the DBI can tell you how many records it updated. But I don't remember how to do this off hand. Curtis -----Original Message----- From: Roode, Eric [mailto:ero...@barrack.com] Sent: Wednesday, September 21, 2011 8:38 AM To: dbi-users@perl.org Cc: Brandon Phelps Subject: RE: Tail Module + DBI Module, can\'t keep up! On Wednesday, September 21, 2011 10:04 AM, Brandon Phelps [mailto:bphe...@gls.com] wrote: > Subject: Re: Tail Module + DBI Module, can\'t keep up! [...] > 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 [...] Inserting into the table ought to be a very fast operation. Searching it might be fast or might be slow. Updating it ought to be fast, especially if you have the primary key. Here is a shot in the dark: * Create a small auxiliary table with columns for protocol, source_ip, source_port, destination, and whatever the primary key for the sonicwall_connections table is. * Index it on your matching criteria (the protocol, source_ip, source_port, and destination_port). * When you INSERT into sonicwall_connections, step 4a above, also insert into this small table. * Then, when you encounter a connection-close (step 4b above), you won't have to search the whole sonicwall_connections table (which I presume is humongous). * In step 4c, after the UPDATE, delete the row from this auxiliary table. * In a separate program or process, periodically delete old rows from the auxiliary table (connections that never closed, for some reason). This will keep that table small and fast. But this is all just a guess, based on the idea that the SELECT is slowing you down. You really ought to profile it. You may be surprised to find out that the slowness is not where you think it is. -- Eric
