Running profiling against Liberty Keystone (Fernet tokens, cache turned off) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ .. contents:: Parameters ~~~~~~~~~~ =========================== =========== Parameter name Value =========================== =========== OpenStack release Liberty Cache off Token type Fernet Environment characteristics Single node =========================== =========== Endpoint list request stats ~~~~~~~~~~~~~~~~~~~~~~~~~~~ **Control plane request overlook** +--------------------------------------------------------------+-----------+ | **Metric** | **Value** | +--------------------------------------------------------------+-----------+ | Total (*) Keystone DB queries count | 328 | +--------------------------------------------------------------+-----------+ | Total (*) Keystone DB queries time spent, ms | 2570 | +--------------------------------------------------------------+-----------+ | Infrastructure (SELECT 1) Keystone DB queries count | 164 | +--------------------------------------------------------------+-----------+ | Infrastructure (SELECT 1) Keystone DB queries time spent, ms | 1312 | +--------------------------------------------------------------+-----------+ | Real Keystone DB queries count | 164 | +--------------------------------------------------------------+-----------+ | Real Keystone DB queries time spent, ms | 1258 | +--------------------------------------------------------------+-----------+ | SELECT Keystone DB queries count | 164 | +--------------------------------------------------------------+-----------+ | SELECT Keystone DB queries time spent, ms | 1258 | +--------------------------------------------------------------+-----------+ .. note:: (*) OSprofiler uses specific SQLalchemy cursor events to track what's going on with the DB layer. This number includes non-real DB requests "SELECT 1", processed by SQLalchemy itself to make sure that connection to the database is still in place. **Keystone DB queries outliers** +------------------------------------------------------------------------------------------------------+--------------------+ | **DB query** | **Time spent, ms** | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT service.id AS service_id, service.type AS service_type, service.enabled AS service_enabled, | 10 | | service.extra AS service_extra | | | FROM service | | | WHERE service.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT project.id AS project_id, project.name AS project_name, project.domain_id AS | 8 | | project_domain_id, project.description AS project_description, project.enabled AS project_enabled, | | | project.extra AS project_extra, project.parent_id AS project_parent_id, project.is_domain AS | | | project_is_domain | | | FROM project | | | WHERE project.name = %s AND project.domain_id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT endpoint.id AS endpoint_id, endpoint.legacy_endpoint_id AS endpoint_legacy_endpoint_id, | 8 | | endpoint.interface AS endpoint_interface, endpoint.region_id AS endpoint_region_id, | | | endpoint.service_id AS endpoint_service_id, endpoint.url AS endpoint_url, endpoint.enabled AS | | | endpoint_enabled, endpoint.extra AS endpoint_extra | | | FROM endpoint | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT user.id AS user_id, user.name AS user_name, user.domain_id AS user_domain_id, user.password | 23 | | AS user_password, user.enabled AS user_enabled, user.extra AS user_extra, user.default_project_id AS | | | user_default_project_id | | | FROM user | | | WHERE user.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT role.id AS role_id, role.name AS role_name, role.extra AS role_extra | 15 | | FROM role | | | WHERE role.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT project.id AS project_id, project.name AS project_name, project.domain_id AS | 14 | | project_domain_id, project.description AS project_description, project.enabled AS project_enabled, | | | project.extra AS project_extra, project.parent_id AS project_parent_id, project.is_domain AS | | | project_is_domain | | | FROM project | | | WHERE project.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT domain.id AS domain_id, domain.name AS domain_name, domain.enabled AS domain_enabled, | 14 | | domain.extra AS domain_extra | | | FROM domain | | | WHERE domain.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT assignment.type AS assignment_type, assignment.actor_id AS assignment_actor_id, | 3 | | assignment.target_id AS assignment_target_id, assignment.role_id AS assignment_role_id, | | | assignment.inherited AS assignment_inherited | | | FROM assignment | | | WHERE assignment.type = %s AND assignment.actor_id = %s AND assignment.target_id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT `group`.id AS group_id, `group`.name AS group_name, `group`.domain_id AS group_domain_id, | 21 | | `group`.description AS group_description, `group`.extra AS group_extra | | | FROM `group` INNER JOIN user_group_membership ON `group`.id = user_group_membership.group_id | | | WHERE user_group_membership.user_id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT service_provider.id AS service_provider_id, service_provider.enabled AS | 16 | | service_provider_enabled, service_provider.description AS service_provider_description, | | | service_provider.auth_url AS service_provider_auth_url, service_provider.sp_url AS | | | service_provider_sp_url, service_provider.relay_state_prefix AS service_provider_relay_state_prefix | | | FROM service_provider | | | WHERE service_provider.enabled = true | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT service.id AS service_id, service.type AS service_type, service.enabled AS service_enabled, | 23 | | service.extra AS service_extra, endpoint_1.id AS endpoint_1_id, endpoint_1.legacy_endpoint_id AS | | | endpoint_1_legacy_endpoint_id, endpoint_1.interface AS endpoint_1_interface, endpoint_1.region_id AS | | | endpoint_1_region_id, endpoint_1.service_id AS endpoint_1_service_id, endpoint_1.url AS | | | endpoint_1_url, endpoint_1.enabled AS endpoint_1_enabled, endpoint_1.extra AS endpoint_1_extra | | | FROM service LEFT OUTER JOIN endpoint AS endpoint_1 ON service.id = endpoint_1.service_id | | | WHERE service.enabled = true | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT user.id AS user_id, user.name AS user_name, user.domain_id AS user_domain_id, user.password | 9 | | AS user_password, user.enabled AS user_enabled, user.extra AS user_extra, user.default_project_id AS | | | user_default_project_id | | | FROM user | | | WHERE user.name = %s AND user.domain_id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT endpoint.id AS endpoint_id, endpoint.legacy_endpoint_id AS endpoint_legacy_endpoint_id, | 24 | | endpoint.interface AS endpoint_interface, endpoint.region_id AS endpoint_region_id, | | | endpoint.service_id AS endpoint_service_id, endpoint.url AS endpoint_url, endpoint.enabled AS | | | endpoint_enabled, endpoint.extra AS endpoint_extra, service_1.id AS service_1_id, service_1.type AS | | | service_1_type, service_1.enabled AS service_1_enabled, service_1.extra AS service_1_extra | | | FROM endpoint LEFT OUTER JOIN service AS service_1 ON service_1.id = endpoint.service_id | | | WHERE endpoint.enabled = true | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ **Keystone DB queries with multi JOINs inside** +--------------+--------------------+ | **DB query** | **Time spent, ms** | +--------------+--------------------+ Server create request stats ~~~~~~~~~~~~~~~~~~~~~~~~~~~ **Control plane request overlook** +--------------------------------------------------------------+-----------+ | **Metric** | **Value** | +--------------------------------------------------------------+-----------+ | Total (*) Keystone DB queries count | 190 | +--------------------------------------------------------------+-----------+ | Total (*) Keystone DB queries time spent, ms | 1515 | +--------------------------------------------------------------+-----------+ | Infrastructure (SELECT 1) Keystone DB queries count | 95 | +--------------------------------------------------------------+-----------+ | Infrastructure (SELECT 1) Keystone DB queries time spent, ms | 714 | +--------------------------------------------------------------+-----------+ | Real Keystone DB queries count | 95 | +--------------------------------------------------------------+-----------+ | Real Keystone DB queries time spent, ms | 801 | +--------------------------------------------------------------+-----------+ | SELECT Keystone DB queries count | 95 | +--------------------------------------------------------------+-----------+ | SELECT Keystone DB queries time spent, ms | 801 | +--------------------------------------------------------------+-----------+ .. note:: (*) OSprofiler uses specific SQLalchemy cursor events to track what's going on with the DB layer. This number includes non-real DB requests "SELECT 1", processed by SQLalchemy itself to make sure that connection to the database is still in place. **Keystone DB queries outliers** +------------------------------------------------------------------------------------------------------+--------------------+ | **DB query** | **Time spent, ms** | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT service.id AS service_id, service.type AS service_type, service.enabled AS service_enabled, | 10 | | service.extra AS service_extra | | | FROM service | | | WHERE service.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT project.id AS project_id, project.name AS project_name, project.domain_id AS | 28 | | project_domain_id, project.description AS project_description, project.enabled AS project_enabled, | | | project.extra AS project_extra, project.parent_id AS project_parent_id, project.is_domain AS | | | project_is_domain | | | FROM project | | | WHERE project.name = %s AND project.domain_id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT endpoint.id AS endpoint_id, endpoint.legacy_endpoint_id AS endpoint_legacy_endpoint_id, | 8 | | endpoint.interface AS endpoint_interface, endpoint.region_id AS endpoint_region_id, | | | endpoint.service_id AS endpoint_service_id, endpoint.url AS endpoint_url, endpoint.enabled AS | | | endpoint_enabled, endpoint.extra AS endpoint_extra | | | FROM endpoint | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT user.id AS user_id, user.name AS user_name, user.domain_id AS user_domain_id, user.password | 23 | | AS user_password, user.enabled AS user_enabled, user.extra AS user_extra, user.default_project_id AS | | | user_default_project_id | | | FROM user | | | WHERE user.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT role.id AS role_id, role.name AS role_name, role.extra AS role_extra | 10 | | FROM role | | | WHERE role.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT project.id AS project_id, project.name AS project_name, project.domain_id AS | 12 | | project_domain_id, project.description AS project_description, project.enabled AS project_enabled, | | | project.extra AS project_extra, project.parent_id AS project_parent_id, project.is_domain AS | | | project_is_domain | | | FROM project | | | WHERE project.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT domain.id AS domain_id, domain.name AS domain_name, domain.enabled AS domain_enabled, | 52 | | domain.extra AS domain_extra | | | FROM domain | | | WHERE domain.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT assignment.type AS assignment_type, assignment.actor_id AS assignment_actor_id, | 17 | | assignment.target_id AS assignment_target_id, assignment.role_id AS assignment_role_id, | | | assignment.inherited AS assignment_inherited | | | FROM assignment | | | WHERE assignment.type = %s AND assignment.actor_id = %s AND assignment.target_id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT `group`.id AS group_id, `group`.name AS group_name, `group`.domain_id AS group_domain_id, | 26 | | `group`.description AS group_description, `group`.extra AS group_extra | | | FROM `group` INNER JOIN user_group_membership ON `group`.id = user_group_membership.group_id | | | WHERE user_group_membership.user_id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT service_provider.id AS service_provider_id, service_provider.enabled AS | 11 | | service_provider_enabled, service_provider.description AS service_provider_description, | | | service_provider.auth_url AS service_provider_auth_url, service_provider.sp_url AS | | | service_provider_sp_url, service_provider.relay_state_prefix AS service_provider_relay_state_prefix | | | FROM service_provider | | | WHERE service_provider.enabled = true | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT service.id AS service_id, service.type AS service_type, service.enabled AS service_enabled, | 16 | | service.extra AS service_extra, endpoint_1.id AS endpoint_1_id, endpoint_1.legacy_endpoint_id AS | | | endpoint_1_legacy_endpoint_id, endpoint_1.interface AS endpoint_1_interface, endpoint_1.region_id AS | | | endpoint_1_region_id, endpoint_1.service_id AS endpoint_1_service_id, endpoint_1.url AS | | | endpoint_1_url, endpoint_1.enabled AS endpoint_1_enabled, endpoint_1.extra AS endpoint_1_extra | | | FROM service LEFT OUTER JOIN endpoint AS endpoint_1 ON service.id = endpoint_1.service_id | | | WHERE service.enabled = true | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT user.id AS user_id, user.name AS user_name, user.domain_id AS user_domain_id, user.password | 9 | | AS user_password, user.enabled AS user_enabled, user.extra AS user_extra, user.default_project_id AS | | | user_default_project_id | | | FROM user | | | WHERE user.name = %s AND user.domain_id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT endpoint.id AS endpoint_id, endpoint.legacy_endpoint_id AS endpoint_legacy_endpoint_id, | 22 | | endpoint.interface AS endpoint_interface, endpoint.region_id AS endpoint_region_id, | | | endpoint.service_id AS endpoint_service_id, endpoint.url AS endpoint_url, endpoint.enabled AS | | | endpoint_enabled, endpoint.extra AS endpoint_extra, service_1.id AS service_1_id, service_1.type AS | | | service_1_type, service_1.enabled AS service_1_enabled, service_1.extra AS service_1_extra | | | FROM endpoint LEFT OUTER JOIN service AS service_1 ON service_1.id = endpoint.service_id | | | WHERE endpoint.enabled = true | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ **Keystone DB queries with multi JOINs inside** +--------------+--------------------+ | **DB query** | **Time spent, ms** | +--------------+--------------------+ Service list request stats ~~~~~~~~~~~~~~~~~~~~~~~~~~ **Control plane request overlook** +--------------------------------------------------------------+-----------+ | **Metric** | **Value** | +--------------------------------------------------------------+-----------+ | Total (*) Keystone DB queries count | 112 | +--------------------------------------------------------------+-----------+ | Total (*) Keystone DB queries time spent, ms | 605 | +--------------------------------------------------------------+-----------+ | Infrastructure (SELECT 1) Keystone DB queries count | 56 | +--------------------------------------------------------------+-----------+ | Infrastructure (SELECT 1) Keystone DB queries time spent, ms | 308 | +--------------------------------------------------------------+-----------+ | Real Keystone DB queries count | 56 | +--------------------------------------------------------------+-----------+ | Real Keystone DB queries time spent, ms | 297 | +--------------------------------------------------------------+-----------+ | SELECT Keystone DB queries count | 56 | +--------------------------------------------------------------+-----------+ | SELECT Keystone DB queries time spent, ms | 297 | +--------------------------------------------------------------+-----------+ .. note:: (*) OSprofiler uses specific SQLalchemy cursor events to track what's going on with the DB layer. This number includes non-real DB requests "SELECT 1", processed by SQLalchemy itself to make sure that connection to the database is still in place. **Keystone DB queries outliers** +------------------------------------------------------------------------------------------------------+--------------------+ | **DB query** | **Time spent, ms** | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT service.id AS service_id, service.type AS service_type, service.enabled AS service_enabled, | 10 | | service.extra AS service_extra | | | FROM service | | | WHERE service.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT project.id AS project_id, project.name AS project_name, project.domain_id AS | 3 | | project_domain_id, project.description AS project_description, project.enabled AS project_enabled, | | | project.extra AS project_extra, project.parent_id AS project_parent_id, project.is_domain AS | | | project_is_domain | | | FROM project | | | WHERE project.name = %s AND project.domain_id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT endpoint.id AS endpoint_id, endpoint.legacy_endpoint_id AS endpoint_legacy_endpoint_id, | 8 | | endpoint.interface AS endpoint_interface, endpoint.region_id AS endpoint_region_id, | | | endpoint.service_id AS endpoint_service_id, endpoint.url AS endpoint_url, endpoint.enabled AS | | | endpoint_enabled, endpoint.extra AS endpoint_extra | | | FROM endpoint | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT user.id AS user_id, user.name AS user_name, user.domain_id AS user_domain_id, user.password | 11 | | AS user_password, user.enabled AS user_enabled, user.extra AS user_extra, user.default_project_id AS | | | user_default_project_id | | | FROM user | | | WHERE user.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT role.id AS role_id, role.name AS role_name, role.extra AS role_extra | 9 | | FROM role | | | WHERE role.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT project.id AS project_id, project.name AS project_name, project.domain_id AS | 8 | | project_domain_id, project.description AS project_description, project.enabled AS project_enabled, | | | project.extra AS project_extra, project.parent_id AS project_parent_id, project.is_domain AS | | | project_is_domain | | | FROM project | | | WHERE project.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT domain.id AS domain_id, domain.name AS domain_name, domain.enabled AS domain_enabled, | 7 | | domain.extra AS domain_extra | | | FROM domain | | | WHERE domain.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT assignment.type AS assignment_type, assignment.actor_id AS assignment_actor_id, | 3 | | assignment.target_id AS assignment_target_id, assignment.role_id AS assignment_role_id, | | | assignment.inherited AS assignment_inherited | | | FROM assignment | | | WHERE assignment.type = %s AND assignment.actor_id = %s AND assignment.target_id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT `group`.id AS group_id, `group`.name AS group_name, `group`.domain_id AS group_domain_id, | 3 | | `group`.description AS group_description, `group`.extra AS group_extra | | | FROM `group` INNER JOIN user_group_membership ON `group`.id = user_group_membership.group_id | | | WHERE user_group_membership.user_id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT service_provider.id AS service_provider_id, service_provider.enabled AS | 7 | | service_provider_enabled, service_provider.description AS service_provider_description, | | | service_provider.auth_url AS service_provider_auth_url, service_provider.sp_url AS | | | service_provider_sp_url, service_provider.relay_state_prefix AS service_provider_relay_state_prefix | | | FROM service_provider | | | WHERE service_provider.enabled = true | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT service.id AS service_id, service.type AS service_type, service.enabled AS service_enabled, | 14 | | service.extra AS service_extra, endpoint_1.id AS endpoint_1_id, endpoint_1.legacy_endpoint_id AS | | | endpoint_1_legacy_endpoint_id, endpoint_1.interface AS endpoint_1_interface, endpoint_1.region_id AS | | | endpoint_1_region_id, endpoint_1.service_id AS endpoint_1_service_id, endpoint_1.url AS | | | endpoint_1_url, endpoint_1.enabled AS endpoint_1_enabled, endpoint_1.extra AS endpoint_1_extra | | | FROM service LEFT OUTER JOIN endpoint AS endpoint_1 ON service.id = endpoint_1.service_id | | | WHERE service.enabled = true | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT user.id AS user_id, user.name AS user_name, user.domain_id AS user_domain_id, user.password | 7 | | AS user_password, user.enabled AS user_enabled, user.extra AS user_extra, user.default_project_id AS | | | user_default_project_id | | | FROM user | | | WHERE user.name = %s AND user.domain_id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT endpoint.id AS endpoint_id, endpoint.legacy_endpoint_id AS endpoint_legacy_endpoint_id, | 10 | | endpoint.interface AS endpoint_interface, endpoint.region_id AS endpoint_region_id, | | | endpoint.service_id AS endpoint_service_id, endpoint.url AS endpoint_url, endpoint.enabled AS | | | endpoint_enabled, endpoint.extra AS endpoint_extra, service_1.id AS service_1_id, service_1.type AS | | | service_1_type, service_1.enabled AS service_1_enabled, service_1.extra AS service_1_extra | | | FROM endpoint LEFT OUTER JOIN service AS service_1 ON service_1.id = endpoint.service_id | | | WHERE endpoint.enabled = true | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ **Keystone DB queries with multi JOINs inside** +--------------+--------------------+ | **DB query** | **Time spent, ms** | +--------------+--------------------+ Token issue request stats ~~~~~~~~~~~~~~~~~~~~~~~~~ **Control plane request overlook** +--------------------------------------------------------------+-----------+ | **Metric** | **Value** | +--------------------------------------------------------------+-----------+ | Total (*) Keystone DB queries count | 44 | +--------------------------------------------------------------+-----------+ | Total (*) Keystone DB queries time spent, ms | 316 | +--------------------------------------------------------------+-----------+ | Infrastructure (SELECT 1) Keystone DB queries count | 22 | +--------------------------------------------------------------+-----------+ | Infrastructure (SELECT 1) Keystone DB queries time spent, ms | 183 | +--------------------------------------------------------------+-----------+ | Real Keystone DB queries count | 22 | +--------------------------------------------------------------+-----------+ | Real Keystone DB queries time spent, ms | 133 | +--------------------------------------------------------------+-----------+ | SELECT Keystone DB queries count | 22 | +--------------------------------------------------------------+-----------+ | SELECT Keystone DB queries time spent, ms | 133 | +--------------------------------------------------------------+-----------+ .. note:: (*) OSprofiler uses specific SQLalchemy cursor events to track what's going on with the DB layer. This number includes non-real DB requests "SELECT 1", processed by SQLalchemy itself to make sure that connection to the database is still in place. **Keystone DB queries outliers** +------------------------------------------------------------------------------------------------------+--------------------+ | **DB query** | **Time spent, ms** | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT service.id AS service_id, service.type AS service_type, service.enabled AS service_enabled, | 10 | | service.extra AS service_extra | | | FROM service | | | WHERE service.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT project.id AS project_id, project.name AS project_name, project.domain_id AS | 3 | | project_domain_id, project.description AS project_description, project.enabled AS project_enabled, | | | project.extra AS project_extra, project.parent_id AS project_parent_id, project.is_domain AS | | | project_is_domain | | | FROM project | | | WHERE project.name = %s AND project.domain_id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT endpoint.id AS endpoint_id, endpoint.legacy_endpoint_id AS endpoint_legacy_endpoint_id, | 8 | | endpoint.interface AS endpoint_interface, endpoint.region_id AS endpoint_region_id, | | | endpoint.service_id AS endpoint_service_id, endpoint.url AS endpoint_url, endpoint.enabled AS | | | endpoint_enabled, endpoint.extra AS endpoint_extra | | | FROM endpoint | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT user.id AS user_id, user.name AS user_name, user.domain_id AS user_domain_id, user.password | 9 | | AS user_password, user.enabled AS user_enabled, user.extra AS user_extra, user.default_project_id AS | | | user_default_project_id | | | FROM user | | | WHERE user.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT role.id AS role_id, role.name AS role_name, role.extra AS role_extra | 9 | | FROM role | | | WHERE role.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT project.id AS project_id, project.name AS project_name, project.domain_id AS | 8 | | project_domain_id, project.description AS project_description, project.enabled AS project_enabled, | | | project.extra AS project_extra, project.parent_id AS project_parent_id, project.is_domain AS | | | project_is_domain | | | FROM project | | | WHERE project.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT domain.id AS domain_id, domain.name AS domain_name, domain.enabled AS domain_enabled, | 3 | | domain.extra AS domain_extra | | | FROM domain | | | WHERE domain.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT assignment.type AS assignment_type, assignment.actor_id AS assignment_actor_id, | 10 | | assignment.target_id AS assignment_target_id, assignment.role_id AS assignment_role_id, | | | assignment.inherited AS assignment_inherited | | | FROM assignment | | | WHERE assignment.type = %s AND assignment.actor_id = %s AND assignment.target_id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT `group`.id AS group_id, `group`.name AS group_name, `group`.domain_id AS group_domain_id, | 3 | | `group`.description AS group_description, `group`.extra AS group_extra | | | FROM `group` INNER JOIN user_group_membership ON `group`.id = user_group_membership.group_id | | | WHERE user_group_membership.user_id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT service_provider.id AS service_provider_id, service_provider.enabled AS | 3 | | service_provider_enabled, service_provider.description AS service_provider_description, | | | service_provider.auth_url AS service_provider_auth_url, service_provider.sp_url AS | | | service_provider_sp_url, service_provider.relay_state_prefix AS service_provider_relay_state_prefix | | | FROM service_provider | | | WHERE service_provider.enabled = true | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT service.id AS service_id, service.type AS service_type, service.enabled AS service_enabled, | 14 | | service.extra AS service_extra, endpoint_1.id AS endpoint_1_id, endpoint_1.legacy_endpoint_id AS | | | endpoint_1_legacy_endpoint_id, endpoint_1.interface AS endpoint_1_interface, endpoint_1.region_id AS | | | endpoint_1_region_id, endpoint_1.service_id AS endpoint_1_service_id, endpoint_1.url AS | | | endpoint_1_url, endpoint_1.enabled AS endpoint_1_enabled, endpoint_1.extra AS endpoint_1_extra | | | FROM service LEFT OUTER JOIN endpoint AS endpoint_1 ON service.id = endpoint_1.service_id | | | WHERE service.enabled = true | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT user.id AS user_id, user.name AS user_name, user.domain_id AS user_domain_id, user.password | 8 | | AS user_password, user.enabled AS user_enabled, user.extra AS user_extra, user.default_project_id AS | | | user_default_project_id | | | FROM user | | | WHERE user.name = %s AND user.domain_id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT endpoint.id AS endpoint_id, endpoint.legacy_endpoint_id AS endpoint_legacy_endpoint_id, | 14 | | endpoint.interface AS endpoint_interface, endpoint.region_id AS endpoint_region_id, | | | endpoint.service_id AS endpoint_service_id, endpoint.url AS endpoint_url, endpoint.enabled AS | | | endpoint_enabled, endpoint.extra AS endpoint_extra, service_1.id AS service_1_id, service_1.type AS | | | service_1_type, service_1.enabled AS service_1_enabled, service_1.extra AS service_1_extra | | | FROM endpoint LEFT OUTER JOIN service AS service_1 ON service_1.id = endpoint.service_id | | | WHERE endpoint.enabled = true | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ **Keystone DB queries with multi JOINs inside** +--------------+--------------------+ | **DB query** | **Time spent, ms** | +--------------+--------------------+ User list request stats ~~~~~~~~~~~~~~~~~~~~~~~ **Control plane request overlook** +--------------------------------------------------------------+-----------+ | **Metric** | **Value** | +--------------------------------------------------------------+-----------+ | Total (*) Keystone DB queries count | 112 | +--------------------------------------------------------------+-----------+ | Total (*) Keystone DB queries time spent, ms | 675 | +--------------------------------------------------------------+-----------+ | Infrastructure (SELECT 1) Keystone DB queries count | 56 | +--------------------------------------------------------------+-----------+ | Infrastructure (SELECT 1) Keystone DB queries time spent, ms | 297 | +--------------------------------------------------------------+-----------+ | Real Keystone DB queries count | 56 | +--------------------------------------------------------------+-----------+ | Real Keystone DB queries time spent, ms | 378 | +--------------------------------------------------------------+-----------+ | SELECT Keystone DB queries count | 56 | +--------------------------------------------------------------+-----------+ | SELECT Keystone DB queries time spent, ms | 378 | +--------------------------------------------------------------+-----------+ .. note:: (*) OSprofiler uses specific SQLalchemy cursor events to track what's going on with the DB layer. This number includes non-real DB requests "SELECT 1", processed by SQLalchemy itself to make sure that connection to the database is still in place. **Keystone DB queries outliers** +------------------------------------------------------------------------------------------------------+--------------------+ | **DB query** | **Time spent, ms** | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT service.id AS service_id, service.type AS service_type, service.enabled AS service_enabled, | 10 | | service.extra AS service_extra | | | FROM service | | | WHERE service.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT project.id AS project_id, project.name AS project_name, project.domain_id AS | 8 | | project_domain_id, project.description AS project_description, project.enabled AS project_enabled, | | | project.extra AS project_extra, project.parent_id AS project_parent_id, project.is_domain AS | | | project_is_domain | | | FROM project | | | WHERE project.name = %s AND project.domain_id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT user.id AS user_id, user.name AS user_name, user.domain_id AS user_domain_id, user.password | 9 | | AS user_password, user.enabled AS user_enabled, user.extra AS user_extra, user.default_project_id AS | | | user_default_project_id | | | FROM user | | | WHERE user.domain_id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT endpoint.id AS endpoint_id, endpoint.legacy_endpoint_id AS endpoint_legacy_endpoint_id, | 8 | | endpoint.interface AS endpoint_interface, endpoint.region_id AS endpoint_region_id, | | | endpoint.service_id AS endpoint_service_id, endpoint.url AS endpoint_url, endpoint.enabled AS | | | endpoint_enabled, endpoint.extra AS endpoint_extra | | | FROM endpoint | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT user.id AS user_id, user.name AS user_name, user.domain_id AS user_domain_id, user.password | 14 | | AS user_password, user.enabled AS user_enabled, user.extra AS user_extra, user.default_project_id AS | | | user_default_project_id | | | FROM user | | | WHERE user.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT role.id AS role_id, role.name AS role_name, role.extra AS role_extra | 4 | | FROM role | | | WHERE role.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT project.id AS project_id, project.name AS project_name, project.domain_id AS | 12 | | project_domain_id, project.description AS project_description, project.enabled AS project_enabled, | | | project.extra AS project_extra, project.parent_id AS project_parent_id, project.is_domain AS | | | project_is_domain | | | FROM project | | | WHERE project.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT domain.id AS domain_id, domain.name AS domain_name, domain.enabled AS domain_enabled, | 12 | | domain.extra AS domain_extra | | | FROM domain | | | WHERE domain.id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT assignment.type AS assignment_type, assignment.actor_id AS assignment_actor_id, | 9 | | assignment.target_id AS assignment_target_id, assignment.role_id AS assignment_role_id, | | | assignment.inherited AS assignment_inherited | | | FROM assignment | | | WHERE assignment.type = %s AND assignment.actor_id = %s AND assignment.target_id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT `group`.id AS group_id, `group`.name AS group_name, `group`.domain_id AS group_domain_id, | 8 | | `group`.description AS group_description, `group`.extra AS group_extra | | | FROM `group` INNER JOIN user_group_membership ON `group`.id = user_group_membership.group_id | | | WHERE user_group_membership.user_id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT service_provider.id AS service_provider_id, service_provider.enabled AS | 4 | | service_provider_enabled, service_provider.description AS service_provider_description, | | | service_provider.auth_url AS service_provider_auth_url, service_provider.sp_url AS | | | service_provider_sp_url, service_provider.relay_state_prefix AS service_provider_relay_state_prefix | | | FROM service_provider | | | WHERE service_provider.enabled = true | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT service.id AS service_id, service.type AS service_type, service.enabled AS service_enabled, | 12 | | service.extra AS service_extra, endpoint_1.id AS endpoint_1_id, endpoint_1.legacy_endpoint_id AS | | | endpoint_1_legacy_endpoint_id, endpoint_1.interface AS endpoint_1_interface, endpoint_1.region_id AS | | | endpoint_1_region_id, endpoint_1.service_id AS endpoint_1_service_id, endpoint_1.url AS | | | endpoint_1_url, endpoint_1.enabled AS endpoint_1_enabled, endpoint_1.extra AS endpoint_1_extra | | | FROM service LEFT OUTER JOIN endpoint AS endpoint_1 ON service.id = endpoint_1.service_id | | | WHERE service.enabled = true | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT user.id AS user_id, user.name AS user_name, user.domain_id AS user_domain_id, user.password | 8 | | AS user_password, user.enabled AS user_enabled, user.extra AS user_extra, user.default_project_id AS | | | user_default_project_id | | | FROM user | | | WHERE user.name = %s AND user.domain_id = %s | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ | SELECT endpoint.id AS endpoint_id, endpoint.legacy_endpoint_id AS endpoint_legacy_endpoint_id, | 13 | | endpoint.interface AS endpoint_interface, endpoint.region_id AS endpoint_region_id, | | | endpoint.service_id AS endpoint_service_id, endpoint.url AS endpoint_url, endpoint.enabled AS | | | endpoint_enabled, endpoint.extra AS endpoint_extra, service_1.id AS service_1_id, service_1.type AS | | | service_1_type, service_1.enabled AS service_1_enabled, service_1.extra AS service_1_extra | | | FROM endpoint LEFT OUTER JOIN service AS service_1 ON service_1.id = endpoint.service_id | | | WHERE endpoint.enabled = true | | | | | | | | | +------------------------------------------------------------------------------------------------------+--------------------+ **Keystone DB queries with multi JOINs inside** +--------------+--------------------+ | **DB query** | **Time spent, ms** | +--------------+--------------------+