<div dir="ltr"><br><div class="gmail_extra"><br><br><div class="gmail_quote">On Sun, Apr 6, 2014 at 11:03 PM, Gilad Chaplik <span dir="ltr"><<a href="mailto:gchaplik@redhat.com" target="_blank">gchaplik@redhat.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="">----- Original Message -----<br>
> From: "Liran Zelkha" <<a href="mailto:liran.zelkha@gmail.com">liran.zelkha@gmail.com</a>><br>
> To: "Gilad Chaplik" <<a href="mailto:gchaplik@redhat.com">gchaplik@redhat.com</a>><br>
</div><div><div class="h5">> Cc: "Kobi Ianko" <<a href="mailto:kobi@redhat.com">kobi@redhat.com</a>>, <a href="mailto:devel@linode01.ovirt.org">devel@linode01.ovirt.org</a>, "engine-devel" <<a href="mailto:engine-devel@ovirt.org">engine-devel@ovirt.org</a>><br>
> Sent: Sunday, April 6, 2014 8:51:02 PM<br>
> Subject: Re: [Devel] [Engine-devel] vds_dynamic refactor<br>
><br>
> On Sun, Apr 6, 2014 at 6:32 PM, Gilad Chaplik <<a href="mailto:gchaplik@redhat.com">gchaplik@redhat.com</a>> wrote:<br>
><br>
> > ----- Original Message -----<br>
> > > From: "Liran Zelkha" <<a href="mailto:liran.zelkha@gmail.com">liran.zelkha@gmail.com</a>><br>
> > > To: "Kobi Ianko" <<a href="mailto:kobi@redhat.com">kobi@redhat.com</a>><br>
> > > Cc: "Gilad Chaplik" <<a href="mailto:gchaplik@redhat.com">gchaplik@redhat.com</a>>, <a href="mailto:devel@linode01.ovirt.org">devel@linode01.ovirt.org</a>,<br>
> > "engine-devel" <<a href="mailto:engine-devel@ovirt.org">engine-devel@ovirt.org</a>><br>
> > > Sent: Sunday, April 6, 2014 3:40:13 PM<br>
> > > Subject: Re: [Devel] [Engine-devel] vds_dynamic refactor<br>
> > ><br>
> > > On Sun, Apr 6, 2014 at 3:37 PM, Kobi Ianko <<a href="mailto:kobi@redhat.com">kobi@redhat.com</a>> wrote:<br>
> > ><br>
> > > > Joining in...<br>
> > > > From my point of view, in real life a user should have that many VDSs<br>
> > on<br>
> > > > one Engine (from a DB point of view).<br>
> > > > Modern DB system handles tables with millions of records 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 do 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 several<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=9.30..46.75 rows=6 width=9060) (actual<br>
> time=0.063..0.068 rows=1 loops=1)<br>
> Join Filter: (vds_static.vds_id = vds_statistics.vds_id)<br>
> -> Seq Scan on vds_statistics (cost=0.00..1.01 rows=1 width=109)<br>
> (actual time=0.008..0.008 rows=1 loops=1)<br>
> -> Nested Loop (cost=9.30..45.64 rows=6 width=8983) (actual<br>
> time=0.048..0.052 rows=1 loops=1)<br>
> Join Filter: (vds_groups.vds_group_id = vds_static.vds_group_id)<br>
> -> Nested Loop Left Join (cost=0.00..9.29 rows=1 width=1389)<br>
> (actual time=0.013..0.013 rows=1 loops=1)<br>
> -> Seq Scan on vds_groups (cost=0.00..1.01 rows=1<br>
> width=1271) (actual time=0.003..0.003 rows=1 loops=1)<br>
> -> Index Scan using pk_storage_pool on storage_pool<br>
> (cost=0.00..8.27 rows=1 width=134) (actual time=0.008..0.008 rows=1<br>
> loops=1)<br>
> Index Cond: (vds_groups.storage_pool_id = id)<br>
> -> Hash Right Join (cost=9.30..36.28 rows=6 width=7610) (actual<br>
> time=0.033..0.037 rows=1 loops=1)<br>
> Hash Cond: (vds_spm_id_map.vds_id = vds_static.vds_id)<br>
> -> Seq Scan on vds_spm_id_map (cost=0.00..22.30 rows=1230<br>
> width=20) (actual time=0.003..0.003 rows=1 loops=1)<br>
> -> Hash (cost=9.29..9.29 rows=1 width=7606) (actual<br>
> time=0.019..0.019 rows=1 loops=1)<br>
> Buckets: 1024 Batches: 1 Memory Usage: 2kB<br>
> -> Nested Loop (cost=0.00..9.29 rows=1 width=7606)<br>
> (actual time=0.012..0.013 rows=1 loops=1)<br>
> -> Seq Scan on vds_dynamic (cost=0.00..1.01<br>
> rows=1 width=1895) (actual time=0.006..0.006 rows=1 loops=1)<br>
> -> Index Scan using pk_vds_static on vds_static<br>
> (cost=0.00..8.27 rows=1 width=5711) (actual time=0.005..0.006 rows=1<br>
> loops=1)<br>
> Index Cond: (vds_id = 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 be neglectable);<br>
currently we have an unrelated problem, we need to solve it.<br></blockquote><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="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
* > We start with VDS because in an idle system, with 200 hosts and several<br>
<div class="">> 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 time?<br></blockquote><div> 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. </div>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
* you didn't reply on my of my suggestion of constructing the VDS records in 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 problem, since we can cache some information and only query the dynamic/statistics part of VDS, but that's another matter. </div>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div class="HOEnZb"><div class="h5"><br>
<br>
><br>
> ><br>
> > > ><br>
> > > > ----- Original Message -----<br>
> > > > > From: "Gilad Chaplik" <<a href="mailto:gchaplik@redhat.com">gchaplik@redhat.com</a>><br>
> > > > > To: "Liran Zelkha" <<a href="mailto:liran.zelkha@gmail.com">liran.zelkha@gmail.com</a>><br>
> > > > > Cc: <a href="mailto:devel@linode01.ovirt.org">devel@linode01.ovirt.org</a>, "engine-devel" <<a href="mailto:engine-devel@ovirt.org">engine-devel@ovirt.org</a><br>
> > ><br>
> > > > > Sent: Sunday, April 6, 2014 3:32:26 PM<br>
> > > > > Subject: Re: [Devel] [Engine-devel] vds_dynamic refactor<br>
> > > > ><br>
> > > > > ----- Original Message -----<br>
> > > > > > From: "Liran Zelkha" <<a href="mailto:liran.zelkha@gmail.com">liran.zelkha@gmail.com</a>><br>
> > > > > > To: "Gilad Chaplik" <<a href="mailto:gchaplik@redhat.com">gchaplik@redhat.com</a>><br>
> > > > > > Cc: "Itamar Heim" <<a href="mailto:iheim@redhat.com">iheim@redhat.com</a>>, <a href="mailto:devel@linode01.ovirt.org">devel@linode01.ovirt.org</a>,<br>
> > > > > > "engine-devel" <<a href="mailto:engine-devel@ovirt.org">engine-devel@ovirt.org</a>><br>
> > > > > > Sent: Sunday, April 6, 2014 3:26:24 PM<br>
> > > > > > Subject: Re: [Engine-devel] vds_dynamic refactor<br>
> > > > > ><br>
> > > > > > On Sun, Apr 6, 2014 at 3:18 PM, Gilad Chaplik <<a href="mailto:gchaplik@redhat.com">gchaplik@redhat.com</a><br>
> > ><br>
> > > > wrote:<br>
> > > > > ><br>
> > > > > > > ----- Original Message -----<br>
> > > > > > > > From: "Itamar Heim" <<a href="mailto:iheim@redhat.com">iheim@redhat.com</a>><br>
> > > > > > > > To: "Liran Zelkha" <<a href="mailto:liran.zelkha@gmail.com">liran.zelkha@gmail.com</a>><br>
> > > > > > > > Cc: "Gilad Chaplik" <<a href="mailto:gchaplik@redhat.com">gchaplik@redhat.com</a>>,<br>
> > > > <a href="mailto:devel@linode01.ovirt.org">devel@linode01.ovirt.org</a>,<br>
> > > > > > > "engine-devel" <<a href="mailto:engine-devel@ovirt.org">engine-devel@ovirt.org</a>><br>
> > > > > > > > Sent: Sunday, April 6, 2014 11:33:12 AM<br>
> > > > > > > > Subject: Re: [Engine-devel] vds_dynamic refactor<br>
> > > > > > > ><br>
> > > > > > > > On 04/06/2014 11:32 AM, Liran Zelkha wrote:<br>
> > > > > > > > ><br>
> > > > > > > > ><br>
> > > > > > > > ><br>
> > > > > > > > > On Sun, Apr 6, 2014 at 11:22 AM, Itamar Heim <<br>
> > <a href="mailto:iheim@redhat.com">iheim@redhat.com</a><br>
> > > > > > > > > <mailto:<a href="mailto:iheim@redhat.com">iheim@redhat.com</a>>> wrote:<br>
> > > > > > > > ><br>
> > > > > > > > > On 04/03/2014 07:51 PM, Liran Zelkha wrote:<br>
> > > > > > > > ><br>
> > > > > > > > > The problem is with both updates and selects.<br>
> > > > > > > > > For selects - to get all the information for the VDS<br>
> > we<br>
> > > > have<br>
> > > > > > > > > multiple<br>
> > > > > > > > > joins. Adding another one will hurt performance even<br>
> > > > more.<br>
> > > > > > > > > For updates - we have vds_static thats hardly<br>
> > changed.<br>
> > > > > > > > > vds_statistics<br>
> > > > > > > > > that changes all the time. vds_dynamic is not changed<br>
> > > > allot -<br>
> > > > > > > but<br>
> > > > > > > > > is<br>
> > > > > > > > > updated all the time because of the status. I think<br>
> > it's<br>
> > > > best<br>
> > > > > > > to<br>
> > > > > > > > > split<br>
> > > > > > > > > it to the two existing tables (BTW - relevant for VM<br>
> > as<br>
> > > > well)<br>
> > > > > > > > ><br>
> > > > > > > > ><br>
> > > > > > > > > but we don't update it unless the status has changed,<br>
> > which<br>
> > > > is a<br>
> > > > > > > > > rare occurance?<br>
> > > > > > > > ><br>
> > > > > > > > > Actually - no. We can definitely 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 places in the code.<br>
> > > > > > > ><br>
> > > > > > > > what would be updated vds_dyanmic for status not originating in<br>
> > > > update<br>
> > > > > > > > run time info?<br>
> > > > > > ><br>
> > > > > > > We have separate DB flows for that (updateStatus and<br>
> > > > > > > updatePartialVdsDynamicCalc and more in<br>
> > VdsDynamicDAODbFacadeImpl).<br>
> > > > > > > A question: do you know if we update status in updateVdsDynamic?<br>
> > :-)<br>
> > > > not<br>
> > > > > > > sure but I found a possible race for pending 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 having 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 everyone 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 positive<br>
> > scenarios, and not that often.<br>
> ><br>
> ><br>
> > > > ><br>
> > > > > ><br>
> > > > > _______________________________________________<br>
> > > > > Devel mailing list<br>
> > > > > <a href="mailto:Devel@ovirt.org">Devel@ovirt.org</a><br>
> > > > > <a href="http://lists.ovirt.org/mailman/listinfo/devel" target="_blank">http://lists.ovirt.org/mailman/listinfo/devel</a><br>
> > > > ><br>
> > > ><br>
> > ><br>
> ><br>
><br>
</div></div></blockquote></div><br></div></div>