Uploaded image for project: 'PUBLIC - Liferay Portal Community Edition'
  1. PUBLIC - Liferay Portal Community Edition
  2. LPS-64261

Performance problem because of missing index in DB

    Details

      Description

      During permission checking, the user's organization, usergroup will be joined to the user's group. This join is handled in portal.xml and affects GroupFinder, OrganizationFinder, RoleFinder, UserFinder, UserGroupFinder performance.

      The issue is related to the Group table, as Group_ is joined to Users_UserGroups, User_, Users_Orgs, Organization tables, where in the mapping tables there's only two columns with ids, hence no compound index on classPK field in Group_ table can be used there. Because of this, during the joins, DB does a full table scan what kills performance.

      The issue can be observed with 100-200.000 users in the DB and with load test with 40 concurrent logged in users. With this amount of concurrent users MySQL started to report Group_.classPK based joined queries in the slowquery log and the response time went up to several seconds or even a minute.

      This issue can be fixed by manually add an index on Group_.classPK, but it should be done automatically during table creation.

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                1 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Days since last comment:
                  2 years, 51 weeks, 4 days ago

                  Packages

                  Version Package
                  6.2.X EE
                  7.0.1 CE GA2