Cache with JpaStore constantly queries database

[mobe]

Hi,

first of all, I am using Infinispan 9.2.2.Final and you find my cache configuration attached.

I start my cache at application startup.

When I later retrieve the cache using embeddedCacheManager.getCache("distanceCache"), I start to constantly see SQL select queries being logged from a different thread called 'persistence-thread' in my server console.

At first I thought this is because my in-memory cache entries have not yet been populated but when I execute this code a second time, the behavior is the same.

Also the queries that are being logged look quite weird.

The first query is always a select all query, i.e. it fetches all rows in my cache table.

Then it seems that for each row, Infinispan is again fetching the row with a by-id query.

The code that is executed asynchronously in the 'persistence-thread' is in org.infinispan.persistence.util.PersistenceManagerCloseableSupplier#get and this PersistenceManagerCloseableSupplier seems to be instantiated when I call list() on my Infinispan dsl query. Here is the stack trace for it:

innerIterator:616, CacheLoaderInterceptor$WrappedEntrySet (org.infinispan.interceptors.impl)

spliterator:519, CacheLoaderInterceptor$AbstractLoaderSet (org.infinispan.interceptors.impl)

getStream:558, CacheLoaderInterceptor$AbstractLoaderSet (org.infinispan.interceptors.impl)

stream:549, CacheLoaderInterceptor$AbstractLoaderSet (org.infinispan.interceptors.impl)

stream:119, WriteableCacheCollectionMapper (org.infinispan.util)

getIterator:60, EmbeddedQuery (org.infinispan.query.dsl.embedded.impl)

listInternal:65, BaseEmbeddedQuery (org.infinispan.query.dsl.embedded.impl)

list:57, BaseEmbeddedQuery (org.infinispan.query.dsl.embedded.impl)

list:102, DelegatingQuery (org.infinispan.query.dsl.embedded.impl)

getDistancesByClientLocation:69, GeoDataAccessImpl (com.ordami.geo.service.impl.data)

getDistancesByClientLocation$$super:-1, GeoDataAccessImpl$Proxy$_$$_WeldSubclass (com.ordami.geo.service.impl.data)

invoke0:-1, NativeMethodAccessorImpl (sun.reflect)

invoke:62, NativeMethodAccessorImpl (sun.reflect)

invoke:43, DelegatingMethodAccessorImpl (sun.reflect)

invoke:498, Method (java.lang.reflect)

proceedInternal:49, TerminalAroundInvokeInvocationContext (org.jboss.weld.interceptor.proxy)

proceed:77, AroundInvokeInvocationContext (org.jboss.weld.interceptor.proxy)

invokeInNoTx:141, TransactionalInterceptorBase (com.arjuna.ats.jta.cdi.transactional)

doIntercept:53, TransactionalInterceptorSupports (com.arjuna.ats.jta.cdi.transactional)

intercept:79, TransactionalInterceptorBase (com.arjuna.ats.jta.cdi.transactional)

intercept:47, TransactionalInterceptorSupports (com.arjuna.ats.jta.cdi.transactional)

invoke:-1, GeneratedMethodAccessor1585 (sun.reflect)

invoke:43, DelegatingMethodAccessorImpl (sun.reflect)

invoke:498, Method (java.lang.reflect)

invoke:73, SimpleInterceptorInvocation$SimpleMethodInvocation (org.jboss.weld.interceptor.reader)

executeAroundInvoke:84, InterceptorMethodHandler (org.jboss.weld.interceptor.proxy)

executeInterception:72, InterceptorMethodHandler (org.jboss.weld.interceptor.proxy)

invoke:56, InterceptorMethodHandler (org.jboss.weld.interceptor.proxy)

invoke:79, CombinedInterceptorAndDecoratorStackMethodHandler (org.jboss.weld.bean.proxy)

invoke:68, CombinedInterceptorAndDecoratorStackMethodHandler (org.jboss.weld.bean.proxy)

getDistancesByClientLocation:-1, GeoDataAccessImpl$Proxy$_$$_WeldSubclass (com.ordami.geo.service.impl.data)

getDistancesByClientLocation:-1, GeoDataAccessImpl$Proxy$_$$_WeldClientProxy (com.ordami.geo.service.impl.data)

getDistances:29, DistancesResourceImpl (com.ordami.geo.rest.impl.resource)

getDistances:-1, DistancesResourceImpl$Proxy$_$$_WeldClientProxy (com.ordami.geo.rest.impl.resource)

invoke0:-1, NativeMethodAccessorImpl (sun.reflect)

invoke:62, NativeMethodAccessorImpl (sun.reflect)

invoke:43, DelegatingMethodAccessorImpl (sun.reflect)

invoke:498, Method (java.lang.reflect)

invoke:140, MethodInjectorImpl (org.jboss.resteasy.core)

invokeOnTarget:295, ResourceMethodInvoker (org.jboss.resteasy.core)

invoke:249, ResourceMethodInvoker (org.jboss.resteasy.core)

invoke:236, ResourceMethodInvoker (org.jboss.resteasy.core)

invoke:406, SynchronousDispatcher (org.jboss.resteasy.core)

invoke:213, SynchronousDispatcher (org.jboss.resteasy.core)

service:228, ServletContainerDispatcher (org.jboss.resteasy.plugins.server.servlet)

service:56, HttpServletDispatcher (org.jboss.resteasy.plugins.server.servlet)

service:51, HttpServletDispatcher (org.jboss.resteasy.plugins.server.servlet)

service:790, HttpServlet (javax.servlet.http)

handleRequest:85, ServletHandler (io.undertow.servlet.handlers)

handleRequest:62, ServletSecurityRoleHandler (io.undertow.servlet.handlers.security)

handleRequest:36, ServletDispatchingHandler (io.undertow.servlet.handlers)

handleRequest:78, SecurityContextAssociationHandler (org.wildfly.extension.undertow.security)

handleRequest:43, PredicateHandler (io.undertow.server.handlers)

handleRequest:131, SSLInformationAssociationHandler (io.undertow.servlet.handlers.security)

handleRequest:57, ServletAuthenticationCallHandler (io.undertow.servlet.handlers.security)

handleRequest:43, PredicateHandler (io.undertow.server.handlers)

handleRequest:46, AbstractConfidentialityHandler (io.undertow.security.handlers)

handleRequest:64, ServletConfidentialityConstraintHandler (io.undertow.servlet.handlers.security)

handleRequest:60, AuthenticationMechanismsHandler (io.undertow.security.handlers)

handleRequest:77, CachedAuthenticatedSessionHandler (io.undertow.servlet.handlers.security)

handleRequest:50, NotificationReceiverHandler (io.undertow.security.handlers)

handleRequest:43, AbstractSecurityContextAssociationHandler (io.undertow.security.handlers)

handleRequest:43, PredicateHandler (io.undertow.server.handlers)

handleRequest:61, JACCContextIdHandler (org.wildfly.extension.undertow.security.jacc)

handleRequest:43, PredicateHandler (io.undertow.server.handlers)

handleRequest:68, GlobalRequestControllerHandler (org.wildfly.extension.undertow.deployment)

handleRequest:43, PredicateHandler (io.undertow.server.handlers)

handleFirstRequest:292, ServletInitialHandler (io.undertow.servlet.handlers)

access$100:81, ServletInitialHandler (io.undertow.servlet.handlers)

call:138, ServletInitialHandler$2 (io.undertow.servlet.handlers)

call:135, ServletInitialHandler$2 (io.undertow.servlet.handlers)

call:48, ServletRequestContextThreadSetupAction$1 (io.undertow.servlet.core)

call:43, ContextClassLoaderSetupAction$1 (io.undertow.servlet.core)

lambda$create$0:105, SecurityContextThreadSetupAction (org.wildfly.extension.undertow.security)

call:-1, 1432452184 (org.wildfly.extension.undertow.security.SecurityContextThreadSetupAction$$Lambda$884)

lambda$create$0:1508, UndertowDeploymentInfoService$UndertowThreadSetupAction (org.wildfly.extension.undertow.deployment)

call:-1, 977270871 (org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction$$Lambda$885)

lambda$create$0:1508, UndertowDeploymentInfoService$UndertowThreadSetupAction (org.wildfly.extension.undertow.deployment)

call:-1, 977270871 (org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction$$Lambda$885)

lambda$create$0:1508, UndertowDeploymentInfoService$UndertowThreadSetupAction (org.wildfly.extension.undertow.deployment)

call:-1, 977270871 (org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction$$Lambda$885)

lambda$create$0:1508, UndertowDeploymentInfoService$UndertowThreadSetupAction (org.wildfly.extension.undertow.deployment)

call:-1, 977270871 (org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction$$Lambda$885)

lambda$create$0:1508, UndertowDeploymentInfoService$UndertowThreadSetupAction (org.wildfly.extension.undertow.deployment)

call:-1, 977270871 (org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction$$Lambda$885)

dispatchRequest:272, ServletInitialHandler (io.undertow.servlet.handlers)

access$000:81, ServletInitialHandler (io.undertow.servlet.handlers)

handleRequest:104, ServletInitialHandler$1 (io.undertow.servlet.handlers)

executeRootHandler:326, Connectors (io.undertow.server)

run:812, HttpServerExchange$1 (io.undertow.server)

runWorker:1149, ThreadPoolExecutor (java.util.concurrent)

run:624, ThreadPoolExecutor$Worker (java.util.concurrent)

run:748, Thread (java.lang)

However, the SQL queries are executed as soon as I run embeddedCacheManager.getCache("distanceCache") which is before I invoke the list() method. This is one of the things I don't understand here.

Anyways, my expectation is that not database queries are executed at all in this scenario since the cache should have all required entries available in memory.

Thanks

[william.burns]

This is a non indexed query, which means it will use streams underneath to run the query. Streams is run against all data in memory and in the store (since we don't know if there are additional elements not in memory). If you wish to skip the STORE you can use the Flag (Infinispan JavaDoc All 9.3.0.Alpha1 API) on the AdvancedCache to prevent the stream from reading from the underlying store.

Also you mentioned that "all required entries available in memory" - are you somehow passing this to the DSL to tell us only a subset of keys to access (otherwise we wouldn't know which to attempt - in which case we would have to test all)?

[mobe]

Hi William, thanks for your response.

What you are saying makes sense to me - no idea why that was not obvious to me ^^

So as far as I understand I have 2 options for having the query execute locally (please correct me if I am wrong):

• Use a replicated cache instead - this alone should suffice for the query to remain local because all cache content is local, right? Optionally, I could add a non-shared index with mode ALL to speed things up.
• Stick with the invalidation cache with an index (not sure if shared or non-shared?) of mode ALL - so the local index could be used to get the complete set of IDs of the cache elements in the result set and the elements that are not residing in local memory would then be fetched from the store.

Option number 2 would have some benefits for cache writes.

[mobe]

Just noticed that invalidation caches cannot be configured with an index. I think this fact is missing in the documentation.

[mobe]

OK so I tried this now using a replicated cache (see my adapted infinispan config attached) both with and without indexing.

But in both cases, the database is hit when executing the same query several times in sequence. What am I doing wrong?

[william.burns]

Unfortunately, having a cache as replicated doesn't mean that it has all entries in memory. It just means that all nodes have the same entries in memory. We will still need to consult the underlying store.

The only way to guarantee you won't touch the store is the flag I mentioned.

cache = cache.getAdvancedCache().withFlags(Flag.SKIP_CACHE_LOAD);

You should be able to pass that cache into the SearchManager then. If not you could use

cache = cache.getAdvancedCache().withFlags(Flag.SKIP_CACHE_LOAD);
cache.entrySet().stream() ...

directly and use distributed streams directly by invoking filter or other methods. This will for sure ignore the loader.

[mobe]

Thank you for the clarification.

Is there any way to force all nodes to hold all cache elements in memory so that I can be sure that no elements are missed for queries run with the SKIP_CACHE_LOAD flag (willingly risking to run out of memory)?

Since eviction is deactivated by default, configuring preloading on the cache should guarantee that all data is in local memory, right?

[mobe]

william.burns is there no solution to what I want to achieve?

[william.burns]

If you have preload enabled (on a shared store, which JpaStore should be configured as), without eviction in a REPL cache then the in memory contents should match the store contents. So in that case using Flag.SKIP_CACHE_LOAD should be equivalent.