BIND 10 #2610: Query for large zone in SQLite datasrc takes a very long time

[BIND 10 Development]

#2610: Query for large zone in SQLite datasrc takes a very long time
-------------------------------------+-------------------------------------
            Reporter:  vorner        |                        Owner:
                Type:  defect        |                       Status:  new
            Priority:  medium        |                    Milestone:  New
           Component:  data source   |  Tasks
            Keywords:                |                   Resolution:
           Sensitive:  0             |                 CVSS Scoring:
         Sub-Project:  DNS           |              Defect Severity:  N/A
Estimated Difficulty:  0             |  Feature Depending on Ticket:
         Total Hours:  0             |          Add Hours to Ticket:  0
                                     |                    Internal?:  0
-------------------------------------+-------------------------------------

Comment (by jinmei):

 Replying to [comment:4 jinmei]:

 > > > Maybe I wasn't clear in the description. I sent a query. The
 > > > server started to eat 100% CPU and then, after working for that 12
 > > > seconds, it sent answer (and returned to being idle).
 >
 > FYI, I just tested a simple query with a very large zone at its apex
 > and didn't see this problem.  So it may also be related to the
 > characteristic of your zone data.

 I guess you created your sqlite3 DB file by (new) b10-loadzone from
 the scratch.  In this case the creation of the DB and tables are done
 by sqlite3_accessor, and it doesn't create indexes.  If my guess is
 correct, it's quite likely to be the reason for your problem.

 An operational workaround would be to create the indexes by hand:

 {{{
 % sqlite3 zone.sqlite3
 sqlite> CREATE INDEX zones_byname ON zones (name);
 sqlite> CREATE INDEX records_byname ON records (name);
 sqlite> CREATE INDEX records_byrname ON records (rname);
 sqlite> CREATE INDEX records_bytype_and_rname ON records (rdtype, rname);
 sqlite> CREATE INDEX nsec3_byhash ON nsec3 (hash);
 sqlite> CREATE INDEX nsec3_byhash_and_rdtype ON nsec3 (hash, rdtype);
 }}}

 For a more complete solution, I propose scheduling #1861 at this
 opportunity, and have (new) b10-loadzone use it.

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