query group permission sql make DB server CPU used 100%

Issue #15432 closed
zhou feng
created an issue

hello! I have a problem between use bitbucket server . I update bitbucket server from v4.11 to Atlassian Bitbucket v5.6.1. after start server about 10 minutes later , the server become very slow. I, ask DBA Analysis of reasons. get the message as follows:

bitbucket make the db server CPU used 100%, because of the flower sql:

select internalsc0_.id as id1_45_, internalsc0_.group_name as group_na2_45_, internalsc0_.perm_id as perm_id3_45_, internalsc0_.user_id as user_id4_45_, internalsc0_.project_id as project_1_69_, internalsc0_.repo_id as repo_id1_75_, internalsc0_.clazz_ as clazz_ from (select id, group_name, perm_id, user_id, null as project_id, null as repo_id, 1 as clazz_ from sta_global_permission union select id, group_name, perm_id, user_id, project_id, null as repo_id, 2 as clazz_ from sta_project_permission union select id, group_name, perm_id, user_id, null as project_id, repo_id, 3 as clazz_ from sta_repo_permission) internalsc0_ cross join sta_permission_type internalpe1_ where internalsc0_.perm_id = internalpe1_.perm_id and (internalsc0_.user_id is null) and internalsc0_.group_name = 'sgs-gw-core-api-dev' order by id asc limit 1001

DBA Suggestion optimization the sql like:

SELECT internalsc0_.id AS id1_45_, internalsc0_.group_name AS group_na2_45_, internalsc0_.perm_id AS perm_id3_45_, internalsc0_.user_id AS user_id4_45_, internalsc0_.project_id AS project_1_69_, internalsc0_.repo_id AS repo_id1_75_, internalsc0_.clazz_ AS clazz_ FROM ( SELECT id, group_name, perm_id, user_id, NULL AS project_id, NULL AS repo_id, 1 AS clazz_ FROM sta_global_permission WHERE group_name = 'sgs-gw-core-api-dev' UNION SELECT id, group_name, perm_id, user_id, project_id, NULL AS repo_id, 2 AS clazz_ FROM sta_project_permission WHERE group_name = 'sgs-gw-core-api-dev' UNION SELECT id, group_name, perm_id, user_id, NULL AS project_id, repo_id, 3 AS clazz_ FROM sta_repo_permission WHERE group_name = 'sgs-gw-core-api-dev' ) internalsc0_ CROSS JOIN sta_permission_type internalpe1_ WHERE internalsc0_.perm_id = internalpe1_.perm_id AND (internalsc0_.user_id IS NULL) ORDER BY id ASC LIMIT 1001

2017-12-26_214138.png

Comments (6)

  1. Log in to comment