[bind10-dev] Adding another index to the records SQLite3 table

[JINMEI Tatuya / 神明達哉]

At Mon, 24 Sep 2012 19:02:32 +0200,
Michal 'vorner' Vaner <michal.vaner at nic.cz> wrote:

> > Basically, it increases file size by about 20% and decreases insert
> > performance by about 28%.
> 
> This looks like quite a lot. Do we iterate more often than insert? I'm not sure
> about it (XfrOut vs. XfrIn ‒ there are more secondary servers than primary ones,
> aren't they?). Also, is it known how much faster the iteration is?

Note that we use the iteration not only for xfrout but also for
loading the zone in to memory.

In my experiments, the iteration overhead without indices is
significant.  To complete this query "select rdtype, ttl, sigtype,
rdata, name FROM records WHERE zone_id = 1 ORDER BY rname, rdtype" for
a zone containing 8,541,516 records,

- With indices: 100sec
- Without indices: 428sec

So I personally think we somehow need to address the issue.  If we
don't like to include indices at the DB level, one possibility would
be to do it within the DatabaseClient implementation.  At least the
records are grouped by their names (actually alphabetically sorted,
but it's not really that important), and since the number of records
of a single name should be reasonably small, having in-memory buffer
within DatabaseClient and sort them may not be that bad.

Also, not directly related to this, but I'm about to propose changing
the data type for the RR types from text to integer to improve normal
lookup performance.  I'm not sure if it really helps, but I wonder
whether it might be possible indices on integers are cheaper than
those on texts.  If so, we may first try to make this level of schema
change and see how indices on them work.

---
JINMEI, Tatuya
Internet Systems Consortium, Inc.