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

#181

Post by [deleted] » Thu, Jun 28, 2018, 1:23 PM UTC

TeMPOraL wrote: post #160

crdoconnor wrote: post #133

Earlier quoted context omitted.

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

> This fetishization of performance seems to be a cultural thing in tech. Tell me where you've seen that, because I'd love to move there. From my experience, there is a common fetishization of non-performance . The "premature optimization" adage taken to its extreme - "we can buy more hardware", "developer time > machine time", "who cares about wasting electricity of millions of our users", etc.

[deleted]

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

#182

Post by matwood » Thu, Jun 28, 2018, 1:25 PM UTC

TekMol wrote: post #52

fuzzy2 wrote: post #37

Earlier quoted context omitted.

An ORM doesn’t have to be slow. Most can source their data from custom queries or stored procedures. Not using an ORM requires writing lots of code to do what the ORM would otherwise do. Is that really appropriate today? Should we not take advantage of the available CPU/RAM? I say ORM is the correct choice in many cases. Not always.

I say ORM is the correct choice in many cases I'm still undecided about this. Would be fun to compare some actual approaches. Which is your favorite ORM?

> Which is your favorite ORM?

jOOQ. Not really an ORM, but handles the tedium of mapping results to an object while letting me write type checked SQL. IMO, it is the best solution for dealing with an RDBMS. I wish every language had a jOOQ equivalent.

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

#183

Post by matwood » Thu, Jun 28, 2018, 1:28 PM UTC

weavie wrote: post #62

patkai wrote: post #33

Earlier quoted context omitted.

I love to write SQL queries and even use them extensively in my code. However, almost everyone I talk to resists this, and warns me that "one day you will regret..." It gives me an uneasy feeling that I actually might, though it hasn't happened yet.

I think one of the problems with SQL queries is that as far as your application is concerned they are just strings. There's no typing information or even syntax checking. Also if you do something like 'select * from' then there the results returned aren't deterministic. If you scatter these throughout your code and then the database schema changes, you have a hell of a refactoring job to make sure everything still wo…

> There's no typing information or even syntax checking.

Intellij is pretty good at highlighting and pointing out typo errors. The code will still compile though.

> no typing information

Given the popularity of javascript, I thought this would now be considered a feature ;)

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

#184

Post by tonyarkles » Thu, Jun 28, 2018, 1:38 PM UTC

olavk wrote: post #149

Arnt wrote: post #145

Earlier quoted context omitted.

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.

The authors of the paper, if I’m remembering from reading it earlier today, built a static analyzer to help find these types of problems! Some of the work has already been done!

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

#185

Post by projectramo » Thu, Jun 28, 2018, 1:41 PM UTC

The real joy of this article was the following:

Some academics (Yang, Subramanian, Lu, Yan, Cheung) were able to produce massive improvements in about a dozen large, mature, battle tested open source projects using just a few lines of code.

This should give hope to all those tepidly trying to get into open source. Just go and take a look at the dozens of open source projects in Django or whatever and you could improve the performance by keeping an eye on the ORM.

Better yet you might find another thing that makes them even better with ease.

Of course, I should add, I think the really clever thing the academics did is to come up with this random link clicking program to time the worst load times of the projects. That whole setup was gold.

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

#186

Post by gldalmaso » Thu, Jun 28, 2018, 1:43 PM UTC

adrianmsmith wrote: post #69

weavie wrote: post #62

Earlier quoted context omitted.

I think one of the problems with SQL queries is that as far as your application is concerned they are just strings. There's no typing information or even syntax checking. Also if you do something like 'select * from' then there the results returned aren't deterministic. If you scatter these throughout your code and then the database schema changes, you have a hell of a refactoring job to make sure everything still wo…

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…

Came here to endorse jOOQ as well. It alleviates a lot of boring boilerplate like ORMs do, but you don't trade off control neither take the risk of wrong use silently causing problems.

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

#187

Post by bpicolo » Thu, Jun 28, 2018, 1:47 PM UTC

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.

In postgres and mysql, at least, the database has to re-scan data every time you run a limit/offset query query. It gets progressively slower as your offset increases.

The efficient way to handle it is to set a lower limit on the pkid (or other atomically increasing row) of the last record fetched, and fetch in ascending order e.g.

    SELECT * from my_table where id > (last_row_id_seen) ORDER BY id asc limit 20;

Then you have an indexed jump to the correct rows to return. It's pretty straightforward to abstract a batched database iterator with this pattern for use application-wide (usually can be done in ~20-50 loc), but no ORM that I'm aware of supports this pattern natively.

Do note that, by extension, it's impossible to efficiently batch iterate over tables that don't have a unique, orderable key in postgres and mysql.

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

#188

Post by theandrewbailey » Thu, Jun 28, 2018, 1:50 PM UTC

ianamartin wrote: post #41

czechdeveloper wrote: post #16

Earlier quoted context omitted.

From my experience, most of my waiting time is IO, not CPU load. Even simple stuff, such as SELECT COUNT(*) to display total count in grid on big table can take seconds. It does not have to be complicated.

You have a problem. And it’s probably related to your db architecture, which was probably designed by devs with ORMs.

Makes sense: devs with ORMs don't know about indexes, which are proper DB architecture.

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

#189

Post by mcbits » Thu, Jun 28, 2018, 1:50 PM UTC

willvarfar wrote: post #13

mr__y wrote: post #12

Earlier quoted context omitted.

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.

Although its really difficult to fathom how loading a webpage takes billions of cycles anyhow. It never used to. Remember the Internet in 2000? Pages are getting slower despite broadband speeds going through the roof and processors getting faster and there being so much more RAM etc.

Yep, I remember trying to optimize my 1-pixel spacer GIF from 53 bytes down to 47 bytes to lighten up the load of a 200KB page of random interwoven table, font, and center tags that took 30-60 seconds to download.

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

#190

Post by theandrewbailey » Thu, Jun 28, 2018, 1:51 PM UTC

sheepz wrote: post #18

crocal wrote: post #6

Earlier quoted context omitted.

Too much CPU and too much available memory will get you there. When in time of abundance, we humans are not good at optimizing.

It's more of an economics issue. It does not make sense to optimize, because bandwidth and CPU power are so abundant. Optimizing will take time away from adding new features.

Failure to optimize in these situations creates a performance bug.