[Yum-devel] Sqlite performance + tuning

[Panu Matilainen]

On Thu, 22 Mar 2007, seth vidal wrote:

> On Wed, 2007-03-21 at 15:39 +0100, Florian Festi wrote:
>>> If you have some example code to make the indexes on the fly I'll give
>>> it a run on the XO and see how bad it really ends up being.
>>
>> Just execute the following SQL statements (terminate with ";" when in the
>> sqlite3 shell)
>>
>> CREATE INDEX IF NOT EXISTS pkgprovides ON provides (pkgKey)
>> CREATE INDEX IF NOT EXISTS pkgrequires ON requires (pkgKey)
>> CREATE INDEX IF NOT EXISTS pkgconflicts ON conflicts (pkgKey)
>> CREATE INDEX IF NOT EXISTS pkgobsoletes ON obsoletes (pkgKey)
>>
>
> it's about 15s for fc6 core-only on the XO - a bit long. Moreover it
> creates a problem if we make it on the client in that the sqlite db will
> no longer match the checksum in the repomd.xml. So we'll be downloading
> metadata that isn't, otherwise, needing it. Not much fun, that.
>
> So if we're going to get the advantage from the indexes we need to do
> them on the repo side. The only decision now is if the additional speed
> is worth the tradeoff of more to download.

Well, for current apt-rpm implementation of the sqlite repodata, the 
difference for internal cache generation for FE development repo on my 
laptop is 15 minutes vs 15 seconds.

If those indexes aren't there, to get to a decent performance level I'd 
need to read all of PRCO data for a repo into memory at a time.

Anyway, just a datapoint that for different usage patterns the time 
difference can be *huge*.

 	- Panu -