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

#121

Post by unilynx » Thu, Jun 28, 2018, 10:21 AM UTC

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.

So if I do our equivalent of the ORM API Misuse case:

  IF(RecordExists(SELECT * FROM schema.variants WHERE track_inventory = 0))
  {
    ... 
  }

(RecordExists is a function that only checks whether the query returned something, and has been marked that way) the compiler will already reduce this to:

  IF(RecordExists(SELECT FROM schema.variants WHERE track_inventory = 0 LIMIT 1))
  {
    ... 
  }

And likewise a function that selects all columns from a database and returns only one field, has the select reduced to only selecting that one column.

The drawback, of course, is that any SQL features of the underlying database not exposed by the scripting language, are unreachable unless you fallback to sending raw query strings again.

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

#122

Post by AsyncAwait » Thu, Jun 28, 2018, 10:27 AM UTC

strken wrote: post #77

grey-area wrote: post #54

Earlier quoted context omitted.

I use an ORM. I don't perform queries like that because the ORM makes it easy to build complex queries, joins, limiting the data returned etc, then execute them in one query, it's a convenience, not a straightjacket. The problem is not ORMs, the problem is just people not thinking about the resources their query over some data takes, and trying to do things in memory that are better done in the database (as in your e…

One of the biggest problems with ActiveRecord (the ORM in the article) is that it uses extremely obtuse names for extremely common methods. There are three different methods for finding how many things meet a given criteria[0], and none of them do the same thing. There are three different methods for loading data from an associated table[1], and none of them do the same thing either. None of the method names explain…

This is mostly due to its popularity and various people needing different use cases, the long-time maintainer is now working on a mach cleaner ORM for Rust, called Diesel[1].

1 - http://diesel.rs

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

#123

Post by EGreg » Thu, Jun 28, 2018, 10:28 AM UTC

olavk wrote: post #120

EGreg wrote: post #104

Earlier quoted context omitted.

Not that again. ORMs achieve one main thing: being able to map your app’s objects to a relational database and back. But there are tons of other benefits: 1) Avoid all injection attacks by default by binding variables rather than interpolating their vakues 2) Write SQL code for you to automatically, so you always have balanced parentheses and no typos or errors mixing statements 3) Autogenerate classes and methods fr…

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, but then you have to use those. Better to avoid joins in the DB and do it in the app. You can then use a graph database instead of a relational database, going from O(log N) lookups to O(1) lookups for related data.

Oh and finally, the newest (and pretty cool) craze of BFT, Byzantine Fault Tolerance. You can't achieve that if you're doing joins across different publishers, because they're not supposed to be able to access each other's stuff "just like that".

Our ORM supports joins, even with multiple indexes, it even lets you define relationships and figures out the joins FOR YOU, but it is discouraged if you're building scalable sites.

  By the way thank you for proving point #6 hehe

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

#124

Post by olavk » Thu, Jun 28, 2018, 10:30 AM UTC

Arnt wrote: post #110

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…

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.

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

#125

Post by adrianmsmith » Thu, Jun 28, 2018, 10:32 AM UTC

sudhirj wrote: post #86

adrianmsmith wrote: post #73

Earlier quoted context omitted.

> Some are advertised that way As an example of this, the influential DHH of Basecamp blogged saying just that: https://m.signalvnoise.com/conceptual-compression-means-begi... "Basecamp 3 has about 42,000 lines of code, and not a single fully formed SQL statement as part of application logic!"

Given that these are the people who wrote the Rails ORM, you'd expect that they know how to use the ORM to generate high quality SQL. Which is actually quite doable - in Rails / ActiveRecord you're much better served by knowing what happens for every ORM call, and the default development log prints every generated SQL query as well. Think it now also provides alerts when the queries are slow.

True, but my point was that that blog post supported the point that ORMs are sometimes promoted as a way to avoid needing to know SQL. The intended audience of that blog post was not people who write ORMs, it was to persuade people who are writing applications that they don’t need to learn how to use a database, that they only need to learn how to use an ORM.

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

#126

Post by collyw » Thu, Jun 28, 2018, 10:41 AM UTC

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 with raw SQL (I never tried, but it doens't make too much sense, as it basically generates a set of views per table). Model methods don't make sense using SQL queries.

You should be able to write SQL if you want to be able to use the ORM effectively and I am certainly glad I knew it well before started using Django's ORM.

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

#127

Post by pbowyer » Thu, Jun 28, 2018, 10:45 AM UTC

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…

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

The 10x slowdown I noticed was moving from native database functions (PHP's PDO or MySQLi extensions) to Doctrine2's underlying database abstraction layer, DBAL. Doing a prepared query (with the same SQL) in the native extension was 10x faster than DBAL, which uses the native extension under the wrapper. I never got to why - DBAL is doing more (fair enough) but not enough for this amount of slowdown.

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

#128

Post by olavk » Thu, Jun 28, 2018, 10:46 AM UTC

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,…

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?

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

#129

Post by collyw » Thu, Jun 28, 2018, 10:49 AM UTC

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.

In my experience this seems to be a problem of people using frameworks without getting to understand them in any depth (probably because they are off chasing the next hyped up thing). Django is easy to get started with. Its also easy to produce a shitload of queries unless you spend some time getting to understand the ORM and how to optimize queries with it.

In my previous work we were generating over a 1000 queries for the front page (we were showing maybe 20-25 products with different options). Everything was done in nested loops, with new queries to the database each iteration.

The kid who had written that was apparently building his own framework when I looked up his webpage. Learn to use Django properly before you go building your own crap versions please.

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

#130

Post by theapemachine » Thu, Jun 28, 2018, 10:51 AM UTC

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…

Pretty sure that is what tailing the log during development is all about. I don't see what is so obscure about it, in the Rails based examples in the post, one can just see the logs flying by with the exact queries being executed.