[Spacewalk-list] Repo Sync speed problem

[Jan Pazdziora]

On Tue, Aug 16, 2011 at 04:58:37PM +0200, pradelles nicolas wrote:
> We use Oracle as database backend, client version 11.2.0.2 on Spacewalk server and 10.2.0.4 on external AIX Oracle server.
> 
> After checking with a DBA, the Oracle server is very CPU stressed by the reposync script, specially by the following SQL command:
> ---
> select /*+index(rhnPackageCapability rhn_pkg_cap_name_version_uq)*/ id from rhnPackageCapability where name = :name and version is null
> ---
> 
> It seems the script is reading continuously the table "rhnPackageCapability" to search file path (column "name"). As there is no index on this column (quite normal for a filepath list), the "select" is parsing the +2'000'000 lines of the table at each select.
> 

How come you have no index on that column? We create the index there

	http://git.fedorahosted.org/git/?p=spacewalk.git;a=blob;f=schema/spacewalk/common/tables/rhnPackageCapability.sql#l32

and in the select we explicitly hint for it to be used. If you don't
have the index rhn_pkg_cap_name_version_uq in your database schema,
something went wrong with the database schema population, or
something/someone has removed it.

-- 
Jan Pazdziora
Principal Software Engineer, Satellite Engineering, Red Hat