In-memory cache architecture/technology?

manpreet Best Answer 3 years ago

I'm trying to work out of I can take advantage of a caching layer in my web application or not (and if so which technology).

Our web app has and internal and external component and I would like if possible to add an in-memory cache tier between the Web App and DB Tier for the public external component. We are suffering DB performance issues and I want to alleviate stress on the DB as much as possible (plus make our public facing site of the component lightening fast).

The external component offers a location search facility based on a post code. E.g enter post code for an area and you get 50 results back each time (the data is relatively stale) the DB might change (new record added 1 per day) so I was thinking if a cache tier was possible then I could invalidate the cache nightly and then load it again (as opposed to the cache aside pattern).

Question:

1. Based on my overview above e.g. postcode mapping to multiple records (JSON or serializable objects) can I use a cache tier to store the data in-memory (total size of data ~100 MG, heaps of RAM free) and retrieve multiple records back per post code based on a caching technology "key-value data stores"?
2. If number 1 above is feasible, what caching technology, we are using a PHP front end, Zend server has an im-memory cache but it doesn't look mature, I would prefer Redis over Memcached for caching, thoughts?
3. If pre-loading the cache nightly is not achievable, thoughts on a better approach to utilise the cache?
4. If in-memory caching is not achievable at all (based on my requirement) then should I look at opmtiising the DB (it's SQL Server), e.g. loading the search table into SQL cache on SQL Server start-up?
5. Other, something I'm missing?

Thanks in advance, all comments welcome!

Cheers,