Changeset 32 for trunk


Ignore:
Timestamp:
03/25/11 17:16:38 (14 years ago)
Author:
Kris Deugau
Message:

/trunk/dnsbl

Changeset to improve export speed on DNSBL database. By keeping
IP counts for each block, and directly tracking the parent of
each block and IP, an export taking ~90 seconds can be brought
down under 20.

  • updated initial tabledef SQL
  • Add addparents.sql SQL tabeldef and setparents.pl data update script
  • Tweak reporting of IP since it now requires the parent block(s) so the parent field can be filled in

Also add delist-ip script and Makefile

Location:
trunk/dnsbl
Files:
4 added
5 edited

Legend:

Unmodified
Added
Removed

  • trunk/dnsbl/DNSBL.pm

    r29 r32  
    138138sub initexport {
    139139  $parsth = $dbh->prepare("SELECT count(i.ip),b.block,b.level,b.listme AS oobblock,o.listme AS ooborg ".
    140         "FROM iplist i INNER JOIN blocks b ON i.ip << b.block INNER JOIN orgs o ON b.orgid = o.orgid ".
     140        "FROM iplist i INNER JOIN blocks b ON i.parent = b.block INNER JOIN orgs o ON b.orgid = o.orgid ".
    141141        "WHERE b.block >>= ? ".
    142142        "GROUP BY b.block,b.level,b.listme,o.listme ORDER BY b.block");
    143   $sthmoron = $dbh->prepare("SELECT ip,s4list FROM iplist WHERE ip << ? ORDER BY ip");
     143  $sthmoron = $dbh->prepare("SELECT ip,s4list FROM iplist WHERE parent = ?");
    144144}
    145145
    … …  
    166166  if ($rep =~ /^[\d.]+$/) {
    167167    # weesa gonna ASS-U-ME IP addresses are sanely formatted.
    168     $sth = $dbh->prepare("SELECT count FROM iplist WHERE ip=?");
    169     $sth->execute($rep) or die "eep?  ".$dbh->errstr."\n";
    170     $rows = $sth->rows;
    171     if ($rows == 0) {
    172       $sth = $dbh->prepare("INSERT INTO iplist (ip) VALUES (?)");
    173     } elsif ($rows == 1) {
    174       $sth = $dbh->prepare("UPDATE iplist SET count=count+1 WHERE ip=?");
    175     } else {
    176       die "db corrupt: found $rows matches on $rep\n";
     168    eval {
     169      $sth = $dbh->prepare("SELECT count FROM iplist WHERE ip=?");
     170      $sth->execute($rep) or die "eep?  ".$dbh->errstr."\n";
     171      $rows = $sth->rows;
     172      if ($rows == 0) {
     173        $sth = $dbh->prepare("INSERT INTO iplist (ip,parent) VALUES ".
     174                "(?,(SELECT block FROM blocks WHERE block >> ? ORDER BY level DESC LIMIT 1))");
     175        $sth->execute($rep,$rep) or die "couldn't add entry for $rep: ".$dbh->errstr."\n";
     176      } elsif ($rows == 1) {
     177        $sth = $dbh->prepare("UPDATE iplist SET count=count+1 WHERE ip=?");
     178        $sth->execute($rep) or die "couldn't update listing for $rep: ".$dbh->errstr."\n";
     179      } else {
     180        die "db corrupt: found $rows matches on $rep\n";
     181      }
     182      $sth = $dbh->prepare("SELECT block FROM blocks WHERE block >> ?");
     183      $sth->execute($rep);
     184      my $updsth = $dbh->prepare("UPDATE blocks SET ipcount=(SELECT count(*) FROM iplist WHERE ip << ?) WHERE block=?");
     185      while (my ($block) = $sth->fetchrow_array) {
     186        $updsth->execute($block,$block);
     187      }
     188      $dbh->commit;
     189    };
     190    if ($@) {
     191      my $msg = $@;
     192      return "failed adding $rep: $msg";
    177193    }
    178     $sth->execute($rep) or die "couldn't update listing for $rep: ".$dbh->errstr."\n";
    179194  } else {
    180195    return;
    181196  }
    182   $dbh->commit;
    183197  return $rows;
    184198} # end report()
    … …  
    228242  return "$blockin not a single CIDR range" if !$block;
    229243
    230   local $dbh->{AutoCommit} = 1; # force autocommit
    231 
    232   my $sth = $dbh->prepare("INSERT INTO blocks (block,orgid,level) VALUES (?,?,?)");
    233   $sth->execute("$block",$orgid,$level);
    234   return $sth->errstr if $sth->err;
     244#  local $dbh->{AutoCommit} = 1;        # force autocommit
     245
     246  my $sth;
     247  eval {
     248    my $parent = '0/0';
     249    if ($level > 0) {
     250      $sth = $dbh->prepare("SELECT block FROM blocks WHERE block >> ? ORDER BY level DESC LIMIT 1");
     251      $sth->execute("$block");
     252      ($parent) = $sth->fetchrow_array;
     253    }
     254    $sth = $dbh->prepare("INSERT INTO blocks (block,orgid,level,parent,ipcount) VALUES (?,?,?,?,".
     255        "(SELECT count(*) FROM iplist WHERE ip << ?))");
     256    $sth->execute("$block",$orgid,$level,$parent,"$block");
     257    $sth = $dbh->prepare("UPDATE iplist SET parent=? WHERE parent=? AND ip << ?");
     258    $sth->execute("$block",$parent,"$block");
     259    $dbh->commit;
     260  };
     261  if ($@) {
     262    my $msg = $@;
     263    eval { dbh->rollback; };
     264    return "failed to add $block: $msg";
     265  }
    235266  # nb: no need to return anything, since the CIDR block is the key
    236267}
    … …  
    287318    return if !$block;
    288319
    289     $sth = $dbh->prepare("SELECT count(*) FROM iplist WHERE ip << ?");
     320    $sth = $dbh->prepare("SELECT ipcount FROM blocks WHERE block = ?");
    290321    $sth->execute($entity);
    291322    my ($bcount) = $sth->fetchrow_array;
    … …  
    343374  }
    344375
    345   my $sth = $dbh->prepare("SELECT count(*) FROM blocks WHERE block << ?");
     376  my $sth = $dbh->prepare("SELECT count(*) FROM blocks WHERE parent = ?");
    346377  $sth->execute($container);
    347378  my ($nblocks) = $sth->fetchrow_array();
    … …  
    356387  my $bcount;
    357388  if ($container ne '0.0.0.0/0') {
    358     $sth = $dbh->prepare("SELECT count(*) FROM iplist WHERE ip << ?");
    359     $sth->execute($container);
    360     ($bcount) = $sth->fetchrow_array();
    361 
    362     $sth = $dbh->prepare("SELECT b.listme,o.listme ".
     389    $sth = $dbh->prepare("SELECT b.ipcount,b.listme,o.listme ".
    363390        "FROM blocks b INNER JOIN orgs o ON b.orgid=o.orgid ".
    364391        "WHERE b.block = ?");
    365392    $sth->execute($container);
    366     ($listme,$listorg) = $sth->fetchrow_array();
     393    ($bcount,$listme,$listorg) = $sth->fetchrow_array();
    367394
    368395    $bitmask |= $bitfields{$level-1} if $bcount >= $autolist{$masklen};
    … …  
    374401  if ($nblocks > 0) {
    375402    my $sthsubblocks = $dbh->prepare("SELECT block FROM blocks ".
    376         "WHERE level=? and block << ? ORDER BY block, masklen(block) DESC");
     403        "WHERE level = ? AND parent = ?");
    377404    $sthsubblocks->execute($level, $container);
    378405    while (my ($cidr) = $sthsubblocks->fetchrow_array()) {
    … …  
    490517
    491518  my $cidr = new NetAddr::IP "$block";
    492   my $sth = $dbh->prepare("select count(*) from iplist where ip << ?");
     519  my $sth = $dbh->prepare("SELECT ipcount FROM blocks WHERE block = ?");
    493520  $sth->execute("$cidr");
    494521  my ($count) = $sth->fetchrow_array;

  • trunk/dnsbl/browse.cgi

    r25 r32  
    5454my $basesql = "SELECT b.block,o.orgname,b.listme,o.listme,b.comments,o.comments ".
    5555        "FROM blocks b INNER JOIN orgs o ON b.orgid=o.orgid ".
    56         "WHERE b.block <<= ";
     56        "WHERE b.parent = ";
    5757my $sth0 = $dbh->prepare($basesql."'0/0' AND b.level=0 ORDER BY block");
    5858#my $sth0 = $dbh->prepare($basesql."'64/8' AND b.level=0 ORDER BY block");
    5959my $sth1 = $dbh->prepare($basesql."? AND b.level=1 ORDER BY block");
    6060my $sth2 = $dbh->prepare($basesql."? AND b.level=2 ORDER BY block");
    61 my $sthiplist = $dbh->prepare("select * from iplist where ip <<= ? order by ip");
     61my $sthiplist = $dbh->prepare("select * from iplist where parent = ? order by ip");
    6262
    6363my %ipseen;

  • trunk/dnsbl/dnsbl.cgi

    r29 r32  
    9696
    9797  my $err = '';
    98   my $count = $dnsbl->report($webvar{ip});
    9998  my $org0id = $dnsbl->orgexists($webvar{org0});
    10099  if (!$org0id) {
    … …  
    132131    }
    133132  }
     133  my $count = $dnsbl->report($webvar{ip});
    134134
    135135  $page->param(ip => $webvar{ip});

  • trunk/dnsbl/dnsbl.sql

    r29 r32  
    55SET client_encoding = 'UTF8';
    66SET check_function_bodies = false;
     7SET client_min_messages = warning;
    78
    89--
    9 -- TOC entry 7 (OID 196077)
    1010-- Name: blocks; Type: TABLE; Schema: public; Owner: dnsbl
    1111--
    … …  
    1616    orgid integer,
    1717    "level" integer DEFAULT 0,
    18     comments text DEFAULT ''::text
     18    comments text DEFAULT ''::text,
     19    parent cidr NOT NULL,
     20    ipcount integer NOT NULL
    1921);
    2022
    2123
    2224--
    23 -- TOC entry 8 (OID 196085)
    2425-- Name: iplist; Type: TABLE; Schema: public; Owner: dnsbl
    2526--
    … …  
    2930    count integer DEFAULT 1,
    3031    s4list boolean DEFAULT false,
    31     added timestamp with time zone DEFAULT now()
     32    added timestamp with time zone DEFAULT now(),
     33    parent cidr NOT NULL
    3234);
    3335
    3436
    3537--
    36 -- TOC entry 9 (OID 196090)
    3738-- Name: orgs; Type: TABLE; Schema: public; Owner: dnsbl
    3839--
    … …  
    4748
    4849--
    49 -- TOC entry 10 (OID 196099)
    5050-- Name: autolist; Type: TABLE; Schema: public; Owner: dnsbl
    5151--
    … …  
    5858
    5959--
    60 -- TOC entry 11 (OID 196147)
    6160-- Name: misc; Type: TABLE; Schema: public; Owner: dnsbl
    6261--
    … …  
    7069-- for tracking IPs that get removed from listing
    7170-- note we don't want a PK on IP as with iplist, since we may end up removing an IP more than once.  :/
     71--
     72-- Name: waslisted; Type: TABLE; Schema: public; Owner: dnsbl
     73--
    7274
    7375CREATE TABLE waslisted (
    … …  
    7981);
    8082
     83
    8184--
    82 -- Data for TOC entry 23 (OID 196099)
     85-- Name: autolist_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
     86--
     87
     88ALTER TABLE ONLY autolist
     89    ADD CONSTRAINT autolist_pkey PRIMARY KEY (masklen);
     90
     91
     92--
     93-- Name: blocks_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
     94--
     95
     96ALTER TABLE ONLY blocks
     97    ADD CONSTRAINT blocks_pkey PRIMARY KEY (block);
     98
     99
     100--
     101-- Name: iplist_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
     102--
     103
     104ALTER TABLE ONLY iplist
     105    ADD CONSTRAINT iplist_pkey PRIMARY KEY (ip);
     106
     107
     108--
     109-- Name: orgs_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
     110--
     111
     112ALTER TABLE ONLY orgs
     113    ADD CONSTRAINT orgs_pkey PRIMARY KEY (orgid);
     114
     115
     116--
     117-- Name: blocks_parent_index; Type: INDEX; Schema: public; Owner: dnsbl
     118--
     119
     120CREATE INDEX blocks_parent_index ON blocks USING btree (parent);
     121
     122
     123--
     124-- Name: ip_parent_index; Type: INDEX; Schema: public; Owner: dnsbl
     125--
     126
     127CREATE INDEX ip_parent_index ON iplist USING btree (parent);
     128
     129
     130--
     131-- Name: blocks_orgid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnsbl
     132--
     133
     134ALTER TABLE ONLY blocks
     135    ADD CONSTRAINT blocks_orgid_fkey FOREIGN KEY (orgid) REFERENCES orgs(orgid);
     136
     137
     138--
    83139-- Name: autolist; Type: TABLE DATA; Schema: public; Owner: dnsbl
    84140--
    … …  
    1181740       2147483648
    119175\.
    120 
    121 
    122 --
    123 -- TOC entry 12 (OID 196101)
    124 -- Name: blocks_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
    125 --
    126 
    127 ALTER TABLE ONLY blocks
    128     ADD CONSTRAINT blocks_pkey PRIMARY KEY (block);
    129 
    130 
    131 --
    132 -- TOC entry 13 (OID 196103)
    133 -- Name: iplist_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
    134 --
    135 
    136 ALTER TABLE ONLY iplist
    137     ADD CONSTRAINT iplist_pkey PRIMARY KEY (ip);
    138 
    139 
    140 --
    141 -- TOC entry 14 (OID 196105)
    142 -- Name: orgs_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
    143 --
    144 
    145 ALTER TABLE ONLY orgs
    146     ADD CONSTRAINT orgs_pkey PRIMARY KEY (orgid);
    147 
    148 
    149 --
    150 -- TOC entry 15 (OID 196215)
    151 -- Name: autolist_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
    152 --
    153 
    154 ALTER TABLE ONLY autolist
    155     ADD CONSTRAINT autolist_pkey PRIMARY KEY (masklen);
    156 
    157 
    158 --
    159 -- TOC entry 25 (OID 196107)
    160 -- Name: blocks_orgid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnsbl
    161 --
    162 
    163 ALTER TABLE ONLY blocks
    164     ADD CONSTRAINT blocks_orgid_fkey FOREIGN KEY (orgid) REFERENCES orgs(orgid);
    165 

  • trunk/dnsbl/export-dnsbl

    r29 r32  
    4747my $mode = $ARGV[0] || 'tiny';
    4848
    49 #$dnsbl->export($ipref,$mode,1,'65.60/18');
    50 #$dnsbl->export($ipref,$mode,1,'67.136.0.0/14');
    51 #$dnsbl->export($ipref,$mode,1,'83.76/15');
    52 #$dnsbl->export($ipref,$mode,1,'76.73.0.0/17');
    53 #$dnsbl->export($ipref,$mode,1,'174.36.0.0/15');
    5449$dnsbl->initexport;
     50#$dnsbl->export($ipref,$mode,1,'50.22.0.0/15');
    5551$dnsbl->export($ipref,$mode);
    5652
Note: See TracChangeset for help on using the changeset viewer.