Cache for large read only database recommendation

Question

I am building site on with Spring, Hibernate and Mysql. The mysql database contains information on coordinates and locations etc, it is never updated only queried. The database contains 15000 rows of coordinates and 48000 rows of coordinate connections. Every time a request is processed, the application needs to read all these coordinates which is taking approx 3-4 seconds. I would like to set up a cache, to allow quick access to the data. I'm researching memcached at the moment, can you please advise if this would be my best option?

Answer 1

Have you looked at Hibernate sharding as well? Hibernate allows you to put different rows in different databases making look up easier. It has been available for the past 3 years. This is a good place to start.

Answer 2

Interesting question. I have very successfully used ehcache on a hibernate project but then I did not just have read only data.

Memcached was not originally designed for use with hibernate like contexts though I don't see any reason why it cannot be used. The only downside with memcached in your context is that you probably don't need the funky distributed cache ability since you'll probably have enough memory on one server to handle your load and thus you won't gain anything from the client server overhead of memcached.

I don't think you'll regret your decision to go with memcached. It's very widely used to cache web sites, I see no reason why you can't use it in your context.

Answer 3

While there is a CacheProvider implementation for Memcached, here are my thoughts:

• Memcached is not officially supported, the above CacheProvider is a third party implementation (this may not be a concern though).
• Memcached runs out-process, it's a client server cache and there is network and marshalling overhead for each request.
• A cache like EhCache runs in-process (in the same JVM) so accesses are faster (whether you use a memory store or a disk store) and there won't be any network communication in read-only mode between cluster nodes i.e. no overhead at all.
• I think that Ehcache would be significantly faster (see references below).

In other words, I would go for Ehcache.

References:

• Comparing Memcached and Ehcache Performance
• EhCache vs Memcached caching for JEE Applications