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

              sophia.zhang Sophia Zhang
              vilmos.papp Vilmos Papp
              Kiyoshi Lee Kiyoshi Lee
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                6 years, 11 weeks, 5 days ago

                Packages

                  Version Package
                  6.2.X EE
                  7.0.1 CE GA2