Changeset 368

Timestamp:

07/26/12 18:18:22 (12 years ago)

Author:

Kris Deugau

Message:

/trunk

First stage implementation of export caching for performance. See #38.
A lot of the actual performance boost comes from indexes on a couple of
columns in WHERE clauses:

• status column on domains and revzones
• type, domain_id and rdns_id columns on records

Net speedup on ~100K records and ~3K zones was
~120s -> 20s (no cache) -> 4s (cache active)

Location:

trunk

Files:

• DNSDB.pm (modified) (7 diffs)
• dns-1.0-1.2.sql (modified) (3 diffs)

trunk/DNSDB.pm

@@ -30 +30 @@
 use NetAddr::IP qw(:lower);
 use POSIX;
+use Fcntl qw(:flock);
+
 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
 
@@ -142 +144 @@
 #               cssdir  => 'templates/',
                 sessiondir      => 'session/',
+                exportcache     => 'cache/',
 
                 # Session params
@@ -1271 +1274 @@
       $config{log_failures}     = $1 if /^log_failures\s*=\s*([a-z01]+)/i;
       $config{perpage}          = $1 if /^perpage\s*=\s*(\d+)/i;
+      $config{exportcache}      = $1 if m{^exportcache\s*=\s*([a-z0-9/_.-]+)}i;
     }
     close CFG;
@@ -4209 +4213 @@
   my %recflags;
 
-  my $domsth = $dbh->prepare("SELECT domain_id,domain,status FROM domains WHERE status=1");
+  my $domsth = $dbh->prepare("SELECT domain_id,domain,status,changed FROM domains WHERE status=1");
   my $recsth = $dbh->prepare("SELECT host,type,val,distance,weight,port,ttl,record_id ".
-        "FROM records WHERE domain_id=? AND NOT type=65283");
+        "FROM records WHERE domain_id=? AND type < 65280");     # Just exclude all types relating to rDNS
+  my $zonesth = $dbh->prepare("UPDATE domains SET changed=0 WHERE domain_id=?");
   $domsth->execute();
-  while (my ($domid,$dom,$domstat) = $domsth->fetchrow_array) {
-    $recsth->execute($domid);
-    while (my ($host,$type,$val,$dist,$weight,$port,$ttl,$recid) = $recsth->fetchrow_array) {
-      next if $recflags{$recid};
+  while (my ($domid,$dom,$domstat,$changed) = $domsth->fetchrow_array) {
+##fixme: need to find a way to block opening symlinked files without introducing a race.
+#       O_NOFOLLOW
+#              If  pathname  is a symbolic link, then the open fails.  This is a FreeBSD extension, which was
+#              added to Linux in version 2.1.126.  Symbolic links in earlier components of the pathname  will
+#              still be followed.
+# but that doesn't help other platforms.  :/
+    sysopen(ZONECACHE, "$config{exportcache}/$dom", O_RDWR|O_CREAT);
+    flock(ZONECACHE, LOCK_EX);
+    if ($changed || -s "$config{exportcache}/$dom" == 0) {
+      $recsth->execute($domid);
+      while (my ($host,$type,$val,$dist,$weight,$port,$ttl,$recid) = $recsth->fetchrow_array) {
+        next if $recflags{$recid};
 ##fixme:  need to store location in the db, and retrieve it here.
 # temporarily hardcoded to empty so we can include it further down.
-      my $loc = '';
+        my $loc = '';
 
 ##fixme:  record validity timestamp. tinydns supports fiddling with timestamps.
@@ -4225 +4239 @@
 # timestamps are TAI64
 # ~~ 2^62 + time()
-      my $stamp = '';
-
-      # support tinydns' auto-TTL
-      $ttl = '' if $ttl == '0';
-
-      _printrec_tiny($datafile,'n',\%recflags,$dom,$host,$type,$val,$dist,$weight,$port,$ttl,$loc,$stamp);
-      $recflags{$recid} = 1;
-
-    } # while ($recsth)
+        my $stamp = '';
+
+        # support tinydns' auto-TTL
+        $ttl = '' if $ttl == '0';
+
+        _printrec_tiny($datafile, 'n', \%recflags,
+                $dom, $host, $type, $val, $dist, $weight, $port, $ttl, $loc, $stamp);
+
+        _printrec_tiny(*ZONECACHE, 'n', \%recflags,
+                $dom, $host, $type, $val, $dist, $weight, $port, $ttl, $loc, $stamp)
+                if *ZONECACHE;
+        # in case the zone shrunk, get rid of garbage at the end of the file.
+        truncate(ZONECACHE, tell(ZONECACHE));
+
+        $recflags{$recid} = 1;
+      } # while ($recsth)
+    } else {
+      # domain not changed, stream from cache
+      print $datafile $_ while <ZONECACHE>;
+    }
+    close ZONECACHE;
+    # mark domain as unmodified
+    $zonesth->execute($domid);
   } # while ($domsth)
 
-  my $revsth = $dbh->prepare("SELECT rdns_id,revnet,status FROM revzones WHERE status=1 ".
+  my $revsth = $dbh->prepare("SELECT rdns_id,revnet,status,changed FROM revzones WHERE status=1 ".
         "ORDER BY masklen(revnet) DESC");
 # For reasons unknown, we can't sanely UNION these statements.  Feh.
@@ -4256 +4284 @@
         "FROM records WHERE rdns_id=? AND not type=6 ".
         "ORDER BY masklen(CAST(val AS inet)) DESC, CAST(val AS inet)");
+  $zonesth = $dbh->prepare("UPDATE revzones SET changed=0 WHERE rdns_id=?");
   $revsth->execute();
-  while (my ($revid,$revzone,$revstat) = $revsth->fetchrow_array) {
-    $soasth->execute($revid);
-    my (@zsoa) = $soasth->fetchrow_array();
-    _printrec_tiny($datafile,'y',\%recflags,$revzone,
+  while (my ($revid,$revzone,$revstat,$changed) = $revsth->fetchrow_array) {
+##fixme: need to find a way to block opening symlinked files without introducing a race.
+#       O_NOFOLLOW
+#              If  pathname  is a symbolic link, then the open fails.  This is a FreeBSD extension, which was
+#              added to Linux in version 2.1.126.  Symbolic links in earlier components of the pathname  will
+#              still be followed.
+# but that doesn't help other platforms.  :/
+    my $tmpzone = NetAddr::IP->new($revzone);
+    sysopen(ZONECACHE, "$config{exportcache}/".$tmpzone->network->addr, O_RDWR|O_CREAT);
+    flock(ZONECACHE, LOCK_EX);
+    if ($changed || -s "$config{exportcache}/".$tmpzone->network->addr == 0) {
+      # need to fetch this separately since the rest of the records all (should) have real IPs in val
+      $soasth->execute($revid);
+      my (@zsoa) = $soasth->fetchrow_array();
+      _printrec_tiny($datafile,'y',\%recflags,$revzone,
         $zsoa[0],$zsoa[1],$zsoa[2],$zsoa[3],$zsoa[4],$zsoa[5],$zsoa[6],'','');
 
-    $recsth->execute($revid);
-    while (my ($host,$type,$val,$dist,$weight,$port,$ttl,$recid) = $recsth->fetchrow_array) {
-      next if $recflags{$recid};
+      $recsth->execute($revid);
+      while (my ($host,$type,$val,$dist,$weight,$port,$ttl,$recid) = $recsth->fetchrow_array) {
+        next if $recflags{$recid};
 ##fixme:  need to store location in the db, and retrieve it here.
 # temporarily hardcoded to empty so we can include it further down.
-      my $loc = '';
+        my $loc = '';
 
 ##fixme:  record validity timestamp. tinydns supports fiddling with timestamps.
@@ -4274 +4314 @@
 # timestamps are TAI64
 # ~~ 2^62 + time()
-      my $stamp = '';
-
-      # support tinydns' auto-TTL
-      $ttl = '' if $ttl == '0';
-
-      _printrec_tiny($datafile,'y',\%recflags,$revzone,$host,$type,$val,$dist,$weight,$port,$ttl,$loc,$stamp);
-      $recflags{$recid} = 1;
-
-    } # while ($recsth)
+        my $stamp = '';
+
+        # support tinydns' auto-TTL
+        $ttl = '' if $ttl == '0';
+
+        _printrec_tiny($datafile, 'y', \%recflags, $revzone,
+                $host, $type, $val, $dist, $weight, $port, $ttl, $loc, $stamp);
+        _printrec_tiny(*ZONECACHE, 'y', \%recflags, $revzone,
+                $host, $type, $val, $dist, $weight, $port, $ttl, $loc, $stamp)
+                if *ZONECACHE;
+        # in case the zone shrunk, get rid of garbage at the end of the file.
+        truncate(ZONECACHE, tell(ZONECACHE));
+
+        $recflags{$recid} = 1;
+      } # while ($recsth)
+    } else {
+      # zone not changed, stream from cache
+      print $datafile $_ while <ZONECACHE>;
+    }
+    close ZONECACHE;
+    # mark domain as unmodified
+    $zonesth->execute($revid);
   } # while ($domsth)
 

trunk/dns-1.0-1.2.sql

@@ -27 +27 @@
 ALTER TABLE domains ALTER COLUMN changed SET DEFAULT true;
 ALTER TABLE domains ALTER COLUMN changed SET NOT NULL;
+-- ~2x performance boost iff most zones are fed to output from the cache
+CREATE INDEX dom_status_index ON domains (status);
 
 CREATE TABLE revzones (
@@ -38 +40 @@
     changed boolean DEFAULT true NOT NULL
 );
+CREATE INDEX rev_status_index ON revzones (status);
 
 ALTER TABLE log ADD COLUMN rdns_id INTEGER;
@@ -49 +52 @@
 UPDATE records SET rdns_id=0;
 ALTER TABLE records ALTER COLUMN rdns_id SET NOT NULL;
+-- ~120s -> 75s performance boost on 100K records when always exporting all records
+CREATE INDEX rec_types_index ON records (type);
+-- Further ~1/3 performance gain, same dataset
+CREATE INDEX rec_domain_index ON records (domain_id);
+CREATE INDEX rec_revzone_index ON records (rdns_id);
 
 -- May as well drop and recreate;  this is nominally static and loaded from the