[Spacewalk-list] Repo Sync speed problem

[pradelles nicolas]

I have created this index following the sql file, but without "UNIQUE" option because the actual table contains duplicates keys. After a new repos sync test the speed is incredible faster :)

Thanks for your help.
Nicolas



-----Original Message-----
From: spacewalk-list-bounces at redhat.com [mailto:spacewalk-list-bounces at redhat.com] On Behalf Of Jan Pazdziora
Sent: mercredi 17 août 2011 09:18
To: spacewalk-list at redhat.com
Subject: Re: [Spacewalk-list] Repo Sync speed problem

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

_______________________________________________
Spacewalk-list mailing list
Spacewalk-list at redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-list

This message may contain confidential or privileged information and is 
intended only for the personal and confidential use of the designated
recipient(s) named above. If you are not one of the intended recipients 
of this message you are hereby notified that any review, dissemination, 
distribution or copying is strictly prohibited. 
This communication is for information purposes only and should not be 
regarded as an official statement of Eutelsat S.A. or any of its 
subsidiaries. E-mail transmission cannot be guaranteed to be secure or 
error-free. Therefore, Eutelsat S.A. does not claim that this information
is complete or accurate and it should not be treated as such.