Asset Publisher and queries count

In this post, I will mainly describe my expierience with Asset Publisher in Liferay 6.1 (87489 revision). In the previous post I showed how Asset Publisher constructs entry query and how many records it picks up.

The starting point of my test was the following configuration:

  • About 10 000 AssetEntries
  • First page with 1 Asset Publisher without any filters. It gets ~4900 records.
  • Second page with 1 Asset Publisher with categories filter. It gets ~ 300 records.

Following table shows results of my tests.

Scenario Guest Logged In user
1st request.
Asset Publisher with ~4900 results. 20 per page.
~ 9800 queries to database ~ 29300 queries to database
2nd request.
Asset Publisher with ~4900 results. 20 per page.
0 queries to database ~ 29300 queries to database
1st request.
Asset Publisher with ~300 results. 20 per page.
~ 900 queries to database ~ 900 queries to database
2nd request.
Asset Publisher with ~300 results. 20 per page.
0 queries to database ~ 30 queries to database

Interesting situation is the second row of this table (Asset Publisher with ~4900 results. 20 per page and logged in user). Asset Publisher retrievs 5000 records from database. Now in a loop it sends one query to transform AssetEntry to specific entity (JournalArticle in my case) and sends two queries to check permissions. It gives three queries in one record.

So Liferay sends 2 * (5000 * 3 queries per item) = 30 000 queries. In the trunk version the second query is cached so it gives 15 000 queries.

Fortunately, most of the queries is partially cached.

From a business point of view I want to display 20 articles in one AssetPublisher with pagination so I don’t understand why my portal sends over 30 000 queries to database. It’s a horrible situation when simple task frequently overuses my server resources.

One of bottleneck in Liferay 6.1

A few days ago we’ve prepared a jmeter’s test. Our goal was to find a bottlenecks in the newest Liferay 6.1 portal. So we load about 7k AssetEntries (JournalArticles only) and drag and drop one Asset Publisher on the site. To my surprise – page loaded for about 30 seconds. In my mind was born a strange thought: is it possible have we done something wrong? Maybe our content’s importer is killing the portal. So it’s time to debug Liferay.

In AssetEntryServiceImpl we found interesting code:

protected Object[] filterQuery(AssetEntryQuery entryQuery)
throws PortalException, SystemException {
int end = entryQuery.getEnd();
int start = entryQuery.getStart();
entryQuery.setEnd(end + PropsValues.ASSET_FILTER_SEARCH_LIMIT);
List entries = assetEntryLocalService.getEntries(entryQuery);

We want to pick 20 entries and Liferay get 20 + 5000 records from database. Twenty – because AssetPublisher has to display this records, plus 5000 – just in case. It gives 10040 records per request in every Asset Publisher. Furthermore – Liferay calls this method two times in every request. Firstly to get countEntries (pagination), secondly to get entities.
Later in this method Liferay checks permissions on every entity and picks only entities which have View permission. I understand this logic, but it’s very expensive.

Maybe Liferay developers should find more optimal way to get Asset Entries. I leave this question open.