Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Squeeze the hell out of the system you have

blog.danslimmon.com

Post reply on HN
Print view View original

41–50 of 383 posts

Re: Squeeze the hell out of the system you have

#41

Post by javajosh »

I'll probably get down-voted for saying this (again), but a key way to squeeze unimaginable amounts of performance is to lean into stored procedures.

Look, I get it, the devx sucks. And it feels proprietary, icky, COBOL-like experience. It means you have to dwell in the database. What are you, a db admin?!

But I'm telling you, the payoff is worth it. (and also, if you ship it you own it so yes you're a db admin). My company ran for many years on 3 machines, despite it's extremely heavy page weight because the original author wrote it stored procs from the beginning. (He also liberally threw away data, which was great, but that's another post.) Part of my job was to migrate away from .NET and to Java and JavaScript - and another engineer wrote an ingenious tool that would generate Java bindings to SQL Server stored procs that made it really nice to work with them. And the performance really was outrageous - 100x better than any system I've worked with before or since. Those 3 boxes handled 300k very data intensive monthly actives, and that was like 10 years ago.

Don't worry - even if you lean into SPs there is still plenty of engineering to do! It's just that your data layer will simplify, and your troubleshooting actually gets easier, not harder. I liked the custom bindings - a bit like ActiveRecord, and no ORM. But really, truly: if you want to squeeze, move some queries into SPs and prepare to be amazed.

Re: Squeeze the hell out of the system you have

#42

Post by latchkey »

skybrian wrote: post #24
endisneigh wrote: post #6

The bit on the database performance issues leads me to my hottest, flamiest take for new projects: - Design your application's hot path to never use joins. Storage is cheap, denormalize everything and update it all in a transaction. It's truly amazing how much faster everything is when you eliminate joins. For your ad-hoc queries you can replicate to another database for analytical purposes. On this note, I have mixe…

I’m wondering if indexes and materialized views can be used to do basically the same thing? That is, assuming they contain all the columns you want.

The issue is writes, not reads.

Re: Squeeze the hell out of the system you have

#43

Post by newlisp »

veave wrote: post #27
endisneigh wrote: post #6

The bit on the database performance issues leads me to my hottest, flamiest take for new projects: - Design your application's hot path to never use joins. Storage is cheap, denormalize everything and update it all in a transaction. It's truly amazing how much faster everything is when you eliminate joins. For your ad-hoc queries you can replicate to another database for analytical purposes. On this note, I have mixe…

>Design your application's hot path to never use joins. Storage is cheap, denormalize everything and update it all in a transaction. It's truly amazing how much faster everything is when you eliminate joins. Anybody has documentation about this with examples?

Duplicate data to avoid joins, use serializable transactions to update all the duplicated data.

Re: Squeeze the hell out of the system you have

#44

Post by lern_too_spel »

deathanatos wrote: post #17
gary_0 wrote: post #7

No mention of caching? If your database is getting hammered with SELECTs, isn't putting a cache in front of it something that should at least be considered?

I've been in the OP's situation, and this exact suggestion was made in my case. Welcome to one of the hardest problems in CS: cache invalidation. If you have a dataset for which cache invalidation is easy (e.g., data that is written and never updated), yeah, absolutely go for this. In our case, and most cases I've seen, it wasn't so simple, and "split this off to a DB better suited to it" was less complex (maybe stil…

There are systems that will do that for you like https://readyset.io/.

Re: Squeeze the hell out of the system you have

#45

Post by giantrobot »

skybrian wrote: post #24
endisneigh wrote: post #6

The bit on the database performance issues leads me to my hottest, flamiest take for new projects: - Design your application's hot path to never use joins. Storage is cheap, denormalize everything and update it all in a transaction. It's truly amazing how much faster everything is when you eliminate joins. For your ad-hoc queries you can replicate to another database for analytical purposes. On this note, I have mixe…

I’m wondering if indexes and materialized views can be used to do basically the same thing? That is, assuming they contain all the columns you want.

There's always money in the banana sta...materialized views. Materialized views will get you quite a ways on read heavy workloads.

Re: Squeeze the hell out of the system you have

#46

Post by kreetx »

deathanatos wrote: post #23

> Split up the monolith into multiple interconnected services, each with its own data store that could be scaled on its own terms. Just to note: you don't have to split out all the possible microservices at this junction. You can ask, "what split would have the most impact?" In my case, we split out some timeseries data from Mongo into Cassandra. Cass's table structure was a much better fit — that dataset had a well…

In a way, in the article they also did a split: specific heavy select queries were offloaded to a replica.

Re: Squeeze the hell out of the system you have

#47

Post by tibbetts »

endisneigh wrote: post #6

The bit on the database performance issues leads me to my hottest, flamiest take for new projects: - Design your application's hot path to never use joins. Storage is cheap, denormalize everything and update it all in a transaction. It's truly amazing how much faster everything is when you eliminate joins. For your ad-hoc queries you can replicate to another database for analytical purposes. On this note, I have mixe…

Premature denomalization is expensive complexity. Denormalization is a great tool, maybe an under-used tool. But you should wait until there are hot paths before using it.

Re: Squeeze the hell out of the system you have

#49

Post by deely3 »

joshstrange wrote: post #39
iamwil wrote: post #22

Earlier quoted context omitted.

If you don't use joins, how do you associate records from two different tables when displaying the UI? Do you just join in the application? Or something else?

Single Table Design is the way forward here. I can highly recommend The DynamoDB Book [0] and anything (talks, blogs, etc) that Rick Houlihan has put out. In previous discussions the author shared a coupon code ("HACKERNEWS") that will take $20-$50 off the cost depending on the package you buy. It worked earlier this year for me when I bought the book. It was very helpful and I referred back to it a number of times.…

And if you don't want to spend money, you can get idea from this article:

https://www.alexdebrie.com/posts/dynamodb-single-table/

Im really curious about real life performance on different databases, especially in situation where RAM is smaller than database size.

Re: Squeeze the hell out of the system you have

#50

Post by deely3 »

joshstrange wrote: post #40
veave wrote: post #27

Earlier quoted context omitted.

>Design your application's hot path to never use joins. Storage is cheap, denormalize everything and update it all in a transaction. It's truly amazing how much faster everything is when you eliminate joins. Anybody has documentation about this with examples?

See "Single Table Design" which I talked about in this comment above: https://news.ycombinator.com/item?id=37093357

And if you don't want to spend money, you can get basic idea from this article: https://www.alexdebrie.com/posts/dynamodb-single-table/
Post reply on HN