Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

How not to structure database-backed web apps: performance bugs in the wild

blog.acolyer.org

Post reply on HN
Print view View original

301–310 of 319 posts

Re: How not to structure database-backed web apps: performance bugs in the wild

#301

Post by EGreg »

olavk wrote: post #151
EGreg wrote: post #141

Earlier quoted context omitted.

The app doesn’t hold the entire database at a time. The app simply does the following: 1) Get the root record(s) from id(s) 2) See what related records it needs, combine them into a list of ids, partition list by shard 3) Ask each shard for the corresponding records 4) Repeat from 2 if necessary 5) Return this whole tree / graph to the user Graph databases can do this in O(1) instead of O(log N) lookups. Relational j…

A graph database basically caches related entities with the root entity, so lookups are fast when the query follows the paths of the graph. The price is that any other query is extremely costly. But if it works for your use case, more power to you. But not really relevant in a discussion about ORMs - Object Relational Mappers. The relational model was designed to address the limitations of the network/graph database,…

Most applications don’t need that. But if they do, just have indexes!

Re: How not to structure database-backed web apps: performance bugs in the wild

#302

Post by candu »

matwood wrote: post #179
candu wrote: post #162

Earlier quoted context omitted.

I've also seen: SELECT * FROM DATA WHERE x > 12 where there's no corresponding index. There's also the infamous N+1 query pattern: // get list of ids for each id in ids r = SELECT * FROM DATA WHERE id=:id if r.x > 12 do_something(r.y) IMHO, both are signs of not fully understanding what the database does for you. In the same vein as the "learn JS before frameworks" argument, I'd argue devs should at least learn and u…

> I'd argue devs should at least learn and understand SQL, indices, etc. This was all dev 101 when I was coming up (yikes, almost 20 years ago now).

Yeah, I wish it were common knowledge...although, if you ask 10 different devs, you'll get 10 different answers on what "ought" to be included in a basic CS / developer education.

(You don't know the minutiae of pointer arithmetic!? You're not familiar with Docker, Vagrant, or AWS Lambda!? You can't construct a sed / awk one-liner with your eyes taped shut!? You don't know about concurrent skip lists!? You've never completed SICP or read Purely Functional Data Structures!? And so on.)

Re: How not to structure database-backed web apps: performance bugs in the wild

#303

Post by matwood »

lukaseder wrote: post #281
matwood wrote: post #178

Earlier quoted context omitted.

jOOQ for Java does something similar. It's so good, I'm likely stuck with Java on the backend until I stop using RDBMs.

Hey Mat, thanks for spreading the love. Hope you never stop using RDBMS! Fancy a couple of jOOQ stickers? :)

I wouldn't use the stickers, so they would be wasted. Just keep making jOOQ a great to use library. :)

And while I have you here...your blog posts are always great and very informative. It's interesting to see the differences between RDBMSs (I really only have experience with MSSQL/MYSQL some PG), and how they do one thing or another.

Re: How not to structure database-backed web apps: performance bugs in the wild

#305

Post by rapind »

blattimwind wrote: post #196
latch wrote: post #23

I've worked on moderately busy backend platforms (~10K-20k rps handled on a ~4 e5-2650 and aiming for 5ms 95p response times). It greatly depends on what you're doing, but for the majority of systems which are read heavy (and that most certainly includes "dynamic" sites like Amazon or Wikipedia), I hold to two major beliefs: 1 - Have very long TTLs on your internal cache servers with a way to proactively purge (messa…

0 - Caching antipattern 101: key = calculate_cache_key() if not cache.has(key): data = expensive_calculation() cache.store(key, data) else: data = cache.get(key)

Varnish + Grace period == solved

Re: How not to structure database-backed web apps: performance bugs in the wild

#306

Post by rapind »

brightball wrote: post #210
memset wrote: post #207

Earlier quoted context omitted.

This is interesting because this is generally how I implement caching! What would pseudocode look like for a non-antipattern?

Depends on what you are doing. To frame it another way, what happens if 100 requests come in at the same time for that expensive value when it isn’t in the cache yet? The expensive calculation will be run 100 times at the same time. Ideally, you’d rather refresh the cache value in the background once and never allow duplicate requests for it from the web. If you’re running a language that makes it easier to deduplica…

CacheEx sounds interesting. Basically a debounce. Pretty sure you could solve this outside of the application layer with Varnish but that depends on how the view is composed. I prefer using a grace / stale period but that only works if it's acceptable to return stale data during computation instead of queuing it up.

Re: How not to structure database-backed web apps: performance bugs in the wild

#307

Post by manigandham »

coldtea wrote: post #249
manigandham wrote: post #213

Earlier quoted context omitted.

> I don't believe in choices ...ok, people still make choices though, you're not controlling their minds. Perhaps educate your workforce so they make the right decisions by themselves, it's more effective and takes less effort than trying to coerce them through generalizations.

> ...ok, people still make choices though, you're not controlling their minds. No, but as a PM you can dictate they don't use an ORM.

Try reading the 2nd part of my comment.

Re: How not to structure database-backed web apps: performance bugs in the wild

#308

Post by brightball »

rapind wrote: post #306
brightball wrote: post #210

Earlier quoted context omitted.

Depends on what you are doing. To frame it another way, what happens if 100 requests come in at the same time for that expensive value when it isn’t in the cache yet? The expensive calculation will be run 100 times at the same time. Ideally, you’d rather refresh the cache value in the background once and never allow duplicate requests for it from the web. If you’re running a language that makes it easier to deduplica…

CacheEx sounds interesting. Basically a debounce. Pretty sure you could solve this outside of the application layer with Varnish but that depends on how the view is composed. I prefer using a grace / stale period but that only works if it's acceptable to return stale data during computation instead of queuing it up.

Well CacheEx is just using functionality that comes naturally on the BEAM here. This is one of the reasons that a lot of CDN's like Cloudfront are written in Erlang.

CacheEx gets the ability to check for the presence of the cache key in Erlang Term Storage (ETS) which is basically an in-memory cache. If the key is present, it just returns the value.

If it's not, it sends checks to see if a process exists with the cache key name. If there isn't one, it creates one to request the resource.

For any other requests that come in until the value has been created, they will be directed to the process that is getting the value.

When the process that was calculating things comes back, it will save the value to ETS and then also send it back to all of the queued processes that have been waiting for it.

In the case of Varnish, you'd be expecting it to send back the entire completed view...HTML and all. This isn't something that you need to worry about with Elixir because the view is never actually rendered in the application. It's broken down into pieces that are never duplicated in memory and then replayed directly to the socket...meaning you really only ever need to cache expensive data and not what it's transformed into.

Here's a good read on why this view layer is so fast, if you're curious. Most people report shock that their uncached performance with Elixir and Phoenix is on par with statically cached HTML. I didn't believe it until I saw it myself.

https://www.bignerdranch.com/blog/elixir-and-io-lists-part-2...

Re: How not to structure database-backed web apps: performance bugs in the wild

#309

Post by lukaseder »

matwood wrote: post #303
lukaseder wrote: post #281

Earlier quoted context omitted.

Hey Mat, thanks for spreading the love. Hope you never stop using RDBMS! Fancy a couple of jOOQ stickers? :)

I wouldn't use the stickers, so they would be wasted. Just keep making jOOQ a great to use library. :) And while I have you here...your blog posts are always great and very informative. It's interesting to see the differences between RDBMSs (I really only have experience with MSSQL/MYSQL some PG), and how they do one thing or another.

> Just keep making jOOQ a great to use library. :)

Will do! Thanks for the nice words about the blog.

Re: How not to structure database-backed web apps: performance bugs in the wild

#310

Post by mikermcneil »

spraak wrote: post #261
olavk wrote: post #202

Earlier quoted context omitted.

What ORM's does not support joins?

Waterline (for Sails.js) is horrible. Last I looked there's no way to do a kind of `deepPopulate` (requiring an inner join)

Sorry you feel that way! The rest of the team and I work hard on it as often as we can. But deep populate is not a feature we’ll be adding— Sails and Waterline are opinionated, and I don’t feel that the performance penalty and potential issues are worth it— instead, we recommend using a native SQL query when you need to do a custom join.

Built in support for WHERE subqueries, on the other hand, is on our roadmap. Currently working to finish core MSSQL support first though. Hope that helps!

Post reply on HN