Michael: Hello, and welcome to Postgres FM, a
weekly show about all thingss Postgres girl.

I'm Michael founder of PG mustard.

And this is my cos Nilay founder of Postgres AI.

Hey Nilay what are we going to talk about today?

Nikolay: Hi, Michael, let's talk about checkpoint tuning,

Michael: Yes.

Write ahead.

Log in general.

Right?

Wow.

Wow.

Configuration.

We had a really good request for this topic from one of our listeners.

Nikolay: right.

So not only checkpoint two, but checkpoint two is a part of it.

Michael: Yes, absolutely.

Thank you to Chelsea for this one.

Nikolay: Uhhuh.

Thank you.

Uh, Very interconnected topics.

We, we should think about them, both wall configuration and point tuning.

They come.

Michael: Yeah.

Should we go through them one at a time in
terms of, well, so what, what is checkpoint?

What are checkpoints?

Should we start there?

Or what is the, what is, wow.

Nikolay: right.

So what, what are checkpoints checkpoints to talk about checkpoints, we
need to talk about wall first, write a head log and right head log is

like the basic, absolutely fundamental concept of FA database system.

Not only relational uh, which says like that first data is recorded.

Different place to, to some additional some
kind of binary log or like write ahead log.

And only then it's changed in memory in data pages.

And this allows us to build recovery system.

This allows us to survive unexpected restarts failures various bug and so on.

And in POS August, this also allows us to.

Uh, Physical replication because it's built on top of, recovery subsystem.

So if you think that you change some row in a table,
first, this change is recorded into right head log.

And only when we know that these changes recorded reliably in.

it's already on disk only.

Then you can see committed you, you can, the user can
seek commit and the actual change in data it's still

can be only in memory, not flashed into disk yet.

Right?

So, so basically we record twice.

We record to red headlock the change and we change data.

In place where it's stored permanently, but this flushing
to disk in the second case it's not occurred synchronously

so we can seek meet, but the data file tabled index files,
they are not yet in actual state, on disk and the checkpoint.

It's the process of writing so-called dirty.

Blocks, dirty buffers uh, two disk so dirty in this
context means changed, but not yet saved on disk.

So, if it's already saved, it's called clean buffer.

If it's not yet saved, it's called dirty buffer.

And when we have dirty buffers, it means that we changed a lot.

We already reflected these changes involved.

It's like the rule number.

Right ahead look, but it's not yet saved on this.

When checkpoint happens, all dirty buffers are saved
and it means that next time we will need to start.

For example, after some crash, power loss, anything we we, don't care
about previous changes because they're already reflected on disc.

Michael: Yeah.

So we only have to worry about things since the last checkpoint

Nikolay: right.

And we need to redo them.

Right, right, right.

Michael: Yeah.

And just before we move on from the basics . Because
I had to read up a bit about this the main, the main

reason we need this is to prevent data loss on recovery.

So it's the idea of the dual system.

The idea of, of having this is for the, the
D in acid, I believe the durability side.

So preventing

Nikolay: Right.

A, a C I D is like the core concept of uh, database
system should be like, if it says data committ.

it never can lose it.

Otherwise it's a bad system.

So right ahead.

Look exactly allows us to, to, to, to have it.

And without checkpoint, we would need to keep a lot of changes and replay
a lot of changes and start type time after any crash would be very long.

Right.

And so checkpoints happen all the time.

They happen like kind of on.

They also can happen during, for example, when we need to shut down
the server or restart, there is so called uh, Moy shutdown checkpoint.

So PSUs doesn't report that shut completed
until this shut checkpoint finishes.

right.

So, so it's also important to understand, and that's why sometimes
we can see that shutdown takes some, a significant time because

we have a lot of dirty buffers and we need to sell them first.

Michael: Yeah on this topic, I saw a good recommendation.

I think it was from you actually around Postgres
upgrades and the idea of taking a checkpoint triggering

a checkpoint manually to, uh, reduce the time needed.

Nikolay: Right.

Because During During shutdown down, checkpoint
POS doesn't respond to new queries anymore.

It says uh, shutting down.

So come later.

Right.

But if we run manual checkpoint a SQL command checkpoint.

It can be in parallel.

So we save a lot of dirty buffers ourselves, like just
running, select in terminal in P SQL, for example.

And then when shutdown check what happens.

It's already fast because there's very low number of dirty buffers.

Right?

Michael: Yeah.

Nikolay: So it it's always recommended when we need to restart
server, for example, for minor upgrade, or we need, for example,

to switch, to perform switchover this like manual not manual can
go to rent, of course, but additional checkpoint, I would say.

uh, Oh, explicit, explicit checkpoint should be there
because after it shut down, checkpoint will happen.

So we need to help it to be shorter, faster,

Michael: Yeah.

So you, you already mentioned a couple of uses
for the right head log one's recovery on crash.

One is replication, I believe put in time recovery is another one.

So tools like PG backrest make use of it.

I believe.

Nikolay: right, right.

Well, it's a different topic, but of course, right.

If, if we store full copies of P data by the way,
they never are consistent in consistent state.

They, if we, if you copy picture data on live server, it's not consistent.

So you need a bunch of walls to be able to reach consistent point
always but you also can store all the whole stream of walls, addition.

and uh, if this allows you to take some so-called Facebook up, picture
data, like corresponding to some point in time, and then you can replace

additional walls and reach new point in time and it can be arbitrary.

So if you store continuous stream of
falls in your archive, you can choose any.

You want, the only problem usually is if your database is
quite big, the initial copy P data initially takes time.

And roughly like very, very rough rule is one terabyte per
hour, but can be slower, can be faster, but very rough rule.

So if you have 10 terabyte database should be
prepared for five or 10 or 15 hours of initial copy.

And I wonder why people.

Don't use often cloud snapshots for that to, to speed it.

right.

It would make sense completely, but I know sometimes
they're not reliable cloud snapshots in Amazon and Google.

Like there are issues with them sometimes.

Also making them takes time, but we could do it on it's.

Like, I, I, I go, like I put us to different field let's let's
postpone discussion of backups and, and other things, but you're right.

Point in time, recovery is never a area of application.

Of wall, but the primary goal is to allow us to
recover from, from unexpected shutdown or power.

I think so it's, it's like in games, right?

Michael: I lost you briefly

Nikolay: yeah.

I see, I see let, let me listen to you then.

so

Michael: Well, yeah, so I'd be, I think it's getting better.

So we, we have a lot of parameters for tuning these things.

We have a lot of parameters for controlling how, how fast these
things happen, how much has to happen before they kick in.

There's a few settings that it seems like we
really shouldn't touch pretty much ever as well.

But it also seems that especially on high right workloads,
there are some really big wins we can get by changing some

Nikolay: Right.

Defaults are not good enough for you usually, right?

Michael: Okay.

Is that generally true?

Or like if I have a read heavy application and not, not a ton of rights,
am I likely to, what, what, how would I know if I'm hitting problems?

What's the kind of, what are the first telltale signs?

Nikolay: Well, that's a good question.

Usually.

it's good to evaluate a distance between two daily backups, for example.

And you, you can, even in SQL, you can even take to LSN.

Usually when backup is taken, you know, LSN log, sequence number.

So it's like position in wall.

It's all sequential.

It has some specific structure.

There are a few articles.

We will attach articles, explaining how to
read LSM and how to understand this structure.

So if you take to LSN and convert them to PG underscore
LSM data type in progress, you can subtract one

from another and the difference will be in bites.

So this difference, and then you can run PG Pretty or P size
and you can see it, the difference in megabytes, gigabytes,

actually gigabytes actually, but Pogs doesn't use this notation
so you can understand how much wall you generate during a day.

Right?

If this size is it's small, like gigabytes or 10 gigabytes, it's quite
small, probably you don't need specific checkpoint and well tuning.

but if you have one terabyte generated per day, it's quite
a lot, and I'm sure you need to go away from defaults and,

and you will have better performance, better everything.

For example, wall compression is not enabled by default.

And, And,

Michael: I missed that one.

That's

Nikolay: yeah, but I, I, I'm going to check if it's enabled in recent
version because PSGs defaults are improving, but still they are Legg.

If you have heavily loaded systems, you definitely want to tune.

So if you have for Pogs 13, for example, while compression is disabled
by default, let's talk about how, like, what is written in walls.

Oh, first of all, like just like simplify the explanation people usually use.

About checkpoints.

It's like in games, you want to save your progress.

Right.

And if something bad happens you, you will repeat fewer steps.

Right.

So it's very simple analogy.

Yeah.

It's still in Pogo in POS 14.

It's still not enabled while compression.

Yeah.

Pogo 15 is still not enable.

and this is I think should be enabled in most set ups.

I, I, I'm almost sure on just it's enabled.

So and if you can, for example, do checkpoints very rarely once per week,
it's insanely low, but in this case there are high chances that if crash

happens, you will need to wait a lot while POS replace many walls, right?

A lot, a lot to do in terms of software you do.

And during this period you will be down.

Your system is down.

So no, not good.

That's why I check like logically.

I would say it's good to have checkpoints more often.

Right.

Michael: It seems like a gold deluxe problem right too often.

And you have a lot of overhead, but too infrequent
and it will take a long time to recover.

So it feels like there's a balance.

Nikolay: There is trade off here.

And there are two kinds of overhead.

We will talk about it in a second, but
to understand where overhead comes from.

in spite of dirty buffers.

Let's talk about what is written to wall by default.

Full page rights are enabled, right?

And what, what is full page, right?

It's if you change anything in some table
in some row, it may be very small change.

SVI writes whole page.

to write a headlock.

Why?

Because there is a difference.

Buffer is usually eight kilobyte size Kiy size , but
file system probably uses block size four Kiy bytes.

And you don't want to have partial right
during writing to disk reported a success.

But you, wrote only.

Half of it.

So that's why full page writers need it.

And by the way first PSUs first talks about Aurora from grant McAllister.

If I'm not mistaken they are very well.

We.

Find links to YouTube and probably slide deck.

They explain very well, this problem about
full page rights and this big overhead.

So the, when first change in the page occurs, first time
it's written after checkpoint it's full page, right?

If you change it once again, only small
Delta is written, So it's not full page.

Right.

But only until the next check.

if checkpoint happened, all changes are initially again, full page rights.

If checkpoints are very frequent, we have a lot of full page rights.

If checkpoints are not frequent, very often we
have repetitive changes in this of the same page.

So we changed.

We, for example, wrote inserted something.

We insert once again into same page.

It's new change again.

And again, we, we update something.

We have had to update hip only two top update.

So we change it the same page.

And this means we touch the same page.

We write to it multiple types in this case.

so I'm saying not only the number of rights
matters, also the nature of rights matters.

If you have heart updates, touching the same page, many, many times you
will benefit from rare checkpoints a lot because only one full page right.

Will happen after checkpoint.

And then you benefit having very.

You write a little bit to, to write a headlock EV every
subsequent change until next checkpoint of course.

Michael: yeah, that's super interesting.

And I think also.

it explains why people, so there's, there's some people I've seen.

I, I suspect this is a very bad idea, but turning off full page rights in
order to increase throughput, but it feels like a very risky thing to do.

And that's that there, I feel like we're gonna cover quite a few better

Nikolay: some systems can afford if uh, what you, you need to understand
what your, your file system is your disc and like what settings they use.

And in some cases it's possible, but it's quite dangerous.

You should understand all the risks and be a hundred percent sure that
it's it's possible in your system, but usually we don't go this way.

Usually we use X four with 4k box size and so on.

And we, we want full page rights enabled.

So back to compression compression is applied only to full page rights.

Michael: Interesting.

Nikolay: right.

So we, we don't compress as I understand POS
doesn't com compress these, small changes.

It compresses only these.

first time we change something in a page.

We record this page fully and we can compress it.

And compression is not enabled by.

and if you enable it, you can see huge benefit in terms of how much
wall you write, why we care about volume here, because if we have

right heavy system, of course writing a lot, additionally to wall
it, it like it's additional overhead on disk if you have 10 replicas,

sometimes people have it, all replicas need to receive this data.

F they work rep physical replication, logical as well.

It works through wall through writer headlock.

So if we write a lot, we need to stand over network a lot.

They want wall compression enabled to compress all full, page rights, and
we want checkpoints to happen rarely to have fewer full page rights as well.

So I would tune.

Maxwell size and checkpoint timeout to, to have very, very, not
frequent checkpoints, but in this case, if they are not frequent

again, start up time after crash, also follow over, for example, it,
the timing of these procedures will be very, very bad, long minutes.

Some I see various engineers struggl.

To understand why.

For example, shutdown takes so long why this start takes so long and
they become nervous and that at extreme cases they use kill minus.

So secure, like POG survives because we have right
headlock and we just redo, but do also takes a lot.

They, they kill it.

It's it's not acceptable.

Only in rare cases.

We should do it.

It's like last resort.

We should not do it.

But on after read, PSG starts and start up takes also many minutes.

They're still nervous.

It's not a good situation.

That's why people need to understand.

Like how much wall needed to be written and distance between checkpoints,

Michael: Yeah, let's go back to a couple of those ones you mentioned.

So my understanding is that checkpoint timeout is a maximum time
between checkpoints and that's default, quite low is five minutes.

Nikolay: Very low.

Michael: so what would be a sensible
starting point for most people in terms of

Nikolay: Yeah, so usually the main metric here is how
long you can afford being down in the, in the bad case.

In the case of an incident, this is the main number you need to understand.

You need to talk with your business people and find some number
like, okay, we can be down up to two minutes, for example, right.

From there, you start thinking.

if we have this like requirement or SLO service
level objective, if, if we are SRE, right?

So if, if we have two minutes, let's think, during two minutes,
how much we can replay, we can measure it with, with experiment.

We can, for example set checkpoint time out and
Maxwell size to value insanely big numbers.

Then we can have a lot of rights happening, PG bench, for example,
then we can wait until new checkpoint one checkpoint finishes.

Now there is about to finish and then we
kill Stan, our PSUs crash it on purpose.

And then we see recovery and just measure the speed of recovery,
how much how many bites of wall we can replay per second per minute.

And this gives us understanding of how much wall we can
afford to replay, not to exceed two minutes of downtime.

For example,

Michael: Yep.

Perfect.

Nikolay: from this, we can start thinking like,
this is very important to understand recovery.

in terms of bites per second bites per minute or gigabytes per minute.

anything here from there, we can understand how, like how many bites of
wall we produce when everything is normal, during quiet, busy hours, usually

at night, for example, we have lower activity at daytime on working days.

Probably we.

More activity, right.

But usually we say, okay, we produce, like, for
example, one wall per second, it's quite good speed.

Each wall means this file.

There is also confusion in terms because is a, I remember
the commutation says wall file is some abstract thing.

It's like two gigabytes and wall segment is 16.

Maybe bites, but if you go to picture wall
directory, you will see H file will be 16 megabytes.

Usually, as I remember, they use it and they have 64 Miyes British wall.

So I say walls.

Each wall is usually 16 megabytes.

So one wall per second, during normal, quite busy
hours, it means we produce 16 megabytes per second, or.

Right.

So, okay.

And it means that, okay, what is our replace speed?

What is our production speed?

And from there, we can understand during which time we generate that amount
of wall data, which will give us two minutes of recovery time, right.

Quite complex.

I understand.

Michael: well, it feels like luckily we've
got that second parameter in max wall

Nikolay: No, no, no, no.

Let's, let's let's pause.

Yes.

I'm talking about how to, like, in my opinion, how to understand
what's the best what's like normal checkpoint time out for you.

Right?

So in this case we understand, okay.

Recovery time is this production time of production.

Is this.

So we can conclude that to have not more than two minutes of recovery
time, we, we need to have to, to, we produce this number of wall data.

So we will set point time out, probably like half an hour.

This is like quite, maybe 15 minutes, 30 minutes.

It depends, of course observing concrete system.

We can.

make some conclusion.

Okay.

We want 30 minutes for example, but then we start
distinguishing plan checkpoints and requested checkpoints.

Requested checkpoints is like POS has two logics.

One logic is okay.

One schedule when time comes time to, to have checkpoint every 30 minutes.

For example, by default is five minutes.

I think it's too.

Right.

But then if there is another parameter called Maxwell size, and I think it's
very, very important parameter to understand it's our protection for the cases

when we have elevated activity, And we want to have more frequent checkpoints
because we want to be protected again from writing too much data to wall and.

Again longer, wait again.

The same logic.

If we understand the, how much we produce
the speed of production, we can say, okay.

Maxwell size also roughly corresponds to so, so checkpoint
time out and Maxwell size, they tuning can be correlated here.

Michael: Yeah.

So my, my understanding is you, it, it sounds like we should
rely on checkpoint timeout for the majority of the time.

That should be the thing that, that kicks off checkpoints, but.

If if more than that amount of wow is generated more
than the amount we're expected, we could set an amount in

Nikolay: We should say, we should say that so usually like, and default is
there very small one, gigabyte default is insanely small for modern workloads.

Usually like I recommend to go up sometimes up to hundred gigabytes.

Which we need to understand this recovery trade off.

Right?

So we need to measure recovery and guarantee our business that we
will not be down more than for example, two minutes or five minutes.

Right.

But right.

Maxwell size protects us from the cases when we have more
rights and PSGs can decide to perform requested checkpoint.

We see it on logs, by the way, logging of checkpoints.

We.

Enable always, as far as I remember recently,
default was changed and login is, is now enabled.

I, I remember discussion in hackers and mailing list.

So log checkpoint should be enabled for all checkpoints.

I I'm hundred percent sure.

This is what you want to understand is default is, was false.

Default is disabled in August 12, disabled in August 14.

but enabled in Pogs 15, which will be released very soon.

So this, this is new change in Pogs.

15 log check point is enabled and I recommend enabling it for
any Pogs, so I, I also saw some DBAs see that like 90% of all

checkpoints are requested, they occur according to Maxwell.

This is a problem.

No, it's not a problem.

It's not a problem because request a checkpoint
and time checkpoint like planned on schedule.

They are the same actually, like not no big difference, but of
course you want to be in order, everything should be in order,

of course you want like it's, it's just a sign that probably you
need to reconsider savings, but it's not a, an urgency situation.

Right.

Michael: Yeah,

Nikolay: Well,

Michael: good.

Nikolay: There is another uh, checkpoint completion target.

We, we didn't mention.

And by default it's 0.7 or, or,

or

Michael: this changed.

I, yeah, I looked this up until very recently.

It was not 0.5.

Nikolay: oh, 0.5 is terrible.

I would say

it's not what you.

Michael: Yeah.

But in 14 it was increased to 0.9.

Nikolay: great.

This is good number.

So what it, what is it?

Since when you run manual checkpoint, explicit checkpoint, it goes full speed.

So it rises dirty buffers to disc as fast as possible.

And it produces some stress on disc it's.

It's a okay.

Stress, but it's normally you want to be.

More gentle with your disc system.

Right?

So that's why we spread it over time.

And 0.9 checkpoint completion.

Target means that between two checkpoints,
90% of time, we want to spend the writing.

And 10% of time we are resting.

Maybe you want even more 99.

I don't know, like 99% of time.

So, and, and, and this is important because It's
hard to understand the distance between checkpoints.

It's quite tricky question.

Logging will report something, but you can think about when checkpoint starts.

This is like the beginning.

So 10 minutes between them is like, or 15 minutes between them.

Or 30, it's fine.

But what I wanted to deliver, this is very tricky.

It, it bothered me a few years actually, and only in the book
of Gogo already mentioned it, this Pogo scale internals of.

So I, I read it in Russian even earlier now it's published.

Both parts are published in English.

It's very good.

It explains everything in detail with links to source code.

And finally, I understood why, if we set Maxwell size, one gigabyte, the
distance in bites measured in bites, it can be like 300 something megabytes.

So it's like three times.

Michael: Why is that?

Nikolay: so explanations is interesting.

I'm looking at it right now.

So I know I knew it from practice.

I just like when I said, well, I saw, oh, you have default one gigabyte.

You know that it means that the actual distance measured
and bites will be 300 megabytes is tiny, like distance.

It means that checkpoint will disturb your system constantly.

And they, I even saw the case of very large.

Where people had um, some cleanup job on the background happening.

And then before big event marketing event, they disabled this job.

And then a couple of months later they realized that
job is disabled and some engineer very experienced one.

But not Pogo expert.

He like said, okay, this job was not painful at all.

It, it was working many years.

So he went ahead and tried to delete 10 million million
rows using one delete and put system down for 10 minutes

because they didn't have PO uh, checkpoint tuning in place.

So Maxwell size was default.

One gigabyte actual actual distance was 300 gigabytes.

I will explain.

it means that when you produce a lot, you
have checkpoints happening all the time.

Boom, boom, boom, boom.

And a lot of full page rights.

Boom, boom, boom.

It's not compressed and discs work quite good, like enterprise discs, but
not and V me unfortunately, and they just, situation happened and they went.

For 10 minutes since then, just one delete.

I even had a talk in, I did it in Russia sometime ago.

Like just about this case, how delete can put your one, one line of delete
can put your S down even before you worked very well and like critical system.

But so checkpoint tuning is important thing to have.

So if you have one gigabyte until post this 11, it was,
if you have checkpoint completion, target close to.

you should divide by three since post August 11.

You should divide, divide by two.

So if you have one gigabyte box full size,
actual distance will be half a gigabyte.

Roughly if your checkpoint completion target is 0.9 because
posts needs everything since last checkpoint and also

everything between previous one and the latest one latest.

Michael: Oh,

Nikolay: and before post 11 additional cycle was needed.

so two successful cycles and a tail, right?

Not tail it's this tail is before not behind us.

It's in front of us.

Right.

So if checkpoint competition target is 0.9, like
roughly three times, like three intervals needed.

Michael: Yeah.

Nikolay: That's why you need to raise Maxwell size anyway.

Right?

Michael: Yeah, that seems like a, almost
everybody would want to increase that one.

I I've read like.

There's some other interesting ones that are be keen on your view on,
and also actually, I guess, should, are people on cloud providers?

You mentioned RDS a couple of times.

Are they generally more protected from this?

Because they've been tuned already.

Nikolay: Tuning here means increasing Maxwell size increase,
Maxwell size, but do it not blindly understanding recovery.

Michael: Yeah.

The other ones side for, for example, I've read that, that can increase right.

Performance if you increase that number

Nikolay: my practice.

I cannot say anything here.

Like I, I, I didn't dive deeply enough to discuss this, but

Maxwell sizes, my favorite topic

Michael: Yeah.

If you haven't had to worry about meanwhile size, then I can't imagine it's.

That important.

So, yeah.

Good to know.

And yeah, the, the only other one I wanted to ask about is,

Nikolay: let's let's sorry, like it's so important.

I just want to emphasize it, you know, like, so if we have very short
distance in terms of Maxwell size, first checkpoints, and we have unexpected

or maybe expected someone decided to do it, a lot of rat activity.

We can measure it with experiments and what I found that, you know think
loans is good to iterate, but we cannot use think loans here because we need

to our discount file system behave exactly the same as Amazon production.

So I found good recipe, how to have some workload
which will not touch our physical layout of data.

It's.

Transaction of massive delete, like delete 10 or a hundred million rows, but
cancel it in the, in the beginning, roll back, delete will write to X max.

We discussed it a couple of months ago.

Probably it'll write the transaction ID, which deleted double, but

if transaction got cancel, , this is virtually zero.

Zero means it's this table is still alive.

So we produce a lot of wall, produce a big stress on system, but then we
say nothing changed and we can do another experiment on the same system.

It's perfect workload for a lab, right?

So we can have a sequence of experiments with different Maxwell.

and see using like IO top, or I know
iostat, we can see, we can use monitoring.

I recommend to using need data because it has export button.

You can export all graphs and you can see how this cloud behave.

And usually if you have one gigabyte Maxwell size, and discs are not
very powerful, you'll see plateau because it's saturated . Right.

Then.

double your Maxwell size plate again, double max.

At some point you will see your system under such stress.

It's already not plateau because, and IOP
shows when Maxwell size is small IOP shows.

If you're ordered by right throughput check
pointer will be number one, it writes.

200, 300, I don't know, 500 max per second, like, right, right, right.

Also I, promise to explain two reasons of our head one.

We already discussed full page rights.

If we just finished our checkpoint and we needed to start
another because Maxwell size commands us to, have them very.

it's like insane.

Checkpointing insane.

Checkpointing like check pointer went mad.

Right?

So for example, I saw like every 15 seconds.

Boom, boom, boom, boom.

Because these deletes, like 300 mix.

It's quite quite Fastly.

So again, again, again, default settings.

So, so full page rights is one type of overhead.

So basically, oh, also make your deletes no se.

for example, you can have some index on some random column and you can say
let's delete first 10 million rows ordered by this column, but it's random.

So first apple is first page.

Second.

Apple is page number thousand.

So we , jump between various page.

and this is the worst type because we, we could benefit.

Like if you, if they are sequential, probably all changes
in one page will happen inside one checkpointing cycle.

But if we jump between pages, we constantly produce a lot of full page rights
and we need to produce them once again, because checkpoint just finished.

Right.

This is the worst situation.

And this happens, this is exactly what put that system down that I explained.

So second I didn't realize, but it's quite obvious.

Second overhead is quite obvious.

If our page was dirty, it was checkpoint.

Now it's clean checkpoint.

Uh, Okay.

We missed once again, it became dirty.

Again.

We need to write it again.

If two rights would be inside one checkpointing cycle, we would produce only.

But if two hour visits happened in different
checkpoint cycles, we need to perform two discards.

Michael: it's more IO.

Nikolay: Right?

Right.

So sequential delete is not, is not that bad.

A random delete according to some index can be right?

Michael: that's a good point.

So as well as all.

Nikolay: Sorry.

I like I so so much fun.

I spent some months exploring it and we made a lot very good.

Like I would say enterprise scale experiments and I, I, I can take any system
and show exactly how recovery will behave, how exactly you need to tune.

I, I can like show graphs.

It like it's quite expensive in terms of
time and probably money research of system.

But I think big systems.

They need to understand their workload, their
system, and understand what will happen.

So this random delete, I, I say this, like I named it double unfortunate.

You can be unfortunate because you crashed.

And you you unfortunate twice because you
crash during some uh, random intensive rights.

In this case, you definitely want to understand your Maxwell point I'm out
and you want your this choreograph not to have Plato, but be like spiky.

And this is a sign that you have some room for other eyes.

This is like our research shows like, okay, at 16
gigabytes or 32 gigabytes, we already don't have Plato.

So this is our desired setting for Maxwell size,
maybe a hundred gigabytes seven divided by two.

Like we need to understand since S well, but, and then we say,
okay, but in this case, recovery time can be in at normal time.

It'll be this at bed times when somebody is writing random.

a lot.

It can be these like 10 minutes.

Can you afford it or it's not good here.

So, uh, you see how much like I, I had in the past with Maxwell size,

especially.

Michael: Yeah.

This is great.

And I hope people are encouraged to go.

And you can easily check this by the way.

Can't you just with all like with all Postgres
parameters, you can just use show, show max, well size.

If it's, if you get one gigabyte back maybe time to have a look at that.

Same with checkpoint timeout.

So show checkpoint, time out, check out that if it's, if
it comes back five minutes or it might say 300 seconds.

Again, another one to look at

anything else.

Nikolay: there are other things, but let's, let's
stop at this point because we are out of time.

Definitely here.

I apologize for too, too many details in this case.

Michael: I don't think, I don't think that's the kind of feedback
we, I, if, if anybody thinks we did do too many details, let me know,

Nikolay: Right.

Michael: think that's gonna be the,

Nikolay: And I, again, I, I want to like advertise need data here,
because if you do some experiments and some, you, you take like

same virtual machine, same settings, everything as production,
you do this very unfortunate, massive delete rolled back again,

delete, but can you check with various Citrix, do install net data
and export all dashboard with all this Cayo and everything to file.

And then you can compare, you can open and browser several.

Several files and, right, right.

And see exactly the difference in behavior for different settings.

It's so convenient

and you can

store the,

those artifacts term.

Michael: Yeah, I really, I enjoy doing showed me that.

I, I also wanted to advertise a few things.

There's a couple of great websites for checking out parameters.

If you want to see like what they mean.

Obviously the Postgres documentation's
great, but there's also Postgres code.nf.

Oh, Postgres comp by I'll link up.

And PGP here as well.

I find great for this kind of thing.

They have a section on this I found useful.

So I'll, I'll share those as well.

Nikolay: right.

But like you, I, I suppose if you have, have a lot of LTP
system, you probably will set checkpoint time out of 15 or 30

minutes and Maxwell size to something like 32 gigabytes, at
least maybe more but better to conduct full-fledged research.

and make decisions based on your requirements from business.

Michael: Yeah,

Nikolay: Good.

Michael: so much Cola.

Thanks everyone.

Nikolay: Thank you everyone.

Yes.

Share like share, share, share, share is important.

Most important, probably.

And if you, by the way I have special request to our listeners today.

If you have a IO device, please go to apple podcast.

and like us please.

And write some review.

would

Michael: Yeah, I dunno if you saw, but we got a nice one already.

So thank you to that person.

Nikolay: Good.

Thank you, Michael.

Michael: Cheers everyone.

Nikolay: Bye