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

341–350 of 383 posts

Re: Squeeze the hell out of the system you have

#341

Post by camgunz »

atmosx wrote: post #318
camgunz wrote: post #313

Earlier quoted context omitted.

Similar experience to my sibling here: I've had a couple microservice shops and they really soured me on them and SOA in general. - You have so many deploy stacks that it's literally boggling. I had to deploy code that used everything from make to GitHub CI to Jenkins to serverless just to push a single change. - You have infinite implementations of your business logic. This is for two major reasons. First it's just…

> I'm not saying a monolith doesn't have problems, but I think the cons of microservices get very little play. I was about to make the same point. I hear you though. In my case CI/CD, application framework, programming language, etc. was common. We manage to shoot ourselves in the foot multiple times because (a) our CI/CD was _way_ too smart, allowing for _way_ to many things to happen for us (SRE team) in non-standa…

Yeah it feels like microservices is very hard on SREs.

Re: Squeeze the hell out of the system you have

#342

Post by wizofaus »

tremon wrote: post #316
wizofaus wrote: post #71

Earlier quoted context omitted.

Can't say I've ever come across a scenario where a join itself was the performance bottleneck. If there's any single principle I have observed is "don't let a table get too big". More often than not it's historical-record type tables that are the issue - but the amount of data you need for day-to-day operations is usually a tiny fraction of what's actually in the table, and you're bound to start finding operations on…

don't let a table get too big I'd amend that to "don't let your scan coverage get too big". Understanding how much data must be loaded in memory and compared is essential to writing performant database applications. And yes, those characteristics change over time as the data grows, so there may be a one-size-fits-all solution. But "table too large" can pretty much always be solved by adding better indexes or by parti…

If your primary issue is read/query performance, then sure, well-designed indexes, partitioning and, as you say, carefully constructed WHERE clauses are often enough to maintain decent performance even with millions of records. But if you then to do deletions or bulk updates or schema transformations you're in for some serious downtime.

Re: Squeeze the hell out of the system you have

#343

Post by sgarland »

yashap wrote: post #331
zepolen wrote: post #291

Earlier quoted context omitted.

For 10 million users + telephones, this takes 1ms. create table users ( id serial primary key not null, created_at timestamp not null default now() ); create table users_telephones ( user_id int references users(id) not null, is_primary boolean not null default true, telephone varchar not null ); insert into users select i, NOW() + (random() * (interval '90 days')) + '30 days' from generate_series(1, 10000000) i; ins…

Ty for benchmarking, but this isn’t a good benchmark for the issue I’m talking about. This is only fast because 100% of users have a phone number as a primary contact, so the join filter is essentially meaningless. If in the contact table, the filtered number is a small percentage of the total (e.g. most users have an email as their primary contact, not a phone number), but still a good size (e.g. there’s still hundr…

> It’s probably also fast because you have a warm cache - e.g. there’s enough memory for the DB to have the indexes 100% in memory, which is just not feasible with large DBs in the real world, where you can easily have >100GB of indexes + hot data, and the DB can’t keep it all in memory.

That's the point? Sure there is a scale where it's infeasible, but you can quite easily (albeit it's pricey) get DB instances with hundreds or thousands of GiB of RAM. Even if you can't get everything into it, your working set is often not the size of the entire data set. My company's main MySQL primary has around 800 GiB of storage, and only about 1/3 of that in RAM. Disk read IOPS are usually 0.

Nevertheless, I recreated this in Postgres 15.

The total index size of the two tables is 790 MB according to `\di+*`, so I'll set `shared_buffers` to 263 MB, then restart and re-run.

For reference, time with current settings is about 6.8 msec for `is_primary`, and 1395 msec for `NOT is_primary`.

After a restart, I ran the query for `NOT is_primary` first, which took 1740 msec. The first run of the query with `is_primary` took 21 msec.

My DB is hosted on older hardware, and the files themselves live on NVMe drives presented via Ceph over 1GBe.

EDIT: I forgot that Postgres uses OS cache for a lot of its memory, not just its own. Re-did this, running `sync; sync; sudo sh -c 'echo 3 > /proc/sys/vm/drop_caches'` in between shutting down/starting up Postgres. 16726 msec and 79 msec, respectively. So yes, a lot slower, but a. I don't think this is realistic for a production database b. I'm still not clear about how you think JOINs enter into this. The slowdown comes entirely from having to run a table scan.

Re: Squeeze the hell out of the system you have

#344

Post by nerdponx »

camgunz wrote: post #295
nerdponx wrote: post #150

Earlier quoted context omitted.

The problem is with using `session.add(obj)` instead of `session.scalars(insert(TheClass).returning(TheClass), data)`. If there's a way to get generated SQL from an AsyncSession, please do let me know.

I think you have a couple options [0], I currently have a project that uses 'echo' in debug mode. [0]: https://stackoverflow.com/questions/27748053/how-to-log-sql-...

Yep, that's the same output that's emitted in the sqlalchemy.engine logger. If you use the logger you can use Pytest caplog to capture it.

Re: Squeeze the hell out of the system you have

#345

Post by fuzztester »

sbuk wrote: post #252
fuzztester wrote: post #182

Earlier quoted context omitted.

Nope. Let's juxtapose them and see: Von Moltke: "No battle plan survives contact with the enemy." Tyson: "Everybody has a plan until you get hit in the face." Pretty much the same meaning, and Von Moltke's quote is three words shorter, so no, Tyson's quote is not simpler. Also, Tyson was ungrammatical, IMO: "Everybody" vs. "you" in the same sentence, referring to the same entity. Grammar experts, correct me if I am w…

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, looks like you could deploy my new SaaS at the end of your above comment, for great good:

https://news.ycombinator.com/item?id=37064183

Re: Squeeze the hell out of the system you have

#346

Post by eru »

fuzztester wrote: post #327
eru wrote: post #218

Earlier quoted context omitted.

Simplicity isn't a function of number of words alone. > Also, Tyson was ungrammatical, IMO: > "Everybody" vs. "you" in the same sentence, referring to the same entity. Seems perfectly understandable to English speakers. (And that's pretty close to how English grammar is defined by descriptive linguists.) Have a look at http://fine.me.uk/Emonds/ for an exploration of these kinds of concepts.

Oh, it was perfectly understandable to me too, even though I am not a native English speaker (but I have been told by native speakers that my English is quite good). "Everybody" seems to be in the third person and "you" is in the second person, so I thought it was a mismatch (since in the same sentence, etc.), and so was ungrammatical. Let anyone tell me if I am wrong, would like to know. And see my reply to sbuk, it…

Real world grammar is a lot more fluid and flexible, than just blindly following a bunch of fixed, mechanical rules.

The mechanical rules are just an imperfect attempt at capturing parts of the richness of real world language, or more precisely: language variants of different dialects and speakers.

Of course, there's a whole world of class markers overlaid here as well. If you want to sound middle-class educated in most of the English speaking world, you have to avoid "ain't" and say things like "It is I" or "Bob and I went shopping.", instead of the more natural "It's me!" or "Bob and me went shopping." That's what Emond calls 'Grammatically Deviant Prestige Constructions'. The whole point is that they aren't part of a naturally learnable variant of English, so they can only be acquired by schooling.

Most people who speak prestige-English over-generalise, and also say things like "She likes Bob and I.".

See fine.me.uk/Emonds/ for details.

Re: Squeeze the hell out of the system you have

#347

Post by lanstin »

lifeisstillgood wrote: post #307
anonzzzies wrote: post #245

Earlier quoted context omitted.

It was a low/nocode environment; anyone (with enough rights) could knock up a simple app with rules/workflows and share it with the company. It made collecting, distributing and organising information easy if you knew what you were doing. It also created complex monsters as it was both too easy and too hard to use. I liked it a lot; we moved from Notes to Exchange and Sharepoint back in the day and it was awful for e…

Oh. I struggle with the value of low/no code vs learning to code and providing common libraries.

For your company you have a lot of smart people other than coders. And Notes had a rich collaborative set of intrinsically that you could hip out work flow applications like an accountant with spreadsheets. And built in security and auditing and all that. And since you had the ability to craft tools to fit the exact situation, automation of processes went so fast and was done by people familiar with the business side of the process. We did have a Notes team that would do apps for teams that couldn’t but also had a rich ecosystem of business line apps that were so much better than spreadsheet apps or Access apps.

Re: Squeeze the hell out of the system you have

#348

Post by yashap »

sgarland wrote: post #343
yashap wrote: post #331

Earlier quoted context omitted.

Ty for benchmarking, but this isn’t a good benchmark for the issue I’m talking about. This is only fast because 100% of users have a phone number as a primary contact, so the join filter is essentially meaningless. If in the contact table, the filtered number is a small percentage of the total (e.g. most users have an email as their primary contact, not a phone number), but still a good size (e.g. there’s still hundr…

> It’s probably also fast because you have a warm cache - e.g. there’s enough memory for the DB to have the indexes 100% in memory, which is just not feasible with large DBs in the real world, where you can easily have >100GB of indexes + hot data, and the DB can’t keep it all in memory. That's the point? Sure there is a scale where it's infeasible, but you can quite easily (albeit it's pricey) get DB instances with…

First off, ty for running all these benchmarks, above and beyond!

FWIW, I don’t think joins are bad, I’m 100% for normalized DB schemas with joins. But I’ve done tonnes of performance work over the past ~10 years, and run into a bunch of real world cases where, when caches are cold (which does happen frequently with large datasets and limited budgets), queries similar to the above (join two tables, read a page of data, sorting on one table and filtering on the other) take 10s of seconds, sometimes even minutes with very large datasets. In those cases, the only solution has been to denormalize so we can create a compound index, with filter key(s) as the prefix, sort key as the suffix, which makes the query consistently fast. I am not at all suggesting this as the default, better to default to normalized with joins, and only do this for these specific cases. Generally a company just has one or a few cases where this is necessary, can just do the ugly denormalization + compound indexes for these few hot spots. But when ppl say “joins are slow”, these cases are examples of where it’s true.

Re: your above 17 second query, if you do something like adding fields to the user table for primary and secondary contact method (denormalizing), and then create a compound index with the necessary filter keys first, and the sort key second, I think you’ll find the query (which no longer needs a join) is quite fast even if caches are ice cold.

Re: Squeeze the hell out of the system you have

#349

Post by fuzztester »

eru wrote: post #346
fuzztester wrote: post #327

Earlier quoted context omitted.

Oh, it was perfectly understandable to me too, even though I am not a native English speaker (but I have been told by native speakers that my English is quite good). "Everybody" seems to be in the third person and "you" is in the second person, so I thought it was a mismatch (since in the same sentence, etc.), and so was ungrammatical. Let anyone tell me if I am wrong, would like to know. And see my reply to sbuk, it…

Real world grammar is a lot more fluid and flexible, than just blindly following a bunch of fixed, mechanical rules. The mechanical rules are just an imperfect attempt at capturing parts of the richness of real world language, or more precisely: language variants of different dialects and speakers. Of course, there's a whole world of class markers overlaid here as well. If you want to sound middle-class educated in m…

>See fine.me.uk/Emonds/ for details.

Nope. Not important to me.

Re: Squeeze the hell out of the system you have

#350

Post by zepolen »

justinlloyd wrote: post #334
zepolen wrote: post #292

Earlier quoted context omitted.

He cached everything and delayed writes too. It's easy to make a system fast when it's not realtime.

Incredibly dismissive of somebody's work, aren't ya? I regret breaking my own self-imposed rule of never answering follow-up questions on HN because there's always somebody willing to hand-wave away six months of my life and 40 years of real-world experience with a flippant comment of "oh, but that's easy if you don't have too..."

Fragile ego too.
Post reply on HN