Description of problem: engine=> EXPLAIN ANALYZE select * from getdisksvmguid('fa464094-a656-49dc-8384-465c5308cff7', 't', NULL, 'f'); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Function Scan on getdisksvmguid (cost=0.00..260.00 rows=1000 width=3170) (actual time=6643.344..6643.345 rows=1 loops=1) Total runtime: 6643.390 ms Version-Release number of selected component (if applicable): 3.6.2 How reproducible: 100% Steps to Reproduce: 1. loaded engine (500 hosts 7Kvms * 3 disks) Actual results: slow query, hit the CPU usage. Expected results: stable CPU, faster query. Additional info:
removing the "GROUP BY" from all_disks_including_snapshots save ~6 sec. by doing that the same amount of rows returns and query runs much faster: "Function Scan on getdisksvmguid (cost=0.00..260.00 rows=1000 width=3170) (actual time=1735.764..1735.764 rows=1 loops=1)" "Total runtime: 1735.820 ms" but it still needs some improvements. cause it takes too much CPU when running it in bulks.
(In reply to Eldad Marciano from comment #1) > removing the "GROUP BY" from all_disks_including_snapshots save ~6 sec. > > by doing that the same amount of rows returns and query runs much faster: > "Function Scan on getdisksvmguid (cost=0.00..260.00 rows=1000 width=3170) > (actual time=1735.764..1735.764 rows=1 loops=1)" > "Total runtime: 1735.820 ms" > > > > but it still needs some improvements. > cause it takes too much CPU when running it in bulks. Removing the GROUP BY is wrong - it will return multiple entries for templates that have multiple copies.
this bug has big impact in terms of performance wise. any chance to fix it for the next release?
(In reply to Eldad Marciano from comment #3) > this bug has big impact in terms of performance wise. > > any chance to fix it for the next release? This mainly depends on the RFE in bug 1142762. Once that's done, we could have a better estimation.
Moving from 4.0 alpha to 4.0 beta since 4.0 alpha has been already released and bug is not ON_QA.
oVirt 4.0 beta has been released, moving to RC milestone.
This bug was reported on 3.6, and probably existed way before hand. This is not a blocker.
(In reply to Allon Mureinik from comment #8) > This bug was reported on 3.6, and probably existed way before hand. This is > not a blocker. Eldad - do we have the latest on 4.0? Just for comparison? (we've changed so much - platform, Postgres, JBoss, Java, disk refactoring in engine... I hope we are in the same or better performance than 3.6!)
we about to start with 4.0 very soon. anyway it seems like that SP getdisksvmguid didn't change since we discovered this problem so we probably face it in 4.0 as well. w'll update as soon as we have some results.
by using that patch https://gerrit.ovirt.org/#/c/62044/2 this query SP runs by ~200 ms.
(In reply to Eldad Marciano from comment #0) > Description of problem: > engine=> EXPLAIN ANALYZE select * from > getdisksvmguid('fa464094-a656-49dc-8384-465c5308cff7', 't', NULL, 'f'); > QUERY PLAN > > ----------------------------------------------------------------------------- > ---------------------------------------------- > Function Scan on getdisksvmguid (cost=0.00..260.00 rows=1000 width=3170) > (actual time=6643.344..6643.345 rows=1 loops=1) > Total runtime: 6643.390 ms (In reply to Eldad Marciano from comment #12) > by using that patch https://gerrit.ovirt.org/#/c/62044/2 > this query SP runs by ~200 ms. So it improves by a factor of ~33, which sounds like a pretty decent improvement to me. Thanks Eldad!
Retargetting to 4.0.4 based on this comment.