Note: This bug is displayed in read-only format because the product is no longer active in Red Hat Bugzilla.

Bug 1790746

Summary: Webadmin appears to do several queries instead of bulk select in Hosts tab
Product: Red Hat Enterprise Virtualization Manager Reporter: Germano Veit Michel <gveitmic>
Component: ovirt-engineAssignee: Dominik Holler <dholler>
Status: CLOSED DEFERRED QA Contact: Lucie Leistnerova <lleistne>
Severity: medium Docs Contact:
Priority: unspecified    
Version: 4.3.7CC: dholler, mburman, michal.skrivanek, mkalinin, mperina, pelauter
Target Milestone: ---   
Target Release: ---   
Hardware: x86_64   
OS: Linux   
Whiteboard:
Fixed In Version: Doc Type: If docs needed, set a value
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2021-03-24 22:29:02 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: Network RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:
Bug Depends On:    
Bug Blocks: 1766815    

Description Germano Veit Michel 2020-01-14 05:19:10 UTC 
Description of problem:

This is another child of BZ1766815, more specifically comment https://bugzilla.redhat.com/show_bug.cgi?id=1766815#c38

We saw the webadmin doing 100 selects instead of a bulk select when the user navigates to the hosts tab, taking 15s just to run all SQLs and producing delays for the user.

While that particular problem does not look to be present in 4.3.7, I was checking what webadmin does in relation to DB operations does when the user navigates to the hosts tab. While better than what we see on 4.2.8 customer logs, I'm afraid there is room for more improvement.

Just with 2 hosts, we have these 3 when the Hosts tab loads:

[A]
2020-01-14 14:59:30,144+10 DEBUG [org.ovirt.engine.ui.frontend.server.gwt.GenericApiGWTServiceImpl] (default task-2) [] Server: RunMultipleQuery result [amount of queries: 2]

These are getdnsresolverconfigurationbydnsresolverconfigurationid and getnameserversbydnsresolverconfigurationid, alread being tracked in BZ1754363.

-----/-----

[B]
2020-01-14 14:59:30,251+10 DEBUG [org.ovirt.engine.ui.frontend.server.gwt.GenericApiGWTServiceImpl] (default task-2) [] Server: RunMultipleQuery result [amount of queries: 4]

These are IsDefaultRouteRoleNetworkAttachedToHostQuery. I think to add the exclamation tooltip for default route on each host icon, but not sure.

Possible problems:
* Why running 4 times the same thing? I only have 2 hosts...
* Could be a bulk operation instead of 1 per host? What if the customer has 100 hosts, would it trigger 200?

-----/-----

[C]
2020-01-14 14:59:30,315+10 DEBUG [org.ovirt.engine.ui.frontend.server.gwt.GenericApiGWTServiceImpl] (default task-2) [] Server: RunMultipleQuery result [amount of queries: 2]

Problems:
* Could be a bulk operation instead of 1 per host?


Version-Release number of selected component (if applicable):
ovirt-engine-4.3.7.2-0.1.el7.noarch 

How reproducible:
Always

Steps to Reproduce:
1. Enable DEBUG
2. Navigate to Hosts tab on an env with more than 1 host

Actual results:
In customer case, engine did 100 GetVdsInterfacesByVdsId, I'm afraid there are more things in 4.3 that could produce similar behaviour.

Expected results:
Less SQLs

Additional info:

[B]
2020-01-14 14:59:30,196+10 DEBUG [org.ovirt.engine.ui.frontend.server.gwt.GenericApiGWTServiceImpl] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Server: RunMultipleQuery invoked! [amount of queries: 4]
2020-01-14 14:59:30,196+10 DEBUG [org.ovirt.engine.ui.frontend.server.gwt.GenericApiGWTServiceImpl] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Query type 'IsDefaultRouteRoleNetworkAttachedToHost', Parameters 'IsDefaultRouteRoleNetworkAttachedToHostQueryParameters:{refresh='false', filtered='false'}'
2020-01-14 14:59:30,197+10 DEBUG [org.ovirt.engine.ui.frontend.server.gwt.GenericApiGWTServiceImpl] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Server: RunQuery invoked!
2020-01-14 14:59:30,197+10 DEBUG [org.ovirt.engine.ui.frontend.server.gwt.GenericApiGWTServiceImpl] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Query type 'IsDefaultRouteRoleNetworkAttachedToHost', Parameters 'IsDefaultRouteRoleNetworkAttachedToHostQueryParameters:{refresh='false', filtered='false'}'
2020-01-14 14:59:30,198+10 DEBUG [org.ovirt.engine.ui.frontend.server.gwt.GenericApiGWTServiceImpl] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] IP '192.168.150.254', Session ID 'yX6Ewy2Q-zfH-McmwNEXn9tv5V-ApRS4bYIrq_sE'
2020-01-14 14:59:30,198+10 DEBUG [org.ovirt.engine.core.bll.Backend] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing query IsDefaultRouteRoleNetworkAttachedToHost with isFiltered : false for user admin@internal-authz.
2020-01-14 14:59:30,201+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL query
2020-01-14 14:59:30,202+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL statement [select * from  getallnetworkbyclusterid(?, ?, ?)]
2020-01-14 14:59:30,206+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL query
2020-01-14 14:59:30,206+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL statement [select * from  getnetworkattachmentsbyhostid(?)]
2020-01-14 14:59:30,212+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL query
2020-01-14 14:59:30,213+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL statement [select * from  getqosbyqosid(?)]
2020-01-14 14:59:30,217+10 DEBUG [org.ovirt.engine.core.bll.IsDefaultRouteRoleNetworkAttachedToHostQuery] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Query IsDefaultRouteRoleNetworkAttachedToHostQuery took 16 ms
2020-01-14 14:59:30,217+10 DEBUG [org.ovirt.engine.core.common.di.interceptor.DebugLoggingInterceptor] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] method: runQuery, params: [IsDefaultRouteRoleNetworkAttachedToHost, IsDefaultRouteRoleNetworkAttachedToHostQueryParameters:{refresh='false', filtered='false'}], timeElapsed: 19ms
2020-01-14 14:59:30,223+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL query
2020-01-14 14:59:30,223+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL statement [select * from  getallnetworkbyclusterid(?, ?, ?)]
2020-01-14 14:59:30,226+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL query
2020-01-14 14:59:30,226+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL statement [select * from  getnetworkattachmentsbyhostid(?)]
2020-01-14 14:59:30,229+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL query
2020-01-14 14:59:30,229+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL statement [select * from  getqosbyqosid(?)]
2020-01-14 14:59:30,231+10 DEBUG [org.ovirt.engine.core.bll.IsDefaultRouteRoleNetworkAttachedToHostQuery] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Query IsDefaultRouteRoleNetworkAttachedToHostQuery took 9 ms
2020-01-14 14:59:30,231+10 DEBUG [org.ovirt.engine.core.common.di.interceptor.DebugLoggingInterceptor] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] method: runQuery, params: [IsDefaultRouteRoleNetworkAttachedToHost, IsDefaultRouteRoleNetworkAttachedToHostQueryParameters:{refresh='false', filtered='false'}], timeElapsed: 12ms
2020-01-14 14:59:30,235+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL query
2020-01-14 14:59:30,236+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL statement [select * from  getallnetworkbyclusterid(?, ?, ?)]
2020-01-14 14:59:30,238+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL query
2020-01-14 14:59:30,239+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL statement [select * from  getnetworkattachmentsbyhostid(?)]
2020-01-14 14:59:30,240+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL query
2020-01-14 14:59:30,241+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL statement [select * from  getqosbyqosid(?)]
2020-01-14 14:59:30,242+10 DEBUG [org.ovirt.engine.core.bll.IsDefaultRouteRoleNetworkAttachedToHostQuery] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Query IsDefaultRouteRoleNetworkAttachedToHostQuery took 7 ms
2020-01-14 14:59:30,242+10 DEBUG [org.ovirt.engine.core.common.di.interceptor.DebugLoggingInterceptor] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] method: runQuery, params: [IsDefaultRouteRoleNetworkAttachedToHost, IsDefaultRouteRoleNetworkAttachedToHostQueryParameters:{refresh='false', filtered='false'}], timeElapsed: 10ms
2020-01-14 14:59:30,245+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL query
2020-01-14 14:59:30,245+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL statement [select * from  getallnetworkbyclusterid(?, ?, ?)]
2020-01-14 14:59:30,248+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL query
2020-01-14 14:59:30,248+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL statement [select * from  getnetworkattachmentsbyhostid(?)]
2020-01-14 14:59:30,249+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL query
2020-01-14 14:59:30,249+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Executing prepared SQL statement [select * from  getqosbyqosid(?)]
2020-01-14 14:59:30,250+10 DEBUG [org.ovirt.engine.core.bll.IsDefaultRouteRoleNetworkAttachedToHostQuery] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] Query IsDefaultRouteRoleNetworkAttachedToHostQuery took 5 ms
2020-01-14 14:59:30,250+10 DEBUG [org.ovirt.engine.core.common.di.interceptor.DebugLoggingInterceptor] (default task-2) [dbc43b87-8759-4277-8c6f-a3373b8d44a9] method: runQuery, params: [IsDefaultRouteRoleNetworkAttachedToHost, IsDefaultRouteRoleNetworkAttachedToHostQueryParameters:{refresh='false', filtered='false'}], timeElapsed: 7ms


[C]
2020-01-14 14:59:30,289+10 DEBUG [org.ovirt.engine.ui.frontend.server.gwt.GenericApiGWTServiceImpl] (default task-2) [424fb6f3-d307-4c5a-b67a-61e1fb1b9a7b] Server: RunMultipleQuery invoked! [amount of queries: 2]
2020-01-14 14:59:30,292+10 DEBUG [org.ovirt.engine.ui.frontend.server.gwt.GenericApiGWTServiceImpl] (default task-2) [424fb6f3-d307-4c5a-b67a-61e1fb1b9a7b] Query type 'GetEntitiesNameMap', Parameters 'QueryParametersBase:{refresh='false', filtered='false'}'
2020-01-14 14:59:30,293+10 DEBUG [org.ovirt.engine.ui.frontend.server.gwt.GenericApiGWTServiceImpl] (default task-2) [424fb6f3-d307-4c5a-b67a-61e1fb1b9a7b] Server: RunQuery invoked!
2020-01-14 14:59:30,294+10 DEBUG [org.ovirt.engine.ui.frontend.server.gwt.GenericApiGWTServiceImpl] (default task-2) [424fb6f3-d307-4c5a-b67a-61e1fb1b9a7b] Query type 'GetEntitiesNameMap', Parameters 'QueryParametersBase:{refresh='false', filtered='false'}'
2020-01-14 14:59:30,294+10 DEBUG [org.ovirt.engine.ui.frontend.server.gwt.GenericApiGWTServiceImpl] (default task-2) [424fb6f3-d307-4c5a-b67a-61e1fb1b9a7b] IP '192.168.150.254', Session ID 'yX6Ewy2Q-zfH-McmwNEXn9tv5V-ApRS4bYIrq_sE'
2020-01-14 14:59:30,296+10 DEBUG [org.ovirt.engine.core.bll.Backend] (default task-2) [424fb6f3-d307-4c5a-b67a-61e1fb1b9a7b] Executing query GetEntitiesNameMap with isFiltered : false for user admin@internal-authz.
2020-01-14 14:59:30,300+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [424fb6f3-d307-4c5a-b67a-61e1fb1b9a7b] Executing prepared SQL query
2020-01-14 14:59:30,300+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [424fb6f3-d307-4c5a-b67a-61e1fb1b9a7b] Executing prepared SQL statement [select * from  getentitiesnamemap()]
2020-01-14 14:59:30,303+10 DEBUG [org.ovirt.engine.core.bll.GetEntitiesNameMapQuery] (default task-2) [424fb6f3-d307-4c5a-b67a-61e1fb1b9a7b] Query GetEntitiesNameMapQuery took 3 ms
2020-01-14 14:59:30,303+10 DEBUG [org.ovirt.engine.core.common.di.interceptor.DebugLoggingInterceptor] (default task-2) [424fb6f3-d307-4c5a-b67a-61e1fb1b9a7b] method: runQuery, params: [GetEntitiesNameMap, QueryParametersBase:{refresh='false', filtered='false'}], timeElapsed: 7ms
2020-01-14 14:59:30,309+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [424fb6f3-d307-4c5a-b67a-61e1fb1b9a7b] Executing prepared SQL query
2020-01-14 14:59:30,309+10 DEBUG [org.ovirt.engine.core.dal.dbbroker.PostgresDbEngineDialect$PostgresJdbcTemplate] (default task-2) [424fb6f3-d307-4c5a-b67a-61e1fb1b9a7b] Executing prepared SQL statement [select * from  getentitiesnamemap()]
2020-01-14 14:59:30,311+10 DEBUG [org.ovirt.engine.core.bll.GetEntitiesNameMapQuery] (default task-2) [424fb6f3-d307-4c5a-b67a-61e1fb1b9a7b] Query GetEntitiesNameMapQuery took 2 ms
2020-01-14 14:59:30,312+10 DEBUG [org.ovirt.engine.core.common.di.interceptor.DebugLoggingInterceptor] (default task-2) [424fb6f3-d307-4c5a-b67a-61e1fb1b9a7b] method: runQuery, params: [GetEntitiesNameMap, QueryParametersBase:{refresh='false', filtered='false'}], timeElapsed: 7ms
Comment 2 Dominik Holler 2020-05-28 10:01:19 UTC 
The number of UI query IsDefaultRouteRoleNetworkAttachedToHostQuery can be reduced, like proposed in https://gerrit.ovirt.org/#/c/107014/1 .
The number of SQL queries per IsDefaultRouteRoleNetworkAttachedToHostQuery / IsDefaultRouteRoleNetworkAttachedToHostsQueryParameters can be reduced.
It might be useful to use an array parameter in sql, if this improves performance.