How to check the depth of group nesting in Jira Server and Data Center
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Too many levels of group nesting in your Jira Server and Data Center instance can cause instance instability — including outages and noticeable performance drops under heavy load — and increase the time for directory synchronization and user authentication.
If you’re experiencing performance issues or want to make sure that you don’t exceed the recommended guardrails, check how deeply groups are nested in your system.
Solution
To check the depth of group nesting in your system, run the appropriate query against your database:
Run the following query if you're using PostgreSQL or MySQL:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
with recursive group_hierarchy as (
select
child_id,
parent_id,
parent_name,
1 as depth_of_nested_groups
from
cwd_membership
where
cwd_membership.membership_type = 'GROUP_GROUP'
union all
select
cwd_membership.child_id,
cwd_membership.parent_id,
cwd_membership.parent_name,
depth_of_nested_groups + 1
from
cwd_membership
join group_hierarchy on
group_hierarchy.parent_id = cwd_membership.child_id
where
cwd_membership.membership_type = 'GROUP_GROUP'
)
select
*
from
group_hierarchy
Run the following query if you're using Microsoft SQL Server:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
with group_hierarchy as (
select
child_id,
parent_id,
parent_name,
1 as depth_of_nested_groups
from
cwd_membership
where
cwd_membership.membership_type = 'GROUP_GROUP'
union all
select
cwd_membership.child_id,
cwd_membership.parent_id,
cwd_membership.parent_name,
depth_of_nested_groups + 1
from
cwd_membership
join group_hierarchy on
group_hierarchy.parent_id = cwd_membership.child_id
where
cwd_membership.membership_type = 'GROUP_GROUP'
)
select
*
from
group_hierarchyRun the following query if you're using Oracle Database:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
with group_hierarchy(child_id, parent_id, parent_name, depth) as (
select
child_id,
parent_id,
parent_name,
1 as depth
from
cwd_membership
where
cwd_membership.membership_type = 'GROUP_GROUP'
union all
select
cwd_membership.child_id,
cwd_membership.parent_id,
cwd_membership.parent_name,
depth + 1
from
cwd_membership
join group_hierarchy on
group_hierarchy.parent_id = cwd_membership.child_id
where
cwd_membership.membership_type = 'GROUP_GROUP'
)
select
*
from
group_hierarchyWas this helpful?