[Spacewalk-devel] [PATCH] Speedup Taskomatic cleanup-query X-fold!?!
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: rhnPackageChangeLogData301'103 rows rhnPackageChangeLogRec4'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 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 ) ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
Re: [Spacewalk-devel] [PATCH] Speedup Taskomatic cleanup-query X-fold!?!
Hello Jonathan, > Testdriving the two queries got me the following: > Original:still running since 30minutes now (top-CPU time) > modified: 3184ms The speed improvement is very nice. However here're my comments: - both DISTINCT and ORDER are syntactically correct for Oracle - ORDER BY cannot make any speed improvements, in opposite, it take some time to sort the query - regarding DISTINCT, I'm not sure, if it can have such a great impact on performance in this concrete case Could you, please, send explain plans for the original and the 'DISTINCT variant'? Regards, Tomas -- Tomas Lestach RHN Satellite Engineering, Red Hat ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
Re: [Spacewalk-devel] [PATCH] Speedup Taskomatic cleanup-query X-fold!?!
Hello Tomas, yes, of course I know that an 'ORDER BY' will actually increase the CPU effort for the query. However, my consideration was: Why is the original query running eternally? My thoughts were: The IDs to be filtered against in the DELETE [...] WHERE id NOT IN ([subselect]) were randomly ordered; I know that working on a non-sorted data input is requiring much more effort than just going for a quick sort and working on the product. So there's the reason for that. But let me get these 'explain's for you: ### ORIGINAL: ### spacewalk_1_5=# explain Delete from rhnPackageChangeLogData WHERE id NOT IN ( SELECT changelog_data_id FROM rhnPackageChangeLogRec); QUERY PLAN -- Seq Scan on rhnpackagechangelogdata (cost=108146.08..11134104860.94 rows=151984 width=6) Filter: (NOT (SubPlan 1)) SubPlan 1 -> Materialize (cost=108146.08..170240.97 rows=4465189 width=8) -> Seq Scan on rhnpackagechangelogrec (cost=0.00..86237.89 rows=4465189 width=8) (5 rows) MODIFICATION: spacewalk_1_5=# explain Delete from rhnPackageChangeLogData WHERE id NOT IN ( SELECT DISTINCT changelog_data_id FROM rhnPackageChangeLogRec ORDER BY changelog_data_id ASC); QUERY PLAN - --- Seq Scan on rhnpackagechangelogdata (cost=104819.05..115188.64 rows=151984 width=6) Filter: (NOT (hashed SubPlan 1)) SubPlan 1 -> Sort (cost=104433.39..104626.22 rows=77132 width=8) Sort Key: rhnpackagechangelogrec.changelog_data_id -> HashAggregate (cost=97400.86..98172.18 rows=77132 width=8) -> Seq Scan on rhnpackagechangelogrec (cost=0.00..86237.89 rows=4465189 width= 8) (7 rows) Ok, so I went for an 'EXPLAIN ANALYZE', on three queries: The Original, my 'original' modification and modification without the 'ORDER BY'; Turns out, without the ORDER BY, the query is another 600msec faster, while the 'Explain Analyze' of the original is again *still* executing (since 5++ Minutes). So my bad for not testing without the ORDER BY - a hunch of mine in the wrong direction. Anyhow: Here are the 'EXPLAIN ANALYSE' of Mod, and Mod-without-ORDER-BY: # Modified: # spacewalk_1_5=# explain analyze Delete from rhnPackageChangeLogData WHERE id NOT IN ( SELECT DISTINCT changelog_data_id FROM rhnPackageChangeLogRec ORDER BY changelog_data_id ASC); QUERY PLAN - - Seq Scan on rhnpackagechangelogdata (cost=104819.05..115188.64 rows=151984 width=6) (actual tim e=3476.682..3476.682 rows=0 loops=1) Filter: (NOT (hashed SubPlan 1)) SubPlan 1 -> Sort (cost=104433.39..104626.22 rows=77132 width=8) (actual time=3149.620..3192.113 row s=301937 loops=1) Sort Key: rhnpackagechangelogrec.changelog_data_id Sort Method: quicksort Memory: 26442kB -> HashAggregate (cost=97400.86..98172.18 rows=77132 width=8) (actual time=2473.983. .2553.576 rows=301937 loops=1) -> Seq Scan on rhnpackagechangelogrec (cost=0.00..86237.89 rows=4465189 width= 8) (actual time=0.008..730.139 rows=4466337 loops=1) Total runtime: 3483.172 ms (9 rows) Modified without ORDER-BY spacewalk_1_5=# explain analyze Delete from rhnPackageChangeLogData WHERE id NOT IN ( SELECT DISTINCT changelog_data_id FROM rhnPackageChangeLogRec); QUERY PLAN - --- Seq Scan on rhnpackagechangelogdata (cost=98365.01..108734.60 rows=151984 width=6) (actual time =2869.294..2869.294 rows=0 loops=1) Filter: (NOT (hashed SubPlan 1)) SubPlan 1 -> HashAggregate (cost=97400.86..98172.18 rows=77132 width=8) (actual time=2494.079..2577. 750 rows=301937 loops=1) -> Seq Scan on rhnpackagechangelogrec (cost=0.00..86237.89 rows=4465189 width=8) (ac tual time=0.011..741.078 rows=4466337 loops=1) Total runtime: 2873.545 ms (6 rows) ## Original... ## [no output yet, if it completes before my remote ssh is cut, I'll report it.] Best -Jonathan On 08/19/2011 02:54 PM, Tomas Lestach wrote: Hello Jonathan, Testdriving the two queries got me the following: Original:still running since 30minutes now (top-CPU time) modified: 3184ms The speed improvement is very nice. However here're my comments: - both DISTINCT and ORDER are syntactically correct for Oracle - ORDER BY cannot make any speed improvements, in opposite, it take some time to sort the query - regarding DI
Re: [Spacewalk-devel] [PATCH] Speedup Taskomatic cleanup-query X-fold!?!
On Mon, Aug 22, 2011 at 01:45:11PM +0200, Jonathan Hoser wrote: > Hello Tomas, > > yes, of course I know that an 'ORDER BY' will actually increase the CPU > effort for the query. > However, my consideration was: Why is the original query running eternally? > My thoughts were: The IDs to be filtered against in the DELETE [...] > WHERE id NOT IN ([subselect]) were randomly ordered; > I know that working on a non-sorted data input is requiring much more > effort than just going for a quick sort and working on the product. > So there's the reason for that. > > But let me get these 'explain's for you: > ### > ORIGINAL: > ### > spacewalk_1_5=# explain Delete from rhnPackageChangeLogData WHERE id NOT > IN ( SELECT changelog_data_id FROM rhnPackageChangeLogRec); > QUERY PLAN > -- > Seq Scan on rhnpackagechangelogdata (cost=108146.08..11134104860.94 > rows=151984 width=6) >Filter: (NOT (SubPlan 1)) >SubPlan 1 > -> Materialize (cost=108146.08..170240.97 rows=4465189 width=8) >-> Seq Scan on rhnpackagechangelogrec (cost=0.00..86237.89 > rows=4465189 width=8) > (5 rows) > > > MODIFICATION: > > spacewalk_1_5=# explain Delete from rhnPackageChangeLogData WHERE id NOT > IN ( SELECT DISTINCT changelog_data_id FROM rhnPackageChangeLogRec ORDER > BY changelog_data_id ASC); > QUERY PLAN > > - > --- > Seq Scan on rhnpackagechangelogdata (cost=104819.05..115188.64 > rows=151984 width=6) >Filter: (NOT (hashed SubPlan 1)) >SubPlan 1 > -> Sort (cost=104433.39..104626.22 rows=77132 width=8) >Sort Key: rhnpackagechangelogrec.changelog_data_id >-> HashAggregate (cost=97400.86..98172.18 rows=77132 width=8) > -> Seq Scan on rhnpackagechangelogrec > (cost=0.00..86237.89 rows=4465189 width= > 8) > (7 rows) > > All right. I see there is some speed improvement in the DISTINCT variant for Postgresql. We've tried to check explain plans for Oracle and there was no difference. Committing as: c529c7ea18211e9b5f0ee95469625893d9f8e30e The fix will be available in nightly repo in spacewalk-java-1.6.28-1. Regards, Tomas -- Tomas Lestach RHN Satellite Engineering, Red Hat > Ok, so I went for an 'EXPLAIN ANALYZE', on three queries: > The Original, my 'original' modification and modification without the > 'ORDER BY'; > Turns out, without the ORDER BY, the query is another 600msec faster, > while the 'Explain Analyze' of the original is again *still* executing > (since 5++ Minutes). > So my bad for not testing without the ORDER BY - a hunch of mine in the > wrong direction. > > Anyhow: Here are the 'EXPLAIN ANALYSE' of Mod, and Mod-without-ORDER-BY: > > # > Modified: > # > spacewalk_1_5=# explain analyze Delete from rhnPackageChangeLogData > WHERE id NOT IN ( SELECT DISTINCT changelog_data_id FROM > rhnPackageChangeLogRec ORDER BY changelog_data_id ASC); > > QUERY PLAN > > - > - > Seq Scan on rhnpackagechangelogdata (cost=104819.05..115188.64 > rows=151984 width=6) (actual tim > e=3476.682..3476.682 rows=0 loops=1) >Filter: (NOT (hashed SubPlan 1)) >SubPlan 1 > -> Sort (cost=104433.39..104626.22 rows=77132 width=8) (actual > time=3149.620..3192.113 row > s=301937 loops=1) >Sort Key: rhnpackagechangelogrec.changelog_data_id >Sort Method: quicksort Memory: 26442kB >-> HashAggregate (cost=97400.86..98172.18 rows=77132 > width=8) (actual time=2473.983. > .2553.576 rows=301937 loops=1) > -> Seq Scan on rhnpackagechangelogrec > (cost=0.00..86237.89 rows=4465189 width= > 8) (actual time=0.008..730.139 rows=4466337 loops=1) > Total runtime: 3483.172 ms > (9 rows) > > > Modified without ORDER-BY > > spacewalk_1_5=# explain analyze Delete from rhnPackageChangeLogData > WHERE id NOT IN ( SELECT DISTINCT changelog_data_id FROM > rhnPackageChangeLogRec); > > QUERY PLAN > > - > --- > Seq Scan on rhnpackagechangelogdata (cost=98365.01..108734.60 > rows=151984 width=6) (actual time > =2869.294..2869.294 rows=0 loops=1) >Filter: (NOT (hashed SubPlan 1)) >SubPlan 1 > -> HashAggregate (cost=97400.86..98172.18 rows=77132 width=8) > (actual time=2494.079..2577. > 750 rows=301937 loops=1) >-> Seq Scan on rhnpackagechangelogrec (cost=0.00..86237.89 > rows=4465189 width=8) (ac > tual time
