Ask Questions and Find Answers

I´m experiencing the same thing. I upgraded from liferay 7.4.3.43 to 7.4.3.112. Loading the control-panel is slow the first time but as long as it is in cache its fast as it should be.

I´ve developed a good amount of portlets for our need, and under the older version loading times were between 150ms to 450ms for a page, no matter if it had been loaded lately or not.

Now with version 7.4.3.112 the loading times are usually 1500 - 3000ms and in bad cases even 5900ms when loading a page with a couple of instances of a portlet. The page is then put in some kind of cache, which means that the page loading times are normal after that ie 150-450ms, but they are cached only for like minutes.

I´ve been working for over a week trying to figure out what is wrong. I´ve used glowroot that recognizes that there are slow traces, and that it is jdbc queries primarily that are causing these. There are database queries that take 2800ms which have never before done this. Im feeling quite helpless at the moment as this seems to be impossible for me to debug.

If you find any solution to this problem, please do share!

I´ve found the solution to my problems. Do backup everything before trying these, for me it did not cause any trouble, but one can never be sure.

I created new database table indexes for slow sql queries to speed up everything: 

CREATE INDEX idx_portletPreferencesId ON PortletPreferenceValue (portletPreferencesId); 
CREATE INDEX idx_portlet_ctCollection ON PortletPreferenceValue (portletPreferencesId, ctCollectionId); 
CREATE INDEX idx_company_name_scope ON ResourcePermission (companyId, name, scope); 
CREATE INDEX idx_primKey_roleId ON ResourcePermission (primKey, roleId);
CREATE INDEX idx_liveGroupId ON Group_ (liveGroupId); 
CREATE INDEX idx_userId ON Users_Groups (userId); 
CREATE INDEX idx_classNameId ON Group_ (classNameId); 
CREATE INDEX idx_companyId ON Group_ (companyId); 
CREATE INDEX idx_parentGroupId ON Group_ (parentGroupId); 
CREATE INDEX idx_groupKey ON Group_ (groupKey); 
CREATE INDEX idx_ownerType ON PortletPreferences (ownerType); 
CREATE INDEX idx_plid ON PortletPreferences (plid); 
CREATE INDEX idx_portletId ON PortletPreferences (portletId);
CREATE INDEX idx_owner_plid_portletId ON PortletPreferences (ownerType, plid, portletId); 
CREATE INDEX idx_configUuid ON AMImageEntry (configurationUuid); 
CREATE INDEX idx_fileVersionId ON AMImageEntry (fileVersionId); 
CREATE INDEX idx_ctCollectionId ON AMImageEntry (ctCollectionId); 
CREATE INDEX idx_config_fileVersion_ctCollection ON AMImageEntry (configurationUuid, fileVersionId, ctCollectionId); 

The last ones with amimageentry probably has the least impact, but i have a pretty image heavy site, so that seemed to have speed up some pages.

I had two test pages with my custom portlets on them:

Before creating indexes:
page 1: ~1500ms
page 2: ~3000ms

After creating indexes:
page 1: ~240ms
page 2: ~530ms

It would be an understatement to say that the improvment was massive, hope this helps you too!

Hi,

Due to some refactors to improve how database build the b-trees, some indexes were missed. The right indexes will be regenerated when upgrading to the version 7.4 U120 and higher (see https://liferay.atlassian.net/browse/LPD-24730). If you are between 112 and 119 (both included) you can use the script provided by Sebastian (thanks!) but remember to remove then when upgrading to newer Liferay versions since you will get sql exceptions trying to create the liferay ones that will collide with them.

​​​​​​​Cheers!