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

21–30 of 319 posts

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

#21

Post by TekMol »

Give me an O! Give me an R! Give me an M!

What does that spell? SLOW PERFORMANCE!

Todays programmers dont understand data. They understand frameworks. To find the nr of all cars that are out of insurance they write:

    10 Nr=0
    20 Hey framework, give me all cars!
    Framework: Ok, here are 8001093 business objects representing all the cars in our DB. Each has all the attributes the car has. Color, mileage etc.
    30 Thanks!
    40 Foreach Cars as Car
    50 If Car->insurance_end_date
I see variants of this everywhere. And the performance impact is just untoppable. It's often several million times slower then a simple sql query.

But beefy hardware with lots of ram and memory takes care of it. 'Our software is enterprise grade, so of course it cannot run on commodity hardware'.

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

#22

Post by olavk »

siscia wrote: post #10

I never understood why I don't enjoy working with ORM but reading this article make it clearer for me. They blend the distinction between working in memory vs accessing the db. From one side it is very convenient, however I really feel that such performance sensitive operation should be carefully considered and that most SQL should be written by hand.

Writing SQL by hand does not solve typical performance problems like n+1 queries. If on the other hand you know enough to avoid n+1 queries, then you can also avoid them when using an ORM, and save a lot of work.

If you like writing SQL by hand, by all means do so, but you will not automatically get better performance by handwritten SQL as compared to ORM generated SQL.

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

#23

Post by latch »

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 (message queues) and refresh in the background. Caching shouldn't be a compromise between freshness and performance. Have both!

2 - Generate message/payloads/views asynchronously in background workers and have your synchronous path as streamlined as possible (select 1 column from 1 table with indexes filters). Avoid serialization. Precaculate and denormalize. Any personalization or truly dynamic content can be done: 1 - By having the client make separate requests for that data 2 - Merging the data into the payload with some composition. 3 - Glueing bytes together (easier/safer with protocol buffers than json)

Do things asynchronously. Use message queues / streams.

Beyond that, GC becomes noticeable. For example, Go's net/http used to (might still) allocate much more than other 3rd party options.

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

#24

Post by mping »

My 2 cents are: if you don't know what queries your orm API is generating, you shouldn't use one. I mean, sometimes there are bugs here and there, but people should know the methods they call.

It's the equivalent of making a rest call and complaining about latency. Yeah, it's a method, why is it taking so long?

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

#25

Post by radicalbyte »

mr__y wrote: post #12
mozumder wrote: post #3

Its always amazing to see web pages take 2-4 seconds of back-end processing. On a modern CPU, that's about 10 billion instructions. 10 billion instructions to send a few kilobytes of data. There's such a waste in back-end server design. If you're measuring response times are in seconds, and not in microseconds, you're doing something seriously wrong.

on the other hand there are savings in development time, which might or might not be more expensive than cpu cycles in question. Especially for smaller scale projects. I'm not saying that 4 seconds to load a page is something to be happy about, but sometimes it's cheaper to "waste" cpu cycles than to spend more time developing faster solution.

It only saves development time for the first version in my experience unless it's being used by an highly capable team.

The months lost to the clueless developers massaging their horrible ORM code dominates in time.

In the long term properly factored code wins; and lightweight ORMs have a place. Heavy ORMs (Hibernate, old Entity Framework) should only be used if you absolutely must have specific features and are incapable of building that into a more logical place in your code.

Use a light weight ORM for simple CRUD actions (one with a small dependency graph). For performance critical create/update operations use stored procedures (just be careful with complex business logic; if you implement it, remember to test it). Use light weight ORM for mapping efficient SQL views to api/frontend. Use a workflow/async/job processing for anything which has to be long running or crosses system bounds (think credit card processing).

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

#26

Post by pbowyer »

mr__y wrote: post #12
mozumder wrote: post #3

Its always amazing to see web pages take 2-4 seconds of back-end processing. On a modern CPU, that's about 10 billion instructions. 10 billion instructions to send a few kilobytes of data. There's such a waste in back-end server design. If you're measuring response times are in seconds, and not in microseconds, you're doing something seriously wrong.

on the other hand there are savings in development time, which might or might not be more expensive than cpu cycles in question. Especially for smaller scale projects. I'm not saying that 4 seconds to load a page is something to be happy about, but sometimes it's cheaper to "waste" cpu cycles than to spend more time developing faster solution.

This. I am building an app and the listing screen is taking 4-5 seconds to fetch and display the database records. Why? The SQL query is complicated (7 joins due to filtering and permission restrictions), and on its own takes 1.7s to run. The ORM to enable pagination [0] runs this query twice plus a third query, adding up to over 3s. Plus other queries and rendering output (all times in debug mode).

Does it matter? No - this screen will be accessed 30 times a day max. Better to have it work inefficiently and spend time on other tasks, than make it efficient - however much I want to.

0. https://www.doctrine-project.org/projects/doctrine-orm/en/2....

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

#27

Post by sfvisser »

TekMol wrote: post #21

Give me an O! Give me an R! Give me an M! What does that spell? SLOW PERFORMANCE! Todays programmers dont understand data. They understand frameworks. To find the nr of all cars that are out of insurance they write: 10 Nr=0 20 Hey framework, give me all cars! Framework: Ok, here are 8001093 business objects representing all the cars in our DB. Each has all the attributes the car has. Color, mileage etc. 30 Thanks! 40…

People do this because it’s easier, takes less time, and is within the paradigm their head is already in all day.

We should fix our abstractions instead of telling people they don’t understand data. The latter is fine every so often, but doesn’t scale and isn’t even true most of the time.

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

#28

Post by superasn »

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 project a lot more.

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

#29

Post by doesnt_know »

FooBarWidget wrote: post #17
crocal wrote: post #4

... Which begs the question: what good is an ORM if it does not prevent by design such issues? Here, we are essentially saying users of ORM must also have in their mind the SQL version. Or call an expert after the mess is done :/...

I find this question to be very weird. I have never seen ORMs as tools for completely abstracting away the database. I have always seen them as convenience APIs for using the database. They exist to make your code shorter, less repetitive, more readable, easier to reason about, and more maintainable; not to make you forget about the database altogether. I also don't see ORMs as exclusive. It's fine to use ORMs for 95…

> I have never seen ORMs as tools for completely abstracting away the database.

Some are advertised that way. Entity Framework Code First, Migrations etc.

At our place, our DB dev team is larger than our api team, which in turn collectively dwarfs our front end teams. ORMs in this sort of environment have never really been given a chance, but I feel like it would be easy to justify them in many situations...

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

#30

Post by ianamartin »

The only big problem with ORMs is when developers are allowed to use them to construct database schemata.

As long as you have a not-crazy person in charge of the initial design, you can allow—and even encourage—developers to make versioned changes to the database.

If you let us start from scratch with ORMs, it will suck for everyone.

There’s a fundamental mismatch between what developers think of as objects and how relational designers think of tables.

ORMs can smooth that over. But nothinking can undo the wrong-headed-ness of a developer thinking of a relational dB as an object store.

Post reply on HN