Hibernate query cache considered harmful?

42

Over the last few months we’ve had a whole bunch of people doing a whole bunch of Hibernate performance tuning. I’m going to do a few blogs on some really common things that we ran into that might help you as well. The first one I wanted to talk about was Hibernate query caching.

Hibernate caching overview

Hibernate has several levels and kinds of cache. There is a first level cache of Hibernate entities stored in and scoped specifically to a particular open Session. In Hibernate, entity objects are never shared between sessions.

There is also a second level cache that is shared across sessions. The second level cache is divided into regions of four types: entity, collection, query, and timestamp. Entity and collection regions cache the data from entities and relationships (but not the entities themselves). The query and timestamp caches are related and the subject of our focus here.

The query cache is used to store the result set of a query made against the database. The key of the query cache contains the query string itself and any bind parameters passed with the query. The value consists not of the entity field values but just the primary key identifiers for all of the entities returned by the query. When a query is made that hits the query cache, that set of entity identifiers can be retrieved and then resolved through the first or second level caches instead of retrieving those entities from the database.

The timestamp cache keeps track of the last update timestamp for each table (this timestamp is updated for any table modification). If query caching is on, there is exactly one timestamp cache and it is utilized by all query cache instances. Any time the query cache is checked for a query, the timestamp cache is checked for all tables in the query. If the timestamp of the last update on a table is greater than the time the query results were cached, then the entry is removed and the lookup is a miss.

Using the query cache

Two hibernate properties control whether the query cache is enabled:

  • hibernate.cache.use_second_level_cache=true|false
  • hibernate.cache.use_query_cache=true|false

The first turns on the second level cache in general and the second turns on the query cache regions. If the second is set to false, the query and timestamp cache regions are not created or used.

By default, turning on query caching does not actually cause any queries to be cached. Instead, Query or Criteria that should be cached must be explicitly marked that way. For example, a Query can be executed in this way:

Query bandsByName = session.createQuery("from Band b where b.name = :name");
bandsByName.setString("name", bandName);
bandsByName.setCacheable(true);
//bandsByName.setCacheRegion("special.region");

or if you’re using JPA with annotations, you can use setHint(“org.hibernate.cacheable”, true) on a javax.persistence.Query, etc. Note here that you can define your own specially named query cache with setCacheRegion instead of the default one. This isn’t particularly useful unless you have a very specific requirement to be able to evict everything from a particular query cache or to set specific eviction policies on a per-query basis. There is only ever one timestamp cache shared by all query caches.

You can also create a cacheable query through a Criteria:

Criteria crit = session.createCriteria(Company.class);
crit.add( Restrictions.naturalId().set("taxId", "209384092") 
           ).setCacheable(true)
            .uniqueResult();

When Hibernate executes a query based on one of these constructs, it will do something like this:

In the final step, entities are assembled from the identifiers held in the result of the lookup and that process may hit the second level cache to hydrate the returned entities.

More caching is better, right?

I think many users who turn on the second level cache also turn on the query cache because they don’t know what the query cache does. (I certainly turned it on the first time I tried second level cache.) Well, as it turns out, that’s a bad idea. The Hibernate query cache is actually not only not helpful but downright harmful to latency and scalability in many common scenarios.

If I were to summarize one piece of advice from this whole article it would be: turn off the query cache unless a) you know why you’re turning it on and b) you can measure a real improvement in a realistic load. Let’s look at some reasons why…

  1. The primary reason that the query cache is often not useful is that the results are constantly being invalidated by table modifications. As I mentioned earlier, any table modification causes the timestamp cache to be updated. When you do a lookup that returns any entity through the query cache, it’s possible it is invalidated by an insert or update totally unrelated to that entity. For example, you might be doing a query by social security number on a Person. If any other Person in your db has been inserted or updated since the last hit on that ssn, the result will be thrown out (even though it’s likely still valid).

    It is quite common to set up a query cache but find that the hit rate is very low as the data is constantly being invalidated. But fear not – there is a way around this issue which is discussed in the next section.

  2. Probably the next most common query cache problem is memory usage. Query cache is notorious for gobbling up your heap, mainly because of its keys. First you have the query string itself, which is quite commonly hundreds of characters long and is frequently repeated with different bind parameters. There is a great discussion of that issue and some advanced ways to avoid it here.

    Second, the bind parameters were until recently the actual objects passed in to the Query or Criteria (if that’s how you constructed the query). That meant that it was quite easy to put session-scoped entities and other complex objects into your cache where they would sit forever, possibly holding references to collections and large chunks of your session cache. This issue is described here and was actually fixed by HHH-3383 in the recent Hibernate 3.2.7 and 3.3.2 releases which convert those entities to identifiers.

  3. Another issue we found internally in testing at Terracotta (but which I haven’t seen mentioned anywhere else) is that turning on the query cache introduces a new source of lock contention in the timestamp cache. This cache has a single coarse lock that is locked for all inserts/updates/deletes to update the timestamp of a table and also for every lookup that occurs through the query cache. Under load, this lock can easily become a bottleneck.

    Interestingly, the first part can become an issue even if NO queries are cached! One place you might see that is when doing multi-threaded table loading – every insert into any table across all threads must obtain the same lock in the timestamp cache, even if no queries are being cached. This is where it can be quite dangerous to turn on query cache if you aren’t actually using it.

  4. Not really a problem per se, but a common misconfiguration that can occur is with the eviction settings for the timestamp cache. Because you want your query cache to always be able to find the last update timestamp in the timestamp cache, you want to be sure that the timestamp cache does not evict entries before the query cache! In general, it is recommended that the timestamp cache be eternal or evicted on a “time to live” longer than the query cache “time to live”. The timestamp cache should not use “time to idle” based eviction.

    You might wonder what happens in the case where a timestamp is evicted too early. I think if I’m reading the code right in UpdateTimestampsCache.isUpToDate(), this situation is treated as the timestamp cache saying no modifications have occurred and allowing a query cache entry to stand. It is possible in that case for updates to have occurred before or after timestamp eviction that would not be noticed and you might actually see stale values. If so, that would be a pretty subtle bug.

When is the query cache useful?

The one case that seems to be a sweet spot for using the query cache is when you frequently need to look up an entity based on a natural key. A natural key is a field or fields that form a unique way to identify an entity in a table but is not the primary key. For example, you might have a Person table with an auto-generated primary key in the database. But several identifiers for a Person might form a natural key, such as social security number or email address.

If you frequently have a user or external input providing you a natural key for lookup, your normal second level entity cache is NOT helpful because it caches based on primary key. In this case, you can use a Criteria-based cached query lookup on an immutable natural key column to do that first hop from natural key to primary key and remember that step for the future.

That query (including the bind parameter holding the natural key value) will be mapped to a result of one primary key id. You can then leverage the normal second level entity cache to do the primary key lookup.

If you noticed in the workflow up above, there is a special check made for whether the lookup is on a natural immutable key. This hint can only be supplied by a Criteria, not by a Query or other means. The columns must be marked as natural keys and flagged as immutable in your mapping file.

If you do this, you skip the check on the timestamp cache! This is because for an *immutable* natural key, it’s impossible for a table modification to change the mapping of natural to primary key. Skipping that check makes the query cache higher performance and avoids the invalidation problem, yielding much higher hit rates on your query cache.

Note that this still doesn’t avoid the lock contention created in the timestamp cache synchronization.

Musings

At Terracotta, we’re working on a new Hibernate second level cache implementation for Terracotta 3.1 and we’ve so far been focusing on performance tuning the entity and collection caches. The numbers we’re seeing with the new clustered cache look great and we can’t wait to have people try it out.

We see dramatic slow-downs any time we turn on the query cache (even if no queries are cached) as that introduces the lock contention in the timestamp cache. [Note that this is in no way specific to Terracotta - we see it with all second-level caches. The contention is in Hibernate's UpdateTimestampsCache, not in any cache provider.]

In the Terracotta cache implementation, we use a special highly concurrent distributed data structure for each cache region. That actually allows us to remove much of the synchronization present in Hibernate above the cache. We have started tinkering with this (probably for the next release) and we think it will dramatically improve the throughput query cache users see by eliminating the lock contention.

As I did the research to write this blog, I also started to see that the whole invalidation by table is very blunt. It seems like there should be a more general purpose way to solve this natural key lookup problem in a fine-grained way that leveraged the very common case where lookups and changes are done on a per-key basis and avoid the invalidation by table altogether.

Helpful links

Comments

42 Responses to “Hibernate query cache considered harmful?”
  1. Nick says:

    So, in short, a query cache makes sense only when

    a) the query parameters are an immutable natural key for the entity (with timestamp checking turned off), i.e. there will never be another entity matching the same parameters.

    or

    b) the entities are (almost) never updated.

    Is that right?

  2. Alex says:

    Yup. Although even in the second case, you might still be introducing enough lock contention that you’re not offsetting the db offload. That will depend completely on your app and load.

    As always, you should run a performance test with it on and off. If it’s slower with it on, you either need to tune what you’re doing or reconsider your choices.

  3. Zach Bailey says:

    Great Post Alex!

    Unfortunately we ran into this problem with the query/timestamps cache the hard way and after a couple of long days trying to figure out why we had so much lock contention, we finally found the silver bullet of disabling not just the query cache but also the timestamps cache. This made a huge difference for us in terms of application throughput.

    Another thing that is worth mentioning that can be very detrimental to 2nd level cache performance is executing native SQL queries. If you look deep in the depths of the Hibernate Query infrastructure (SQLCustomQuery, NativeSQLQueryPlan, BulkOperationCleanupAction), you will see that any time a custom SQL is run, it clears ALL your entity/collection caches.

    The easiest way to see this is to turn on debug logging of the caches (either hibernate or ehcache/etc) and watch as all your cached data is dumped when a native SQL query is run. Yikes!

    Cheers,
    -Zach

  4. Alex says:

    Thanks Zach! That’s a great find that I wasn’t aware of.

  5. Franz See says:

    Hi Alex,

    Insightful article. Question though, in “Understanding Caching in Hibernate – Part Two : The Query Cache”, they used query cache without the second level cache. Will you still have those memory consumptions and invalidated query cache problems?

    Thanks

  6. Alex says:

    Franz,

    Regardless of whether you are caching entities and collections, you will still have the same problems with memory consumption in the query cache and invalidation.

  7. Nat says:

    TimestampCache also uses cache miss to verify that query is updated. This causes a performance issue when using many distributed cache. Distributed cache often has certain group of machines that are responsible for certain cache keys. Therefore, to detect that the cache entry is missing, it has to go to remote machines to check. While for cache hit, it can just check in the local cache whether the cache entry is still valid and return it right away. Therefore, if the query cache is still valid (i.e. cache key doesn’t exist), it takes a bit longer to verify that

  8. Alex says:

    Yep, this is a common distributed cache problem although it’s not really specific to the query cache. With the Terracotta cache, this actually isn’t an issue as the key set is local.

  9. Shawn Clowater says:

    I’m a bit late to the party but wanted to throw out another issue when enabling the query cache.

    With the query cache enabled any long running batch is going to suffer since the action queue will hold onto all of your entity references you modify until after the transaction completes even if you flush/clear your session to help reduce the memory consumption.

    http://opensource.atlassian.com/projects/hibernate/browse/HHH-3028

  10. Harshit says:

    Great post alex. i like reading your articles. I will need to rethink once again whether to use query cache for my application or not .

  11. ash says:

    If I understand correctly this post indicates Terracotta are looking at providing a new query cache! Could you please provide a time frame for the release and if possible some more details on it as we are very interested in seeing whether it will suite our needs.
    cheers

  12. Alex says:

    @ash – I’d say we’ve done some thinking about it. Really, I think doing it the right way requires changes to Hibernate itself. I’ve had some very brief discussions with members of the Hibernate team about it and hopefully we’ll be able to work on it in the future. There is no active work on it right now.

  13. Brett says:

    Can you avoid the timestamp invalidation by using nonstrict read/write / read-only caching? I designed my system to use only read only caching and the performance is stunning. The only ones I trust to manage changing state are postgres, oracle and myself. That’s a tough problem to solve, especially with my needs – distributed and fast. But I’m open to any alternatives to make my life easier, Teracotta looks interesting. :)

  14. Alex says:

    @Brett – I don’t think using read-only or non-strict matters, what matters is whether you are making insert/update/delete statements. If query caching is on, those ops hit the timestamp cache for invalidation.

  15. Russell says:

    Alex,

    Interesting article. We use query caching in our app; it’s very common for us to have cases with many repetitions of the same query. Some of these can be designed or coded as batch operations; but often it keeps the design cleaner and saves us a lot of refactoring if we can enable query caching.

    I wanted to contribute a note about a bug in Hibernate’s query caching, which I found particularly frustrating in one use case:
    http://opensource.atlassian.com/projects/hibernate/browse/HHH-3339

    The gist is that after data is saved to a table, HQL queries that include the “query space” for that table won’t be cached within the context of the same Hibernate session that the save was done in. Very aggravating.

    Hope this helps someone else who runs across the same issue! Someone posted a patch on the JIRA, but it hasn’t made it in. I’m using the patched version of StandardQueryCache.

  16. Naveen says:

    Hi,

    Was this query key issue fixed in hibernate 3.5.1?

    I am using the following maven dependencies:

    org.hibernate
    hibernate-entitymanager
    3.5.1-Final

    org.hibernate
    hibernate-ehcache
    3.5.1-Final

    Can they be used in production and live environments or should i wait for another release?

    Currently we are using hibernate 3.3.2-GA maven dependencies and we are getting Java Heap: OutOfMemoryErrors on Live production environment.

    Did hibernate 3-5-1 final version fixed this Query key memory issue? Can i use that version of hibernate for production live environment? If not, plz suggest me a version of hibernate that has fixed this QueryKey bloddy memory issues so that i can add it as maven dependency and use it.

  17. Naveen says:

    We are currently using these dependencies:

    org.hibernate
    hibernate-entitymanager
    3.4.0.GA

    org.hibernate
    hibernate-ehcache
    3.3.2.GA

    But we are getting OutofMemoryError with the above dependencies.

    I am facing strange exceptions with hibernate 3.5.1-Final. We are using spring 2.5.1

  18. Alex says:

    @Naveen: I believe the latest versions of Hibernate 3.2, 3.3, 3.4 and 3.5 have the query key fixes. If you are seeing OOMEs then I would use a heap profiler (either ones that come with the JDK/VisualVM or a commercial one like YourKit) to examine where you heap is being spent. It should be obvious in a heap dump if the problem is in your cache. Another easy check is simply to turn off query caching and see if you still get OOMEs. In short, investigate the problem, don’t just guess at what it might be.

  19. Dan G says:

    Alex,

    Great article. What are some ways to performance test the hibernate cache? I’m running System.currentTimeMillis() in my tests and wondering what else there might be.

    Thanks!

  20. Alex says:

    @Dan: Hibernate has a bunch of statistics built into the cache and other parts of the code and you can turn that stuff on to get lots of information. One major downside is that the stats code has a lot of concurrency issues that will also affect the performance of your code (see http://tech.puredanger.com/2009/05/13/hibernate-concurrency-bugs/ for more details). Terracotta has since provided patches that address issues to Hibernate and I believe they are addressed in newest versions of the code.

    If you’re using Ehcache as your Hibernate cache provider (which I would recommend), then you can use the Ehcache Monitor to get to those same stats I mentioned above and view them in a nice console. The monitor is backwards-compatible to pretty old versions of Ehcache and doesn’t require Terracotta at all. You can find more info on trying it out here: http://ehcache.org/documentation/monitor.html

  21. Dan G says:

    Thanks Alex. I’ve got it up and running on my local machine.

    Something I noticed that was odd about the monitor is inside the contents tab for my cached element details I see:

    The element couldn’t be found in the cache anymore. Yet I’m still getting hits for those elements.

  22. Alex says:

    @Dan – I would recommend following up on the Ehcache forums at http://forums.terracotta.org/forums/forums/show/16.page

    Someone there should be able to answer your question or help debug it if it’s an issue.

  23. Himanshu says:

    Hi Alex,
    We have 2 master tables, each having approx 800,000 records.

    TableA is updated once every 24 hours

    TableB is updated throughout the day but only incremental data. Data inserted is anywhere between 100s to 100 thousands.

    TableA lookup query is -
    SELECT * FROM TableA WHERE ID =

    TableB has 3-4 diff queries based on different criterias
    SELECT * FROM TableB WHERE Col1 =
    SELECT * FROM TableB WHERE Col2 =
    SELECT * FROM TableB WHERE Col3 =

    We are in dicussion the best possible way to cache data – either store it in some kind of java collection, may be ConcurrentHashMap or EHCache. Going through your article, using query cache isn’t a good option.

    Ours is a multi-threaded application wherein everyday approx. 1 million records are processed. Can you please suggest on this?

  24. Simon says:

    Hi Alex

    This is a fantastic article and has proven extremely useful for us. We are seeing some amazing results from effective use of terracotta/ehcache for cached query results based on natural ids.

    I am exploiting the natural identifier query cache ‘sweet spot’ in a particular case where I’m using Restrictions.naturalId() for a field which although immutable, is not strictly a natural identifier. It works perfectly until a new entity with the same ‘natural ID’ is created. The cached query never evicts and therefore becomes stale. What I’ve done is manually update the query cache result to add the newly created entity, as part of the creation of the new entity. It works as far as my unit testing has proven so far. I’d normally avoid this sort of under-the-hood tinkering with hibernate, however the performance benefit is simply too good to ignore.

    It would be great if hibernate allowed means for similar special treatment for queries based on immutable fields, with support for new entities matching the cached query criteria.

    Wondering if you’ve seen a similar approach before or have any comment about how the query cache can be made a bit smarter to avoid unnecessary db hits/cache invalidations?

    I’ve recently posted on the hibernate forums with a more detailed breakdown:
    https://forum.hibernate.org/viewtopic.php?f=1&t=1006626

    Cheers,

    Simon

  25. Alex says:

    @Himanshu: Your data size and change rate don’t seem too scary – I think Ehcache (possibly backed by Terracotta store) would work well, but it’s hard to say just based on this. You might want to ask over on http://forums.terracotta.org.

  26. Alex says:

    @Simon: Thanks, I’m glad you found it helpful. Your approach seems like a reasonable hack to me. I think the query cache could be redone to do far more targeted invalidation (per key instead of per table). Or even by letting you plug in your own customized algorithm.

  27. Himanshu says:

    Hi,
    Thanks for the feedback. I’ve gone through caching document but still bit confused how actually it works. I created config file – app-cache.xml and configured DO like:

    My understanding was that after every 60 sec data would be refreshed. But that is not the case. Data is updated after 120 sec (I think as defined in defaultCache).

    In my application, data is updated in the backend directly, there is no UI. Hence I think even if I change data before 120 sec has expired, it is not reflected because cache could not detect the change. Is there a way to overcome this? I tried many combinations but nothing seems to work. Is it possible that any change in DB should be detected even if time interval has not expired yet?

  28. Himanshu says:

    In continuation, I defined defaultCache in my config and set the timeToLiveSeconds = 86400. And every 5 min I’m updating database. This works. Change is detected and fresh data is displayed.

  29. I feel hibernate caching causes performance bottle necks on complicated systems. You are much better turning it all off and using something like membase or couchdb to make specific caching solutions that you have total control over. Everyone here is talking about tweaking ‘generic settings’ without looking from a bit higher up of what they are really trying to achieve. see cargocultsoftwaredevelopment.blogspot.com

  30. Alex says:

    @SpeakingStones: In general I agree with you. Hibernate and its cache layers can be used to abstract some common patterns but when we’ve built high-throughput systems we ultimately resorted to customized caching with Ehcache and Terracotta (which can be much faster than Membase or Couchdb as the data is in process).

  31. Thanks for the great article.

    Assume that we have a hibernate query which returns a list of some entities. This query is db heavy (i.e 200ms) so we use a query cache region to store the results (or just the ids – it doesn’t matter).

    The problem is that if 2 threads request this almost at the same time they end up both hitting the database for the same query. What we’d like to do is to make the 2nd thread (which arrived later) to detect that a db load (and an eventual put in cache) is in progress and block (for a bit, e.g 500ms) until the 1st thread ends. That way we solve the multiple population of the same query cache region.

    Is this problem solvable with generic hibernate and ehcache configuration or, as should I resort to customized caching with direct ehcache handling like you mention in your last comment (March 15, 2011)?

  32. Alex says:

    @Ioannis: This is commonly referred to as the “thundering herd” problem. I have not worked on Hibernate caching for year+ now so I don’t recall what support exists in Hibernate for handling this issue.

    Ehcache has support for handling this issue via the SelfPopulatingCache cache decorator.

  33. Jflo says:

    Hey… should we expect a query cache full of NaturalId restricted queries to be invalidated after an insert into the corresponding table? I am seeing that in my implementation and feel like this is defeating the whole point of the exercise…

  34. Alex says:

    @Jflo: Did you read the article? This is covered in #1 under “More caching is better, right?”.

  35. jflo says:

    Looks like I answered my own issue, thanks. It turns out that if part of your @NaturalId is also a @ManyToOne, any inserts on the table will flush the cache. We were able to change the relation to an Enum, and the expected behavior resumed.

  36. Dave Chauhan says:

    Hi Alex, I’m quite impressive with your article and your understanding on EhCache and second level cache. I’ve not done much in hibernate caching so far, but there seems to be some up coming challenges for me in my current project. So, i’m trying to lend my hands as much as i can just by reading blogs, hibernate sites, and articles like the one posted by you. Nice one!

    Could you please recommend web sites, or provide me some documents you might be having on hibernate caching in a cluster based environment, appreciate, and thanks in advance.

  37. Cristian Ortiz says:

    Hi guys i am learning hibernate i found this thread very useful. i am doing a small web app based on JavaEE using hibernate my DB is only for Query purposes. i am using the hibernate 3 caches. (1 level, 2 level, and query caches(only by regions).). my DB is updated directly. without UI. how can i make to my caches (three of them.) expires every 30 days(my DB Data is updated every 30 days.). thanks a lot.

    [sorry by my poor english]
    best regards from Venezuela.

    chiperortiz@hotmail.com

  38. Cristian Ortiz says:

    Nobody? :(

  39. punny 2011 says:

    hi, I am new person in this area. I can not understand yet the difference between caching in Hibernate(each type) with buffering in database such as Oracle.I would be grateful If you can explain me this or let me know where I can find comparison between these two options.

  40. Shaik says:

    Hi,

    We are facing a problem with memcache. We are using hibernate-memcached-version1.5-SNAPSHOT and spymemcached-provider-version3.0.2.

    The following are the configuration

    persistence.xml
    —————

    dto
    —-

    @Cacheable(true)
    @org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_WRITE)

    In GenericDao we are setting query hints cacheable to true for loadAll().

    We are using loadAll() method to fetch all the records.
    Whenever we made a request the loadAll query is executing and also the queries based on id are executing.

    Please let me know we are missing anything.

  41. Shaik says:

    Hi,

    We are facing a problem with memcache. We are using hibernate-memcached-version1.5-SNAPSHOT and spymemcached-provider-version3.0.2.

    The following are the configuration

    persistence.xml
    —————

    !– Enable 2nd Level Cache –
    property name=”hibernate.cache.use_second_level_cache” value=”true”

    property name=”hibernate.cache.use_query_cache” value=”true”

    !– MemCache Configuration –

    property name=”hibernate.cache.region.factory_class” value=”com.googlecode.hibernate.memcached.MemcachedRegionFactory”

    property name=”hibernate.cache.use_minimal_puts” value=”true”

    property name=”hibernate.cache.use_structured_entries” value=”true”

    property name=”hibernate.memcached.servers” value=”${hibernate.memcached.server.host.port}”

    property name=”hibernate.memcached.cacheTimeSeconds” value=”1800″

    dto
    —-

    @Cacheable(true)
    @org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_WRITE)

    In GenericDao we are setting query hints cacheable to true for loadAll().

    We are using loadAll() method to fetch all the records.
    Whenever we made a request the loadAll query is executing and also the queries based on id are executing.

    Please let me know we are missing anything.

  42. Kelvin says:

    Hi Shaik,
    What do you meant by
    “Whenever we made a request the loadAll query is executing and also the queries based on id are executing.”
    ?