Locator Cache, max size 1000 cause heavy database queries

Description

related discussion
https://mattermost.idempiere.org/idempiere/pl/oid5axpjppnofgdokr71zdsako

Issue
We report from our production database, pg_stat_activity stats 2 hours 4,4 mill ad_reference queries and 2,3 mill locator queries. We are running 7.1z with 16GB RAM JVM 50%. Locators up to 9000.

Igor Pojzl reported: found the cause of this. it is caused by 2 things. first is MLocatorLookup Loader on line 437. The second thing is we have about 3000 locators in tenant, and the cache has a max size 1000 which causes each Locator Component to rotate locators in the cache. When the first Locator editor component end we have the last 1000 locators in the cache and the next Locator Editor will not find any of the first 2000.

Temp. solution
Norbert: we changed to10 000 then the max queries were 44000 instead 2mill in 2 hours. This is acceptable for now.

@CarlosRuiz notes
something similar happened in IDEMPIERE-3781. My solution there was to set max size to zero - in this specific case - you can check the commit Workaround: start the app with parameter -DCache.MaxSize=2000 (or whatever your number of regions are) - however, this workaround can affect adversely memory usage.

suggestion
hi @norbertbede , i think it would be helpful to others if you can create a new Jira ticket to document these incidents. Besides DB hit, the other significant impact is on memory usage so it would be useful to know the size of memory configuration that your have for this as well. I don't know what is it yet but it does feel like that we should come up with something more flexible for this.

Environment

private implementation

Assignee

Unassigned

Reporter

Norbert Bede

Labels

Tested By

Norbert Bede

Affects versions

Priority

Major
Configure