Re: [ovirt-devel] [Devel] [Engine-devel] vds_dynamic refactor

9 Apr 2014

      ------=_Part_3278465_796563103.1397046843672
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

Why not move only status with changes a lot to statistics and leave everything as is? 

Yaniv 

----- Original Message -----
...
From: "Liran Zelkha" <liran.zelkha@gmail.com>
To: "Gilad Chaplik" <gchaplik@redhat.com>
Cc: "Kobi Ianko" <kobi@redhat.com>, devel@linode01.ovirt.org, "engine-devel"
<engine-devel@ovirt.org>
Sent: Monday, April 7, 2014 8:51:00 AM
Subject: Re: [Devel] [Engine-devel] vds_dynamic refactor
...
On Sun, Apr 6, 2014 at 11:03 PM, Gilad Chaplik < gchaplik@redhat.com > wrote:
...
...
----- Original Message -----
...
...
From: "Liran Zelkha" < liran.zelkha@gmail.com >
...
...
To: "Gilad Chaplik" < gchaplik@redhat.com >
...
...
Cc: "Kobi Ianko" < kobi@redhat.com >, devel@linode01.ovirt.org ,
"engine-devel" < engine-devel@ovirt.org >
...
...
Sent: Sunday, April 6, 2014 8:51:02 PM
...
...
Subject: Re: [Devel] [Engine-devel] vds_dynamic refactor
...
...
...
...
On Sun, Apr 6, 2014 at 6:32 PM, Gilad Chaplik < gchaplik@redhat.com >
wrote:
...
...
...
...
...
----- Original Message -----
...
...
...
...
From: "Liran Zelkha" < liran.zelkha@gmail.com >
...
...
...
...
To: "Kobi Ianko" < kobi@redhat.com >
...
...
...
...
Cc: "Gilad Chaplik" < gchaplik@redhat.com >, devel@linode01.ovirt.org
,
...
...
...
"engine-devel" < engine-devel@ovirt.org >
...
...
...
...
Sent: Sunday, April 6, 2014 3:40:13 PM
...
...
...
...
Subject: Re: [Devel] [Engine-devel] vds_dynamic refactor
...
...
...
...
...
...
...
...
On Sun, Apr 6, 2014 at 3:37 PM, Kobi Ianko < kobi@redhat.com > wrote:
...
...
...
...
...
...
...
...
...
Joining in...
...
...
...
...
...
From my point of view, in real life a user should have that many
VDSs
...
...
...
on
...
...
...
...
...
one Engine (from a DB point of view).
...
...
...
...
...
Modern DB system handles tables with millions of records and many
...
...
...
...
...
relations, Do we really have a performance issue here?
...
...
...
...
...
We could prefer a more easy to maintain implantation in this case
over
...
...
...
DB
...
...
...
...
...
performance
...
...
...
...
...
...
...
...
...
...
Yes we do. We make many queries on the VDS view, which is a VERY
...
...
...
complex
...
...
...
...
view.
...
...
...
...
...
...
...
...
...
...
Actually I quite agree with Kobi, what is the plan for VMs? why do we
...
...
...
start with VDS...
...
...
...
what is the biggest deploy do you know of?
...
...
...
...
...
We start with VDS because in an idle system, with 200 hosts and several
...
...
thousands VMs, this is what you get as the top queries against the
...
...
database. Look at how many times getvds is called.
...
...
[image: Inline image 1]
...
...
BTW - the second query is an example of abusing the dynamic query
...
...
mechanism. The 4th query (an update command) is a set of useless
...
...
update_vds_dynamic commands.
...
...
...
...
For reference, the explain plan of get VDS is something like this:
...
...
...
...
QUERY PLAN
...
...
...
...
-----------------------------------------------------------------------------------------------------------------------------------------------------------
...
...
Nested Loop (cost=9.30..46.75 rows=6 width=9060) (actual
...
...
time=0.063..0.068 rows=1 loops=1)
...
...
Join Filter: (vds_static.vds_id = vds_statistics.vds_id)
...
...
-> Seq Scan on vds_statistics (cost=0.00..1.01 rows=1 width=109)
...
...
(actual time=0.008..0.008 rows=1 loops=1)
...
...
-> Nested Loop (cost=9.30..45.64 rows=6 width=8983) (actual
...
...
time=0.048..0.052 rows=1 loops=1)
...
...
Join Filter: (vds_groups.vds_group_id = vds_static.vds_group_id)
...
...
-> Nested Loop Left Join (cost=0.00..9.29 rows=1 width=1389)
...
...
(actual time=0.013..0.013 rows=1 loops=1)
...
...
-> Seq Scan on vds_groups (cost=0.00..1.01 rows=1
...
...
width=1271) (actual time=0.003..0.003 rows=1 loops=1)
...
...
-> Index Scan using pk_storage_pool on storage_pool
...
...
(cost=0.00..8.27 rows=1 width=134) (actual time=0.008..0.008 rows=1
...
...
loops=1)
...
...
Index Cond: (vds_groups.storage_pool_id = id)
...
...
-> Hash Right Join (cost=9.30..36.28 rows=6 width=7610) (actual
...
...
time=0.033..0.037 rows=1 loops=1)
...
...
Hash Cond: (vds_spm_id_map.vds_id = vds_static.vds_id)
...
...
-> Seq Scan on vds_spm_id_map (cost=0.00..22.30 rows=1230
...
...
width=20) (actual time=0.003..0.003 rows=1 loops=1)
...
...
-> Hash (cost=9.29..9.29 rows=1 width=7606) (actual
...
...
time=0.019..0.019 rows=1 loops=1)
...
...
Buckets: 1024 Batches: 1 Memory Usage: 2kB
...
...
-> Nested Loop (cost=0.00..9.29 rows=1 width=7606)
...
...
(actual time=0.012..0.013 rows=1 loops=1)
...
...
-> Seq Scan on vds_dynamic (cost=0.00..1.01
...
...
rows=1 width=1895) (actual time=0.006..0.006 rows=1 loops=1)
...
...
-> Index Scan using pk_vds_static on vds_static
...
...
(cost=0.00..8.27 rows=1 width=5711) (actual time=0.005..0.006 rows=1
...
...
loops=1)
...
...
Index Cond: (vds_id = vds_dynamic.vds_id)
...
...
Total runtime: 0.299 ms
...
...
(19 rows)
...
...
...
...
It's terrible. Adding any additional join will make this worse. Please
...
...
don't add any more tables...

...
...
Thank you for the detailed explanation, my comments:

...
...
* a very long time isn't an argument for not adding another table (should
be
neglectable);
...
currently we have an unrelated problem, we need to solve it.

...
Of course it is. A very long time for a query that you execute many times is
THE factor. Who said the join has no performance effect? Have you tested it?
Under load? Under many writes/updates?
...
...
* > We start with VDS because in an idle system, with 200 hosts and several
...
...
thousands VMs, this is what you get as the top queries against the
...
...
database.

...
...
so, if fetching VMs takes 10 minutes? and its get called a single time?

...
Where do you see 10 minutes? If you are looking at the red bar it's the
inherent time - total query time * number of queries.
...
...
* you didn't reply on my of my suggestion of constructing the VDS records
in
the DB without using joins.

...
If you mean materialized views - we don't have it in Postgres just yet... And
even if we do, since we do many updates to vds_statistics and vds_dynamic -
I'm not sure it will have positive impact on our performance. If you mean
joins in the database - everything that is based on VDS is done in the
database. Part of the problem, since we can cache some information and only
query the dynamic/statistics part of VDS, but that's another matter.
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
----- Original Message -----
...
...
...
...
...
> From: "Gilad Chaplik" < gchaplik@redhat.com >
...
...
...
...
...
> To: "Liran Zelkha" < liran.zelkha@gmail.com >
...
...
...
...
...
> Cc: devel@linode01.ovirt.org , "engine-devel" <
> engine-devel@ovirt.org
...
...
...
...
...
...
...
...
...
> Sent: Sunday, April 6, 2014 3:32:26 PM
...
...
...
...
...
> Subject: Re: [Devel] [Engine-devel] vds_dynamic refactor
...
...
...
...
...
>
...
...
...
...
...
> ----- Original Message -----
...
...
...
...
...
> > From: "Liran Zelkha" < liran.zelkha@gmail.com >
...
...
...
...
...
> > To: "Gilad Chaplik" < gchaplik@redhat.com >
...
...
...
...
...
> > Cc: "Itamar Heim" < iheim@redhat.com >,
> > devel@linode01.ovirt.org
> > ,
...
...
...
...
...
> > "engine-devel" < engine-devel@ovirt.org >
...
...
...
...
...
> > Sent: Sunday, April 6, 2014 3:26:24 PM
...
...
...
...
...
> > Subject: Re: [Engine-devel] vds_dynamic refactor
...
...
...
...
...
> >
...
...
...
...
...
> > On Sun, Apr 6, 2014 at 3:18 PM, Gilad Chaplik <
> > gchaplik@redhat.com
...
...
...
...
...
...
...
...
...
wrote:
...
...
...
...
...
> >
...
...
...
...
...
> > > ----- Original Message -----
...
...
...
...
...
> > > > From: "Itamar Heim" < iheim@redhat.com >
...
...
...
...
...
> > > > To: "Liran Zelkha" < liran.zelkha@gmail.com >
...
...
...
...
...
> > > > Cc: "Gilad Chaplik" < gchaplik@redhat.com >,
...
...
...
...
...
devel@linode01.ovirt.org ,
...
...
...
...
...
> > > "engine-devel" < engine-devel@ovirt.org >
...
...
...
...
...
> > > > Sent: Sunday, April 6, 2014 11:33:12 AM
...
...
...
...
...
> > > > Subject: Re: [Engine-devel] vds_dynamic refactor
...
...
...
...
...
> > > >
...
...
...
...
...
> > > > On 04/06/2014 11:32 AM, Liran Zelkha wrote:
...
...
...
...
...
> > > > >
...
...
...
...
...
> > > > >
...
...
...
...
...
> > > > >
...
...
...
...
...
> > > > > On Sun, Apr 6, 2014 at 11:22 AM, Itamar Heim <
...
...
...
iheim@redhat.com
...
...
...
...
...
> > > > > <mailto: iheim@redhat.com >> wrote:
...
...
...
...
...
> > > > >
...
...
...
...
...
> > > > > On 04/03/2014 07:51 PM, Liran Zelkha wrote:
...
...
...
...
...
> > > > >
...
...
...
...
...
> > > > > The problem is with both updates and selects.
...
...
...
...
...
> > > > > For selects - to get all the information for the VDS
...
...
...
we
...
...
...
...
...
have
...
...
...
...
...
> > > > > multiple
...
...
...
...
...
> > > > > joins. Adding another one will hurt performance even
...
...
...
...
...
more.
...
...
...
...
...
> > > > > For updates - we have vds_static thats hardly
...
...
...
changed.
...
...
...
...
...
> > > > > vds_statistics
...
...
...
...
...
> > > > > that changes all the time. vds_dynamic is not changed
...
...
...
...
...
allot -
...
...
...
...
...
> > > but
...
...
...
...
...
> > > > > is
...
...
...
...
...
> > > > > updated all the time because of the status. I think
...
...
...
it's
...
...
...
...
...
best
...
...
...
...
...
> > > to
...
...
...
...
...
> > > > > split
...
...
...
...
...
> > > > > it to the two existing tables (BTW - relevant for VM
...
...
...
as
...
...
...
...
...
well)
...
...
...
...
...
> > > > >
...
...
...
...
...
> > > > >
...
...
...
...
...
> > > > > but we don't update it unless the status has changed,
...
...
...
which
...
...
...
...
...
is a
...
...
...
...
...
> > > > > rare occurance?
...
...
...
...
...
> > > > >
...
...
...
...
...
> > > > > Actually - no. We can definitely see times we are
> > > > > updating
...
...
...
...
...
> > > > > vds_dynamic
...
...
...
...
...
> > > > > with no reason at all. I tried to create patches for that
> > > > > -
...
...
...
but
...
...
...
...
...
it
...
...
...
...
...
> > > > > happens from many different places in the code.
...
...
...
...
...
> > > >
...
...
...
...
...
> > > > what would be updated vds_dyanmic for status not
> > > > originating
> > > > in
...
...
...
...
...
update
...
...
...
...
...
> > > > run time info?
...
...
...
...
...
> > >
...
...
...
...
...
> > > We have separate DB flows for that (updateStatus and
...
...
...
...
...
> > > updatePartialVdsDynamicCalc and more in
...
...
...
VdsDynamicDAODbFacadeImpl).
...
...
...
...
...
> > > A question: do you know if we update status in
> > > updateVdsDynamic?
...
...
...
:-)
...
...
...
...
...
not
...
...
...
...
...
> > > sure but I found a possible race for pending resources (cpu,
...
...
...
mem),
...
...
...
...
...
LOL
...
...
...
...
...
> > > :-)
...
...
...
...
...
> > >
...
...
...
...
...
> > > I think we do but not sure. Will check.
...
...
...
...
...
>
...
...
...
...
...
> Of course it is, that was a rhetorical question :-) (a lot of
...
...
...
emoticons
...
...
...
...
...
and
...
...
...
...
...
> LOLs ;-))
...
...
...
...
...
>
...
...
...
...
...
> >
...
...
...
...
...
> >
...
...
...
...
...
> > > Still holds my original thought for having vds_on_boot.
...
...
...
...
...
> > >
...
...
...
...
...
> > >
...
...
...
...
...
> > >
...
...
...
...
...
> > Let's talk f2f on Tuesday?
...
...
...
...
...
>
...
...
...
...
...
> I'd prefer to reach conclusions here, I'd like everyone to be
...
...
...
involved
...
...
...
...
...
in a
...
...
...
...
...
> root issue like this one.
...
...
...
...
...
...
...
...
...
...
...
...
...
What is the update frequency of this field?
...
...
...
...
...
...
...
...
...
...
which field?
...
...
...
status? pending resources? on boot fields?
...
...
...
iinm, status is updated mostly by user actions, at least in positive
...
...
...
scenarios, and not that often.
...
...
...
...
...
...
...
...
...
...
...
>
...
...
...
...
...
> >
...
...
...
...
...
> _______________________________________________
...
...
...
...
...
> Devel mailing list
...
...
...
...
...
> Devel@ovirt.org
...
...
...
...
...
> http://lists.ovirt.org/mailman/listinfo/devel
...
...
...
...
...
>
...
...
...
...
...
...
...
...
...
...
...
...
...
...

...
_______________________________________________
Devel mailing list
Devel@ovirt.org
http://lists.ovirt.org/mailman/listinfo/devel
------=_Part_3278465_796563103.1397046843672
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable
...
<br>
> > ><br>
> > > > > Sent: Sunday, April 6, 2014 3:32:26 PM<br>
> > > > > Subject: Re: [Devel] [Engine-devel] vds_dynamic re=
factor<br>
> > > > ><br>
> > > > > ----- Original Message -----<br>
> > > > > > From: "Liran Zelkha" <<a href=3D"mailto:li=
ran.zelkha@gmail.com" target=3D"_blank">liran.zelkha@gmail.com</a>><br>
> > > > > > To: "Gilad Chaplik" <<a href=3D"mailto:gch=
aplik@redhat.com" target=3D"_blank">gchaplik@redhat.com</a>><br>
> > > > > > Cc: "Itamar Heim" <<a href=3D"mailto:iheim=
@redhat.com" target=3D"_blank">iheim@redhat.com</a>>, <a href=3D"mailto:=
devel@linode01.ovirt.org" target=3D"_blank">devel@linode01.ovirt.org</a>,<b=
r>
> > > > > > "engine-devel" <<a href=3D"mailto:engine-d=
evel@ovirt.org" target=3D"_blank">engine-devel@ovirt.org</a>><br>
> > > > > > Sent: Sunday, April 6, 2014 3:26:24 PM<br>
> > > > > > Subject: Re: [Engine-devel] vds_dynamic refac=
tor<br>
> > > > > ><br>
> > > > > > On Sun, Apr 6, 2014 at 3:18 PM, Gilad Chaplik=
 <<a href=3D"mailto:gchaplik@redhat.com" target=3D"_blank">gchaplik@redh=
at.com</a><br>
> > ><br>
> > > > wrote:<br>
> > > > > ><br>
> > > > > > > ----- Original Message -----<br>
> > > > > > > > From: "Itamar Heim" <<a href=3D"=
mailto:iheim@redhat.com" target=3D"_blank">iheim@redhat.com</a>><br>
> > > > > > > > To: "Liran Zelkha" <<a href=3D"m=
ailto:liran.zelkha@gmail.com" target=3D"_blank">liran.zelkha@gmail.com</a>&=
gt;<br>
> > > > > > > > Cc: "Gilad Chaplik" <<a href=3D"=
mailto:gchaplik@redhat.com" target=3D"_blank">gchaplik@redhat.com</a>>,<=
br>
> > > > <a href=3D"mailto:devel@linode01.ovirt.org" target=3D"_=
blank">devel@linode01.ovirt.org</a>,<br>
> > > > > > > "engine-devel" <<a href=3D"mailto:eng=
ine-devel@ovirt.org" target=3D"_blank">engine-devel@ovirt.org</a>><br>
> > > > > > > > Sent: Sunday, April 6, 2014 11:33:1=
2 AM<br>
> > > > > > > > Subject: Re: [Engine-devel] vds_dyn=
amic refactor<br>
> > > > > > > ><br>
> > > > > > > > On 04/06/2014 11:32 AM, Liran Zelkh=
a wrote:<br>
> > > > > > > > ><br>
> > > > > > > > ><br>
> > > > > > > > ><br>
> > > > > > > > > On Sun, Apr 6, 2014 at 11:22 A=
M, Itamar Heim <<br>
> > <a href=3D"mailto:iheim@redhat.com" target=3D"_blank">iheim@redha=
t.com</a><br>
> > > > > > > > > <mailto:<a href=3D"mailto:i=
heim@redhat.com" target=3D"_blank">iheim@redhat.com</a>>> wrote:<br>
> > > > > > > > ><br>
> > > > > > > > >     On 04/03/2014 07=
:51 PM, Liran Zelkha wrote:<br>
> > > > > > > > ><br>
> > > > > > > > >         Th=
e problem is with both updates and selects.<br>
> > > > > > > > >         Fo=
r selects - to get all the information for the VDS<br>
> > we<br>
> > > > have<br>
> > > > > > > > >         mu=
ltiple<br>
> > > > > > > > >         jo=
ins. Adding another one will hurt performance even<br>
> > > > more.<br>
> > > > > > > > >         Fo=
r updates - we have vds_static thats hardly<br>
> > changed.<br>
> > > > > > > > >         vd=
s_statistics<br>
> > > > > > > > >         th=
at changes all the time. vds_dynamic is not changed<br>
> > > > allot -<br>
> > > > > > > but<br>
> > > > > > > > >         is=
<br>
> > > > > > > > >         up=
dated all the time because of the status. I think<br>
> > it's<br>
> > > > best<br>
> > > > > > > to<br>
> > > > > > > > >         sp=
<html><body><div style=3D"font-family: Arial; font-size: 10pt; color: #0000=
00"><div>Why not move only status with changes a lot to statistics and leav=
e everything as is?<br></div><div><br></div><div><br></div><div><br></div><=
div><br></div><div>Yaniv</div><div><br></div><hr id=3D"zwchr"><blockquote s=
tyle=3D"border-left:2px solid #1010FF;margin-left:5px;padding-left:5px;colo=
r:#000;font-weight:normal;font-style:normal;text-decoration:none;font-famil=
y:Helvetica,Arial,sans-serif;font-size:12pt;"><b>From: </b>"Liran Zelkha" &=
lt;liran.zelkha@gmail.com><br><b>To: </b>"Gilad Chaplik" <gchaplik@re=
dhat.com><br><b>Cc: </b>"Kobi Ianko" <kobi@redhat.com>, devel@lino=
de01.ovirt.org, "engine-devel" <engine-devel@ovirt.org><br><b>Sent: <=
/b>Monday, April 7, 2014 8:51:00 AM<br><b>Subject: </b>Re: [Devel] [Engine-=
devel] vds_dynamic refactor<br><div><br></div><div dir=3D"ltr"><br><div cla=
ss=3D"gmail_extra"><br><div><br></div><div class=3D"gmail_quote">On Sun, Ap=
r 6, 2014 at 11:03 PM, Gilad Chaplik <span dir=3D"ltr"><<a href=3D"mailt=
o:gchaplik@redhat.com" target=3D"_blank">gchaplik@redhat.com</a>></span>=
 wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex"><div class=3D"">----- Original Message -----=
<br>
> From: "Liran Zelkha" <<a href=3D"mailto:liran.zelkha@gmail.com" tar=
get=3D"_blank">liran.zelkha@gmail.com</a>><br>
> To: "Gilad Chaplik" <<a href=3D"mailto:gchaplik@redhat.com" target=
=3D"_blank">gchaplik@redhat.com</a>><br>
</div><div><div class=3D"h5">> Cc: "Kobi Ianko" <<a href=3D"mailto:ko=
bi@redhat.com" target=3D"_blank">kobi@redhat.com</a>>, <a href=3D"mailto=
:devel@linode01.ovirt.org" target=3D"_blank">devel@linode01.ovirt.org</a>, =
"engine-devel" <<a href=3D"mailto:engine-devel@ovirt.org" target=3D"_bla=
nk">engine-devel@ovirt.org</a>><br>

> Sent: Sunday, April 6, 2014 8:51:02 PM<br>
> Subject: Re: [Devel] [Engine-devel] vds_dynamic refactor<br>
><br>
> On Sun, Apr 6, 2014 at 6:32 PM, Gilad Chaplik <<a href=3D"mailto:gc=
haplik@redhat.com" target=3D"_blank">gchaplik@redhat.com</a>> wrote:<br>
><br>
> > ----- Original Message -----<br>
> > > From: "Liran Zelkha" <<a href=3D"mailto:liran.zelkha@gmai=
l.com" target=3D"_blank">liran.zelkha@gmail.com</a>><br>
> > > To: "Kobi Ianko" <<a href=3D"mailto:kobi@redhat.com" targ=
et=3D"_blank">kobi@redhat.com</a>><br>
> > > Cc: "Gilad Chaplik" <<a href=3D"mailto:gchaplik@redhat.co=
m" target=3D"_blank">gchaplik@redhat.com</a>>, <a href=3D"mailto:devel@l=
inode01.ovirt.org" target=3D"_blank">devel@linode01.ovirt.org</a>,<br>
> > "engine-devel" <<a href=3D"mailto:engine-devel@ovirt.org" targ=
et=3D"_blank">engine-devel@ovirt.org</a>><br>
> > > Sent: Sunday, April 6, 2014 3:40:13 PM<br>
> > > Subject: Re: [Devel] [Engine-devel] vds_dynamic refactor<br>
> > ><br>
> > > On Sun, Apr 6, 2014 at 3:37 PM, Kobi Ianko <<a href=3D"ma=
ilto:kobi@redhat.com" target=3D"_blank">kobi@redhat.com</a>> wrote:<br>
> > ><br>
> > > > Joining in...<br>
> > > > From my point of view, in real life a user should have =
that many VDSs<br>
> > on<br>
> > > > one Engine (from a DB point of view).<br>
> > > > Modern DB system handles tables with millions of record=
s and many<br>
> > > > relations, Do we really have a performance issue here?<=
br>
> > > > We could prefer a more easy to maintain implantation in=
 this case over<br>
> > DB<br>
> > > > performance<br>
> > > ><br>
> > > > Yes we do. We make many queries on the VDS view, which =
is a VERY<br>
> > complex<br>
> > > view.<br>
> > ><br>
> ><br>
> > Actually I quite agree with Kobi, what is the plan for VMs? why d=
o we<br>
> > start with VDS...<br>
> > what is the biggest deploy do you know of?<br>
> ><br>
> We start with VDS because in an idle system, with 200 hosts and severa=
l<br>
> thousands VMs, this is what you get as the top queries against the<br>
> database. Look at how many times getvds is called.<br>
> [image: Inline image 1]<br>
> BTW - the second query is an example of abusing the dynamic query<br>
> mechanism. The 4th query (an update command) is a set of useless<br>
> update_vds_dynamic commands.<br>
><br>
> For reference, the explain plan of get VDS is something like this:<br>
><br>
> QUERY PLAN<br>
><br>
> ----------------------------------------------------------------------=
---------------------------------------------------------------------------=
----------<br>
>  Nested Loop  (cost=3D9.30..46.75 rows=3D6 width=3D9060) (ac=
tual<br>
> time=3D0.063..0.068 rows=3D1 loops=3D1)<br>
>    Join Filter: (vds_static.vds_id =3D vds_statistics.vds_id=
)<br>
>    ->  Seq Scan on vds_statistics  (cost=3D0.00=
..1.01 rows=3D1 width=3D109)<br>
> (actual time=3D0.008..0.008 rows=3D1 loops=3D1)<br>
>    ->  Nested Loop  (cost=3D9.30..45.64 rows=3D=
6 width=3D8983) (actual<br>
> time=3D0.048..0.052 rows=3D1 loops=3D1)<br>
>          Join Filter: (vds_groups.vds_group_i=
d =3D vds_static.vds_group_id)<br>
>          ->  Nested Loop Left Join &n=
bsp;(cost=3D0.00..9.29 rows=3D1 width=3D1389)<br>
> (actual time=3D0.013..0.013 rows=3D1 loops=3D1)<br>
>                ->  Seq=
 Scan on vds_groups  (cost=3D0.00..1.01 rows=3D1<br>
> width=3D1271) (actual time=3D0.003..0.003 rows=3D1 loops=3D1)<br>
>                ->  Ind=
ex Scan using pk_storage_pool on storage_pool<br>
>  (cost=3D0.00..8.27 rows=3D1 width=3D134) (actual time=3D0.008..0=
.008 rows=3D1<br>
> loops=3D1)<br>
>                     =
 Index Cond: (vds_groups.storage_pool_id =3D id)<br>
>          ->  Hash Right Join  (c=
ost=3D9.30..36.28 rows=3D6 width=3D7610) (actual<br>
> time=3D0.033..0.037 rows=3D1 loops=3D1)<br>
>                Hash Cond: (vds=
_spm_id_map.vds_id =3D vds_static.vds_id)<br>
>                ->  Seq=
 Scan on vds_spm_id_map  (cost=3D0.00..22.30 rows=3D1230<br>
> width=3D20) (actual time=3D0.003..0.003 rows=3D1 loops=3D1)<br>
>                ->  Has=
h  (cost=3D9.29..9.29 rows=3D1 width=3D7606) (actual<br>
> time=3D0.019..0.019 rows=3D1 loops=3D1)<br>
>                     =
 Buckets: 1024  Batches: 1  Memory Usage: 2kB<br>
>                     =
 ->  Nested Loop  (cost=3D0.00..9.29 rows=3D1 width=3D760=
6)<br>
> (actual time=3D0.012..0.013 rows=3D1 loops=3D1)<br>
>                     =
       ->  Seq Scan on vds_dynamic  (cost=
=3D0.00..1.01<br>
> rows=3D1 width=3D1895) (actual time=3D0.006..0.006 rows=3D1 loops=3D1)=
<br>
>                     =
       ->  Index Scan using pk_vds_static on vd=
s_static<br>
>  (cost=3D0.00..8.27 rows=3D1 width=3D5711) (actual time=3D0.005..=
0.006 rows=3D1<br>
> loops=3D1)<br>
>                     =
             Index Cond: (vds_id =3D vds=
_dynamic.vds_id)<br>
>  Total runtime: 0.299 ms<br>
> (19 rows)<br>
><br>
> It's terrible. Adding any additional join will make this worse. Please=
<br>
> don't add any more tables...<br>
<br>
</div></div>Thank you for the detailed explanation, my comments:<br>
<br>
* a very long time isn't an argument for not adding another table (should b=
e neglectable);<br>
currently we have an unrelated problem, we need to solve it.<br></blockquot=
e><div>Of course it is. A very long time for a query that you execute many =
times is THE factor. Who said the join has no performance effect? Have you =
tested it? Under load?  Under many writes/updates? </div>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
<br>
* > We start with VDS because in an idle system, with 200 hosts and seve=
ral<br>
<div class=3D"">> thousands VMs, this is what you get as the top queries=
 against the<br>
> database.<br>
<br>
</div>so, if fetching VMs takes 10 minutes? and its get called a single tim=
e?<br></blockquote><div> Where do you see 10 minutes? If you are looki=
ng at the red bar it's the inherent time - total query time * number of que=
ries. </div>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
<br>
* you didn't reply on my of my suggestion of constructing the VDS records i=
n the DB without using joins.<br></blockquote><div>If you mean materialized=
 views - we don't have it in Postgres just yet... And even if we do, since =
we do many updates to vds_statistics and vds_dynamic - I'm not sure it will=
 have positive impact on our performance. If you mean joins in the database=
 - everything that is based on VDS is done in the database. Part of the pro=
blem, since we can cache some information and only query the dynamic/statis=
tics part of VDS, but that's another matter. </div>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
<div class=3D"HOEnZb"><div class=3D"h5"><br>
<br>
><br>
> ><br>
> > > ><br>
> > > > ----- Original Message -----<br>
> > > > > From: "Gilad Chaplik" <<a href=3D"mailto:gchapl=
ik@redhat.com" target=3D"_blank">gchaplik@redhat.com</a>><br>
> > > > > To: "Liran Zelkha" <<a href=3D"mailto:liran.zel=
kha@gmail.com" target=3D"_blank">liran.zelkha@gmail.com</a>><br>
> > > > > Cc: <a href=3D"mailto:devel@linode01.ovirt.org" ta=
rget=3D"_blank">devel@linode01.ovirt.org</a>, "engine-devel" <<a href=3D=
"mailto:engine-devel@ovirt.org" target=3D"_blank">engine-devel@ovirt.org</a=
lit<br>
> > > > > > > > >         it=
 to the two existing tables (BTW - relevant for VM<br>
> > as<br>
> > > > well)<br>
> > > > > > > > ><br>
> > > > > > > > ><br>
> > > > > > > > >     but we don't upd=
ate it unless the status has changed,<br>
> > which<br>
> > > > is a<br>
> > > > > > > > >     rare occurance?<=
br>
> > > > > > > > ><br>
> > > > > > > > > Actually - no. We can definite=
ly see times we are updating<br>
> > > > > > > > > vds_dynamic<br>
> > > > > > > > > with no reason at all. I tried=
 to create patches for that -<br>
> > but<br>
> > > > it<br>
> > > > > > > > > happens from many different pl=
aces in the code.<br>
> > > > > > > ><br>
> > > > > > > > what would be updated vds_dyanmic f=
or status not originating in<br>
> > > > update<br>
> > > > > > > > run time info?<br>
> > > > > > ><br>
> > > > > > > We have separate DB flows for that (upda=
teStatus and<br>
> > > > > > > updatePartialVdsDynamicCalc and more in<=
br>
> > VdsDynamicDAODbFacadeImpl).<br>
> > > > > > > A question: do you know if we update sta=
tus in updateVdsDynamic?<br>
> > :-)<br>
> > > > not<br>
> > > > > > > sure but I found a possible race for pen=
ding resources (cpu,<br>
> > mem),<br>
> > > > LOL<br>
> > > > > > > :-)<br>
> > > > > > ><br>
> > > > > > > I think we do but not sure. Will check.<=
br>
> > > > ><br>
> > > > > Of course it is, that was a rhetorical question :-=
) (a lot of<br>
> > emoticons<br>
> > > > and<br>
> > > > > LOLs ;-))<br>
> > > > ><br>
> > > > > ><br>
> > > > > ><br>
> > > > > > > Still holds my original thought for havi=
ng vds_on_boot.<br>
> > > > > > ><br>
> > > > > > ><br>
> > > > > > ><br>
> > > > > > Let's talk f2f on Tuesday?<br>
> > > > ><br>
> > > > > I'd prefer to reach conclusions here, I'd like eve=
ryone to be<br>
> > involved<br>
> > > > in a<br>
> > > > > root issue like this one.<br>
> > > ><br>
> > ><br>
> > > What is the update frequency of this field?<br>
> > ><br>
> ><br>
> > which field?<br>
> > status? pending resources? on boot fields?<br>
> > iinm, status is updated mostly by user actions, at least in posit=
ive<br>
> > scenarios, and not that often.<br>
> ><br>
> ><br>
> > > > ><br>
> > > > > ><br>
> > > > > _______________________________________________<br=
...
> > > > > Devel mailing list<br>
> > > > > <a href=3D"mailto:Devel@ovirt.org" target=3D"_blan=
k">Devel@ovirt.org</a><br>
> > > > > <a href=3D"http://lists.ovirt.org/mailman/listinfo=
/devel" target=3D"_blank">http://lists.ovirt.org/mailman/listinfo/devel</a>=
<br>
> > > > ><br>
> > > ><br>
> > ><br>
> ><br>
><br>
</div></div></blockquote></div><br></div></div>
<br>_______________________________________________<br>Devel mailing list<b=
r>Devel@ovirt.org<br>http://lists.ovirt.org/mailman/listinfo/devel</blockqu=
ote><div><br></div></div></body></html>
------=_Part_3278465_796563103.1397046843672--

Re: [ovirt-devel] [Devel] [Engine-devel] vds_dynamic refactor

Yaniv Dary