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

371–380 of 383 posts

Re: Squeeze the hell out of the system you have

#371

Post by wvenable »

taylodl wrote: post #370
wvenable wrote: post #227

Earlier quoted context omitted.

"Show me your flowchart and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won't usually need your flowchart; it'll be obvious." -- Fred Brooks, The Mythical Man Month (1975) > The application doesn't need to know how the data is physically stored in the database. In all the applications that I've designed, the application and the database design are in sync. That's not say that…

There's the physical data model, and there's the logical data model. The application(s) only deal with the logical data model. They don't need to worry about how the data is physically stored. This allows for the forward-compatible evolution of the logical data model which may necessitate extreme changes to the physical data model to keep everything performant. The client application(s) aren't affected by all the cha…

The application usually has a logical data model in the form of objects representing the data, which, in turn stores that data to the physical model. This could be a separate middle tier layer or not. You're proposing another logical model in the database and I don't see the advantage.

These models shouldn't be significantly different from each other. How you store the data physically should be is very much how it's represented logically. And then how the UI represents the data model to the user.

If you have 2 tables -- a summary and a detail for example -- that's going to be similarly represented to the user in the UI. If you break that summary down into another table (because you need to allow, say, more addresses) you're going to bubble that right up the UI for saving and loading. For querying, you might have a materialized view that summarizes the detail data when viewing a bunch of summaries together but that's in addition to rest of the model.

I'm going to need some kind of example to understand the advantage of this.

Re: Squeeze the hell out of the system you have

#372

Post by javajosh »

throwdbaaway wrote: post #368
javajosh wrote: post #41

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…

What do you think about evolving the stored procedures into a stateless GRPC service that fronts the database? For the price of 1 (or 2) additional network hop, you get much better devx, while keeping most of the benefits provided by stored procedures.

I don't think this adds much. The important thing is that your logic runs in the database--how the logic is ultimately exposed is up to you. We did a java app server calling SPs over JDBC with generated, typed bindings and this worked great. You'd have to write a similar tool to generate a gRPC server, but the logic would still be SPs in the database. That's the part that sucks for devs; the bindings are ultimately a detail.

Re: Squeeze the hell out of the system you have

#373

Post by yashap »

throwdbaaway wrote: post #365
yashap wrote: post #359

Earlier quoted context omitted.

Lots of replies to this one! I created a little benchmark that you can easily run yourself, as long as you have Docker installed. It shows how, for cases like the one I described above, the only way to have consistently fast queries (i.e. even with a cold cache) is to denormalize, so you can create the ideal compound index. The normalize/join version takes 15x longer, which can be the difference between 1s and 15s qu…

Nice benchmark script. With EXPLAIN (ANALYZE, BUFFERS), I see that * normalized/join version needs to read 5600 pages * normalized/join version with an additional UNIQUE INDEX .. INCLUDE (type) needs to read 4500 pages * denormalized version only needs to read 66 pages, almost 100x fewer Related to this pagination use case, when using mysql, even the denormalized version may take minutes: https://dom.as/2015/07/30/on…

Ooh ty, will give that article a read! And yeah, that's really the trick to queries that are consistently fast, even with cold caches - read few pages :)

Re: Squeeze the hell out of the system you have

#374

Post by rovolo »

fuzztester wrote: post #338
sbuk wrote: post #252

Earlier quoted context omitted.

It was midnight and a few beers after celebrating a birthday. I'm sorry I offended your grammatical sensibilities. But you really did go full orange site there, didn't you! I will admit to misquoting Mike Tyson; "Everyone has a plan until they get punched in the mouth.", which I hope goes someway to restoring peace and order over a tiny, drunken grammatical slip-up.

BTW, what is "orange site"? I googled it and at least the top few links don't seem relevant.

This site's default theme is orange.

Re: Squeeze the hell out of the system you have

#375

Post by killjoywashere »

eru wrote: post #221
killjoywashere wrote: post #108

Earlier quoted context omitted.

As a military officer who was watching CNN live from inside an aircraft carrier (moored) when he said that, being in charge of anti-terrorism on the ship at the time, it was absolutely foundational to my approach to so many things after that. Here's the actual footage: https://www.youtube.com/watch?v=REWeBzGuzCc Rumsfeld was complicated, but there's no doubt he was very effective at leading the Department. I think mo…

> [...] the Internet lets people indulge in a Dunning-Kruger situation the likes of which humanity has never seen. While we are at it, that infamous Dunning-Kruger study showed didn't even claim what people like to pretend it claimed. In addition the more nuanced claim they did make is not supported by the evidence they collected and presented in their paper. (Their statistics are pretty much useless, and as with any…

I didn't know the names of Dunning or Kruger. I was a medical student who surveyed my classmates on their study habits and also asked them which quintile of the class they believed they stood in. My response rate was high enough that it was impossible to believe so few people from the bottom quintile had responded, and the upper 2 and 3 quintiles were impossibly overpopulated. That's how I learned about the effect. I didn't learn about Dunning and Kruger for several years after that, but when I did, oh boy, did the lights come one.

So, the current fashion of denouncing Dunning and Kruger doesn't jive with me. It was too obvious to discount and I had no idea of the concept when I saw it my own data. I think the misunderstanding has to do with the idea that it's about dumb people being dumb. It's about all of us. We all get it wrong. Even the smart ones. Paradoxically, the smart ones just get it wrong in the less desirable direction.

I think that academic fashionistas may be too clever by half here. Unless you have original data to back up a claim, the internet points aren't worth it. Focus on getting things right.

Re: Squeeze the hell out of the system you have

#376

Post by r00fus »

Izkata wrote: post #332
r00fus wrote: post #172

Earlier quoted context omitted.

Would it be possible to simply use a materialized view for table1_join_table2?

The problem there would be how they fall out of date, and updating them is a heavy operation. An alternative I've heard before is using triggers with regular tables, so updating one automatically updates the relevant other ones.

Materialized views allow insert/update mechanics on many platforms. Alternatively you can refresh them.

Re: Squeeze the hell out of the system you have

#377

Post by fuzztester »

rovolo wrote: post #374
fuzztester wrote: post #338

Earlier quoted context omitted.

BTW, what is "orange site"? I googled it and at least the top few links don't seem relevant.

This site's default theme is orange.

Thanks, but I knew that, and it does not seem to answer my question, unless I didn't get what you meant.

My question was in reply to the comment above by sbuk, excerpted below:

>It was midnight and a few beers after celebrating a birthday. I'm sorry I offended your grammatical sensibilities. But you really did go full orange site there, didn't you!

Re: Squeeze the hell out of the system you have

#378

Post by topspin »

romafirst3 wrote: post #155
topspin wrote: post #134

Earlier quoted context omitted.

> it makes it easy for bad programmers to write bad performing queries That is true of every ORM in existence. The easiest thing to do is naively follow the object graph in code, because that's what the ORM gives you. If the ORM was to somehow add friction here to encourage some other approach it would be panned as "too hard!!1" and fade away into obscurity.

that is what I meant by saying it's a feature. It's easy to get started and do 99% of the stuff. When you are lucky enough to hit performance problems it's easy (for someone with knowledge) to fix your performance problems within the structure of Rails. It's awesome.

> it's easy (for someone with knowledge)

I don't think this is generally true. This sort of work has a lifecycle. By the time the performance problems emerge, often years later, the people involved that understood what is going on, and the tooling involved in building the system, are no longer readily at hand. This gets multiplied by the fact that often an N+1 problem is compounded by decoupling strategies (network APIs, etc.) where the front and backend must both be evolved: The backend needs a new, better API and the frontend has to adopt it. Often these are two completely distinct technology stacks, and also often spread among multiple parties which cannot easily coordinate change.

Anything is possible, of course. But "easy" goes right out the window.

Re: Squeeze the hell out of the system you have

#379

Post by jacquesm »

killjoywashere wrote: post #108
KnobbleMcKnees wrote: post #56

Earlier quoted context omitted.

That was Donald Rumsfeld!? I always assumed this came from some techie or agile guru given how much it's used as a concept in project planning.

As a military officer who was watching CNN live from inside an aircraft carrier (moored) when he said that, being in charge of anti-terrorism on the ship at the time, it was absolutely foundational to my approach to so many things after that. Here's the actual footage: https://www.youtube.com/watch?v=REWeBzGuzCc Rumsfeld was complicated, but there's no doubt he was very effective at leading the Department. I think mo…

Indeed, it is very well possible to be both brilliant and an ethically completely unhinged individual.

Re: Squeeze the hell out of the system you have

#380

Post by yashap »

sgarland wrote: post #369
yashap wrote: post #363

Earlier quoted context omitted.

Yeah, I believe which order is best (sortKey/filterKey or filterKey/sortKey) really depends on the specific data/queries, best to try both and pick the best one - looks like sortKey/filterKey in this case :) But I think this does show how, for specific data/queries, sometimes you do have to denormalize, so that you can create the ideal compound index, for specific problem queries. Should still go with normalized sche…

If you’re limited in RAM and can’t upsize, then yes, this does appear to be a good trade off. You can always refactor later and normalize if necessary. BTW, although it wouldn’t have helped for your specific benchmark schema creation of TYPES, I’ll plug my genSQL tool [0] for generating random data. It’s primarily designed around MySQL, but it can produce CSVs easily, which every DB can load. Turns out a lot of rando…

Looks useful, ty!
Post reply on HN