[Yum-devel] Sqlite performance + tuning

[seth vidal]

On Wed, 2007-03-21 at 09:26 +0100, Florian Festi wrote:
> 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.

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.

-sv