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

131–140 of 319 posts

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

#131

Post by collyw »

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.

You are being downvoted, but it seems perfectly acceptable to many businesses. I wanted to improve the front page on my previous page (generating over 100 queries, taking between 1 and 4 seconds to load). In the end every other crappy new feature was given a higher priority.

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

#132

Post by TekMol »

fuzzy2 wrote: post #94
TekMol wrote: post #90

Earlier quoted context omitted.

What is the code to get the number of cars with expired insurence when using Dapper?

Something along the lines of db.Query ("SELECT * FROM Cars WHERE InsuranceEndDate /edit: Sorry, missed the "number of". Well, you get the idea. It'd use 'QuerySingle' instead.

That does not look very ORMish to me. It might return objects. But isn't one point of an ORM that you tell the ORM which data you want and not what SQL query to send to the DB?

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

#133

Post by crdoconnor »

bryanrasmussen wrote: post #109
vidarh wrote: post #100

Earlier quoted context omitted.

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…

True, but on the other hand if the developer gets told they are loading nearly 20,000 objects they should be smart enough to realize something is going to need to be optimized here, and although I don't know how to do it I better find out.

That depends upon the context. I've seen developers argue over optimizing ETL processes that weren't in the slightest bit time sensitive, took 25 minutes and were going to be run once.

More often than not I've seen developers fret over performance when it's not actually bothering users. This fetishization of performance seems to be a cultural thing in tech.

Conversely, data integrity, normalization and transactionality are usually given far too little weight.

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

#135

Post by lmm »

marcus_holmes wrote: post #31
olavk wrote: post #20

Earlier quoted context omitted.

The value proposition of ORM's is they simplify data access code, making the application simpler to write and more maintainable. I have never seen anybody claim ORMs would eliminate performance issues.

Right up until the point where you have to hack around the ORM to get performance where it needs to be. Then you suddenly have more complex and harder to maintain "magic" code than if you'd just written some simple-but-boring boilerplate and SQL from the start. I'm wading through the tedious and boring process of writing a data access layer for an application at the moment. It's repetitive, there's lots of error-chec…

> Then you suddenly have more complex and harder to maintain "magic" code than if you'd just written some simple-but-boring boilerplate and SQL from the start.

Not my experience at all. Rather you have your straightforward ORM code with maybe a couple of "magic" hint annotations here and there where you need them. Your non-performance-critical queries are plain and simple, your performance-critical queries are less plain but remain readable. Whereas if you write boilerplate SQL the whole time all your queries are "readable" in theory, but there's just so much of them that you can never actually understand more than a fraction and have no way of knowing which differences are important and which are accidents.

(Unless, of course, you throw away the whole ORM infrastructure at the first sign of a performance problem and insist that you absolutely have to run custom SQL directly, disable entity caches, and so on. But don't do that.)

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

#136

Post by lmm »

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

Computers are fast. Correctness is more important than performance. Most businesses fail.

With an ORM you write the first working version of your product quickly, validate product-market fit, and maybe spend a small amount of time profiling and optimising eventually when you need to scale. That's a good tradeoff.

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

#137

Post by dnomad »

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…

The problem isn't "kids these days." The problem is relational databases. It's a profoundly non-intuitive and fundamentally broken model. Developers shudder away from SQL precisely because it is so alien from the problem they're trying to solve.

As for performance, note that even when you know exactly what you're doing it is hard to wring good performance out of relational databases. This is why companies spend hundreds of thousands of dollars a year to hire "Database Administrators" to design and "tune" their database. (What other piece of software requires a highly paid, full time expert?) The most complicated databases are so complicated that we get "Junior DBAs" and "Senior DBAs" and multiple levels of certification.

And let's not forget what happens at extreme scales. At the lowest latencies and the largest data sets relational dbs are simply impossible to use. This might never be a problem for most businesses who are processing a few hundred messages a second (if that) but it should be on the mind of any startup that hopes to one day have millions of customers.

In the long run memory will become cheaper, faster and persistent. (Let us pray.) When that happens most everybody will abandon the big mess that are relational databases and just manipulate objects in memory as the gods intended. Then the relational model just becomes something to scare grand kids with.

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

#138

Post by acutesoftware »

I need a very good reason before using any external library in an attempt to keep the total code base as clean as possible.

It is just too easy to be rushed and bring in a heap of code, so I prefer to use SQL instead of ORM's.

All access to the database is done in a single module and they are wrapped in functions like below

    def get_table_as_list(user_id, cols, tbl, where_clause, params_as_list, conn_str, order_by="1", maxrows='2000'):
        """
        This should be the ONLY place that selects from the database
        """
        db = get_db_conn(conn_str) 
        cur = db.cursor()
        where_clause += ' AND user_id = %s'
        sql = "SELECT " + cols + " FROM " + tbl + " WHERE " + where_clause + " ORDER BY " + order_by + " LIMIT " + maxrows
        params_as_list.append(str(user_id))
        cur.execute(sql, params_as_list)
        res = list(cur.fetchall())
        cur.close()
        db.close()
        return res
The database is designed and built first, then in the application the definitions are done like below

    all_tables = [
        {'tbl':'as_note',
         'cols':['id','title','pinned', 'important','content','folder'],
         'col_types':['id','Text','Checkbox','Checkbox', 'Note','Text'],
        },
        {'tbl':'as_task',
        'cols':['id','Title','Pinned', 'Important','Notes','folder','Done'],
        'col_types':['id','Text','Checkbox','Checkbox','Note','Text','Checkbox'],
        }]
So far it is working well, and it is very simple to add new tables to the schema and have them working in the application.

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

#139

Post by matlock »

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

> Joins were good for the smaller websites, but they don't scale

Most sites do not have to scale beyond this limitation (or can use database followers to throw a bit of money at the problem). Providing Google as an example is a bit exaggerated as almost nothing in the world has the scaling needs that google has.

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

#140

Post by matwood »

weavie wrote: post #84
adrianmsmith wrote: post #69

Earlier quoted context omitted.

You can get the best of both worlds by using e.g. jOOQ in Java (allows you to write e.g. db.select(MY_TABLE.MY_COL).from(MY_TABLE) where those values are generated from the database therefore they exist and are of the right type. It maps 1:1 to the SQL statement that gets executed so there's no magic e.g. extra n+1 queries being introduced without you noticing. But if you change your schema, re-generate, and immediat…

I wasn't familiar with jOOQ before. It looks pretty comprehensive.

Honestly, jOOQ is one of the killer libraries for Java that keep me using it for backend code.
Post reply on HN