[Yum-devel] Sqlite performance + tuning

[Florian Festi]

seth vidal wrote:
> On Mon, 2007-03-19 at 17:29 +0100, Florian Festi wrote:
>> Hi!
>>
>> As yum now also resolves only on the xml/sqlite data some of our results 
>> might interesting for the yum development, too.
>>
>> It turned out that there where no indices in the sqlite database for some of 
>> the common use cases:
>>
>> returnPrco() in sqlitesack.py:
>> CREATE INDEX pkgprovides ON provides (pkgKey)
>> CREATE INDEX pkgrequires ON requires (pkgKey)
>> CREATE INDEX pkgconflicts ON conflicts (pkgKey)
>> CREATE INDEX pkgobsoletes ON obsoletes (pkgKey)
>>
>> Those could be handy if the YumSqlitePackageSack.searchFiles() would try to 
>> make direct use of those columns:
>> CREATE INDEX filenames ON files (name)
>> CREATE INDEX dirnames ON filelist (dirname)
>>
>> I doubt that this will give any visible improvements with the current 
>> depsolver as it is currently hidden by other problems but as the other 
>> problems will get fixed the difference will increase. With our own resolver 
>> the time needed for resolving an "install *" on FC6 + extras + updates (6580 
>> packages) dropped from 10 to 3 Minutes.
>>
>> As there are plans to directly ship the sqlite dbs insted of the XML 
>> metadata, it might be worth checking if it is better to create the indices 
>> on the client side (sorry, didn't any checks on myself yet).
>>
>> It also turned out that if any quadratic behavior and looping over files is 
>> avoided resolving is reasonably fast.
> 
> How much bigger do these indexes make the sqlite db files?

FC6 primary.xml.gz.sqlite
-rw-r--r-- 1 ffesti ffesti  6289408 Mar 21 09:18 core
-rw-r--r-- 1 ffesti ffesti  7028736 Mar 21 09:17 core.index
-rw-r--r-- 1 ffesti ffesti  9871360 Mar 21 09:18 extras
-rw-r--r-- 1 ffesti ffesti 11011072 Mar 21 09:17 extras.index
-rw-r--r-- 1 ffesti ffesti  4164608 Mar 21 09:18 updates
-rw-r--r-- 1 ffesti ffesti  4686848 Mar 21 09:17 updates.index

-rw-r--r-- 1 ffesti ffesti 1441465 Mar 21 09:18 core.bz2
-rw-r--r-- 1 ffesti ffesti 1577672 Mar 21 09:17 core.index.bz2
-rw-r--r-- 1 ffesti ffesti 2178462 Mar 21 09:18 extras.bz2
-rw-r--r-- 1 ffesti ffesti 2393192 Mar 21 09:17 extras.index.bz2
-rw-r--r-- 1 ffesti ffesti  887487 Mar 21 09:18 updates.bz2
-rw-r--r-- 1 ffesti ffesti  985659 Mar 21 09:17 updates.index.bz2

So quite exactly 10% bigger. But we can create the indices on the client 
side if we want to save the bandwidth. Creating the indices shouldn't be 
that expensive.

Florian