[Yum-devel] Sqlite performance + tuning

[Florian Festi]

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.

If the upper point is observed the sqlite db is fast enough to do all 
searching. That way it can replace the memory and time consuming creation of 
in memory indices/hashes.

At several places it is possible to choose which way to resolve thing: from 
the requesting or from the fulfilling side. The numbers differ a lot in some 
cases to the advantage of the non obvious (obsoletes, file requires, 
obsolete conflicts)

I kept the single points short to avoid too much text. Feel free to ask 
about details.


Have fun

	Florian