Changeset 368 for trunk/dns-1.0-1.2.sql

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)

File:

• trunk/dns-1.0-1.2.sql (modified) (3 diffs)

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