[ovirt-devel] Database layout for vm icons

Thu Mar 19 14:26:02 UTC 2015

Hi all,

I post a conversation with Eli regarding vm icons (and screenhots) there to find wide agreement on database layout. Requirements are summarized in conversation below. 

Based on discussion with Eli and later with Tomas and Michal the current proposal corresponds to option (3) at https://www.lucidchart.com/documents/view/b6dbbcde-d2f5-4594-b265-6c70918acde5 . Mapping to business entities: VmIcon class corresponding to vm_icons table, icon_info and vm_static tables are transparently joined to VmBase class.

Reasoning:

* there are no loop references that developer don't like and my perform badly in case only small (i.e. not directly referenced) icon is required (no for option (2))
* it is clean, easy to maintain, saves number of columns in vm_static table (compared to option (1)), related things are grouped together
* icon_info is joined into VmBase in order to avoid complicated caching and invalidation using timestamps (if it depends on timstamp of vm_static then unnecessary refreshes can happen, if it depends on extra timestamp in vm_static extra column is needed; and besides it brings unnecessary complexity)

I'll be glad for any comments.

Best regards
Jakub

----- Forwarded Message -----
From: "Jakub Niedermertl" <jniederm at redhat.com>
To: "Eli Mesika" <emesika at redhat.com>
Sent: Wednesday, March 18, 2015 4:44:50 PM
Subject: Re: Database layout for vm icons

I assume, based on your answer, that list of requirements was not complete (sorry for that). Other requirements are:

 * if screenshot is not availble: basic userportal list shows large icons of all available VMs
 * if screenshot is availble: basic userportal list shows small icons and small screenshots of all available VMS
 * each of 'New VM', 'Edit VM', 'Edit Template', 'New Pool', 'Edit Pool' dialog requests one or two large icons during its opening - these dialogs contains (sometimes disabled yet visible) icon editor widget, dialogs can be invoked form extended userportal or webadmin
 * icons are large (about 20kB each for png format), hence images (icons and screenshots) are cached using their guid as a key. Each image is requested at most once.
 * images are downloaded on demand
 * ids of predefined (associated wit operating systems) icons are precached on clients because they can't be changed while engine is running

Plus there are general assumptions:
 * queries to server (= engine) are expensive (especially sequence of dependent queries)
 * database joins are expensive

So:
 * each update of basic userportal requires to resolve large icon ids of all available icons or (small icon ids plus small screenshot ids)
 * each update of extended userportal reuqires to resolve all small icon ids
 * graphics needs to be downloaded anyway, the problem is how to get ids of images
 * the overall performance can depend on what users do: how many VMs have custom id, how many VMs has screenshot 
 * I'd like to extend my question from db layout to also how db layouts should be mapped to BE.

----- Original Message -----
> From: "Eli Mesika" <emesika at redhat.com>
> To: "Jakub Niedermertl" <jniederm at redhat.com>
> Cc: "Oved Ourfali" <oourfali at redhat.com>
> Sent: Wednesday, March 18, 2015 2:14:05 PM
> Subject: Re: Database layout for vm icons
> 
> 
> 
> ----- Original Message -----
> > From: "Jakub Niedermertl" <jniederm at redhat.com>
> > To: "Eli Mesika" <emesika at redhat.com>
> > Sent: Tuesday, March 17, 2015 7:28:13 PM
> > Subject: Database layout for vm icons
> > 
> > Hi Eli,
> > 
> > I'm working on VM icons feature (http://www.ovirt.org/Features/VM_Icons),
> > customizable icons for userportal. This feature should by followed by vm
> > screenshots. I'd like to discuss db layout for storing icons (and
> > screenshots) since you will probably review the db related part.
> > 
> > Requirements are approximately following:
> > * there are default icons (provided by us, undeletable) derived from
> > selected
> > operating system (like Tux, Windows, ...) and custom icons (uploaded by
> > users)
> > * all icons are stored in db
> > * there are two sizes for each icon (small and large), small one is
> > generally
> > different image from large one, not just a scale
> > * there are screenshots stored in same table as icons
> > * there are two sizes of each screenshot (small and large) - just scaled
> > image
> > * each vm has exactly one icon pair (small and large): default (derived
> > from
> > os) or custom (uploaded by user)
> > * each vm has at most one screenshot pair (small and large)
> > * data in vm_icons table has to be
> >   - either referenced from other table, i.e. used
> >   - or marked as default_for_os by operating system identifier
> >   i.e. table doesn't contain any library of unused icons
> > 
> > What would you suggest as most suitable layout?
> > 
> > I prepared three options at
> > https://www.lucidchart.com/documents/view/b6dbbcde-d2f5-4594-b265-6c70918acde5
> > 
> > 1) The most natural one. It consumes many columns in vm_static table.
> 
> I don't like adding that which imply adding that to the VM BE and returning
> those fields also from the web admin.
> 

This performs best. No need for db joins or additional queries to get image ids.

> > 
> > 2) Only large icons (and screenshots) are referenced from vm_static, small
> > versions are referenced from the large version. The advantage is that
> > information about the pair is contained in one table, disadvantage is that
> > many developers doesn't like loop references.
> 
> This is a hybrid solution that uses loops as you stated and I am not a hybrid
> not loops (which performs bad) in the database

It performs as (1) i.e. well for opening dialogs, basic userportal without screenshot. For extended userportal or basic userportal with many screenshots it requires one additional bulk query to resolve small icon ids (such query generally can't be cached until assumption that small icon can't be changed without changing large icon is accepted). Or VmBase entity can be mapped using joins to vm_static and vm_icons - no additional query but join for VmBase retireve.

> 
> > 
> > 3) Optimization of multiple columns used in version 1, it breaks 3. normal
> > form.
> 
> I would go for that, only additional field in vm_static that points to the
> info , web admin stayed as is and only in user portal you will pay for
> getting the additional information.
> 

If icon_info table has separate BE then there is additional query to resolve image ids all the time. If icon_info table is joined into VmBase entity then there is an additional join all the time.

> > 
> > Thanks for advice.
> > 
> > Best regards.
> > Jakub
> > 
> 

Taking into account aforementioned facts, what solution do you consider the best.

Thanks for advice.

Best regards
Jakub