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

141–150 of 319 posts

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

#141

Post by EGreg »

olavk wrote: post #128
EGreg wrote: post #123

Earlier quoted context omitted.

No, that's not true if you're building a huge site. Google has been avoiding joins as early as 2005. Joins were good for the smaller websites, but they don't scale. By avoiding joins, you have shared-nothing models that can be partitioned horizontally aka sharding . Now true, the latest and greatest databases such as CockroachDB go out of their way to try to do joins for you across partitions, even in an ACID manner,…

So you have to use sharding because your databases have limited capacity. But you can just join in the app, because the app have unlimited memory?

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 joins are just one way to achieve this, which can be made atomic in the ACID sense.

However, as you scale up your website, eg with 100,000,000 users, it would be silly to do massive joins. Google even says this in their docs now, for BigQuery.

Instead, design your systems from the beginnig to be as parallel as possible, if you think they will scale.

Look at the problems with Ethereum for example. Or Twitter fail whales of the past.

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

#142

Post by GrumpyNl »

l0b0 wrote: post #95
manigandham wrote: post #85

Earlier quoted context omitted.

Not this again. ORMs are tools, basically dynamic code generators that run SQL and map the results to in-memory objects, and vice-versa. Some are simplistic and others are incredibly advanced, and the code itself is usually faster than your own sql->objects logic that you would write otherwise. The issues with performance are almost always with the way the tool is used, like choosing a bad algorithms or the wrong dat…

Not this again. ORMs are some of the most impenetrable code you'll ever see. Understanding the performance characteristics of a CRUD application using SQL is vastly simpler than trying to puzzle out the arcane ways in which ORMs decide to throw up garbage once you add one more thing to the mix.

Dont get me started over the abuse of active record.

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

#143

Post by jrs95 »

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…

Anyone who does this is just bad. I’d be surprised if they could do SQL without an ORM either. I really have to question the value of a CS degree when there are all of these people who have them and still don’t seem to realize that running database queries in a loop is a bad idea. And they’ll still get hired because they’ve successfully memorized some data structure trivia that they’ll almost certainly never use.

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

#144

Post by GrumpyNl »

EGreg wrote: post #123
olavk wrote: post #120

Earlier quoted context omitted.

I was with you some of the way but "making you avoid doing joins in the database" made me drop my monocle. You want joins in the database, they are designed for joins. Moving joins to the client will kill performance and scalability. And any sane ORM will perform the joins in the database by default.

No, that's not true if you're building a huge site. Google has been avoiding joins as early as 2005. Joins were good for the smaller websites, but they don't scale. By avoiding joins, you have shared-nothing models that can be partitioned horizontally aka sharding . Now true, the latest and greatest databases such as CockroachDB go out of their way to try to do joins for you across partitions, even in an ACID manner,…

We are not all google, im extremely happy with my joins and perfomance of Mysql.

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

#145

Post by Arnt »

olavk wrote: post #124
Arnt wrote: post #110

Earlier quoted context omitted.

Just a UI issue. Compilers will warn you if you do stupid common mistakes. Not all mistakes, but many of the stupid common ones. If you make stupid common mistakes with an ORM, why doesn't the ORM warn you?

They require rather sophisticated analysis to detect. A compiler will detect trivial errors, but will not flag if you are using an O(n) algorithm when an O(1) could be used. Not yet, anyway.

I read the paper. A third, maybe half of the stuff the ORMs didn't detect looks simple to detect.

(Greybeards may remember how perl's -W switch switch suddenly detected a frightful amount of performance problems using mostly simple tests. Almost 20 years ago now.)

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

#146

Post by qaq »

guipsp wrote: post #116
qaq wrote: post #93

The problem with ORMs is they have to cater to the lowest common feature-set of the supported RDBMS.

This is not the case for pretty much any modern ORM

Sure it is show me the ORM that will support 2p commit, window functions, CTEs, JSON operators in PG (just a random set of features)

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

#147

Post by ssijak »

collyw wrote: post #126
ssijak wrote: post #55

ORM for saving objects and very simple queries. Writing SQL or using something like JOOQ in Java to write type-safe SQL for everything else.

I disagree. I have used Django extensively. The ORM handles raw SQL queries, but which is great when you need something beyond the capabilities of ORM, but you loose a lot as well when you go that route. Pagination and sorting are pretty easy additions when you retrieve data using the ORM in the standard way, and now you need to add extra code to handle those specifically. I don't think you can use the Django admin w…

What makes you think Django ORM is good ORM? What is bad about adding "extra code" if that code is doing a useful and correct thing? And I wonder why people find Django Admin useful for anything than simple CRUD apps. Writing code so it can go along with django admin is a strange way of thinking, there should be other priorities first.

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

#148

Post by danielbarla »

dagw wrote: post #47
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…

That has nothing to do with ORMs. I've seen plenty of people write code like: SELECT * FROM DATA for each r in result if r.x > 12 do_something(r.y) Exactly the same thing without any ORM. If anything ORMs should improve performance for novices since it makes it a lot easier for people to writer better queries that run on the database.

I agree that this has more to do with the developer than the ORM. I once watched someone write a foreach loop around a set of millions of records, to update their "BatchDate" property to the current date / time. To add insult to injury, to get the current time, they issued a separate query to the DB, in the form of "SELECT GETDATE()", then injected the result - as a string - into the update statement.

To be fair though, ORMs do move us one layer further away from the actual DB, so people tend to be even less likely to understand how to write ORM calls which result in performant SQL calls.

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

#149

Post by olavk »

Arnt wrote: post #145
olavk wrote: post #124

Earlier quoted context omitted.

They require rather sophisticated analysis to detect. A compiler will detect trivial errors, but will not flag if you are using an O(n) algorithm when an O(1) could be used. Not yet, anyway.

I read the paper. A third, maybe half of the stuff the ORMs didn't detect looks simple to detect. (Greybeards may remember how perl's -W switch switch suddenly detected a frightful amount of performance problems using mostly simple tests. Almost 20 years ago now.)

Maybe you are on to something. All the major ORM's are open source (AFAIK) so if you made a write-up of the common issues and how you suggest they could be detected by the ORM, I think it would be very well received.

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

#150

Post by gerbilly »

vidarh wrote: post #100
manigandham wrote: post #85

Earlier quoted context omitted.

Not this again. ORMs are tools, basically dynamic code generators that run SQL and map the results to in-memory objects, and vice-versa. Some are simplistic and others are incredibly advanced, and the code itself is usually faster than your own sql->objects logic that you would write otherwise. The issues with performance are almost always with the way the tool is used, like choosing a bad algorithms or the wrong dat…

It is the developer, but ORMs are so controversial in part because they often obscure that you're doing something crazily ineffective in ways that makes developers that don't understand the abstraction fail to see that they're doing something obviously wrong. It's more stark that you're doing something crazy if you do a SELECT, instantiate objects from each returned row, then apply a filtering rule to that object, th…

>[...] ORMs are so controversial in part because they often obscure that you're doing something crazily ineffective ...

Then just turn on detailed SQL logging while you are coding and keep an eye on the queries generated by your ORM calls.

If you do this methodically for all your DAOs, then you should spot performance problems early when they can still be easily fixed.

Post reply on HN