Attached you will find the patch as per the documentation https://fedorahosted.org/spacewalk/wiki/GitGuide . Sorry about all the emails and not following documentation.
From: spacewalk-devel-boun...@redhat.com [mailto:spacewalk-devel-boun...@redhat.com] On Behalf Of Jeremy D Davis Sent: Thursday, February 13, 2014 3:26 PM To: spacewalk-devel@redhat.com Subject: Re: [Spacewalk-devel] Query question regarding system.listLatestUpgradeablePackages Hello Spacewalk Developers, After asking the last question I decided to go ahead and make a change of my own to see if I can improve performance. I was able to improve the query by at least 6 seconds faster. The old query was taking about 6101 ms to finish where I my query takes about 161 ms. Much improvement. Please let me know if there is anything I need to change. If everything looks good please let me know how I can get this added for the next release of Spacewalk (Prefer 2.1). The new query: <mode name="system_upgradable_package_list_no_errata_info"> <query params="sid"> SELECT n.name, NVL((sp.evr).epoch, ' ') as from_epoch, NVL((sp.evr).version, ' ') as from_version, NVL((sp.evr).release, ' ') as from_release, spa.label as from_arch, spa.label as arch, NVL((up.evr).epoch, ' ') as to_epoch, NVL((up.evr).version, ' ') as to_version, NVL((up.evr).release, ' ') as to_release, up.arch_label as to_arch, up.id as to_package_id FROM (SELECT sp.server_id, sp.name_id, sp.package_arch_id, sp_pe.evr FROM rhnServerPackage sp join rhnPackageEvr sp_pe ON sp_pe.id = sp.evr_id WHERE sp.server_id = :sid) sp JOIN rhnPackageName n ON n.id = sp.name_id JOIN rhnPackageArch spa ON spa.id = sp.package_arch_id, ( SELECT p.id, p.name_id, e.evr, a.id as arch_id, a.label as arch_label FROM (SELECT p.name_id, max(e.evr) as max_evr FROM rhnServerNeededCache nc JOIN rhnPackage p ON p.id = nc.package_id JOIN rhnPackageEvr e ON e.id = p.evr_id WHERE nc.server_id = :sid GROUP BY p.name_id) u JOIN rhnPackageEvr e ON e.evr = u.max_evr JOIN rhnPackage p ON p.name_id = u.name_id AND p.evr_id = e.id JOIN rhnChannelPackage cp ON cp.package_id = p.id JOIN rhnServerChannel sc ON sc.channel_id = cp.channel_id AND sc.server_id = :sid JOIN rhnPackageArch a ON a.id = p.package_arch_id ) up, rhnPackageUpgradeArchCompat puac WHERE puac.package_arch_id = sp.package_arch_id AND puac.package_upgrade_arch_id = up.arch_id AND sp.evr < up.evr AND sp.name_id = up.name_id </query> </mode> Thank you for your time and have a great day! Regards, Jeremy From: spacewalk-devel-boun...@redhat.com<mailto:spacewalk-devel-boun...@redhat.com> [mailto:spacewalk-devel-boun...@redhat.com] On Behalf Of Jeremy D Davis Sent: Wednesday, February 12, 2014 9:44 PM To: spacewalk-devel@redhat.com<mailto:spacewalk-devel@redhat.com> Subject: [Spacewalk-devel] Query question regarding system.listLatestUpgradeablePackages Hello Spacewalk Developers, I was looking at Package_queries.xml and discovered that the following query is being used to pull a list of packages that need to be installed on the server that are being displayed on the Web UI as needing updated. The reason for pulling this query is that we have discovered this query is taking a while to display results and is causing problems for our environment when the system.listLatestUpgradablePackages is used on a lot of servers. So we decided to look at it to see if we could improve the query in some way. The query in question is: <mode name="system_upgradable_package_list_no_errata_info"> <query params="sid"> SELECT n.name, NVL((sp.evr).epoch, ' ') as from_epoch, NVL((sp.evr).version, ' ') as from_version, NVL((sp.evr).release, ' ') as from_release, spa.label as from_arch, spa.label as arch, NVL((up.evr).epoch, ' ') as to_epoch, NVL((up.evr).version, ' ') as to_version, NVL((up.evr).release, ' ') as to_release, up.arch_label as to_arch, up.id as to_package_id FROM (SELECT sp.server_id, sp.name_id, sp.package_arch_id, sp_pe.evr FROM rhnServerPackage sp join rhnPackageEvr sp_pe ON sp_pe.id = sp.evr_id WHERE sp.server_id = :sid) sp JOIN rhnPackageName n ON n.id = sp.name_id JOIN rhnPackageArch spa ON spa.id = sp.package_arch_id, ( SELECT p.id, p.name_id, e.evr, a.id as arch_id, a.label as arch_label FROM (SELECT p.name_id, max(e.evr) as max_evr FROM rhnServerChannel sc JOIN rhnChannelPackage cp ON cp.channel_id = sc.channel_id JOIN rhnPackage p ON p.id = cp.package_id JOIN rhnPackageEvr e ON e.id = p.evr_id WHERE sc.server_id = :sid GROUP BY p.name_id) u JOIN rhnPackageEvr e ON e.evr = u.max_evr JOIN rhnPackage p ON p.name_id = u.name_id AND p.evr_id = e.id JOIN rhnChannelPackage cp ON cp.package_id = p.id JOIN rhnServerChannel sc ON sc.channel_id = cp.channel_id AND sc.server_id = :sid JOIN rhnPackageArch a ON a.id = p.package_arch_id ) up, rhnPackageUpgradeArchCompat puac WHERE puac.package_arch_id = sp.package_arch_id AND puac.package_upgrade_arch_id = up.arch_id AND sp.evr < up.evr AND sp.name_id = up.name_id </query> </mode> My question is why use this really big query when you could use a select * from rhnserverneededcache where serverid=x and pull all the packages that way. Granted you would need to do some joins to get the correct names based on the package id but it just seem like rhnserverneededcache would be a better place to obtain the information than this long query is doing. Please correct me if I am misunderstanding something. Thank you for your time and have a great day! Regards, Jeremy Kind regards, Jeremy Davis, RHCE, RHCSA, LPIC-1 Linux Engineer jdda...@godaddy.com<mailto:jdda...@godaddy.com> This email message and any attachments hereto is intended for use only by the addressee(s) named herein. If you have received this email in error, please immediately notify the sender and permanently delete the original and any copy of this message and its attachments.
0001-Changed-system_upgradable_package_list_no_errata_inf.patch
Description: 0001-Changed-system_upgradable_package_list_no_errata_inf.patch
_______________________________________________ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
