Created attachment 1633388 [details]
engine, postgres, relavant trace reports

Description of problem:
/ovirt-engine/api/clusters takes 196s to complete when many networks are attached to many hosts.

Env has:
10 Clusters each in its own DC
538 Hosts, with 12 FC domains
4k VMs

Networks have been created and attached in large amounts to the following clusters as described:

1 Cluster has 10 physical hosts and have 150 networks assigned per host
1 Cluster has  7 physical hosts and have 150 networks assigned per host
1 Cluster has  8 physical hosts and have 300 networks assigned per host
1 Cluster has 148 nested hosts and have 150 networks assigned per host

[root@rhev-green-01 ~]# /usr/share/ovirt-engine/dbscripts/engine-psql.sh -c "SELECT count(*) from vds_interface_view"
 count
-------
 32582


Issue:
total time 196s of which 144s are spent doing 351,000 queries of the 351,000 queries 1,265 queries are select * from getqosbyqosid(?) this will likely be addressed in https://bugzilla.redhat.com/show_bug.cgi?id=1754363

Testing was also done with a modified dal.jar to remove 'select * from getqosbyqosid(?)' to measure the impact.  
Results in total time drops to 163s, of which 114s are querying with select * from getqosbyqosid(?) removed.
The most significant queries are:


┌───────────────────────────────────────────────────────────────────┬────────────────┬─────────────────┬────────────────────────┬────────────────────┐
│                              Query                                │ Totaltime (ms) │ Execution count │ Time per execution(ms) │ Rows per execution │
├───────────────────────────────────────────────────────────────────┼────────────────┼─────────────────┼────────────────────────┼────────────────────┤
│ select * from getclusterbyclusterid(?, ?, ?)                      │       22,936.7 │          32,011 │                   0.72 │                1.0 │
│ select * from getvdsdynamicbyvdsid(?)                             │       13,423.1 │          32,011 │                   0.42 │                1.0 │
│ select * from getvdsinterfacebyname(?, ?)                         │       12,496.6 │          31,434 │                   0.40 │                1.0 │
│ select * from getvdsstaticbyvdsid(?)                              │        9,655.7 │          32,011 │                   0.30 │                1.0 │
│ select * from getnetworkattachmentbynicidandnetworkid(?, ?)       │        9,651.1 │          32,011 │                   0.30 │                1.0 │
│ select * from getnetwork_clusterbycluster_idandbynetwork_id(?, ?) │        9,140.0 │          32,011 │                   0.29 │                1.0 │
│ select * from getinterfacesbyclusterid(?)                         │        1,332.7 │              10 │                  133.3 │            3,258.2 │
│ select * from getallnetworkbyclusterid(?, ?, ?)                   │           33.8 │              10 │                    3.4 │              144.7 │
└───────────────────────────────────────────────────────────────────┴────────────────┴─────────────────┴────────────────────────┴────────────────────┘
Note the execution count totals per query where only 1 row is returned.
For more details see 'Query stats' in the trace summary attached.


Version-Release number of selected component (if applicable):
rhv-release-4.3.6-7-001.noarch

How reproducible:
reproduces in scale enviroment

Steps to Reproduce:
1. Populate many 150 networks across 150 hosts
2. Issue API /ovirt-engine/api/clusters 


Actual results:
Response takes way to long 100s+

Expected results:
Response time expected between 10-25s max.

Additional info:
System is idle, cpu usage is in an acceptable range of 1.6 cores for ovirt-engine process.
No CPU, memory or IO saturation.
Please look at the trace reports located in the attached zip in slow_cluster_API/traces_as_html_report additional engine, postgres logs are zipped.