These are chat archives for ManageIQ/manageiq/performance

13th
Nov 2018
y0g1t
@y0g1t
Nov 13 2018 05:11
@gmcculloug, I am hitting post Api for provisioning of openstack but it's not working, i am using this payload...
{
"action" : "create",
"resource" : {
"version" : "1.1",
"template_fields" : {
"guid" : "eb6c6b38-e4a9-422c-81dc-ed2a0bf401da"
},
"vm_fields" : {
"number_of_cpus" : 1,
"vm_name" : "testvm",
"vm_memory" : "1024",
"vlan" : "nic1",
"src_vm_id":14,
"instance_type":111
},
"requester" : {
"user_name" : "xxxx",
"owner_first_name" : "xxxx",
"owner_last_name" : "xxxx",
"owner_email" : "xxxx@gmail.com",
"auto_approve" : true,
"owner_manager":"xxxx@gmail.com"
},
"tags" : {
"network_location" : "Internal",
"cc" : "001"
},
"additional_values" : {
"request_id" : "1001"
},
"ems_custom_attributes" : { },
"miq_custom_attributes" : { }
}
}
POST Api: "/api/provision_requests/"
Greg McCullough
@gmcculloug
Nov 13 2018 12:41
@y0g1t I would need some idea what error you are running into and also suggest we move to the https://gitter.im/ManageIQ/manageiq/provisioning room since this is not performance related
I'm only running against a "medium" vmware simulation - so once it gets bigger... it may go elsewhere. but locally all vms are downloaded and then a mongo very large query is created with IN clauses
Definietly not a event_where_clause fan. Besides the one place in the bottlenecks_controller (that puts the sql into the session), looks like we could get out of the sql building business and deal with scopes
Nick LaMuro
@NickLaMuro
Nov 13 2018 20:49

mongo query

?

Keenan Brock
@kbrock
Nov 13 2018 20:49
hmm. really big? - thnx nick
changing event_where_clauseto event_scope (Same thing but builds a where clause) doesn't seem to buy too much.
Nick LaMuro
@NickLaMuro
Nov 13 2018 20:50

hmm. really big?

I always think the DB... or Blazing Saddles...

Keenan Brock
@kbrock
Nov 13 2018 20:51
lol. blazing saddles is free for amazon prime. I knew it had some off humor, but it is REALLY made for people over 18 who are not easily offended and appreciate a satire
Nick LaMuro
@NickLaMuro
Nov 13 2018 20:52
anyway, sorry to derail things... I can try some things with a reference DB set to the version reported (fine it seems), and see what comes of it
Keenan Brock
@kbrock
Nov 13 2018 21:03
ugh. looking at these counts for the bottom of the page.
count of resource pools, vms, templates look wrong
SELECT "relationships".* FROM "relationships"
WHERE ("relationships"."ancestry" LIKE '226/227/228/229/234/%' AND "relationships"."ancestry" NOT LIKE '226/227/228/229/234/%')
AND "relationships"."resource_type" = 'ResourcePool'
this page sure is doing a lot of work. and to add to the humor, it is in a hidden tab (not displayed unless you click around)
Keenan Brock
@kbrock
Nov 13 2018 21:20
ok, so I have this working
--- 405
SELECT 1 AS one
FROM "event_streams"
WHERE TYPE = 'EmsEvent' AND (
       "event_streams"."ems_cluster_id" = 3
    OR "event_streams"."host_id" IN (SELECT "hosts"."id" FROM "hosts" WHERE "hosts"."ems_cluster_id" = 3)
    OR "event_streams"."dest_host_id" IN (SELECT "hosts"."id" FROM "hosts" WHERE "hosts"."ems_cluster_id" = 3)
    OR "event_streams"."vm_or_template_id" IN (SELECT "vms"."id" FROM "vms" WHERE "vms"."ems_cluster_id" = 3)
    OR "event_streams"."dest_vm_or_template_id" IN (SELECT "vms"."id" FROM "vms" WHERE "vms"."ems_cluster_id" = 3)
) LIMIT 1
well (it is close enough to that) - and it has a cost of 405 "units"
Nick LaMuro
@NickLaMuro
Nov 13 2018 21:20
does it help?
Keenan Brock
@kbrock
Nov 13 2018 21:21
it is about the same, but not sure what it would look like with > 10 hosts and >100 vms (this is all I have in my db)
I also cheated and added 2 indexes: hosts.ems_cluster_id, vms.ems_cluster_id
Nick LaMuro
@NickLaMuro
Nov 13 2018 21:21
you probably paid someone to take your SAT too, huh?
Keenan Brock
@kbrock
Nov 13 2018 21:22
I don't know how to generate this (it has a cost of 63) and seem similar to the previous one:
--- 63 (does have short circuiting)
select 1 from event_streams where ems_cluster_id = 3
         AND event_streams.TYPE = 'EmsEvent'
union all
select 1 from event_streams
         join hosts on hosts.id = event_streams.host_id and hosts.ems_cluster_id = 3
         WHERE event_streams.TYPE = 'EmsEvent'
union all
select 1 from event_streams
         join hosts on hosts.id = event_streams.dest_host_id and hosts.ems_cluster_id = 3
         WHERE event_streams.TYPE = 'EmsEvent'
union all
select 1 from event_streams
         join vms on vms.id = event_streams.vm_or_template_id and vms.ems_cluster_id = 3
         WHERE event_streams.TYPE = 'EmsEvent'
union all
select 1 from event_streams
         join vms on vms.id = event_streams.dest_vm_or_template_id and vms.ems_cluster_id = 3
         WHERE event_streams.TYPE = 'EmsEvent'
limit 1;
SATs? yup
Joe Rafaniello
@jrafanie
Nov 13 2018 21:22
@kbrock is it worth asking them what their count of hosts, vms, and event_streams?
Keenan Brock
@kbrock
Nov 13 2018 21:22
I assume vms.count is significant
if not, then we are probably barking up the wrong tree

I thought this would be quicker, but got a cost of 316:

--- 316
select 1 where exists(
  select 1 from event_streams where ems_cluster_id = 3
  AND event_streams.TYPE = 'EmsEvent'
) or exists(
  select 1 from event_streams join hosts on hosts.id = event_streams.host_id and hosts.ems_cluster_id = 3
         WHERE event_streams.TYPE = 'EmsEvent'
) or exists(
  select 1 from event_streams
         join hosts on hosts.id = event_streams.dest_host_id and hosts.ems_cluster_id = 3
         WHERE event_streams.TYPE = 'EmsEvent'
) or exists(
  select 1 from event_streams
         join vms on vms.id = event_streams.vm_or_template_id and vms.ems_cluster_id = 3
         WHERE event_streams.TYPE = 'EmsEvent'
) or exists(
select 1 from event_streams
         join vms on vms.id = event_streams.dest_vm_or_template_id and vms.ems_cluster_id = 3
         WHERE event_streams.TYPE = 'EmsEvent'
);

and I can't generate it easily from arel (but feels like it should be pretty easy)

I had wanted to ensure short cutting, but the union (with union OR union all) works the best
Keenan Brock
@kbrock
Nov 13 2018 21:47
yea - so in my final html, lines 10-286 are expanded assets. that takes a lot of time for my machine.
Think I'm going to try config.assets.debug = false - that may have been the line that sped up the atom editor (advanced search) pages
Keenan Brock
@kbrock
Nov 13 2018 21:53
@jrafanie check the ticket for counts
Joe Rafaniello
@jrafanie
Nov 13 2018 21:54
yeah, I found them
Nick LaMuro
@NickLaMuro
Nov 13 2018 22:37
Okay, I think I might no where the issue lies, and I have most likely addressed this before...
(and this isn't what I talked to with Keenan out of band)

So this seems to be called for each host on that page:

SELECT "compliances".*
FROM "compliances"
WHERE "compliances"."resource_id" = $1
  AND "compliances"."resource_type" = $2
ORDER BY timestamp DESC
LIMIT $3"

Which it really is just part of the last_compliance scope found in ComplianceMixin

or rather... that is called from last_compliance_status, which it only needs the last record's status column
I think I got it working to make this a virtual_attribute here: ManageIQ/manageiq#17475
Nick LaMuro
@NickLaMuro
Nov 13 2018 22:45
going to try rebasing the above and testing it out on fine to see if it helps things
not sure it would get a backport that far back at this point, but I can see if it speeds things up at all
Nick LaMuro
@NickLaMuro
Nov 13 2018 23:35
Oh geez, I fixed this BZ already... https://bugzilla.redhat.com/show_bug.cgi?id=1580982
there are some N+1's that I should revisit, but this was fixed in 5.8.4, but the customer is reported to be using 5.8.3
basically, they are probably having a left join bomb, and causing the puma process to choke

this was the fix:

ManageIQ/manageiq-ui-classic#3989

With the following meant as more meaningful fixes:

ManageIQ/manageiq#17473
ManageIQ/manageiq#17474
ManageIQ/manageiq#17475

Nick LaMuro
@NickLaMuro
Nov 13 2018 23:44
For follow up, we could address the current columns in the Hosts view causing N+1's:
  • last_compliance_status (fixed in ManageIQ/manageiq#17475 )
  • v_total_vms (should be addressed by ManageIQ/manageiq#17474 since it is a virtual_total )
  • v_total_miq_templates (same as v_total_vms)
  • authentications table queries related to ipmi_enabled (I think...)