Bug 1398550 - [z-stream clone - 4.0.6] Postgres DB overloads the CPU when specific bookmarks queries are triggered. [NEEDINFO]
Summary: [z-stream clone - 4.0.6] Postgres DB overloads the CPU when specific bookmark...
Keywords:
Status: CLOSED ERRATA
Alias: None
Product: Red Hat Enterprise Virtualization Manager
Classification: Red Hat
Component: ovirt-engine
Version: 4.0.3
Hardware: Unspecified
OS: Unspecified
unspecified
high
Target Milestone: ovirt-4.0.6
: ---
Assignee: Martin Perina
QA Contact: Lucie Leistnerova
URL:
Whiteboard:
Depends On: 1390484
Blocks:
TreeView+ depends on / blocked
 
Reported: 2016-11-25 08:40 UTC by rhev-integ
Modified: 2020-03-11 15:25 UTC (History)
13 users (show)

Fixed In Version:
Doc Type: If docs needed, set a value
Doc Text:
Clone Of: 1390484
Environment:
Last Closed: 2017-01-10 17:01:31 UTC
oVirt Team: Infra
Target Upstream Version:
mkalinin: needinfo? (kshukla)

Attachments (Terms of Use)
Add an attachment (proposed patch, testcase, etc.)


Links
System ID Private Priority Status Summary Last Updated
Red Hat Product Errata RHBA-2017:0043 0 normal SHIPPED_LIVE Red Hat Virtualization Manager 4.0.6 2017-01-10 21:52:43 UTC
oVirt gerrit 66165 0 None None None 2016-11-25 08:41:02 UTC
oVirt gerrit 67323 0 None None None 2016-11-25 17:35:39 UTC
oVirt gerrit 67324 0 None None None 2016-11-25 17:36:32 UTC

Comment 1 rhev-integ 2016-11-25 08:40:24 UTC 
Description of problem:
     When the following bookmark is chosen 

          Events: Users=xxx  or vm=yyyy

     the postgres DB gets overloaded in case of high amount of VMs.

Version-Release number of selected component (if applicable):

     rhv-m 4.0.4.4-0.1.el7ev

How reproducible:

     100%

Steps to Reproduce:
     1. Create 100VMs
     2. Run the query above

Actual results:

     Postgres consumes 100% of CPU and the query is returned in minutes.

Expected results:

     The records are returned in reasonable time

This comment was originaly posted by rhodain
Comment 5 rhev-integ 2016-11-25 08:40:40 UTC 
Eli, could you please take a look?

This comment was originaly posted by mperina
Comment 8 Lucie Leistnerova 2016-12-12 00:08:20 UTC 
engine with 8 GB memory and 2 CPU
audit_log rows 359892
vm_dynamic rows 1417
users rows 2252

Search on fresh engine finishes in 8-10s and after some work and other searches finishes in 14-16s. Next page of search result sometimes goes to 20s.
That seems as reasonable time.

verified in ovirt-engine-4.0.6.3-0.1.el7ev.noarch
Comment 9 Roy Golan 2016-12-12 08:57:40 UTC 
this is a big improvement and it makes it bearable. Still > 10s for a query with thousands of records shouldn't take that long.

Explain analyze shows huge cost on the Unique function for the distinct because it performs it on *all* the vm fields. I narrowed it down to distinct only on vm name

   SELECT DISTINCT on (vms.vm_name) ...

and the changes in the cost are two orders of magnitude for my machine.

Roman,Martin logically distinct on vm_name should be enough can you please look into that. Also we need to make sure we don't have indexes missing cause again I don't see why this needs to run so slow.
Comment 12 errata-xmlrpc 2017-01-10 17:01:31 UTC 
Since the problem described in this bug report should be
resolved in a recent advisory, it has been closed with a
resolution of ERRATA.

For information on the advisory, and where to find the updated
files, follow the link below.

If the solution does not work for you, open a new bug report.

https://rhn.redhat.com/errata/RHBA-2017-0043.html
Note You need to log in before you can comment on or make changes to this bug.