looking at your code I am guessing your leak is in

$spy[$ct]=$global[$x];
[download]

                - Ant
                - Some of my best work - (1 2 3)

Second, using prepare_cached WITHOUT placeholders (with variable arguments) is a very bad idea. You WILL use alot of memory if there is alot of unique input. If your insert statement will have a limited number of table names, then use prepare_cached, but use it WITH placeholders (search this site and see the DBI docs). If you're going to have an unlimited number of table names per script, then use prepare instead of prepare_cached, but DO still use placeholders (with most databases, the table name can not be a placeholder argument, it might be different with mysql, though not portable).

Last, you're not checking the status of any of your DBI calls (except the connect). You might want to look into RaiseError (see the DBI docs), and wrap the whole thing in an eval{}, then check $@, or check the status of each DBI call (prepare, execute, etc.).

1. You have a variable @global. This is a bad thing. Name it something useful.
2. You do an insert explicating $writer[0] through $writer[24]. join would make this cleaner.
3. qw is your friend. For example,

   @snort   =("large","packet","bad","traffic","inbound","attempt","web",
   +"IIS","cmd.exe","cgi","access","multiple","decode");
   [download]

   becomes

   @snort   = qw(large packet bad traffic inbound attempt web
                 IIS cmd.exe cgi access multiple decode);
   [download]

4. You should die after you cannot connect to the database.
5. Use multi-dimensional arrays, or, better, hashes of arrays. That way, your use of @watcher doesn't require split. Something like

   my @watcher = ( \@pix, \@denies);
   ...
   my @global = @{$watcher[$i]};
   [download]

6. Where do you use $global? (Using warnings would've caught this...)

7. 
                   if($global[$x]=~/ZSP/i){
                   @ret=split(/ZSP/,$global[$x]);
                   $global[$x]="";
                   $global[$x]=join(' ',@ret);
                   }
   [download]

   should be (before using references correctly...)

       if (my @ret = split /ZSP/, $global[$x]) {
           $global[$x] = join ' ', @ret;
       }
   [download]

8. Something like the following might help ...

   
           foreach my $r (@spy) {
               if($input =~ /$r/i){
                   $findings .= ' ' . $r;
                   $fn++;
               }
           }
   [download]

------
We are the carpenters and bricklayers of the Information Age.

Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.

• First of all start with this node: How to get the most of your question from the monks. It has a lot of good info for newbies. Pay attention for tips about 'use strict' and '-w'. It is must have!
• Well, next step separate your code on several subroutines. Rule of thumb that no subroutine show exceed one screen. That meens 25 lines. All rules have exceptions but usually long subroutine means bad style.
• Use DBI's placeholders. See perldoc DBI and What are placeholders in DBI, and why would I want to use them?.
• There is no need for very long completly unreadable lines like in chunk of your code which creates new table. It can be rewritten using heredocs (see Here, doccy doccy. nice doccy. heredoc, treat.). Something like

  
  $sql->do(<<SQL);
  CREATE TABLE IF NOT EXISTS %log (
      id INT (100) not null AUTO_INCREMENT,
      log1 CHAR (255) not null,
      ....
      ....
  )
  SQL
  [download]

  It looks much more readable. Note that I've replaced several method calls prepare, excute. finish with single do.
• Loops like

  
  for($$i=0; $i<$#watcher+1; $i++) {
      $watch="$watcher[$i]";
      ...
      ...
  }
  [download]

  Can be rewritten as

  
  for my $watch (@watcher) {
  }
  [download]

• Well, and finally memory leaks. I think they are caused by using prepare_cached without thinking. prepare_cached caches statement handler for all queries so for same queries one statement handler can be reused. First of all it doesn't makes too much sense for MySQL because it cannot actually prepare queries for later reuse like Oracle (and probably don't need to do it since it is much more lightweight SQL server). Second of all all your queries are different since you do not use placeholders. For now replace prepare_cached with just prepare and reread docs for DBI. Probably your problems will go away.

my @ins_fields= ("log");
my @ins_values= ("''");
for (1..25) {
    $writer[$_ -1] = "value of writer". ($_-1) ;
    push @ins_fields, "log$_";
    push @ins_values, $writer[$_ -1];
}
my $ins_fields = join ",", @ins_fields;
my $ins_values = join ",", @ins_values;
print "INSERT INTO $log ($ins_fields)\nVALUES ($ins_values)";
[download]

        $findings=~s/,//g;
        $findings=~s/ /_/g;
        $findings=~s/\///g;
        $log="$findings";
        $log=~s/:/_/g;
        $log=~s/\./_/g;
        $log=~s/___/_/g;
        $log=~s/__/_/g;
[download]

for (1..10) {
    my $foo = "_" x $_;
    $foo =~ s/_+/_/;
    print "method one : $foo\n";

    my $foo = "__" x $_;
    $foo =~ s/___?/_/;
    print "method two : $foo\n";
}
[download]

Sendmail is there due to the fact that there is a function commented out, #mailman() and that code I did not include, thanks for all your help though! Everyone is helping so much I can't believe it, all great tips!

I see no use of sendmail objects here. (although it might be importing something; I'm not so familiar with it)

sendmail is exported by the Mail::Sendmail module. It is somewhat unfortunately named, as it is a pure-Perl module, and does not require an external program (such as /usr/bin/sendmail). The author himself realises that it is poorly named, and were he to do it all over again, he would choose the name Mail::Simple.

--

g r i n d e r

unless someone silently added code tags, it looks like the code is really well indented... The code tags are breaking up the long lines...

Maybe I'm just picky, but I think it's easier to read after a run through perltidy.

Maybe you're actually bloating the cache that's created when you use $database->prepare_cached(). I suppose that would depend on how many lines of input this script sees.

I wonder why you would even want to cache the results of SQL statements such as CREATE TABLE IF NOT EXISTS. Does this help? I would have guessed that the cache was more useful more SELECT queries.

Anyway, as a longshot, try turning those into $database->prepare() instead, and see if it helps any.

Even better, substitute placeholders for the variables. That way you can prepare() the statements once and execute() them with the correct parameters each timne through the loop. It may not affect the memory leak, but it's useful in many ways.

buckaduck

This application sees about 200,000 lines of input per day, so you think that is it? Tha cache is just getting to be too much?

The way that prepare_cached works is that it uses your entire SQL statement as the key to a hash (the value is the statement handle). So if statements differ in any way (even whitespace), then you are storing another key/value pair in the statement cache (and the database creates another cursor if the database supports cursors). With 200,000 lines of input and no placeholders in sight, you are abusing prepare_cached and are wasting memory. If you use placeholders, then the only difference in your cached statements will be the table names, and you shouldn't run out of memory (assuming there aren't too many table names).

If your perl is 5.6 or later then put in 'use warnings;', if earlier (or if you need to be backward compatible), then '-w' after the 'perl' on the first line. see perlrun for -w and perlvar for the related $^W, or 'perldoc warnings' and 'perldoc perllexwarn' for 'use warnings;'.

You are right, sorry... it is hard to do a quick review of someone else's code :) In a daemonized program, though, you really have to watch for arrays and hashes and what not that just keep getting added to, they are the most common cause of memory leaks... that's why using my in the tightest loop possible is good... then things get garbage collected when they go out of scope...

                - Ant
                - Some of my best work - (1 2 3)