How not to structure database-backed web apps: performance bugs in the wild • Hacker News Board

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

#221

Post by projectramo » Thu, Jun 28, 2018, 2:53 PM UTC

Cthulhu_ wrote: post #216

projectramo wrote: post #185

The real joy of this article was the following: Some academics (Yang, Subramanian, Lu, Yan, Cheung) were able to produce massive improvements in about a dozen large, mature, battle tested open source projects using just a few lines of code. This should give hope to all those tepidly trying to get into open source. Just go and take a look at the dozens of open source projects in Django or whatever and you could improv…

What's intimidating to me is that to make a change like that, you first need to get the code, get it and the tests running, and actually understand what the code does - and whether your fix has the equivalent result or is subtly broken. They didn't just change a single line in an application, they wrote a huge benchmark suite and dug through miles of code to find issues like this. I've no clue how much time they spen…

You think so?

Maybe but the ORM lines should be pretty insulated. If you return the same results and only speed them up you should be okay.

To take an example, replacing any? with exists? doesn't seem to require testing everywhere in the project. I imagine that the average fix had lines like that.

I also suspect that the fixes were pareto distributed -- that a few fixes brought most of the benefit.

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

#222

Post by ravenstine » Thu, Jun 28, 2018, 2:53 PM UTC

knesek wrote: post #82

When I was inexperienced I feared ORMs because of the negative performance impacts I've read they could have. I constantly worried about what would happen if the amount of data increased and I hit ORM induced problem that I could not resolve without major rewrite of data access layer. However, whenever I've actually hit those problems in production, I found the similar thing the authors of the article did - ORM induc…

> Teams spending weeks exchanging SQL DB for No SQL DB because of unsolvable performance problem. When hitting the same problem with NoSQL DB, they find that addition of a simple index is solution in both cases

I don't get the idea that one has to pick either SQL or NoSQL, but a lot of people seem to think this way. Why not use both? The SQL portion can more or less be treated as a rich index of relationships, and the NoSQL portion can handle performance when it makes more sense for data to be embedded in parent documents.

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

#223

Post by mendelk » Thu, Jun 28, 2018, 2:56 PM UTC

Having worked many years both with ORMs and with "old-school" devs slinging raw SQL strings and everything in between, I've come to the conclusion that "ORMs don't kill [performance], people do". :)

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

#224

Post by cleong » Thu, Jun 28, 2018, 2:59 PM UTC

superasn wrote: post #28

This may be a little OT (OT because the points raised in the study are totally valid and mine is just a comment) but for small companies and solo developers ORM or whatever that gets the job done quickly is the way to go. Most sites and web apps never even break a 100k/day hit mark for which I believe inefficiency may not be the biggest issue. But wasting a month tryig to write native Sql queries can hurt your projec…

Depends on the application. In e-commerce and I believe many other industries as well will depend on fast responses for conversions. For example, a page that loads ~2 seconds will start to lose bounce rates/ conversions.

Of course full page cache is the first stopgap for this problem but you'll always have dynamic content.

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

#225

Post by [deleted] » Thu, Jun 28, 2018, 3:10 PM UTC

jlebrech wrote: post #70

what people need isn't an ORM but a DSL, if only there was a Domain Specific Language for Querying the database in a Structured manner.

[deleted]

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

#226

Post by adamzochowski » Thu, Jun 28, 2018, 3:11 PM UTC

psadauskas wrote: post #203

unilynx wrote: post #121

Pretty cool to read. We built a (currently proprietary) CMS with our own scripting language, and instead of going the ORM way, we merged basic SQL into the language itself. We did that mostly to eliminate sending raw strings to databases (and all the injection risks and complexity that comes with it) but it does allow a few extra optimisations because the compiler can look at both the query and the language using it.…

Of course it depends on the exact circumstances, but your query would probably be faster as `SELECT COUNT(*)` than the `LIMIT 1` you optimize to now. In fact, that’s one of the specific optimizations mentioned in the article.

count(*) ends up counting all records, reading one first row is much much faster than finding and counting all.

https://wiki.postgresql.org/wiki/Slow_Counting

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

#227

Post by dustingetz » Thu, Jun 28, 2018, 3:17 PM UTC

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)

CloudFront has this anti-pattern it drives me nuts!

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

#228

Post by dustingetz » Thu, Jun 28, 2018, 3:20 PM UTC

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…

The amazing thing about 1 is that modern immutable database patterns get it for free. When things like https://www.datomic.com/ get mainstream the world is gonna be in a very different place. Not as in today's apps get faster, but as in they are so much faster that new kinds of apps become possible that couldn't have been dreamed of before. My startup http://www.hyperfiddle.net is an experiment in this space

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

#229

Post by always_good » Thu, Jun 28, 2018, 3:32 PM UTC

thermodynthrway wrote: post #173

It's a bit high level to mention this, but it doesn't have the one query problem I see constantly. Paging using offset and limit. I swear every app I've worked on uses it somewhere. And it's horrifically inefficient 90% of the time.

Simply depends on pagination depth.

Unfortunately there aren't any solutions that generalize as well as LIMIT + OFFSET which is why we use it. In fact, other solutions usually take quit a bit of custom tailoring if you want Prev/Next and deep page jumping.

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

#230

Post by mnm1 » Thu, Jun 28, 2018, 3:33 PM UTC

blattimwind wrote: post #199

mnm1 wrote: post #60

Earlier quoted context omitted.

Even if you get 1 hit / day and that takes a long time to return, the ORM has failed your one customer. Inefficiency exists even at small scales with ORMs. If your developers don't know how to write SQL, let them learn. Or fire them if they won't.

> Inefficiency exists even at small scales with ORMs. In other words, you have no idea what you're talking about.

So the ORM taking many seconds for query overhead isn't a problem? This makes the web page served by this api many seconds slower. A second of slowness increases bounce rate by quite a bit and by two to three seconds, most visitors abandon the website. I guess that doesn't matter to you as you post a comment without substance and only an insult.