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

291–300 of 319 posts

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

#291

Post by outworlder »

knesek wrote: post #82

When I was inexperienced I feared ORMs because of the negative performance impacts I've read they could have. I constantly worried about what would happen if the amount of data increased and I hit ORM induced problem that I could not resolve without major rewrite of data access layer. However, whenever I've actually hit those problems in production, I found the similar thing the authors of the article did - ORM induc…

Well, knowing that you need to profile is one step. But then you have to know HOW to profile.

Many years ago, a team member got asked to figure out what the performance implications would be if a specific application were to be changed from PostgreSQL to MongoDB (Mongo was very early at the time). That's a very difficult question to answer in general, but the way he decided to approach the problem was: create two programs. One would ask to connect to PG, grab the current time, run a query (once!), grab the time again, disconnect. And that's it. The other program would do the same for Mongo.

His 'findings' pointed out what some people were already expecting, that MongoDB was magically faster than PostgreSQL. Which was odd, as they both were running a single instance, with a simple data type, which PG should have zero problems handling.

I pointed out that he was measuring connection time too, which is slow on PG. He said that wasn't the case, because he started the timer after connecting to PG. I replied with "No, you are starting the timer after asking to connect to PG, you don't know if the connection is made at that point. Run a simple query first to be sure." He wouldn't accept that.

Turns out that not only that was correct (the mongo library would connect immediately, the PG one would defer until needed), but none of the DBs had indexes defined. So no numbers made any sense, the tested query was not based on any observed production queries, it would only be run one time, etc.

I have not seen a more meaningless 'performance' testing since. But the Powerpoint graphs looked pretty, were only management in the room, they would have likely be convinced to migrate.

All that, for a badly written application, that a single box with SQLite would have zero problems handling.

One the other side, I had a coworker implementing some simple, but effective, profiling of a Rails application. Slow path was traced to the caching code, specifically the code that generated the hash key. That was replaced with a better version and we got massive speedups.

Alright, I think I'll add some questions on profiling in my current team's interview process.

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

#292

Post by outworlder »

ravenstine wrote: post #222
knesek wrote: post #82

When I was inexperienced I feared ORMs because of the negative performance impacts I've read they could have. I constantly worried about what would happen if the amount of data increased and I hit ORM induced problem that I could not resolve without major rewrite of data access layer. However, whenever I've actually hit those problems in production, I found the similar thing the authors of the article did - ORM induc…

> Teams spending weeks exchanging SQL DB for No SQL DB because of unsolvable performance problem. When hitting the same problem with NoSQL DB, they find that addition of a simple index is solution in both cases I don't get the idea that one has to pick either SQL or NoSQL, but a lot of people seem to think this way. Why not use both? The SQL portion can more or less be treated as a rich index of relationships, and th…

Indeed, there is no reason to pick a "side". All big systems I have seen either have both or would benefit from both.

In fact, our current system has PG and several NoSQL databases, because each product has its own strengths.

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

#293

Post by vladmihalcea »

Most ORM-related problems are related to lack of knowledge of how the tool works.

However, if you understand how databases work, how to tune the driver and how to get the ORM tool to generate the same queries you'd otherwise write yourself, then you are fine.

For more details, check out these [14 High-Performance Persistence Tips](https://vladmihalcea.com/14-high-performance-java-persistenc...).

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

#294

Post by workinfunk »

tootie wrote: post #215
mnutt wrote: post #212

Earlier quoted context omitted.

It’s susceptible to Thundering Herd whereby more requests come in for the same cache key before the initial computation is finished, and so you end up with lots of cache misses. The fix is usually to lock the cache key and have subsequent requests wait on the original computation but it’s a bit more complex to code.

I've heard it called Cache Stampede. Any decent framework for memoizing method calls would cover this case though.

No it wouldn't, memoization can't provide responses to calls it hasn't seen yet, that's the whole point.

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

#295

Post by InclinedPlane »

mklepaczewski wrote: post #248
latch wrote: post #23

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 (messa…

1 - TTL should be infinite. If one needs finite TTL it means that cache invalidation logic is bogus.

s/bogus/imperfect/

It's impossible to create perfect systems, so it always makes sense to give yourself an extra out to protect you from unanticipated defects (otherwise known as a "belt and suspenders" approach).

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

#296

Post by InclinedPlane »

raarts wrote: post #239
knesek wrote: post #82

When I was inexperienced I feared ORMs because of the negative performance impacts I've read they could have. I constantly worried about what would happen if the amount of data increased and I hit ORM induced problem that I could not resolve without major rewrite of data access layer. However, whenever I've actually hit those problems in production, I found the similar thing the authors of the article did - ORM induc…

‘Unsolvable performance problems’ that could have been fixed by adding an index?? How did these team members pass their job interviews? By practicing algorithm puzzles?

Funny story: at a, let's say, Fortune 50 tech company there was an investigation into why performance for a certain database query had become atrocious. The problem was that the query was against a table that started out quite small and then grew very large. And the database had been configured to use "query plan stability" to improve predictability of performance. However, the query plan that the db originally came up with for that nearly empty table was a full table scan, which was actually the fastest method under those conditions. Yet it continued to use a full table scan even as the table grew to many tens of millions of rows. It was a simple matter to switch the query to actually make use of the indexes that already existed.

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

#297

Post by syastrov »

I’ve experienced a lot of n+1 queries problems as causes for bad performance. Often times this was a result of wrapping the ORM in abstraction layers (for business logic and fears of being “locked in” to the ORM). We rewrote that part of the application using a different ORM (in Python). Making use of a tool that could help find these problems automatically helped greatly and we didn’t have a single performance problem when we went into production the rewritten service.

This module can detect the n+1 queries problem automatically in Python ORMs: https://github.com/jmcarp/nplusone

Looking forward to seeing more automated tools like this in the Python/Django world.

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

#298

Post by hinkley »

memset wrote: post #207
blattimwind wrote: post #196

Earlier quoted context omitted.

0 - Caching antipattern 101: key = calculate_cache_key() if not cache.has(key): data = expensive_calculation() cache.store(key, data) else: data = cache.get(key)

This is interesting because this is generally how I implement caching! What would pseudocode look like for a non-antipattern?

If your language supports promises, cache the promise and not the result. If it doesn’t support promises, find or write one.

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

#299

Post by rmc »

One of the example Rails applications they use is the code which powers the OpenStreetMap website.

They populated their install by randomly filling in fields on the website. Which doesn't include any map editing! For OSM they suggest changing how the diary feature operates, which is a tiny, almost irrelevant part of the OSM website software stack. The OSM database has millions of geographic objects, and they talk about the diary system on the website.

> For example, when we profile the latest version of Openstreetmap, a collaborative editable map system, we find that a lot of time is spent on generating a location_name string for every diary based on the diary’s longitude, latitude, and language properties stored in the diary_entry table

The paper claims to have filed bug reports, and has URLs. But those links don't exist.

Paper: https://hyperloop-rails.github.io/220-HowNotStructure.pdf openstreetmap-website: https://github.com/openstreetmap/openstreetmap-website/ Claimed Issues submitted: https://github.com/hyperloop-rails/issues-summary

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

#300

Post by olavk »

NightlyDev wrote: post #278
olavk wrote: post #244

Earlier quoted context omitted.

> The query translation and hydration overhead of transforming the data into objects even in a fast language are always going to be problems No they are not. Have you measured this in a real-world application? This overhead is negligible compared to the cost of the query itself. And without an ORM you still have to load the data into some kind of objects or data structures before you pass it to presentation, you will…

It's definetly not always going to be a problem, but sometimes it does. ORM is always slower than writing custom code, but it might be more than fast enough.

Surely it depends on the custom code in question if it is faster or slower than the similar ORM logic?

You may be able to write faster code, but unless you are Donald Knuth, you can't guarantee that any custom code you write will always be faster than some library.

Post reply on HN