Details
-
Bug
-
Resolution: Fixed
-
High
Description
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