what is a good way to cache whole database using Spring hibernate and Ehcache

[Ankur Luthra]

The technology stack I am using is Spring + Hibernate + Ehcache. I need to serve the requests using Cache and not hit Database at all. The approach I am following is to cache the whole data at App Startup using queries such as getSession().createCriteria(.class).list() and then ask Hibernate for entities using session .get() call so that it uses second level cache to resolve stuff.

One challenge I see is my entity classes have a lot of Collection and Association Attributes(Many to Many with extra columns ,One to Many, One to One).

I have two approaches for such cache.

a)Keep all relationships as EAGER fetch.So the cache will be filled with the data with a Big query containing Left Outer Joins on App Startup. I am concerned that loading data eagerly may cause unnecessary long running query returning multiple rows.

b) Keep relationships as LAZY and iterate over all the rows and call .getSetOf to load related entities. I am concerned that I will be virtually iterating whole data at App startup and not sure if it's a good practice.

Since the associations in Hibernate are associated using composition and foreign keys and not stored as individual Ids hence loading of such associations/Collections seems to be an overhead.

I would have preferred having table data in different cache regions with their foreign key relationships as just Ids (not compositions/collections) .I would have cached all such entities in different regions and would have combined the results at run time by iterating over those regions.

Can anyone suggest what approach should I follow.If there is any alternate approach,do suggest.

Thanks in Advance.

P.S:- Posted the same question on SF . Need to make an urgent Decision hence posting duplicate question.
http://stackoverflow.com/questions/33068249/what-is-a-good-way-to-cache-whole-database-using-spring-hibernate-and-ehcache

[Claude Moore]

Ankur Luthra wrote: I need to serve the requests using Cache and not hit Database at all.

May I ask you: why ? Is it by design ? I'm not an expert of Ehcache, but generally speaking the main purpose of a cache is NOT to keep all data in memory, only more accessed ones. Depending on how much is your database large, your approach may even never be feasible.

[A.J. Côté]

Hello,

Strange requirement. Are your tables read-only? If not you will need to hit the database for writes anyway.

Anyway, for read-only tables, I would probably just copy the db to an in-memory database or use a simple one file based database engine with the file kept on a RAM drive.

You should need to fine tune the size of the memory cache available to hibernate/Ehcache anyway so, by making it bigger or big enough, most of the cacheable data would be cached anyway. Why should you insist on having *everything* in the cache?

[Ankur Luthra]

Claude Moore wrote:

Ankur Luthra wrote: I need to serve the requests using Cache and not hit Database at all.

May I ask you: why ? Is it by design ? I'm not an expert of Ehcache, but generally speaking the main purpose of a cache is NOT to keep all data in memory, only more accessed ones. Depending on how much is your database large, your approach may even never be feasible.

Hi,
Yup ,it's by design. Since this Db is mostly read only with very less updates/writes .Also it mostly contains static data hence the reason.

[Ankur Luthra]

A.J. Côté wrote:Hello,

Strange requirement. Are your tables read-only? If not you will need to hit the database for writes anyway.

Anyway, for read-only tables, I would probably just copy the db to an in-memory database or use a simple one file based database engine with the file kept on a RAM drive.

Could you please explain in detail above point?

A.J. Côté wrote:
You should need to fine tune the size of the memory cache available to hibernate/Ehcache anyway so, by making it bigger or big enough, most of the cacheable data would be cached anyway. Why should you insist on having *everything* in the cache?

The reason is it contains mostly static data and there is no point in hitting the Db again and again for the same data. Although data may be asked in different forms but it's mostly going to be static.
Updates are very few .Probably 10-12 updates in a day.I am relying on Ehcache to update the cache when I update any row in Db via session.saveOrUpdate call.

[A.J. Côté]

Well, I can elaborate on more complex solutions but I think you should simply try to raise the database cache first. Once a query is cached in the database cache, the query time can go from 2 seconds to 2 milliseconds.

For example, the default innodb cache size in mysql is something like 16MB and one of our prod database as a innodb cache setting of 1.5GB.

Just configure your database cache to the size of your static data and all your static data will be kept in memory anyway with blazing fast query times compared to using default cache size values.

You would only resort to using more complex caching solutions if you had 64 or 128 application servers hitting your database for static data but if you have 1 or a few application servers hitting your database, simply raising the memory available to your database cache could be sufficient to make you happy with the results!