[Spacewalk-list] Repo Sync speed problem

[Jeremy Davis]

Hello All,

I have this issue with slowness but the index does exist. It is somewhat
quick for repositories that only hve like 2 or 3 package but repositories
that have more than 300 it takes a while to process new packages. Any ideas
as to what might be the cause? Thank you for your time and have a great day!

Thanks,
Jeremy

On Wed, Aug 17, 2011 at 6:57 AM, pradelles nicolas
<npradelles at eutelsat.fr>wrote:

> 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.
>
>
>
> _______________________________________________
> Spacewalk-list mailing list
> Spacewalk-list at redhat.com
> https://www.redhat.com/mailman/listinfo/spacewalk-list
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://listman.redhat.com/archives/spacewalk-list/attachments/20110817/8e7bfba0/attachment.htm>