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

231–240 of 319 posts

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

#231

Post by mnm1 »

olavk wrote: post #65
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 ORMs are not inherently inefficient. And in my time I have seen plenty of n+1 style queries written with raw SQL.

Lazy loading often fetches too little and eager loading too much. Most orms select all columns in all tables regardless of need. The query translation and hydration overhead of transforming the data into objects even in a fast language are always going to be problems, it's just a matter of how big, over a system without the orm. Since orms are superfluous, every line of code in them adds unnecessary overhead. They are indeed inherently inefficient by definition.

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

#232

Post by falsedan »

mnm1 wrote: post #230
blattimwind wrote: post #199

Earlier quoted context omitted.

> 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.

I think you have bigger fish to fry than site performance if you're getting 1 hit per day.

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

#233

Post by cleong »

Nokinside wrote: post #111
bodas wrote: post #96

Earlier quoted context omitted.

That's a good thing, I think. Programmers should have a good understanding of general algorithms but it's up to the frameworks themselves to reduce the need to be "mastered". I don't want to spend time mastering a particular framework because that knowledge is not portable across languages.

I suspect it's good thing also. It may also be the only way to scale programming into larger groups. You can differentiate the workforce. Programming by copy pasting example code withing frameworks may allow skipping the requirement to understand general algorithms. Less required expertise means less pay and cheaper products. You hire 10 low-paid easily replaceable code monkeys who slap together pieces of software fr…

I think part of this is the industry and part of it is the technology. Entire languages in the web are basically meant to be glue. Embedded usually has different requirements from Web, memory and performance are real problems you run into fast if you don't know what your doing. Where in web, memory and performance are a problem of economics, everyone is being served from a cluster that can be scaled if the software is shoddy.

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

#234

Post by ndrr »

tootie wrote: post #215
mnutt wrote: post #212

Earlier quoted context omitted.

It’s susceptible to Thundering Herd whereby more requests come in for the same cache key before the initial computation is finished, and so you end up with lots of cache misses. The fix is usually to lock the cache key and have subsequent requests wait on the original computation but it’s a bit more complex to code.

I've heard it called Cache Stampede. Any decent framework for memoizing method calls would cover this case though.

Do you have an example of one? I'm curious what features they provide over ad hoc memoization mechanics.

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

#235

Post by mnm1 »

thraxil wrote: post #99
mnm1 wrote: post #59

ORMs are really only useful for throwaway projects and beginners. I have yet to see one without serious downsides in both performance and speed of development, something that they are touted to improve but actually make worse. The ORM I'm stuck with now (Doctrine2) adds a 10x overhead to queries. For the most part, we don't even bother optimizing queries in such situations because why waste time on something that cou…

Don't know much about Doctrine2, but that sounds pretty terrible. I'm sorry you are forced to work with something so inefficient. I've been building apps with Django and Django's ORM for the last 10 years and found essentially zero overhead in most cases. Every once in a while there's a slow page, I open up the debug toolbar which shows me every SQL query that was used to generate the page in a nice waterfall diagram…

PHP's concurrency model poses unique challenges to orms but in this case, it's just slow hydration that is the main culprit and that can happen in any language, especially interpreted ones. It's no longer just fetching data and displaying it but fetching, processing (in a slow interpreted language) and then displaying it.

That is one issue. I find the interface the orm provides to also be inferior in every way compared to sql so I just don't get why people would add something that provides even the tiny overhead you describe to get an inferior interface for dealing with the database. Some orms I've used create their own sql like language, so now there is something new to learn (that's useless outside the orm) that provides nothing over actual sql but has all the drawbacks of the orm and sql. Even working with simple objects and queries is more difficult as I hardly know what the orm is doing. When I do look at what it generates, it's always been atrocious in the orms I've used. In addition, trying to wrap my mind around the concepts the orm builds poorly on top of the relational database is truly angering. Taking a wonderful interface to a database and turning it into a set of nonsensical object relationships actually makes it more difficult and more time consuming to write the app with the orm. This is unnecessary complexity for complexity's sake, something proponents of simplicity avoid.

So my experience has not only been that of horrible performance but also that of a horrible ui that slows development to a crawl and creates apps that need to be rewritten to work properly, the exact opposite of what orm proponents claim. One of the main reasons I want to move to a functional language is that orms don't exist there.

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

#236

Post by pjungwir »

> Inefficient Rendering (IR). This one was a surprise to me

He's talking about the common Rails pattern of rendering a small Haml/ERB partial over & over in a loop. I've noticed big perf hits from this before, but never completely understood why. Rendering the exact same result directly in the loop body, without calling a second partial, gives a big speedup.

There is an extensive discussion here:

https://softwareengineering.stackexchange.com/questions/1571...

I would love to have some better understanding around this, although it sounds like no one has a clear idea of the cause.

In development, I've noticed that Rails re-reads the partial file off disk every iteration of the loop. I don't know if that happens in production too, but if so it would explain a lot.

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

#237

Post by mnm1 »

pbowyer wrote: post #127
thraxil wrote: post #99

Earlier quoted context omitted.

Don't know much about Doctrine2, but that sounds pretty terrible. I'm sorry you are forced to work with something so inefficient. I've been building apps with Django and Django's ORM for the last 10 years and found essentially zero overhead in most cases. Every once in a while there's a slow page, I open up the debug toolbar which shows me every SQL query that was used to generate the page in a nice waterfall diagram…

I use Doctrine2 in a number of applications. I have a love/hate relationship with it (mostly through fitting it to legacy database schemas) but it's not slow to put/retrieve data from the database. What's slower is the object mapping (hydration); and if you map database rows and relations into objects yourself then your overhead is going to be similar, just labelled as 'application' overhead rather than 'ORM' overhea…

The only way manual processing of the data from the db could take as long as doctrine hydration is if it's written poorly. An app developer who knows how to write apps properly would never do hydration or any of the stupidity doctrine does. Why would one map database rows into objects in the first place? I've rewritten huge swaths of one of our doctrine apps getting a roughly 5x to 10x improvement in speed and I left a lot of optimizations on the table while still providing a general purpose serializer that uses doctrine's meta data behind the scenes.

As for dbal, I think the overhead is in statement preparation and parameter expansion (for array params) and it can be avoided by using the native functions, though that overhead was never 10x for me.

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

#238

Post by mnm1 »

falsedan wrote: post #232
mnm1 wrote: post #230

Earlier quoted context omitted.

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.

I think you have bigger fish to fry than site performance if you're getting 1 hit per day.

I think you completely missed the point here. If this happens for one customer, it happens for all of them and no amount of hardware thrown at the problem is going to solve this because it's already running on the best hardware available.

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

#239

Post by raarts »

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…

‘Unsolvable performance problems’ that could have been fixed by adding an index??

How did these team members pass their job interviews?

By practicing algorithm puzzles?

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

#240

Post by thermodynthrway »

vorpalhex wrote: post #175
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.

Can you elaborate on the issues with that? I've used that pattern and haven't found any major woes (yet), but also don't usually paginate complicated queries.

DB has to calculate every record from 0-> offset . A user can frequently crash a server by just going to page 100+ in a big list since the database has to load all that data.

Easiest way to prevent this is add enough filtering options that it doesn't inconvenience users to limit depth to page 10 or something

Post reply on HN