How I solved the Slow Login Problem with Liferay

We had been using the Liferay Portal Server for our CMS and UserManagement on a Tomcat server. When the new systems were deployed initially there was no problem initially for the 1st 3 months or so after which we started slow trickling comments from users that the logins were getting slower and slower with each passing month.

The initial logging time was < 5 sec (which is slow but still quite acceptable). Then over period of months – login times started deteriorating to 30 sec and in some cases for some users to 90 sec – which was outrageous.

Liferay is known to be slow (see JIRA ticket LEP-2182, http://support.liferay.com/browse/LEP-2182 )

Especially when using Microsoft SQL Server (see  https://www.liferay.com/web/guest/community/forums/message_boards/message/71332)

And indeed we were using SQLServer as the repository for Liferay.

On further investigation I found out that – it was the Liferay permissions checker algorithm that was causing the slowdown in Login response time.

I found that over a period of time as the resources (which in our case is Analytic Charts that users create over a period of time ) increased – Liferay’s permissions checking Algorithm did not scale up well to handle the response time for checking if the User who is trying to login had permissions to Read/Write/Clone/Migrate all the resource in the system.

So the crux of the slowdown was the – inability of Liferay to scale – checking of different types of permissions across numerous resources. At one point in time we had more than 100,000 resources ( charts in our case ) and Liferay became slower and slower in handling the necessity to check across all the resources in the system.

Solution  :

I found that internally Liferay was using Hibernate’s auto generated SQL ( HQL ) to do the queries to search for the resources the User would be able to Modify/Clone/Migrate.

I found out the ER Diagram of the Liferay DB Tables and how they are interrelated and formulated my own SQL query ( which is given below ) to get back the resources for a given User who belongs to a given Customer. Then I intercepted the Hibernate Calls made from Liferay APIs and injected our own SQL to get back the results using the optimized SQL. The SQL query was simple Union across multiple Tables to check how a user can get permissions – based on Group Permission/Roles. The whole thing now works in < 2 sec response time.

Modified SQL Query to get the Permissions for a given user for a given company

 select distinct r.primKey r, r.companyid c
from user_ u , role_ ro, Users_Roles ur, resource_ r
where ro.name='Administrator' and
r.name like 'com.leapfrogrx.gpharma.service.viewerservice.common.ChartSet' and
r.scope = 'individual' and
r.typeid = 'class' and
ur.userId=u.userId and
ur.roleId = ro.roleId and
r.companyId = u.companyId
and u.userId = 'cbergh.agn'
and  r.companyid = 'ALLERGAN'
union
-- Get All Resources that the User can access directly : just by itself
select distinct  r.primKey r, r.companyid c
from resource_ r, permission_ p, users_permissions up, user_ u
where
r.name like 'com.leapfrogrx.gpharma.service.viewerservice.common.ChartSet' and
r.scope = 'individual' and
r.typeid = 'class' and
r.resourceId = p.resourceId and
r.CompanyId = p.CompanyId and
p.permissionID = up.permissionId and
up.userId = u.userId and u.userId = 'cbergh.agn'
and  r.companyid = 'ALLERGAN'
union
-- Get All Resources that the User can access from the group
select distinct  r.primKey r , r.companyid c
from resource_ r, permission_ p, groups_permissions gp, group_ g, users_groups ug
where
r.name like 'com.leapfrogrx.gpharma.service.viewerservice.common.ChartSet' and
r.scope = 'individual' and
r.typeid = 'class' and
r.resourceId = p.resourceId and
p.permissionID = gp.permissionId and
gp.groupId = g.groupId and
g.groupId = ug.groupId and ug.userId = 'cbergh.agn'
and  r.companyid = 'ALLERGAN'
union
-- Get all the resources the user can access through the Roles
select distinct r.primKey r, r.companyid c
from resource_ r, permission_ p, Roles_Permissions rp, Role_ ro, Users_Roles ur
where
r.name like 'com.leapfrogrx.gpharma.service.viewerservice.common.ChartSet' and
r.scope = 'individual' and
r.typeid = 'class' and
r.resourceId = p.resourceId and
p.permissionID = rp.permissionId and
rp.roleId = ro.roleId and
ro.roleId = ur.roleId and ur.userId = 'cbergh.agn'
and  r.companyid = 'ALLERGAN'
union
-- Get all the resources the user can access through the group Roles
select distinct r.primKey r, r.companyid c
from resource_ r, permission_ p, Roles_Permissions rp, Role_ ro, groups_roles gr, Users_groups ug, group_ g
where
r.name like 'com.leapfrogrx.gpharma.service.viewerservice.common.ChartSet' and
r.scope = 'individual' and
r.typeid = 'class' and
r.resourceId = p.resourceId and
p.permissionID = rp.permissionId and
rp.roleId = ro.roleId and
ro.roleId = gr.roleId and
gr.groupid = g.groupId and
g.groupId = ug.groupId and
ug.userId = 'cbergh.agn'
and  r.companyid = 'ALLERGAN'
union
select distinct primKey, null, null, null from resource_ r where resourceID in
(
select resourceID from permission_ where permissionId in
(
select permissionID from groups_permissions where groupId in
(
select groupId from group_ where className like 'com.liferay.portal.model.UserGroup' and companyId = 'ALLERGAN'
and classPK in ( select u1.userGroupId from userGroup u1, Users_UserGroups u2  where companyId =  'ALLERGAN' and
u1.usergroupId = u2.userGroupId and u2.userId = 'cbergh.agn'
)
)
)
)
and
r.companyid = 'ALLERGAN'
and
r.name like 'com.leapfrogrx.gpharma.service.viewerservice.common.ChartSet'
and r.scope = 'individual'
r.typeid = 'class'
Advertisements

4 responses to “How I solved the Slow Login Problem with Liferay

  1. Excellent solution and great post! I’m getting into liferay and this could prove to be very useful. Thank you.

  2. We’ve brought this to liferay’s attention… maybe they can make it a little more efficient… But we are having the same issue now…

  3. Where did you place the query? We are having the same issue with login.

    Thanks

    • We wrote our own query and call it using JDBC and execute it and get the results and – this is all wrapped up in a method – which we call when we need it in the Application – Hope this helps

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s