[ovirt-devel] When you 'select *' you actually use ~10% of the data

[Michal Skrivanek]

reposting

> On 23 Nov 2016, at 10:22, Arik Hadas <ahadas at redhat.com> wrote:
> 
> +1
> And this should come as no surprise to one that read [1] - it shows that querying 6000 VMs that run on a particular host in the monitoring took 765.774 ms on 3.6 by querying the 'vms' view while on master the same task (on the same database) took 2.703 ms by querying only the dynamic VM data (vm_dynamic).
> Following this result, we (virt) already replaced calls to queries that are based on the heavy 'vms' view with ones that call lighter queries that are based on 'vm_static' and 'vm_dynamic' in most of the core virt flows. We also introduced lighter query named 'vms_monitoring_view' that is already used in several places (and can be used even further) instead of 'vms'. This principle is recommended in other places and for other entities (where appropriate) as well.
> 
> [1] http://www.ovirt.org/blog/2016/08/monitoring-improvements-in-ovirt/
> 
> ---- Original Message —
> On Wed, Nov 23, 2016 at 10:34 AM, Roy Golan <rgolan at redhat.com> wrote:
>> It turns our that our busiest UI grids, 'Vms', 'Hosts', 'Disks' are using
>> fraction of the data they really need. Every time we load the tab, or
>> refresh it we invoke a 'SearchQuery' which effectively is translated to
>> 'SELECT * from VIEW LIMIT 100'.  Our views contains HUGH amount of joins
>> just to feed the monster while we don't need it at all.
>> 
>> See this table to understand how far we got:
>> 
>> Grid name | Grid column count | # of columns in view | # of joins in view
>> 
>> Vms tab   | 14                | 161                  | 9
>> Hosts tab | 11                | 137                  | 8
>> Disks     | 9                 | 58                   | 9
>> 
>> 
>> The numbers are not precise cause few more fields are needed for internal
>> logic probably but this is very close the actual num. The numbers of views
>> involved may even be higher because some of the view are using... more views
>> 
>> 
>> This is not UI specific. Tons of bll code uses the views entities just
>> because
>> its easy while working with tailoring a query to your needs would perform way
>> better. We obviously need to STOP doing that and create code that will
>> encourage
>> us to do get just enough data we need. This is a debate about or dal
>> layer and its
>> relationship with stored procedure AND our coding guidelines with db
>> interaction.
>> 
>> I didn't measure the effec tof moving to specific queries and using the
>> result
>> but I'm pretty sure it's going to be dramatic.
>> 
>> So please, as a start, stop using the views cause it's easy, prefer the
>> tables and create
>> procedure to support your data needs.
>> 
>> 
>> I opened a bug for that to track that and will work on POC to measure the
>> effect
>> 
>> *Bug 1397691* <https://bugzilla.redhat.com/show_bug.cgi?id=1397691> -
>>      [scale] UI grids queries all fields while ~10% is actually needed
>> 
> 
>