-
Bug
-
Resolution: Unresolved
-
Low (View bug fix roadmap)
-
None
-
7.2.7
-
7.02
-
4
-
Severity 3 - Minor
-
0
-
This is a clone of a Confluence bug that has proven to affect JIRA.
Summary
Everywhere in Confluence DB tables, the creator is now set as the user hash. This username hash is stored in the table user_mapping which is made of three columns (user_key, username, lower_username). The last column has a unique index.
Now in cwd_user, there is no index on column lower_user_name. The only index there is on lower_user_name is composed with the other column directory_id.
So this means that for every action where Confluence looks up for creator information, it makes a full table scan on cwd_user. (Unless the caching in Confluence helps to avoid this ? )
Environment
- Confluence 5.9.4
- PostgreSQL 9.3.10
Steps to Reproduce
There are many actions in Confluence which could cause this to occur. One such query which has been found is the below one:
select internalgr2_.id as id, internalgr2_.group_name as group_name, internalgr2_.lower_group_name as lower_gr3_, internalgr2_.active as active, internalgr2_.local as local, internalgr2_.created_date as created_6_, internalgr2_.updated_date as updated_7_, internalgr2_.description as descript8_, internalgr2_.group_type as group_type, internalgr2_.directory_id as directo10_ from cwd_membership hibernatem0_, cwd_user internalus1_, cwd_group internalgr2_ where hibernatem0_.parent_id=internalgr2_.id and ((internalus1_.lower_user_name=:v2 and hibernatem0_.child_user_id=internalus1_.id) and(internalgr2_.directory_id=:v1 and hibernatem0_.parent_id=internalgr2_.id)) order by internalgr2_.lower_group_name;
An explain plan on that shows a table scan is run on the lower_user_name field in cwd_user, if there is enough data in the tables being queried (in a fresh install there is no table scan, probably due to optimization from analyze statistics).
Expected Results
Database query returns quickly without performance impact, by using an index lookup.
Actual Results
Database query returns slowly, and causes a performance hit, as it's using a record lookup (table scan).
Workaround
Manually add an index on lower_user_name in the cwd_user table, for example:
create index idx_user_lower_user_name on cwd_user (lower_user_name);
NOTE: If any future fix adds a new index, you may need to manually remove this index before that.
- is cloned from
-
-
- Closed
-
[JRASERVER-65046] cwd_user table missing a index
mdoar2
in the source you can see that rule 7.6.10.
PostgreSQL schema
CREATE INDEX idx_display_name ON cwd_user USING btree (lower_display_name); CREATE INDEX idx_email_address ON cwd_user USING btree (lower_email_address); CREATE INDEX idx_first_name ON cwd_user USING btree (lower_first_name); CREATE INDEX idx_last_name ON cwd_user USING btree (lower_last_name); CREATE INDEX uk_user_externalid_dir_id ON cwd_user USING btree (external_id, directory_id); CREATE UNIQUE INDEX uk_user_name_dir_id ON cwd_user USING btree (lower_user_name, directory_id);
the same as MySQL
PRIMARY KEY (`ID`), UNIQUE KEY `uk_user_name_dir_id` (`lower_user_name`,`directory_id`), KEY `uk_user_externalid_dir_id` (`EXTERNAL_ID`,`directory_id`), KEY `idx_first_name` (`lower_first_name`), KEY `idx_last_name` (`lower_last_name`), KEY `idx_display_name` (`lower_display_name`), KEY `idx_email_address` (`lower_email_address`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
unfortunately, some queries go through cwd_user -> lower_user_name instead of app_user table -> lower_user_name
In entitymodel.xml in Jira 8.0 I see
<index unique="true" name="uk_user_name_dir_id">
<index-field name="lowerUserName"/>
<index-field name="directoryId"/>
</index>
so this issue may not apply to newer Jira versions?
Finally, I have evaluated that index