3:22 a.m.
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?
3:32 a.m.
New subject: [Devel] [Engine-devel] vds_dynamic refactor
On Sun, Apr 6, 2014 at 11:22 AM, Itamar Heim <iheim(a)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.
3:33 a.m.
New subject: [Devel] [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(a)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?
7:18 a.m.
New subject: [Devel] [Engine-devel] vds_dynamic refactor
----- Original Message -----
From: "Itamar Heim" <iheim(a)redhat.com>
To: "Liran Zelkha" <liran.zelkha(a)gmail.com>
Cc: "Gilad Chaplik" <gchaplik(a)redhat.com>, devel(a)linode01.ovirt.org,
"engine-devel" <engine-devel(a)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(a)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 :-)
Still holds my original thought for having vds_on_boot.
7:26 a.m.
New subject: [Devel] [Engine-devel] vds_dynamic refactor
On Sun, Apr 6, 2014 at 3:18 PM, Gilad Chaplik <gchaplik(a)redhat.com> wrote:
----- Original Message -----
> From: "Itamar Heim" <iheim(a)redhat.com>
> To: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> Cc: "Gilad Chaplik" <gchaplik(a)redhat.com>,
devel(a)linode01.ovirt.org,
"engine-devel" <engine-devel(a)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(a)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.
Still holds my original thought for having vds_on_boot.
Let's talk f2f on Tuesday?
7:32 a.m.
New subject: [Devel] [Engine-devel] vds_dynamic refactor
----- Original Message -----
From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
To: "Gilad Chaplik" <gchaplik(a)redhat.com>
Cc: "Itamar Heim" <iheim(a)redhat.com>, devel(a)linode01.ovirt.org,
"engine-devel" <engine-devel(a)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(a)redhat.com> wrote:
> ----- Original Message -----
> > From: "Itamar Heim" <iheim(a)redhat.com>
> > To: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> > Cc: "Gilad Chaplik" <gchaplik(a)redhat.com>,
devel(a)linode01.ovirt.org,
> "engine-devel" <engine-devel(a)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(a)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.
7:37 a.m.
New subject: [Devel] [Engine-devel] vds_dynamic refactor
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
----- Original Message -----
From: "Gilad Chaplik" <gchaplik(a)redhat.com>
To: "Liran Zelkha" <liran.zelkha(a)gmail.com>
Cc: devel(a)linode01.ovirt.org, "engine-devel" <engine-devel(a)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(a)gmail.com>
> To: "Gilad Chaplik" <gchaplik(a)redhat.com>
> Cc: "Itamar Heim" <iheim(a)redhat.com>, devel(a)linode01.ovirt.org,
> "engine-devel" <engine-devel(a)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(a)redhat.com> wrote:
>
> > ----- Original Message -----
> > > From: "Itamar Heim" <iheim(a)redhat.com>
> > > To: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> > > Cc: "Gilad Chaplik" <gchaplik(a)redhat.com>,
devel(a)linode01.ovirt.org,
> > "engine-devel" <engine-devel(a)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(a)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.
>
_______________________________________________
Devel mailing list
Devel(a)ovirt.org
http://lists.ovirt.org/mailman/listinfo/devel
7:40 a.m.
New subject: [Devel] [Engine-devel] vds_dynamic refactor
On Sun, Apr 6, 2014 at 3:37 PM, Kobi Ianko <kobi(a)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.
----- Original Message -----
> From: "Gilad Chaplik" <gchaplik(a)redhat.com>
> To: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> Cc: devel(a)linode01.ovirt.org, "engine-devel"
<engine-devel(a)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(a)gmail.com>
> > To: "Gilad Chaplik" <gchaplik(a)redhat.com>
> > Cc: "Itamar Heim" <iheim(a)redhat.com>,
devel(a)linode01.ovirt.org,
> > "engine-devel" <engine-devel(a)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(a)redhat.com>
wrote:
> >
> > > ----- Original Message -----
> > > > From: "Itamar Heim" <iheim(a)redhat.com>
> > > > To: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> > > > Cc: "Gilad Chaplik" <gchaplik(a)redhat.com>,
devel(a)linode01.ovirt.org,
> > > "engine-devel" <engine-devel(a)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(a)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?
>
> >
> _______________________________________________
> Devel mailing list
> Devel(a)ovirt.org
> http://lists.ovirt.org/mailman/listinfo/devel
>
10:32 a.m.
New subject: [Devel] [Engine-devel] vds_dynamic refactor
----- Original Message -----
From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
To: "Kobi Ianko" <kobi(a)redhat.com>
Cc: "Gilad Chaplik" <gchaplik(a)redhat.com>, devel(a)linode01.ovirt.org,
"engine-devel" <engine-devel(a)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(a)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?
>
> ----- Original Message -----
> > From: "Gilad Chaplik" <gchaplik(a)redhat.com>
> > To: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> > Cc: devel(a)linode01.ovirt.org, "engine-devel"
<engine-devel(a)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(a)gmail.com>
> > > To: "Gilad Chaplik" <gchaplik(a)redhat.com>
> > > Cc: "Itamar Heim" <iheim(a)redhat.com>,
devel(a)linode01.ovirt.org,
> > > "engine-devel" <engine-devel(a)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(a)redhat.com>
> wrote:
> > >
> > > > ----- Original Message -----
> > > > > From: "Itamar Heim" <iheim(a)redhat.com>
> > > > > To: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> > > > > Cc: "Gilad Chaplik" <gchaplik(a)redhat.com>,
> devel(a)linode01.ovirt.org,
> > > > "engine-devel" <engine-devel(a)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(a)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(a)ovirt.org
> > http://lists.ovirt.org/mailman/listinfo/devel
> >
>
12:51 p.m.
New subject: [Devel] [Engine-devel] vds_dynamic refactor
On Sun, Apr 6, 2014 at 6:32 PM, Gilad Chaplik <gchaplik(a)redhat.com> wrote:
----- Original Message -----
> From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> To: "Kobi Ianko" <kobi(a)redhat.com>
> Cc: "Gilad Chaplik" <gchaplik(a)redhat.com>,
devel(a)linode01.ovirt.org,
"engine-devel" <engine-devel(a)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(a)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...
> >
> > ----- Original Message -----
> > > From: "Gilad Chaplik" <gchaplik(a)redhat.com>
> > > To: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> > > Cc: devel(a)linode01.ovirt.org, "engine-devel"
<engine-devel(a)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(a)gmail.com>
> > > > To: "Gilad Chaplik" <gchaplik(a)redhat.com>
> > > > Cc: "Itamar Heim" <iheim(a)redhat.com>,
devel(a)linode01.ovirt.org,
> > > > "engine-devel" <engine-devel(a)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(a)redhat.com
>
> > wrote:
> > > >
> > > > > ----- Original Message -----
> > > > > > From: "Itamar Heim" <iheim(a)redhat.com>
> > > > > > To: "Liran Zelkha"
<liran.zelkha(a)gmail.com>
> > > > > > Cc: "Gilad Chaplik" <gchaplik(a)redhat.com>,
> > devel(a)linode01.ovirt.org,
> > > > > "engine-devel" <engine-devel(a)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(a)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(a)ovirt.org
> > > http://lists.ovirt.org/mailman/listinfo/devel
> > >
> >
>
3:03 p.m.
New subject: [Devel] [Engine-devel] vds_dynamic refactor
----- Original Message -----
From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
To: "Gilad Chaplik" <gchaplik(a)redhat.com>
Cc: "Kobi Ianko" <kobi(a)redhat.com>, devel(a)linode01.ovirt.org,
"engine-devel" <engine-devel(a)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(a)redhat.com> wrote:
> ----- Original Message -----
> > From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> > To: "Kobi Ianko" <kobi(a)redhat.com>
> > Cc: "Gilad Chaplik" <gchaplik(a)redhat.com>,
devel(a)linode01.ovirt.org,
> "engine-devel" <engine-devel(a)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(a)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.
* > 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?
* you didn't reply on my of my suggestion of constructing the VDS records in the DB
without using joins.
>
> > >
> > > ----- Original Message -----
> > > > From: "Gilad Chaplik" <gchaplik(a)redhat.com>
> > > > To: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> > > > Cc: devel(a)linode01.ovirt.org, "engine-devel"
<engine-devel(a)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(a)gmail.com>
> > > > > To: "Gilad Chaplik" <gchaplik(a)redhat.com>
> > > > > Cc: "Itamar Heim" <iheim(a)redhat.com>,
devel(a)linode01.ovirt.org,
> > > > > "engine-devel" <engine-devel(a)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(a)redhat.com
> >
> > > wrote:
> > > > >
> > > > > > ----- Original Message -----
> > > > > > > From: "Itamar Heim"
<iheim(a)redhat.com>
> > > > > > > To: "Liran Zelkha"
<liran.zelkha(a)gmail.com>
> > > > > > > Cc: "Gilad Chaplik"
<gchaplik(a)redhat.com>,
> > > devel(a)linode01.ovirt.org,
> > > > > > "engine-devel" <engine-devel(a)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(a)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(a)ovirt.org
> > > > http://lists.ovirt.org/mailman/listinfo/devel
> > > >
> > >
> >
>
12:51 a.m.
New subject: [Devel] [Engine-devel] vds_dynamic refactor
On Sun, Apr 6, 2014 at 11:03 PM, Gilad Chaplik <gchaplik(a)redhat.com> wrote:
----- Original Message -----
> From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> To: "Gilad Chaplik" <gchaplik(a)redhat.com>
> Cc: "Kobi Ianko" <kobi(a)redhat.com>, devel(a)linode01.ovirt.org,
"engine-devel" <engine-devel(a)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(a)redhat.com>
wrote:
>
> > ----- Original Message -----
> > > From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> > > To: "Kobi Ianko" <kobi(a)redhat.com>
> > > Cc: "Gilad Chaplik" <gchaplik(a)redhat.com>,
devel(a)linode01.ovirt.org,
> > "engine-devel" <engine-devel(a)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(a)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(a)redhat.com>
> > > > > To: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> > > > > Cc: devel(a)linode01.ovirt.org, "engine-devel" <
engine-devel(a)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(a)gmail.com>
> > > > > > To: "Gilad Chaplik" <gchaplik(a)redhat.com>
> > > > > > Cc: "Itamar Heim" <iheim(a)redhat.com>,
devel(a)linode01.ovirt.org
,
> > > > > > "engine-devel" <engine-devel(a)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(a)redhat.com
> > >
> > > > wrote:
> > > > > >
> > > > > > > ----- Original Message -----
> > > > > > > > From: "Itamar Heim"
<iheim(a)redhat.com>
> > > > > > > > To: "Liran Zelkha"
<liran.zelkha(a)gmail.com>
> > > > > > > > Cc: "Gilad Chaplik"
<gchaplik(a)redhat.com>,
> > > > devel(a)linode01.ovirt.org,
> > > > > > > "engine-devel"
<engine-devel(a)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(a)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(a)ovirt.org
> > > > > http://lists.ovirt.org/mailman/listinfo/devel
> > > > >
> > > >
> > >
> >
>
7:34 a.m.
New subject: [Devel] [Engine-devel] vds_dynamic refactor
------=_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 -----
<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(a)gmail.com</a>&gt;<br > To: "Gilad Chaplik" <<a
href=3D"mailto:gchaplik@redhat.com" target=
=3D"_blank">gchaplik(a)redhat.com</a>&gt;<br </div><div><div
class=3D"h5">> Cc: "Kobi Ianko" <<a
href=3D"mailto:ko=
bi(a)redhat.com" target=3D"_blank">kobi(a)redhat.com</a>&gt;,
<a href=3D"mailto=
:devel@linode01.ovirt.org"
target=3D"_blank">devel(a)linode01.ovirt.org</a>, =
"engine-devel" <<a href=3D"mailto:engine-devel@ovirt.org"
target=3D"_bla=
nk">engine-devel(a)ovirt.org</a>&gt;<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(a)redhat.com"
target=3D"_blank">gchaplik(a)redhat.com</a>&gt; wrote:<br ><br >
> ----- Original Message -----<br >
> > From: "Liran Zelkha" <<a
href=3D"mailto:liran.zelkha@gmai=
l.com"
target=3D"_blank">liran.zelkha(a)gmail.com</a>&gt;<br > > > To: "Kobi Ianko"
<<a href=3D"mailto:kobi@redhat.com" targ=
et=3D"_blank">kobi(a)redhat.com</a>&gt;<br > > > Cc: "Gilad Chaplik"
<<a href=3D"mailto:gchaplik@redhat.co=
m" target=3D"_blank">gchaplik(a)redhat.com</a>&gt;, <a
href=3D"mailto:devel@l=
inode01.ovirt.org"
target=3D"_blank">devel(a)linode01.ovirt.org</a>,<br > > "engine-devel" <<a
href=3D"mailto:engine-devel@ovirt.org" targ=
et=3D"_blank">engine-devel(a)ovirt.org</a>&gt;<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(a)redhat.com</a>&gt; 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 > > > >
----- Original Message -----<br > > > >
> From: "Gilad Chaplik" <<a href=3D"mailto:gchapl=
ik(a)redhat.com"
target=3D"_blank">gchaplik(a)redhat.com</a>&gt;<br > > > > > To: "Liran
Zelkha" <<a href=3D"mailto:liran.zel=
kha(a)gmail.com"
target=3D"_blank">liran.zelkha(a)gmail.com</a>&gt;<br > > > > > Cc: <a
href=3D"mailto:devel@linode01.ovirt.org" ta=
rget=3D"_blank">devel(a)linode01.ovirt.org</a>, "engine-devel"
<<a href=3D=
"mailto:engine-devel@ovirt.org"
target=3D"_blank">engine-devel(a)ovirt.org</a=
> > > > > 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(a)gmail.com"
target=3D"_blank">liran.zelkha(a)gmail.com</a>&gt;<br > > > > > > To:
"Gilad Chaplik" <<a href=3D"mailto:gch=
aplik(a)redhat.com"
target=3D"_blank">gchaplik(a)redhat.com</a>&gt;<br > > > > > > Cc:
"Itamar Heim" <<a href=3D"mailto:iheim=
@redhat.com" target=3D"_blank">iheim(a)redhat.com</a>&gt;, <a
href=3D"mailto:=
devel(a)linode01.ovirt.org"
target=3D"_blank">devel(a)linode01.ovirt.org</a>,<b=
r > > > > > >
"engine-devel" <<a href=3D"mailto:engine-d=
evel(a)ovirt.org"
target=3D"_blank">engine-devel(a)ovirt.org</a>&gt;<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 > > > >
wrote:<br > > > >
> ><br > > > >
> > > ----- Original Message -----<br >
> > > > > > > From: "Itamar
Heim" <<a href=3D"=
mailto:iheim@redhat.com"
target=3D"_blank">iheim(a)redhat.com</a>&gt;<br > > > > > > >
> To: "Liran Zelkha" <<a href=3D"m=
ailto:liran.zelkha@gmail.com"
target=3D"_blank">liran.zelkha(a)gmail.com</a>&=
gt;<br > > > >
> > > > Cc: "Gilad Chaplik" <<a
href=3D"=
mailto:gchaplik@redhat.com"
target=3D"_blank">gchaplik(a)redhat.com</a>&gt;,<=
br > > > > <a
href=3D"mailto:devel@linode01.ovirt.org" target=3D"_=
blank">devel(a)linode01.ovirt.org</a>,<br >
> > > > > > "engine-devel"
<<a href=3D"mailto:eng=
ine-devel(a)ovirt.org"
target=3D"_blank">engine-devel(a)ovirt.org</a>&gt;<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 > > > >
> > > > > 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=
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(a)ovirt.org</a > > > > ><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--
From: "Liran Zelkha" <liran.zelkha(a)gmail.com To: "Gilad Chaplik" <gchaplik(a)redhat.com Cc: "Kobi Ianko" <kobi(a)redhat.com>,
devel(a)linode01.ovirt.org, "engine-devel"
<engine-devel(a)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(a)redhat.com > wrote:
> ----- Original Message -----
> > From: "Liran Zelkha" < liran.zelkha(a)gmail.com
> > To: "Gilad Chaplik" < gchaplik(a)redhat.com
> > Cc: "Kobi Ianko" < kobi(a)redhat.com >, devel(a)linode01.ovirt.org
,
> > "engine-devel" < engine-devel(a)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(a)redhat.com > > wrote:
>
> > > ----- Original Message -----
> > > > From: "Liran Zelkha" < liran.zelkha(a)gmail.com
> > > > To: "Kobi Ianko" < kobi(a)redhat.com
> > > > Cc: "Gilad Chaplik" < gchaplik(a)redhat.com >,
devel(a)linode01.ovirt.org
> > > > ,
> > > "engine-devel" < engine-devel(a)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(a)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(a)redhat.com
> > > > > > To: "Liran Zelkha" < liran.zelkha(a)gmail.com
> > > > > > Cc: devel(a)linode01.ovirt.org , "engine-devel"
<
> > > > > > engine-devel(a)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(a)gmail.com
> > > > > > > To: "Gilad Chaplik" < gchaplik(a)redhat.com
> > > > > > > Cc: "Itamar Heim" < iheim(a)redhat.com
>,
> > > > > > > devel(a)linode01.ovirt.org
> > > > > > > ,
> > > > > > > "engine-devel" < engine-devel(a)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(a)redhat.com
> > >
> > > > > wrote:
> > > > > >
> > > > > > > > ----- Original Message -----
> > > > > > > > > From: "Itamar Heim" <
iheim(a)redhat.com
> > > > > > > > > To: "Liran Zelkha" <
liran.zelkha(a)gmail.com
> > > > > > > > > Cc: "Gilad Chaplik" <
gchaplik(a)redhat.com >,
> > > > > devel(a)linode01.ovirt.org ,
> > > > > > > > "engine-devel" <
engine-devel(a)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(a)redhat.com
> > > > > > > > > > <mailto: iheim(a)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(a)ovirt.org
> > > > > > http://lists.ovirt.org/mailman/listinfo/devel
> > > > >
> > > >
> > >
> >
>
_______________________________________________
Devel mailing list
Devel(a)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
<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(a)gmail.com&gt;<br><b>To: </b>"Gilad
Chaplik" <gchaplik@re=
dhat.com><br><b>Cc: </b>"Kobi Ianko"
&lt;kobi(a)redhat.com&gt;, devel@lino=
de01.ovirt.org, "engine-devel"
&lt;engine-devel(a)ovirt.org&gt;<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(a)redhat.com</a>&gt;</span>=
wrote:<br<br>
<br<br>
> > ><br<br>
> >
><br<br>
> > >
> > > > > > <mailto:<a
href=3D"mailto:i=
heim(a)redhat.com"
target=3D"_blank">iheim(a)redhat.com</a>&gt;&gt; wrote:<br<br>
> > >
> > <a href=3D"http://lists.ovirt.org/mailman/listinfo=
/devel"
target=3D"_blank">http://lists.ovirt.org/mailman/listinfo/de...
<br
8:22 a.m.
New subject: [Devel] [Engine-devel] vds_dynamic refactor
On Wed, Apr 9, 2014 at 3:34 PM, Yaniv Dary <ydary(a)redhat.com> wrote:
Why not move only status with changes a lot to statistics and leave everything as is?
Exactly. No need for a new table. Use the existing ones.
Yaniv
________________________________
From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
To: "Gilad Chaplik" <gchaplik(a)redhat.com>
Cc: "Kobi Ianko" <kobi(a)redhat.com>, devel(a)linode01.ovirt.org,
"engine-devel" <engine-devel(a)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(a)redhat.com> wrote:
>
> ----- Original Message -----
> > From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> > To: "Gilad Chaplik" <gchaplik(a)redhat.com>
> > Cc: "Kobi Ianko" <kobi(a)redhat.com>, devel(a)linode01.ovirt.org,
"engine-devel" <engine-devel(a)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(a)redhat.com>
wrote:
> >
> > > ----- Original Message -----
> > > > From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> > > > To: "Kobi Ianko" <kobi(a)redhat.com>
> > > > Cc: "Gilad Chaplik" <gchaplik(a)redhat.com>,
devel(a)linode01.ovirt.org,
> > > "engine-devel" <engine-devel(a)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(a)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(a)redhat.com>
> > > > > > To: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> > > > > > Cc: devel(a)linode01.ovirt.org, "engine-devel"
<engine-devel(a)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(a)gmail.com>
> > > > > > > To: "Gilad Chaplik"
<gchaplik(a)redhat.com>
> > > > > > > Cc: "Itamar Heim" <iheim(a)redhat.com>,
devel(a)linode01.ovirt.org,
> > > > > > > "engine-devel"
<engine-devel(a)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(a)redhat.com
> > > >
> > > > > wrote:
> > > > > > >
> > > > > > > > ----- Original Message -----
> > > > > > > > > From: "Itamar Heim"
<iheim(a)redhat.com>
> > > > > > > > > To: "Liran Zelkha"
<liran.zelkha(a)gmail.com>
> > > > > > > > > Cc: "Gilad Chaplik"
<gchaplik(a)redhat.com>,
> > > > > devel(a)linode01.ovirt.org,
> > > > > > > > "engine-devel"
<engine-devel(a)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(a)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(a)ovirt.org
> > > > > > http://lists.ovirt.org/mailman/listinfo/devel
> > > > > >
> > > > >
> > > >
> > >
> >
_______________________________________________
Devel mailing list
Devel(a)ovirt.org
http://lists.ovirt.org/mailman/listinfo/devel
3:07 a.m.
New subject: [Devel] [Engine-devel] vds_dynamic refactor
----- Original Message -----
From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
To: "Yaniv Dary" <ydary(a)redhat.com>
Cc: "Gilad Chaplik" <gchaplik(a)redhat.com>, "Kobi Ianko"
<kobi(a)redhat.com>, devel(a)linode01.ovirt.org, "engine-devel"
<engine-devel(a)ovirt.org>
Sent: Wednesday, April 9, 2014 4:22:39 PM
Subject: Re: [Devel] [Engine-devel] vds_dynamic refactor
On Wed, Apr 9, 2014 at 3:34 PM, Yaniv Dary <ydary(a)redhat.com> wrote:
>
> Why not move only status with changes a lot to statistics and leave
> everything as is?
>
>
Exactly. No need for a new table. Use the existing ones.
Why should the dwh read entire dynamic record for each status change/available resources
change? (for VMs as well).
>
>
> Yaniv
>
> ________________________________
>
> From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> To: "Gilad Chaplik" <gchaplik(a)redhat.com>
> Cc: "Kobi Ianko" <kobi(a)redhat.com>, devel(a)linode01.ovirt.org,
> "engine-devel" <engine-devel(a)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(a)redhat.com> wrote:
>>
>> ----- Original Message -----
>> > From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
>> > To: "Gilad Chaplik" <gchaplik(a)redhat.com>
>> > Cc: "Kobi Ianko" <kobi(a)redhat.com>,
devel(a)linode01.ovirt.org,
>> > "engine-devel" <engine-devel(a)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(a)redhat.com>
>> > wrote:
>> >
>> > > ----- Original Message -----
>> > > > From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
>> > > > To: "Kobi Ianko" <kobi(a)redhat.com>
>> > > > Cc: "Gilad Chaplik" <gchaplik(a)redhat.com>,
devel(a)linode01.ovirt.org,
>> > > "engine-devel" <engine-devel(a)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(a)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(a)redhat.com>
>> > > > > > To: "Liran Zelkha"
<liran.zelkha(a)gmail.com>
>> > > > > > Cc: devel(a)linode01.ovirt.org, "engine-devel"
>> > > > > > <engine-devel(a)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(a)gmail.com>
>> > > > > > > To: "Gilad Chaplik"
<gchaplik(a)redhat.com>
>> > > > > > > Cc: "Itamar Heim"
<iheim(a)redhat.com>,
>> > > > > > > devel(a)linode01.ovirt.org,
>> > > > > > > "engine-devel"
<engine-devel(a)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(a)redhat.com
>> > > >
>> > > > > wrote:
>> > > > > > >
>> > > > > > > > ----- Original Message -----
>> > > > > > > > > From: "Itamar Heim"
<iheim(a)redhat.com>
>> > > > > > > > > To: "Liran Zelkha"
<liran.zelkha(a)gmail.com>
>> > > > > > > > > Cc: "Gilad Chaplik"
<gchaplik(a)redhat.com>,
>> > > > > devel(a)linode01.ovirt.org,
>> > > > > > > > "engine-devel"
<engine-devel(a)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(a)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(a)ovirt.org
>> > > > > > http://lists.ovirt.org/mailman/listinfo/devel
>> > > > > >
>> > > > >
>> > > >
>> > >
>> >
>
>
>
> _______________________________________________
> Devel mailing list
> Devel(a)ovirt.org
> http://lists.ovirt.org/mailman/listinfo/devel
>
>
3:44 a.m.
New subject: [Devel] [Engine-devel] vds_dynamic refactor
----- Original Message -----
From: "Gilad Chaplik" <gchaplik(a)redhat.com>
To: "Liran Zelkha" <liran.zelkha(a)gmail.com>
Cc: "Yaniv Dary" <ydary(a)redhat.com>, "Kobi Ianko"
<kobi(a)redhat.com>, devel(a)linode01.ovirt.org, "engine-devel"
<engine-devel(a)ovirt.org>
Sent: Thursday, April 10, 2014 11:07:12 AM
Subject: Re: [Devel] [Engine-devel] vds_dynamic refactor
----- Original Message -----
> From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> To: "Yaniv Dary" <ydary(a)redhat.com>
> Cc: "Gilad Chaplik" <gchaplik(a)redhat.com>, "Kobi Ianko"
<kobi(a)redhat.com>,
> devel(a)linode01.ovirt.org, "engine-devel"
> <engine-devel(a)ovirt.org>
> Sent: Wednesday, April 9, 2014 4:22:39 PM
> Subject: Re: [Devel] [Engine-devel] vds_dynamic refactor
>
> On Wed, Apr 9, 2014 at 3:34 PM, Yaniv Dary <ydary(a)redhat.com> wrote:
> >
> > Why not move only status with changes a lot to statistics and leave
> > everything as is?
> >
> >
> Exactly. No need for a new table. Use the existing ones.
Why should the dwh read entire dynamic record for each status
change/available resources change? (for VMs as well).
The DWH doesn't work like that. We create views over dynamic\statistics and
dynamic\static they go the the configuration and stats tables on the history db side.
From dynamic\statistics we collect every minute no matter what and
from dynamic\static we collect only when update_date changes in static.
We can not
rely on the dynamic update_date since it changes so much, so from my point of view if we
can cause minimal changes in dynamic and move status to statistics it would the best
thing.
What we have currently is a task that run once a hour and checks if any change was made in
the dynamic table (via very ugly joins and rejects) and sync is there was any change.
It would even be better if you also move swap_size which doesn't change much to
dynamic.
>
> >
> >
> > Yaniv
> >
> > ________________________________
> >
> > From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> > To: "Gilad Chaplik" <gchaplik(a)redhat.com>
> > Cc: "Kobi Ianko" <kobi(a)redhat.com>, devel(a)linode01.ovirt.org,
> > "engine-devel" <engine-devel(a)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(a)redhat.com>
> > wrote:
> >>
> >> ----- Original Message -----
> >> > From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> >> > To: "Gilad Chaplik" <gchaplik(a)redhat.com>
> >> > Cc: "Kobi Ianko" <kobi(a)redhat.com>,
devel(a)linode01.ovirt.org,
> >> > "engine-devel" <engine-devel(a)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(a)redhat.com>
> >> > wrote:
> >> >
> >> > > ----- Original Message -----
> >> > > > From: "Liran Zelkha"
<liran.zelkha(a)gmail.com>
> >> > > > To: "Kobi Ianko" <kobi(a)redhat.com>
> >> > > > Cc: "Gilad Chaplik" <gchaplik(a)redhat.com>,
> >> > > > devel(a)linode01.ovirt.org,
> >> > > "engine-devel" <engine-devel(a)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(a)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(a)redhat.com>
> >> > > > > > To: "Liran Zelkha"
<liran.zelkha(a)gmail.com>
> >> > > > > > Cc: devel(a)linode01.ovirt.org,
"engine-devel"
> >> > > > > > <engine-devel(a)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(a)gmail.com>
> >> > > > > > > To: "Gilad Chaplik"
<gchaplik(a)redhat.com>
> >> > > > > > > Cc: "Itamar Heim"
<iheim(a)redhat.com>,
> >> > > > > > > devel(a)linode01.ovirt.org,
> >> > > > > > > "engine-devel"
<engine-devel(a)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(a)redhat.com
> >> > > >
> >> > > > > wrote:
> >> > > > > > >
> >> > > > > > > > ----- Original Message -----
> >> > > > > > > > > From: "Itamar Heim"
<iheim(a)redhat.com>
> >> > > > > > > > > To: "Liran Zelkha"
<liran.zelkha(a)gmail.com>
> >> > > > > > > > > Cc: "Gilad Chaplik"
<gchaplik(a)redhat.com>,
> >> > > > > devel(a)linode01.ovirt.org,
> >> > > > > > > > "engine-devel"
<engine-devel(a)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(a)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(a)ovirt.org
> >> > > > > > http://lists.ovirt.org/mailman/listinfo/devel
> >> > > > > >
> >> > > > >
> >> > > >
> >> > >
> >> >
> >
> >
> >
> > _______________________________________________
> > Devel mailing list
> > Devel(a)ovirt.org
> > http://lists.ovirt.org/mailman/listinfo/devel
> >
> >
>
4:30 a.m.
New subject: [Devel] [Engine-devel] vds_dynamic refactor
----- Original Message -----
From: "Yaniv Dary" <ydary(a)redhat.com>
To: "Gilad Chaplik" <gchaplik(a)redhat.com>
Cc: "Liran Zelkha" <liran.zelkha(a)gmail.com>, "Kobi Ianko"
<kobi(a)redhat.com>, devel(a)linode01.ovirt.org, "engine-devel"
<engine-devel(a)ovirt.org>
Sent: Thursday, April 10, 2014 11:44:32 AM
Subject: Re: [Devel] [Engine-devel] vds_dynamic refactor
----- Original Message -----
> From: "Gilad Chaplik" <gchaplik(a)redhat.com>
> To: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> Cc: "Yaniv Dary" <ydary(a)redhat.com>, "Kobi Ianko"
<kobi(a)redhat.com>,
> devel(a)linode01.ovirt.org, "engine-devel"
> <engine-devel(a)ovirt.org>
> Sent: Thursday, April 10, 2014 11:07:12 AM
> Subject: Re: [Devel] [Engine-devel] vds_dynamic refactor
>
> ----- Original Message -----
> > From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> > To: "Yaniv Dary" <ydary(a)redhat.com>
> > Cc: "Gilad Chaplik" <gchaplik(a)redhat.com>, "Kobi
Ianko"
> > <kobi(a)redhat.com>,
> > devel(a)linode01.ovirt.org, "engine-devel"
> > <engine-devel(a)ovirt.org>
> > Sent: Wednesday, April 9, 2014 4:22:39 PM
> > Subject: Re: [Devel] [Engine-devel] vds_dynamic refactor
> >
> > On Wed, Apr 9, 2014 at 3:34 PM, Yaniv Dary <ydary(a)redhat.com> wrote:
> > >
> > > Why not move only status with changes a lot to statistics and leave
> > > everything as is?
> > >
> > >
> > Exactly. No need for a new table. Use the existing ones.
>
> Why should the dwh read entire dynamic record for each status
> change/available resources change? (for VMs as well).
The DWH doesn't work like that. We create views over dynamic\statistics and
dynamic\static they go the the configuration and stats tables on the history
db side.
From dynamic\statistics we collect every minute no matter what and from
dynamic\static we collect only when update_date changes in static.
dynamic\statistics - dynamic\static
to fully understand it, we have 2 triggers to collect from dynamic?
We can not rely on the dynamic update_date since it changes so much,
so from
my point of view if we can cause minimal changes in dynamic and move status
to statistics it would the best thing.
What we have currently is a task that run once a hour and checks if any
change was made in the dynamic table (via very ugly joins and rejects) and
sync is there was any change.
It would even be better if you also move swap_size which doesn't change much
to dynamic.
oh... I thought that we had a trigger to dynamic (if ^ is correct we have).
+1 (out of 1) for you comment :-) and thanks Yaniv the elaborate explanation!
>
> >
> > >
> > >
> > > Yaniv
> > >
> > > ________________________________
> > >
> > > From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> > > To: "Gilad Chaplik" <gchaplik(a)redhat.com>
> > > Cc: "Kobi Ianko" <kobi(a)redhat.com>,
devel(a)linode01.ovirt.org,
> > > "engine-devel" <engine-devel(a)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(a)redhat.com>
> > > wrote:
> > >>
> > >> ----- Original Message -----
> > >> > From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> > >> > To: "Gilad Chaplik" <gchaplik(a)redhat.com>
> > >> > Cc: "Kobi Ianko" <kobi(a)redhat.com>,
devel(a)linode01.ovirt.org,
> > >> > "engine-devel" <engine-devel(a)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(a)redhat.com>
> > >> > wrote:
> > >> >
> > >> > > ----- Original Message -----
> > >> > > > From: "Liran Zelkha"
<liran.zelkha(a)gmail.com>
> > >> > > > To: "Kobi Ianko" <kobi(a)redhat.com>
> > >> > > > Cc: "Gilad Chaplik"
<gchaplik(a)redhat.com>,
> > >> > > > devel(a)linode01.ovirt.org,
> > >> > > "engine-devel" <engine-devel(a)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(a)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(a)redhat.com>
> > >> > > > > > To: "Liran Zelkha"
<liran.zelkha(a)gmail.com>
> > >> > > > > > Cc: devel(a)linode01.ovirt.org,
"engine-devel"
> > >> > > > > > <engine-devel(a)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(a)gmail.com>
> > >> > > > > > > To: "Gilad Chaplik"
<gchaplik(a)redhat.com>
> > >> > > > > > > Cc: "Itamar Heim"
<iheim(a)redhat.com>,
> > >> > > > > > > devel(a)linode01.ovirt.org,
> > >> > > > > > > "engine-devel"
<engine-devel(a)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(a)redhat.com
> > >> > > >
> > >> > > > > wrote:
> > >> > > > > > >
> > >> > > > > > > > ----- Original Message -----
> > >> > > > > > > > > From: "Itamar Heim"
<iheim(a)redhat.com>
> > >> > > > > > > > > To: "Liran Zelkha"
<liran.zelkha(a)gmail.com>
> > >> > > > > > > > > Cc: "Gilad Chaplik"
<gchaplik(a)redhat.com>,
> > >> > > > > devel(a)linode01.ovirt.org,
> > >> > > > > > > > "engine-devel"
<engine-devel(a)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(a)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(a)ovirt.org
> > >> > > > > >
http://lists.ovirt.org/mailman/listinfo/devel
> > >> > > > > >
> > >> > > > >
> > >> > > >
> > >> > >
> > >> >
> > >
> > >
> > >
> > > _______________________________________________
> > > Devel mailing list
> > > Devel(a)ovirt.org
> > > http://lists.ovirt.org/mailman/listinfo/devel
> > >
> > >
> >
>
7:09 a.m.
New subject: [Devel] [Engine-devel] vds_dynamic refactor
----- Original Message -----
From: "Gilad Chaplik" <gchaplik(a)redhat.com>
To: "Yaniv Dary" <ydary(a)redhat.com>
Cc: "Liran Zelkha" <liran.zelkha(a)gmail.com>, "Kobi Ianko"
<kobi(a)redhat.com>, devel(a)linode01.ovirt.org, "engine-devel"
<engine-devel(a)ovirt.org>
Sent: Thursday, April 10, 2014 12:30:52 PM
Subject: Re: [Devel] [Engine-devel] vds_dynamic refactor
----- Original Message -----
> From: "Yaniv Dary" <ydary(a)redhat.com>
> To: "Gilad Chaplik" <gchaplik(a)redhat.com>
> Cc: "Liran Zelkha" <liran.zelkha(a)gmail.com>, "Kobi Ianko"
> <kobi(a)redhat.com>, devel(a)linode01.ovirt.org, "engine-devel"
> <engine-devel(a)ovirt.org>
> Sent: Thursday, April 10, 2014 11:44:32 AM
> Subject: Re: [Devel] [Engine-devel] vds_dynamic refactor
>
>
>
> ----- Original Message -----
> > From: "Gilad Chaplik" <gchaplik(a)redhat.com>
> > To: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> > Cc: "Yaniv Dary" <ydary(a)redhat.com>, "Kobi Ianko"
<kobi(a)redhat.com>,
> > devel(a)linode01.ovirt.org, "engine-devel"
> > <engine-devel(a)ovirt.org>
> > Sent: Thursday, April 10, 2014 11:07:12 AM
> > Subject: Re: [Devel] [Engine-devel] vds_dynamic refactor
> >
> > ----- Original Message -----
> > > From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> > > To: "Yaniv Dary" <ydary(a)redhat.com>
> > > Cc: "Gilad Chaplik" <gchaplik(a)redhat.com>, "Kobi
Ianko"
> > > <kobi(a)redhat.com>,
> > > devel(a)linode01.ovirt.org, "engine-devel"
> > > <engine-devel(a)ovirt.org>
> > > Sent: Wednesday, April 9, 2014 4:22:39 PM
> > > Subject: Re: [Devel] [Engine-devel] vds_dynamic refactor
> > >
> > > On Wed, Apr 9, 2014 at 3:34 PM, Yaniv Dary <ydary(a)redhat.com>
wrote:
> > > >
> > > > Why not move only status with changes a lot to statistics and leave
> > > > everything as is?
> > > >
> > > >
> > > Exactly. No need for a new table. Use the existing ones.
> >
> > Why should the dwh read entire dynamic record for each status
> > change/available resources change? (for VMs as well).
>
> The DWH doesn't work like that. We create views over dynamic\statistics and
> dynamic\static they go the the configuration and stats tables on the
> history
> db side.
> From dynamic\statistics we collect every minute no matter what and from
> dynamic\static we collect only when update_date changes in static.
dynamic\statistics - dynamic\static
to fully understand it, we have 2 triggers to collect from dynamic?
No, the correct answer is that we don't have any triggers.
dynamic\statistics - we collect every minute for stats like status and cpu usage no matter
what changed.
dynamic\static - we collect either when static table changes and update_date change or
once an hour if dynamic changes, but we check this with join and rejects. This is because
dynamic changes too much right now.
> We can not rely on the dynamic update_date since it changes so much, so
> from
> my point of view if we can cause minimal changes in dynamic and move status
> to statistics it would the best thing.
> What we have currently is a task that run once a hour and checks if any
> change was made in the dynamic table (via very ugly joins and rejects) and
> sync is there was any change.
> It would even be better if you also move swap_size which doesn't change
> much
> to dynamic.
>
oh... I thought that we had a trigger to dynamic (if ^ is correct we have).
+1 (out of 1) for you comment :-) and thanks Yaniv the elaborate explanation!
> >
> > >
> > > >
> > > >
> > > > Yaniv
> > > >
> > > > ________________________________
> > > >
> > > > From: "Liran Zelkha" <liran.zelkha(a)gmail.com>
> > > > To: "Gilad Chaplik" <gchaplik(a)redhat.com>
> > > > Cc: "Kobi Ianko" <kobi(a)redhat.com>,
devel(a)linode01.ovirt.org,
> > > > "engine-devel" <engine-devel(a)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(a)redhat.com>
> > > > wrote:
> > > >>
> > > >> ----- Original Message -----
> > > >> > From: "Liran Zelkha"
<liran.zelkha(a)gmail.com>
> > > >> > To: "Gilad Chaplik" <gchaplik(a)redhat.com>
> > > >> > Cc: "Kobi Ianko" <kobi(a)redhat.com>,
devel(a)linode01.ovirt.org,
> > > >> > "engine-devel" <engine-devel(a)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(a)redhat.com>
> > > >> > wrote:
> > > >> >
> > > >> > > ----- Original Message -----
> > > >> > > > From: "Liran Zelkha"
<liran.zelkha(a)gmail.com>
> > > >> > > > To: "Kobi Ianko"
<kobi(a)redhat.com>
> > > >> > > > Cc: "Gilad Chaplik"
<gchaplik(a)redhat.com>,
> > > >> > > > devel(a)linode01.ovirt.org,
> > > >> > > "engine-devel"
<engine-devel(a)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(a)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(a)redhat.com>
> > > >> > > > > > To: "Liran Zelkha"
<liran.zelkha(a)gmail.com>
> > > >> > > > > > Cc: devel(a)linode01.ovirt.org,
"engine-devel"
> > > >> > > > > > <engine-devel(a)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(a)gmail.com>
> > > >> > > > > > > To: "Gilad Chaplik"
<gchaplik(a)redhat.com>
> > > >> > > > > > > Cc: "Itamar Heim"
<iheim(a)redhat.com>,
> > > >> > > > > > > devel(a)linode01.ovirt.org,
> > > >> > > > > > > "engine-devel"
<engine-devel(a)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(a)redhat.com
> > > >> > > >
> > > >> > > > > wrote:
> > > >> > > > > > >
> > > >> > > > > > > > ----- Original Message -----
> > > >> > > > > > > > > From: "Itamar
Heim" <iheim(a)redhat.com>
> > > >> > > > > > > > > To: "Liran
Zelkha" <liran.zelkha(a)gmail.com>
> > > >> > > > > > > > > Cc: "Gilad
Chaplik" <gchaplik(a)redhat.com>,
> > > >> > > > > devel(a)linode01.ovirt.org,
> > > >> > > > > > > > "engine-devel"
<engine-devel(a)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(a)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(a)ovirt.org
> > > >> > > > > >
http://lists.ovirt.org/mailman/listinfo/devel
> > > >> > > > > >
> > > >> > > > >
> > > >> > > >
> > > >> > >
> > > >> >
> > > >
> > > >
> > > >
> > > > _______________________________________________
> > > > Devel mailing list
> > > > Devel(a)ovirt.org
> > > > http://lists.ovirt.org/mailman/listinfo/devel
> > > >
> > > >
> > >
> >
>
3662
days inactive
3666
days old
17 comments
5 participants
participants (5)
-
Gilad Chaplik -
Itamar Heim -
Kobi Ianko -
Liran Zelkha -
Yaniv Dary