Liferay 6.1.1 slow with 18000 layouts

[Harri T., modificado hace 6 años.]

Hi,

We have recently added a lot of layouts to Liferay 6.1.1 portal and now we have over 18000 of them. Opening a page is extremely slow. Liferay executes query

SELECT * FROM layout WHERE plid = ? 

2 x 18000 times for each page request. How can we avoid this?

See the profiling result below for more information.

Br,

Harri

[David H Nebinger, modificado hace 6 años.]

So have you used your database tools to explain the query? Verify that you're doing index hits instead of full table scans? Optimizing the indexes to ensure they are balanced?

[Harri T., modificado hace 6 años.]

David H Nebinger:

So have you used your database tools to explain the query? Verify that you're doing index hits instead of full table scans? Optimizing the indexes to ensure they are balanced?

Yes, I've used PostgreSQL "EXPLAIN ANALYZE" and there's nothing wrong with indexes. The problem is that the same query is run too many times (for all the plids).

[Olaf Kock, modificado hace 6 años.]

Harri T.:

We have recently added a lot of layouts to Liferay 6.1.1 portal and now we have over 18000 of them. Opening a page is extremely slow. Liferay executes query

SELECT * FROM layout WHERE plid = ? 

2 x 18000 times for each page request. How can we avoid this?

6.1.1 was released in Feb 2013, had an update on that major version in December 2013 (6.1.2). Did you try if this was a known issue in 6.1.1 and is fixed in 6.1.2? However, note that 6.1.x wasn't updated after 6.1.2 any more, so it's now ~4 years old. You should also try if the issue is still reproducible on the latest version.

On the "how can we avoid this", if you don't want to update, I'd look for the place where the statement is executed and double check if it's in an over-eager loop. Another alternative is to check the layout cache size - If I remember correctly (but I may not), the default cache size is 10000 - so after loading 18000 pages, the first 8000 pages would surely be not cached any more - triggering constant reloading the cache.