Re: [Sks-devel] Adding DB_INIT_LOCK to sks-keyserver (revisited)

[Jeff Johnson]

On Feb 27, 2010, at 10:52 AM, Jason Harris wrote:

> On Fri, Feb 26, 2010 at 08:20:15PM -0500, Jeff Johnson wrote:
> > Here's one datapoint from RPM which has used both BDB and Sqlite3:
> > 	sqlite3 (after tuning) was 2.4 times slower than BDB (without  
> > tuning).
> > The straightforward implementation was 14x slower for database  
> > intensive operations.
>
> Interesting.  How big were the BlOBs?

I can only answer roughly. The original measurements were
made in March 2005, and rechecked fall of 2008.

But the average metadata blob size for packages is
        ls -al /var/lib/rpm/Packages
divided by
        rpm -qa | wc -l
which (on a CentOS 5 VM I happen to have around) is
        ~36Kb per metadata blob


> > Can you write down the SQL schema currently used in SKS? Every SQL
> > implementation starts with the schema ...

Oooh, poifect, thank you!

> DB      DESCRIPTION             SCHEMA
> key     raw key blocks          SKS hash -> 1 byte indicator + fastbuild info
>                                 or       -> 1 byte indicator + actual keyblock

The 1b indicator poisons the use of DB->associate. Not
hard (but annoying) to attach the attribute through other
means, but ...

> keyid   primary keyids          SKS hash -> pubkey keyids (32 bit keyid)

Is (SKS hash & 0xffffffff) == keyid always? If so the keyids table
might be finessed out, and the 64b SKS hash used always/everywhere.

> meta    filters                 literally just the single record:
>                                 "filters" -> "yminsky.dedup,yminsky.merge"

So persistent objects. nothing to do with the key store.

> subkeyid subkey keyids          SKS hash -> subkey keyids (32 bit keyid)

Ah subkeys might be the issue that led to a separate keyid table.
Perhaps the subkeys and keyids can be combined into a single table,
and the 64b primary keyid used to retrieve the key-material?

> time    shash timestamps        timestamp -> add/delete flag + shash

What is "shash"? Same as SKS hash?

Is this time stamp contained in key-material or is it related
to when a key is encountered when synchronizing?

> tqeuue  mail queue              currently empty...

*shrug*

> word    userid words            userid word -> SKS hash

OK. What might be done here to support pattern based
searches is not to split the words and using a join,
but rather to save the full userid as a secondary -> primary,
and then apply patterns (using DB_DBT_PARTIAL on the stem of
*RE pattern) and collecting the SKS hashes desired.

The key-material can then be retrieved in a second pass.

The reason for doing so is to minimize the number of keys
in the word store.

Can anyone attempt the PTree schema at a detailed level?

The schema is likely simple, but a description of
the access pattern would start to shed light on turning
on DB_INIT_LOCK ...

Thanks!

73 de Jeff