Performance issue when showing OrganizationManagementPortlet when there are many users in
IDM DB
------------------------------------------------------------------------------------------------
Key: GTNPORTAL-1880
URL:
https://issues.jboss.org/browse/GTNPORTAL-1880
Project: GateIn Portal
Issue Type: Bug
Security Level: Public (Everyone can see)
Components: Identity integration, Performance
Affects Versions: 3.1.0-GA
Environment: EPP 5.1.0.GA, Picketlink 1.1.7.GA
Reporter: Marek Posolda
Assignee: Boleslaw Dawidowicz
Fix For: 3.2.0-GA
Attachments: threadDump1-loadOfAttributes.txt,
threadDump2-loadOfAttributeValues.txt
Time for load of page with organizationManagementPortlet depends on number of users. When
there are very big number of users in picketlink DB (100000 or more) then loading of page
can be few minutes.
Steps to reproduce:
1) Add many users into IDM database
2) Login as john
3) Go to page
http://localhost:8080/portal/private/classic/organization/management
When looking into the source of problem, I've found that Hibernate is using 2 very
expensive SQL select commands, which needs to load attributes and attribute values of ALL
users in DB inspite of the fact that there are only first 10 users shown in the table in
UI. Expensive select commands are:
1) Select for loading names of all attributes of all users ( This is called from
HibernateIdentityStoreImpl.getAttributes )
select attributes0_.IDENTITY_OBJECT_ID as IDENTITY2_4_1_, attributes0_.ATTRIBUTE_ID as
ATTRIBUTE1_1_, attributes0_.ATTRIBUTE_ID as ATTRIBUTE1_9_0_,
attributes0_.IDENTITY_OBJECT_ID as IDENTITY2_9_0_, attributes0_.NAME as NAME9_0_,
attributes0_.ATTRIBUTE_TYPE as ATTRIBUTE4_9_0_, attributes0_.BIN_VALUE_ID as BIN5_9_0_
from jbid_io_attr attributes0_ where attributes0_.IDENTITY_OBJECT_ID in (select this_.ID
from jbid_io this_ inner join jbid_io_type type2_ on this_.IDENTITY_TYPE=type2_.ID inner
join jbid_realm rm1_ on this_.REALM=rm1_.ID where rm1_.NAME='idm_realm' and
type2_.NAME='USER' and this_.NAME like '%' )
2) Select for loading text values of all users ( This is called from
AttributesManagerImpl.convertAttribute )
select textvalues0_.TEXT_ATTR_VALUE_ID as TEXT1_9_0_, textvalues0_.ATTR_VALUE as ATTR2_0_
from jbid_io_attr_text_values textvalues0_ where textvalues0_.TEXT_ATTR_VALUE_ID in
(select attributes0_.ATTRIBUTE_ID from jbid_io_attr attributes0_ where
attributes0_.IDENTITY_OBJECT_ID in (select this_.ID from jbid_io this_ inner join
jbid_io_type type2_ on this_.IDENTITY_TYPE=type2_.ID inner join jbid_realm rm1_ on
this_.REALM=rm1_.ID where rm1_.NAME='idm_realm' and type2_.NAME='USER' and
this_.NAME like '%' ))
Problem with both select commands is that they need to load attributes of all users in DB
even if only attributes for some very limited number of users are really needed. Big
problem seems to be storing of query results into Hibernate Query Cache. I am attaching
part of thread dumps where can be seen which parts are calling SQL commands.
I guess fix can be some change in Hibernate mapping files for HibernateIdentityObject and
HibernateIdentityObjectAttribute.
--
This message is automatically generated by JIRA.
For more information on JIRA, see:
http://www.atlassian.com/software/jira