Description of problem: If you view the Pool Family page, we run an absurd number of SQL queries. I suspect we could rewrite this to do a couple of nice JOINs instead. While most of them are cached by Rails and thus avoid much of an actual performance hit, this is still ridiculous. I'll leave a comment with the log of what gets run. Version-Release number of selected component (if applicable): git HEAD on 0.9-maint (and all branches, really) as of today Steps to Reproduce: 1. Click "Administer" and then "Environments" 2. Look at the Rails log
Created attachment 915414 [details] Comment (This comment was longer than 65,535 characters and has been moved to an attachment by Red Hat Bugzilla).
Patch on list here: http://lists.fedorahosted.org/pipermail/aeolus-devel/2012-February/008956.html
Pushed to master: commit 87f9b85508e273490d8876f8b12c5c239948d733 Author: Matt Wagner <matt.wagner> Date: Fri Feb 17 11:13:43 2012 -0500 BZ #794759 - Pool Family page no longer runs thousands of SQL queries This beats PoolFamily#statistics and Pool#statistics into shape, and also avoids invoking the method over and over again in the views. Resolves https://bugzilla.redhat.com/show_bug.cgi?id=794759
f405407 in aeolus-conductor-0.8.0-30
Started GET "/conductor/pool_families" for 10.11.8.128 at Wed Feb 22 04:36:03 -0500 2012 Processing by PoolFamiliesController#index as HTML User Load (0.2ms) SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1 BasePermissionObject Load (0.1ms) SELECT "base_permission_objects".* FROM "base_permission_objects" WHERE "base_permission_objects"."name" = 'general_permission_scope' LIMIT 1 CACHE (0.0ms) SELECT "base_permission_objects".* FROM "base_permission_objects" WHERE "base_permission_objects"."name" = 'general_permission_scope' LIMIT 1 Permission Load (0.7ms) SELECT * FROM (SELECT DISTINCT ON ("permissions".id) "permissions".id, permissions.id AS alias_0 FROM "permissions" LEFT OUTER JOIN "roles" ON "roles"."id" = "permissions"."role_id" LEFT OUTER JOIN "privileges" ON "privileges"."role_id" = "roles"."id" WHERE ("permissions".permission_object_id = 1 AND "permissions".permission_object_type = 'BasePermissionObject') AND (permissions.user_id=1 and privileges.target_type='PoolFamily' and privileges.action='view')) AS id_list ORDER BY id_list.alias_0 LIMIT 1 Permission Load (0.5ms) SELECT "permissions"."id" AS t0_r0, "permissions"."role_id" AS t0_r1, "permissions"."user_id" AS t0_r2, "permissions"."permission_object_id" AS t0_r3, "permissions"."permission_object_type" AS t0_r4, "permissions"."lock_version" AS t0_r5, "permissions"."created_at" AS t0_r6, "permissions"."updated_at" AS t0_r7, "roles"."id" AS t1_r0, "roles"."name" AS t1_r1, "roles"."scope" AS t1_r2, "roles"."lock_version" AS t1_r3, "roles"."created_at" AS t1_r4, "roles"."updated_at" AS t1_r5, "roles"."assign_to_owner" AS t1_r6, "privileges"."id" AS t2_r0, "privileges"."role_id" AS t2_r1, "privileges"."target_type" AS t2_r2, "privileges"."action" AS t2_r3, "privileges"."lock_version" AS t2_r4, "privileges"."created_at" AS t2_r5, "privileges"."updated_at" AS t2_r6 FROM "permissions" LEFT OUTER JOIN "roles" ON "roles"."id" = "permissions"."role_id" LEFT OUTER JOIN "privileges" ON "privileges"."role_id" = "roles"."id" WHERE "permissions"."id" IN (6) AND ("permissions".permission_object_id = 1 AND "permissions".permission_object_type = 'BasePermissionObject') AND (permissions.user_id=1 and privileges.target_type='PoolFamily' and privileges.action='view') ORDER BY permissions.id ASC Rendered layouts/_admin_nav.html.haml (4.9ms) CACHE (0.0ms) SELECT "base_permission_objects".* FROM "base_permission_objects" WHERE "base_permission_objects"."name" = 'general_permission_scope' LIMIT 1 CACHE (0.0ms) SELECT "base_permission_objects".* FROM "base_permission_objects" WHERE "base_permission_objects"."name" = 'general_permission_scope' LIMIT 1 Permission Load (0.9ms) SELECT * FROM (SELECT DISTINCT ON ("permissions".id) "permissions".id, permissions.id AS alias_0 FROM "permissions" LEFT OUTER JOIN "roles" ON "roles"."id" = "permissions"."role_id" LEFT OUTER JOIN "privileges" ON "privileges"."role_id" = "roles"."id" WHERE ("permissions".permission_object_id = 1 AND "permissions".permission_object_type = 'BasePermissionObject') AND (permissions.user_id=1 and privileges.target_type='PoolFamily' and privileges.action='create')) AS id_list ORDER BY id_list.alias_0 LIMIT 1 Permission Load (0.5ms) SELECT "permissions"."id" AS t0_r0, "permissions"."role_id" AS t0_r1, "permissions"."user_id" AS t0_r2, "permissions"."permission_object_id" AS t0_r3, "permissions"."permission_object_type" AS t0_r4, "permissions"."lock_version" AS t0_r5, "permissions"."created_at" AS t0_r6, "permissions"."updated_at" AS t0_r7, "roles"."id" AS t1_r0, "roles"."name" AS t1_r1, "roles"."scope" AS t1_r2, "roles"."lock_version" AS t1_r3, "roles"."created_at" AS t1_r4, "roles"."updated_at" AS t1_r5, "roles"."assign_to_owner" AS t1_r6, "privileges"."id" AS t2_r0, "privileges"."role_id" AS t2_r1, "privileges"."target_type" AS t2_r2, "privileges"."action" AS t2_r3, "privileges"."lock_version" AS t2_r4, "privileges"."created_at" AS t2_r5, "privileges"."updated_at" AS t2_r6 FROM "permissions" LEFT OUTER JOIN "roles" ON "roles"."id" = "permissions"."role_id" LEFT OUTER JOIN "privileges" ON "privileges"."role_id" = "roles"."id" WHERE "permissions"."id" IN (6) AND ("permissions".permission_object_id = 1 AND "permissions".permission_object_type = 'BasePermissionObject') AND (permissions.user_id=1 and privileges.target_type='PoolFamily' and privileges.action='create') ORDER BY permissions.id ASC PoolFamily Load (0.1ms) SELECT "pool_families".* FROM "pool_families" ORDER BY "pool_families".name asc Quota Load (0.1ms) SELECT "quotas".* FROM "quotas" WHERE "quotas"."id" = 1 LIMIT 1 Pool Load (0.1ms) SELECT "pools".* FROM "pools" WHERE ("pools".pool_family_id = 1) SQL (0.2ms) SELECT COUNT(*) FROM "deployments" WHERE ("deployments".pool_id = 1) SQL (0.2ms) SELECT COUNT(*) FROM "instances" WHERE ("instances".pool_id = 1) AND (state <> 'stopped') SQL (0.2ms) SELECT COUNT(*) FROM "instances" WHERE "instances"."state" IN ('new', 'pending') AND ("instances".pool_id = 1) SQL (0.1ms) SELECT COUNT(*) FROM "instances" WHERE "instances"."state" IN ('create_failed', 'error', 'vanished') AND ("instances".pool_id = 1) Quota Load (0.2ms) SELECT "quotas".* FROM "quotas" WHERE "quotas"."id" = 2 LIMIT 1 Instance Load (0.2ms) SELECT "instances".* FROM "instances" WHERE ("instances".pool_id = 1) ProviderAccount Load (0.1ms) SELECT "provider_accounts".* FROM "provider_accounts" WHERE "provider_accounts"."id" = 1 LIMIT 1 CACHE (0.0ms) SELECT COUNT(*) FROM "deployments" WHERE ("deployments".pool_id = 1) CACHE (0.0ms) SELECT COUNT(*) FROM "instances" WHERE ("instances".pool_id = 1) AND (state <> 'stopped') SQL (0.2ms) SELECT COUNT(*) FROM "instances" WHERE "instances"."state" IN ('running', 'shutting_down') AND ("instances".pool_id = 1) CACHE (0.0ms) SELECT COUNT(*) FROM "instances" WHERE "instances"."state" IN ('new', 'pending') AND ("instances".pool_id = 1) CACHE (0.0ms) SELECT COUNT(*) FROM "instances" WHERE "instances"."state" IN ('create_failed', 'error', 'vanished') AND ("instances".pool_id = 1) CACHE (0.0ms) SELECT COUNT(*) FROM "instances" WHERE "instances"."state" IN ('create_failed', 'error', 'vanished') AND ("instances".pool_id = 1) CACHE (0.0ms) SELECT COUNT(*) FROM "deployments" WHERE ("deployments".pool_id = 1) SQL (0.4ms) SELECT COUNT(*) FROM "catalogs" WHERE ("catalogs".pool_id = 1) Catalog Load (0.1ms) SELECT "catalogs".* FROM "catalogs" WHERE ("catalogs".pool_id = 1) LIMIT 1 CACHE (0.0ms) SELECT "catalogs".* FROM "catalogs" WHERE ("catalogs".pool_id = 1) LIMIT 1 Rendered pool_families/_list.html.haml (168.7ms) Rendered layouts/_admin_environments_tabs.html.haml (169.5ms) Rendered layouts/_masthead.html.haml (1.7ms) Rendered pool_families/index.html.haml within layouts/application (188.6ms) Completed 200 OK in 204ms (Views: 185.7ms | ActiveRecord: 20.1ms) Verified with rpm -qa|grep aeolus aeolus-conductor-daemons-0.8.0-33.el6.noarch aeolus-configure-2.5.0-14.el6.noarch rubygem-aeolus-image-0.3.0-8.el6.noarch aeolus-conductor-0.8.0-33.el6.noarch rubygem-aeolus-cli-0.3.0-9.el6.noarch aeolus-conductor-doc-0.8.0-33.el6.noarch aeolus-all-0.8.0-33.el6.noarch