zberentey/liferay-portal — pull request #111 by vilmospapp (2012-07-09 12:48:14+00:00)
Title: [TECHNICAL SUPPORT] LPS-28364 Fixing Asset Publisher Performance issue

Hi Zsolt,

As Máté will be on holiday, I resend this pull request for you.

There is a performance problem in Asset Publisher's in the DB layer, as Laci investigated the issue he created the fix applied in this pull request.

The detailed information about the nature of the problematic behavior please find below:

Performance problem in the back-end layer has been reproduced and verified with JMeter; Details: with 30 concurrent users (15 sec ramp up time / 3 repeats on the main menu of the portal (Nieuws, Verdieping, Dossiers, Opinie, Video, Shop, Vacatures)) produced 6.9 sec avg. page rendering time while MySQL was feeding mysql-slow.log with those queries heavily which the customer blamed before.

I found a feasible workaround on Friday. My measurements justified that re-structuring the problematic query helps cut down on page rendering time from 6.9 sec -> 3.5 sec AND MySql stopped feeding mysql-slow.log. (long_query_time=1 in my environment)

SELECT DISTINCT ae ... FROM AssetEntry ae
WHERE ae.entryId IN (SELECT DISTINCT ... From AssetCategores ...) AND ...

Will be replaced by

SELECT DISTINCT ae ... FROM AssetEntry ae
INNER JOIN (SELECT ... From AssetCategores ...) c ON ae.entryId = c.entryId
WHERE ...