Re: [Devel] [Engine-devel] vds_dynamic refactor
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? ___ Devel mailing list Devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
Re: [Devel] [Engine-devel] vds_dynamic refactor
- Original Message - From: Itamar Heim ih...@redhat.com To: Liran Zelkha liran.zel...@gmail.com Cc: Gilad Chaplik gchap...@redhat.com, de...@linode01.ovirt.org, engine-devel engine-de...@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 ih...@redhat.com mailto:ih...@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. ___ Devel mailing list Devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
Re: [Devel] [Engine-devel] vds_dynamic refactor
- Original Message - From: Liran Zelkha liran.zel...@gmail.com To: Gilad Chaplik gchap...@redhat.com Cc: Itamar Heim ih...@redhat.com, de...@linode01.ovirt.org, engine-devel engine-de...@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 gchap...@redhat.com wrote: - Original Message - From: Itamar Heim ih...@redhat.com To: Liran Zelkha liran.zel...@gmail.com Cc: Gilad Chaplik gchap...@redhat.com, de...@linode01.ovirt.org, engine-devel engine-de...@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 ih...@redhat.com mailto:ih...@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@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
Re: [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 gchap...@redhat.com To: Liran Zelkha liran.zel...@gmail.com Cc: de...@linode01.ovirt.org, engine-devel engine-de...@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.zel...@gmail.com To: Gilad Chaplik gchap...@redhat.com Cc: Itamar Heim ih...@redhat.com, de...@linode01.ovirt.org, engine-devel engine-de...@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 gchap...@redhat.com wrote: - Original Message - From: Itamar Heim ih...@redhat.com To: Liran Zelkha liran.zel...@gmail.com Cc: Gilad Chaplik gchap...@redhat.com, de...@linode01.ovirt.org, engine-devel engine-de...@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 ih...@redhat.com mailto:ih...@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@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel ___ Devel mailing list Devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel
Re: [Devel] [Engine-devel] vds_dynamic refactor
- Original Message - From: Liran Zelkha liran.zel...@gmail.com To: Gilad Chaplik gchap...@redhat.com Cc: Kobi Ianko k...@redhat.com, de...@linode01.ovirt.org, engine-devel engine-de...@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 gchap...@redhat.com wrote: - Original Message - From: Liran Zelkha liran.zel...@gmail.com To: Kobi Ianko k...@redhat.com Cc: Gilad Chaplik gchap...@redhat.com, de...@linode01.ovirt.org, engine-devel engine-de...@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 k...@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 gchap...@redhat.com To: Liran Zelkha liran.zel...@gmail.com Cc: de...@linode01.ovirt.org, engine-devel engine-de...@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.zel...@gmail.com To: Gilad Chaplik gchap...@redhat.com
Re: [Devel] [Engine-devel] vds_dynamic refactor
On Sun, Apr 6, 2014 at 11:03 PM, Gilad Chaplik gchap...@redhat.com wrote: - Original Message - From: Liran Zelkha liran.zel...@gmail.com To: Gilad Chaplik gchap...@redhat.com Cc: Kobi Ianko k...@redhat.com, de...@linode01.ovirt.org, engine-devel engine-de...@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 gchap...@redhat.com wrote: - Original Message - From: Liran Zelkha liran.zel...@gmail.com To: Kobi Ianko k...@redhat.com Cc: Gilad Chaplik gchap...@redhat.com, de...@linode01.ovirt.org, engine-devel engine-de...@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 k...@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
