BIND 10 #324: improve SQL data source performance

[BIND 10 Development]

#324: improve SQL data source performance
-------------------------------------+-------------------------------------
                   Reporter:  each   |                 Owner:  UnAssigned
                       Type:         |                Status:  assigned
  enhancement                        |             Milestone:  Next-Sprint-
                   Priority:  major  |  Proposed
                  Component:  data   |            Resolution:
  source                             |             Sensitive:  0
                   Keywords:         |           Sub-Project:  DNS
  performance                        |  Estimated Difficulty:  0.0
            Defect Severity:  N/A    |           Total Hours:  0
Feature Depending on Ticket:         |
  sqlite schema upgrade              |
        Add Hours to Ticket:  0      |
                  Internal?:  0      |
-------------------------------------+-------------------------------------
Changes (by shane):

 * milestone:  Year 3 Task Backlog => Next-Sprint-Proposed


Comment:

 Perhaps we could include the relevant bits as part of our performance
 improvement work?

 The schema change is:

 {{{#!patch
 --- a/src/lib/datasrc/sqlite3_datasrc.cc
 +++ b/src/lib/datasrc/sqlite3_datasrc.cc
 @@ -54,9 +54,11 @@ struct Sqlite3Parameters {
  };

  namespace {

 +// SQL statements to create a database file. schema version 2.
  const char* const SCHEMA_LIST[] = {
 +    "BEGIN EXCLUSIVE TRANSACTION",
      "CREATE TABLE schema_version (version INTEGER NOT NULL)",
 -    "INSERT INTO schema_version VALUES (1)",
 +    "INSERT INTO schema_version VALUES (2)",
      "CREATE TABLE zones (id INTEGER PRIMARY KEY, "
      "name STRING NOT NULL COLLATE NOCASE, "
      "rdclass STRING NOT NULL COLLATE NOCASE DEFAULT 'IN', "
 }}}
 {{{#!patch
 --- a/src/lib/datasrc/sqlite3_datasrc.cc
 +++ b/src/lib/datasrc/sqlite3_datasrc.cc
 @@ -67,14 +69,32 @@ const char* const SCHEMA_LIST[] = {
      "rname STRING NOT NULL COLLATE NOCASE, ttl INTEGER NOT NULL, "
      "rdtype STRING NOT NULL COLLATE NOCASE, sigtype STRING COLLATE
 NOCASE, "
      "rdata STRING NOT NULL)",
 -    "CREATE INDEX records_byname ON records (name)",
 -    "CREATE INDEX records_byrname ON records (rname)",
 +    "CREATE INDEX records_byname ON records (zone_id, name, rdtype, "
 +    "sigtype, id, ttl, rdata)",
 +    "CREATE INDEX records_byrname ON records (zone_id, rname, rdtype,
 name)",
      "CREATE TABLE nsec3 (id INTEGER PRIMARY KEY, zone_id INTEGER NOT
 NULL, "
      "hash STRING NOT NULL COLLATE NOCASE, "
      "owner STRING NOT NULL COLLATE NOCASE, "
      "ttl INTEGER NOT NULL, rdtype STRING NOT NULL COLLATE NOCASE, "
      "rdata STRING NOT NULL)",
 -    "CREATE INDEX nsec3_byhash ON nsec3 (hash)",
 +    "CREATE INDEX nsec3_byhash ON nsec3 (zone_id, hash, rdtype, "
 +    "id, ttl, rdata)",
 +    "COMMIT",
 +    NULL
 +};
 }}}

 The important bits the changes to the indexes.

-- 
Ticket URL: <http://bind10.isc.org/ticket/324#comment:4>
BIND 10 Development <http://bind10.isc.org>
BIND 10 Development