Caching issue - too many db hits

[10年前 に Srinivas konakanchi によって更新されました。]

I noticed our application is hitting database , even though caching is turned on.
For example - we have add content link on one of the pages , which uses view_category.jsp.

Portlet portlet = PortletLocalServiceUtil.getPortletById(user.getCompanyId(), portletId);

Above api call seems to be hitting database every time , Is there any specific cache setting that we need to enable to cache the portlets.
I enabled all recommended hibernate cache settings ( first level and second level )

Also , another most common hit on resource permission table.

Liferay CE 6.1 build.

[10年前 に David H Nebinger によって更新されました。]

Cache is probably in place, but you must realize it's not an unlimited cache. Typically it's some sort of sized cache that drops the least recently used record. So, for example, if you're trying to load 1000 records in a cache that only holds 100 records, you'll end up hitting the db over and over again because it cannot hold as many as you're going after.

[10年前 に Srinivas kk によって更新されました。]

Thanks a lot David for your response.

To rule out LRU , I refreshed the same page which has 5 portlets. From logs , i see this api firing sql to get portlet info from database.
I hope , hibernate prints this sql , only when it hits database. ( show_sql set to true ). If it's true , call to get specific portlet info shouldn't be hitting database on page refresh right.

I am checking src code to find to which module is querying resource action tables . ( same queries r fired on page refresh )

[10年前 に Srinivas kk によって更新されました。]

I am using jconsole and logs to monitor the cache performance. I m not doing much , just refreshing the same page multiple times, expected to see minimal db hits.
But still see same set of queries on resource permission table in logs. I dumped hibernates stats , query cache seems to have zero records , but i did turn on query cache. Also number of db queries r high.

11:49:43,660 INFO [ConcurrentStatisticsImpl:113] Logging statistics....
11:49:43,662 INFO [ConcurrentStatisticsImpl:113] start time: 1384960184352
11:49:43,663 INFO [ConcurrentStatisticsImpl:113] sessions opened: 3614
11:49:43,663 INFO [ConcurrentStatisticsImpl:113] sessions closed: 3614
11:49:43,664 INFO [ConcurrentStatisticsImpl:113] transactions: 3881
11:49:43,664 INFO [ConcurrentStatisticsImpl:113] successful transactions: 267
11:49:43,665 INFO [ConcurrentStatisticsImpl:113] optimistic lock failures: 0
11:49:43,665 INFO [ConcurrentStatisticsImpl:113] flushes: 192
11:49:43,666 INFO [ConcurrentStatisticsImpl:113] connections obtained: 3614
11:49:43,666 INFO [ConcurrentStatisticsImpl:113] statements prepared: 4332
11:49:43,667 INFO [ConcurrentStatisticsImpl:113] statements closed: 4332
11:49:43,667 INFO [ConcurrentStatisticsImpl:113] second level cache puts: 5736
11:49:43,668 INFO [ConcurrentStatisticsImpl:113] second level cache hits: 8
11:49:43,668 INFO [ConcurrentStatisticsImpl:113] second level cache misses: 300
11:49:43,669 INFO [ConcurrentStatisticsImpl:113] entities loaded: 6241
11:49:43,669 INFO [ConcurrentStatisticsImpl:113] entities updated: 7
11:49:43,670 INFO [ConcurrentStatisticsImpl:113] entities inserted: 0
11:49:43,670 INFO [ConcurrentStatisticsImpl:113] entities deleted: 0
11:49:43,671 INFO [ConcurrentStatisticsImpl:113] entities fetched (minimize this): 0
11:49:43,671 INFO [ConcurrentStatisticsImpl:113] collections loaded: 0
11:49:43,672 INFO [ConcurrentStatisticsImpl:113] collections updated: 0
11:49:43,672 INFO [ConcurrentStatisticsImpl:113] collections removed: 0
11:49:43,673 INFO [ConcurrentStatisticsImpl:113] collections recreated: 0
11:49:43,673 INFO [ConcurrentStatisticsImpl:113] collections fetched (minimize this): 0
11:49:43,674 INFO [ConcurrentStatisticsImpl:113] queries executed to database: 4025
11:49:43,674 INFO [ConcurrentStatisticsImpl:113] query cache puts: 0
11:49:43,675 INFO [ConcurrentStatisticsImpl:113] query cache hits: 0
11:49:43,675 INFO [ConcurrentStatisticsImpl:113] query cache misses: 0
11:49:43,676 INFO [ConcurrentStatisticsImpl:113] max query time: 4015ms

These r my settings.
hibernate.bytecode.use_reflection_optimizer true
hibernate.cache.provider_class net.sf.ehcache.hibernate.EhCacheProvider
hibernate.cache.region.factory_class com.liferay.portal.dao.orm.hibernate.region.SingletonLiferayEhcacheRegionFactory
hibernate.cache.use_minimal_puts true
hibernate.cache.use_query_cache true
hibernate.cache.use_second_level_cache true
hibernate.cache.use_structured_entries false
hibernate.configs META-INF/mail-hbm.xml,META-INF/portal-hbm.xml,META-INF/ext-hbm.xml
hibernate.generate_statistics true
hibernate.jdbc.batch_size 20
hibernate.jdbc.use_scrollable_resultset true
hibernate.query.factory_class org.hibernate.hql.classic.ClassicQueryTranslatorFactory

query from logs:
[SQL:38] select resourcepe0_.resourcePermissionId as resource1_47_, resourcepe0_.companyId as companyId47_, resourcepe0_.name as name47_, resourcepe0_.scope as scope47_, resourcepe0_.primKey as primKey47_, resourcepe0_.roleId as roleId47_, resourcepe0_.ownerId as ownerId47_, resourcepe0_.actionIds as actionIds47_ from ResourcePermission resourcepe0_ where ((resourcepe0_.companyId=? ))AND((resourcepe0_.name=? ))AND((resourcepe0_.scope=? ))AND((resourcepe0_.primKey=? ))AND(((resourcepe0_.roleId=? )))

[10年前 に Srinivas kk によって更新されました。]

finally got it , custom cache settings defined by me not picked up , so ehcache silently defaulting it to ehcache-failsafe.xml settings , which sets expiry time to 120 seconds.
It explains , why my record is expired on next page refresh. Thanks to hibernate and ehcache logging. Ofcoz jvm stats too.

15:14:55,355 DEBUG [Cache:38] com.liferay.portal.kernel.dao.orm.FinderCache.com.liferay.portal.model.impl.PortletItemImpl.List2 cache hit, but element expired

[9年前 に satish mandava によって更新されました。]

Hi Srinivas,

I also want to implement hibernate second level cache.
Can you please provide the code to implement.
And you mentitoned that it was expiring, how you achieve that.
If you share your code, that will be helpful for me.

thanks,
Satish

[9年前 に Suyash Bhalekar によって更新されました。]

Hi All,

Any update on this, even we are facing similar issues on our Production environment. And seems that whenever permission check is made it takes lot of time for page to load.

Since permission check is ignored for portal admins, Performance is better when logged in as a Portal Admin but poor for non admin users.

Even we observe that Though we have configure the Ehcache and can see some data being written on disk when disk-overflow is enabled, ehcache is still not being hit and hence performance is very slow.

Any help is appreciated.