Hacker News Reader

Comment by maxk42 | Hacker News Reader

This is particularly exasperating for me. I can't tell you how many times in my professional career I've ended up speeding up systems by removing two or three layers of improperly-implemented "caching" and using good ol' MySQL and a basic understanding of algorithmic time complexity to simplify things.

View on HN

Me too. I've seen a few systems that replaced their simple request requiring 10,000 queries "optimized" by requiring 10,000 cache lookups when they should have just added some joins. The bottleneck is the network latency, not the database. The worst I've seen is an nHibernate cache stored in a session variable, half the database was being serialized/deserialized on every http request. Fortunately that was a small database.

Even with in memory caches I've seen systems grind to a halt by death of a thousand cuts, dictionary based entity attribute systems where each attribute is looked up individually. There seems to be a mentality that constant lookup == free lookup and devs don't seem to realize that constant * $bignumber == $biggerNumber. Caching shouldn't be granular.

Obligatory latency numbers every programmer should know: https://gist.github.com/jboner/2841832

It very much depends. I've seen too many cases when business logic was moved to the database layer, with absolutely disastrous effects. Scaling application backends doing map-reduce on a pre-filtered datasets is way easier than scaling database to handle five-way joins. Yet,I've seen opposing cases too, when rdbms was used as a huge key-value storage , and was pushing hundreds of thousands of rows to tens of backends, sucking it's bandwidth dry.

Having worked with Hibernate recently on a smallish database (that isn't expected to grow too big), my take away is to minimize the network trips. If you have to check something for a list/set of things and all of that can be done from, say, two not so big tables, fetch the superset of records via a join with as much criteria pushed to predicates as efficiently possible and handle rest of the logic in application code. It is so much more quicker. Of course, like most things in software, conditions apply like network latency/total no. of records being fetched by the query/potential for data to suddenly grow etc.

Not to cache simple trips to the database is pretty uncontroversial, the are more controversial cases elsewhere.

Say you consume a Restful API to hidrate order data in another system. Do you fetch orders as:

  /orders?ids=id-1,id-2,id-3

or separate calls to

  /orders/id-x

which can be cached and retrieved by id as a memoized function?

Well if you had to pick only one the second is probably better, but the best would be abstracting away order fetching in application code to always fetch single orders and behind the scenes looking up the cache for singles and pooling all the misses into a single request to the plural endpoint.

If this is a common occurrence the former would almost always be better, the second would be absolutely glacial.

> but the best would be abstracting away order fetching in application code to always fetch single orders and behind the scenes looking up the cache for singles and pooling all the misses into a single request to the plural endpoint.

Unless a significant amount of requests have 100% cache hits then I doubt a local cache will make much of a difference at all, all it's saving is a bit of bandwidth.

I had worked on an application that saw less than 100 writes per minute and about 1k reads per min and used a caching layer in front of the DB. Not only was the cache actually slowing us down, it was also inconsistent with the DB. Can't even begin to express the amount of lost dev time and productivity. We couldn't get rid of it, because someone above was convinced that we would need it to scale in the future.

There are only two hard things in computer science: naming things, cache invalidation, and off-by-one errors.

I'm trying to never implement any caching if I can help it. The database itself does caching already as well.

And if you DO need caching, keep your hands off of the application; add a cache layer in front, or between the application and the database. But don't invent it yourself.

  And if you DO need caching, keep your hands off of the application; add a cache layer in front, or between the application and the database. But don't invent it yourself.

so, redis?

My tech lead once speed up our system by just turning off Memcached. :-)

My problem was people using ORM frameworks and having very little knowledge of the database they were using. Slow as molasses, difficult to figure out what is going on because of many layers of extra "stuff".

Same boat. Basically every day.

Reply on news.ycombinator.com