Changeset 23 for trunk

Timestamp:

08/31/10 15:35:18 (14 years ago)

Author:

Kris Deugau

Message:

/trunk/dnsbl

Shuffle processing in DNSBL::export() for 1.5-10x speedup (depending

on DB version, OS, Perl, phase of moon):

• prepare some statement handles more globally, so we're not recreating them for each netblock (moved to new sub initexport())
• pass the bitmask down the recursion chain instead of retrieving parent "islisted?" info for each netblock
• remove complex JOIN on recursion loop so we don't wait so long for the DB to finish

Trim commented stale code.

Location:

trunk/dnsbl

Files:

• DNSBL.pm (modified) (9 diffs)
• export-dnsbl (modified) (1 diff)

trunk/dnsbl/DNSBL.pm

@@ -125 +125 @@
 
 
+## DNSBLDB::initexport()
+# Prepare a couple of statement handles for later processing in export().  Assists in ~3x speed increase.
+my $parsth;
+my $sthmoron;
+sub initexport {
+  $parsth = $dbh->prepare("SELECT count(i.ip),b.block,b.level,b.listme AS oobblock,o.listme AS ooborg ".
+        "FROM iplist i INNER JOIN blocks b ON i.ip << b.block INNER JOIN orgs o ON b.orgid = o.orgid ".
+        "WHERE b.block >>= ? ".
+        "GROUP BY b.block,b.level,b.listme,o.listme ORDER BY b.block");
+  $sthmoron = $dbh->prepare("SELECT ip,s4list FROM iplist WHERE ip << ? ORDER BY ip");
+}
+
+
 ## DNSBL::ipexists()
 # return report count if the IP has been reported, otherwise return undef
@@ -259 +272 @@
   my $level = shift || 0;
   my $container = shift || '0.0.0.0/0';
-  my $oobblock = shift || 0;
-  my $ooborg = shift || 0;
+  my $bitmask = shift || 0;
 
   if ($level > 3) {
@@ -280 +292 @@
   my ($nblocks) = $sth->fetchrow_array();
 
+  # need this for a bunch of things, may as well do it here
+  my ($masklen) = ($container =~ m|/(\d+)$|);
+
+# Update the bitmask variable with the current block info as needed.
+# Much faster than retrieving this data later (~3x faster!).
+  my $listme;
+  my $listorg;
+  my $bcount;
+  if ($container ne '0.0.0.0/0') {
+    $sth = $dbh->prepare("SELECT count(*) FROM iplist WHERE ip << ?");
+    $sth->execute($container);
+    ($bcount) = $sth->fetchrow_array();
+
+    $sth = $dbh->prepare("SELECT b.listme,o.listme ".
+        "FROM blocks b INNER JOIN orgs o ON b.orgid=o.orgid ".
+        "WHERE b.block = ?");
+    $sth->execute($container);
+    ($listme,$listorg) = $sth->fetchrow_array();
+
+    $bitmask |= $bitfields{$level-1} if $bcount >= $autolist{$masklen};
+    $bitmask |= $bitfields{block} if $listme;
+    $bitmask |= $bitfields{org} if $listorg;
+  }
+
+# hm.  can't seem to move this prepare elsewhere.  :(
   if ($nblocks > 0) {
-    my $sql = "SELECT b.block,b.listme,o.orgname,o.listme ".
-        "FROM blocks b INNER JOIN orgs o ON b.orgid=o.orgid ".
-        "WHERE b.level=$level and b.block << '$container' ORDER BY b.block, masklen(b.block) DESC";
-    $sth = $dbh->prepare($sql);
-    $sth->execute();
-    while (my ($cidr,$listblock,$org,$listorg) = $sth->fetchrow_array()) {
-      $self->export($listhosts,$mode,$level+1,$cidr,$listblock,$listorg);
+    my $sthsubblocks = $dbh->prepare("SELECT block FROM blocks ".
+        "WHERE level=? and block << ? ORDER BY block, masklen(block) DESC");
+    $sthsubblocks->execute($level, $container);
+    while (my ($cidr) = $sthsubblocks->fetchrow_array()) {
+      $self->export($listhosts,$mode,$level+1,$cidr,$bitmask);
     }
   } # avoid checking content of subs if we don't have any
@@ -303 +338 @@
   $level--;
 
-  # need this for a bunch of things, may as well do it here
-  my ($masklen) = ($container =~ m|/(\d+)$|);
-
-# Snag all parent block "is-it-listed?" data, and stuff it into a single
-# variable we can use later.  Much faster than retrieving this data
-# individually, for each octet iteration.
-
-  my $mycount = 0;
-  my $sql = "SELECT count(i.ip),b.block,b.level,b.listme AS oobblock,o.listme AS ooborg ".
-        "FROM iplist i INNER JOIN blocks b ON i.ip << b.block INNER JOIN orgs o ON b.orgid = o.orgid ".
-        "WHERE b.block >>= ? ".
-        "GROUP BY b.block,b.level,b.listme,o.listme ORDER BY b.block";
-  my $parsth = $dbh->prepare($sql);
-  $parsth->execute($container);
-  my $pdata = 0;
-  while (my ($pcount,$p,$plev,$pblock,$porg) = $parsth->fetchrow_array) {
-    my ($pmasklen) = ($p =~ m|\d+/(\d+)$|);
-    $pdata |= $bitfields{$plev} if $pcount >= $autolist{$pmasklen};
-    $pdata |= $bitfields{block} if $pblock;
-    $pdata |= $bitfields{org} if $porg;
-    $mycount = $pcount if $p eq $container;
-  }
-
   if ($mode eq 'cidr') {
-    $listhosts->{$container} |= $pdata if $pdata && ($ooborg || $oobblock || ($mycount >= $autolist{$masklen}));
+    $listhosts->{$container} |= $bitmask if $bitmask && ($listme || $listorg || ($bcount >= $autolist{$masklen}));
   } else {
   # if $cidr->masklen is <= 24, iterate on /24 boundaries for bulk sublisting
@@ -333 +345 @@
   # if $cidr->masklen is <= 8, iterate on /8 boundaries for bulk sublisting
 
-    if ($pdata) {
+    if ($bitmask) {
       my @blocksubs;
       if ($masklen <= 30 && $masklen > 24) {
@@ -340 +352 @@
           my $host = "$net$entry";
           $listhosts->{$host} = 0 if !defined($listhosts->{$host});
-          $listhosts->{$host} |= $pdata;
+          $listhosts->{$host} |= $bitmask;
         }
       } elsif ($masklen <= 24 && $masklen > 16) {
@@ -346 +358 @@
         for (my $entry = $octet; $entry < ($octet + $howmany[$masklen]); $entry++) {
           my $twofour = "$net$entry.*";
-          $listhosts->{$twofour} |= $pdata;
+          $listhosts->{$twofour} |= $bitmask;
         }
       } elsif ($masklen <= 16 && $masklen > 8) {
@@ -352 +364 @@
         for (my $entry = $octet; $entry < ($octet + $howmany[$masklen]); $entry++) {
           my $sixteen = "$net$entry.*";
-          $listhosts->{$sixteen} |= $pdata;
+          $listhosts->{$sixteen} |= $bitmask;
         }
       } elsif ($masklen <= 8) {
@@ -358 +370 @@
         for (my $entry = $octet; $entry < ($octet + $howmany[$masklen]); $entry++) {
           my $eight = "$entry.*";
-          $listhosts->{$eight} |= $pdata;
+          $listhosts->{$eight} |= $bitmask;
         }
       }
 
-#print "DEBUG1: $container, ".(@blocksubs + 0)."\n";
-# this seems to be a BIG timesink...  execution time ~1:30 without, ~4:30 with
-#if (0){
-#    $sth = $dbh->prepare("select block,level,listme from blocks where block >> ?");
-#    my $sth2 = $dbh->prepare("select count(*) from iplist where ip << ?");
-#    foreach (@blocksubs) {
-#print "  DEBUG: $_ container-is-listed check\n";
-# collect info on container block(s)
-#      $sth->execute($container);
-#      while (my ($parent, $plev, $listme) = $sth->fetchrow_array()) {
-#       $sth2->execute($parent);
-#       my ($parlen) = ($parent =~ m|/(\d+)|);
-#       my ($parcount) = $sth2->fetchrow_array();
-#print "  DEBUG: $parent: $parlen, $parcount, $plev\n";
-#       $listhosts->{$_} |= $bitfields{$plev} if $parcount >= $autolist{$parlen};  #hmm.
-#       $listhosts->{$_} |= $bitfields{block} if $listme;
-#      }
-#    }
-#}
-
     } # generate autolist entries for ips/octets not (yet) seen in reports
 
   } # cidr vs classful mode
 
-  $sth = $dbh->prepare("SELECT ip,s4list FROM iplist WHERE ip << ? ORDER BY ip");
-  $sth->execute($container);
-  while (my ($ip,$moron) = $sth->fetchrow_array()) {
-    $listhosts->{$ip} |= $pdata;
+  $sthmoron->execute($container);
+  while (my ($ip,$moron) = $sthmoron->fetchrow_array()) {
+    $listhosts->{$ip} |= $bitmask;
     if ($moron) {
       $listhosts->{$ip} = $bitfields{slist};

trunk/dnsbl/export-dnsbl

@@ -26 +26 @@
 #$dnsbl->export($ipref,$mode,1,'76.73.0.0/17');
 #$dnsbl->export($ipref,$mode,1,'174.36.0.0/15');
+$dnsbl->initexport;
 $dnsbl->export($ipref,$mode);