1. Home
  2. Portal
  3. RE: Slow on Liferay Portal with busy database activity on liferay DB schema

RE: Slow on Liferay Portal with busy database activity on liferay DB schema

Olaf Kock, modified 1 Year ago.
Slow on Liferay Portal with busy database activity on liferay DB schema
New Member Post: 1 Join Date: 11/29/23 Recent Posts

Hi,

I am using Liferay Portal CE 6.1.1 CE GA2 (build 6101)

Recently we found the liferay portal was slow. We are using Oracle database and at the busy time, we found the jdbc connections to the oracle DB reached max (jdbc connection pool set to 110).

I spotted the active SQL running on the liferay schema could jump up to like 100 for a short time and the performance on liferay portal is really so and could be timeout.

I found the SQL running is like this with its execution plan:

PLAN_TABLE_OUTPUT
"SQL_ID 9aq32tfnvt3yt"
"--------------------"
"select this_.id_ as id1_105_0_, this_.uuid_ as uuid2_105_0_, "
"this_.resourcePrimKey as resource3_105_0_, this_.groupId as "
"groupId105_0_, this_.companyId as companyId105_0_, this_.userId as "
"userId105_0_, this_.userName as userName105_0_, this_.createDate as "
"createDate105_0_, this_.modifiedDate as modified9_105_0_, "
"this_.classNameId as classNa10_105_0_, this_.classPK as classPK105_0_, "
"this_.articleId as articleId105_0_, this_.version as version105_0_, "
"this_.title as title105_0_, this_.urlTitle as urlTitle105_0_, "
"this_.description as descrip16_105_0_, this_.content as content105_0_, "
"this_.type_ as type18_105_0_, this_.structureId as structu19_105_0_, "
"this_.templateId as templateId105_0_, this_.layoutUuid as "
"layoutUuid105_0_, this_.displayDate as display22_105_0_, "
"this_.expirationDate as expirat23_105_0_, this_.reviewDate as "
"reviewDate105_0_, this_.indexable as indexable105_0_, this_.smallImage "
"as smallImage105_0_, this_.smallImageId as smallIm27_105_0_, "
"this_.smallImageURL as smallIm28_105_0_, this_.status as status105_0_, "
"this_.statusByUserId as statusB30_105_0_, this_.statusByUserName as "
"statusB31_105_0_, this_.statusDate as statusDate105_0_ from "
"JournalArticle this_ where this_.groupId=:1 and this_.articleId=:2 and "
"this_.status=:3 and this_.displayDate<=:4 order by this_.version desc"
" "
"Plan hash value: 865305240"
" "
"-----------------------------------------------------------------------------------------------"
"| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |"
"-----------------------------------------------------------------------------------------------"
"|   0 | SELECT STATEMENT             |                |       |       |   314 (100)|          |"
"|   1 |  SORT ORDER BY               |                |   459 |   364K|   314   (1)| 00:00:01 |"
"|   2 |   TABLE ACCESS BY INDEX ROWID| JOURNALARTICLE |   459 |   364K|   313   (0)| 00:00:01 |"
"|   3 |    INDEX RANGE SCAN          | IX_4D5CD982    |   459 |       |     3   (0)| 00:00:01 |"
"-----------------------------------------------------------------------------------------------"
" "
 

Any idea on what the above SQL is for and what problem is? Any way to improve?

Note that I may not be further enlarge the max db connection pool size as not sure if DB would be overkilled.

thumbnail
Olaf Kock, modified 1 Year ago.
RE: Slow on Liferay Portal with busy database activity on liferay DB schema
Liferay Legend Posts: 6441 Join Date: 9/23/08 Recent Posts

The last time I've been reading Oracle execution plans was looooong ago, but this doesn't look like an alarmingly slow plan, after all, it reads by index.

What does it do? It reads a Web Content Article from disk - and seems to be interested in one (the latest version with a particular status that is not published in the future.

The main culprit, in general, is that you're on a version that has been updated multiple times within the past 10 years. I'm not sure how much help anyone can provide - even the commercial enterprise support for that version is long discontinued. Please consider to upgrade - you can not only get better help on the new versions, but you might want to use the new features as well. Plus, there definitely won't be any more security fixes for 6.1 (and you might already be missing some)