Hi all,
here's a small proposal from my end:
Changing the Taskomatic cleanup-query's subselect to something that
makes it sane and speedy;
I noticed this query being stuck on my Spacewalk 1.4 Servers Postgresql
backend for more than 2800 CPU minutes,
when wanting to upgrade to 1.5.
Anyhow, some stats:
The two tables in question on my current installation:
rhnPackageChangeLogData 301'103 rows
rhnPackageChangeLogRec 4'385'607 rows
DISTINCTING and ORDERing the subquery (see patch) on
'rhnPackageChangeLogRec' brings the relevant row count down to 301'103;
Testdriving the two queries got me the following:
Original: ....still running since 30minutes now (top-CPU time)
modified: 3184ms
I'm not sure if the DISTINCT and ORDER stuff is syntactically correct
for Oracle, or if the issue has shown up anywhere else;
So if someone could please test it on an Oracle installation?
In the worst case, (if I'm really the only one with the issue) it
shouldn't slow down stuff too much - and in the best case accelerate the
query quite a bit.
Questions, comments?
Best
-Jonathan
--
Jonathan Hoser, M.Sc.
Institute of Bioinformatics and System Biology
WWW: http://mips.helmholtz-muenchen.de
Helmholtz Zentrum München
Deutsches Forschungszentrum für Gesundheit und Umwelt (GmbH)
Ingolstädter Landstr. 1
85764 Neuherberg
www.helmholtz-muenchen.de
Aufsichtsratsvorsitzende: MinDir´in Bärbel Brumme-Bothe
Geschäftsführer: Prof. Dr. Günther Wess und Dr. Nikolaus Blum
Registergericht: Amtsgericht München HRB 6466
USt-IdNr: DE 129521671
diff --git a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml
index 051e597..e352bbe 100644
--- a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml
+++ b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml
@@ -295,7 +295,7 @@ SELECT id, channel_label, client, reason, force, bypass_filters, next_action
<write-mode name="taskomatic_package_changelog_cleanup">
<query params="">
DELETE FROM rhnPackageChangeLogData
- WHERE id NOT IN ( SELECT changelog_data_id FROM rhnPackageChangeLogRec )
+ WHERE id NOT IN ( SELECT DISTINCT changelog_data_id FROM rhnPackageChangeLogRec ORDER BY changelog_data_id ASC )
</query>
</write-mode>
_______________________________________________
Spacewalk-devel mailing list
Spacewalk-devel@redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-devel
