‫Welcome, guys.
‫This is a video that I took from my show on my YouTube channel, which is called the Back End Engineering
‫Show, where I discuss different back end technologies and from networking to databases to proxies to
‫low level.
‫Things that related to the backend Web servers, protocols.
‫And this particular episode was discussing in details.
‫The rider had log the reader logs and I thought, I'll put it up in this course as well.
‫But because it's available on my YouTube channel, obviously I'm going to make it preview only here.
‫So you can effectively see this as a preview and I hope you really enjoy it.
‫I think it's a good addition to this course.
‫It kind of glue things together.
‫We talked about we have a dedicated obviously lectures for the wall, but this is almost like a, you
‫know, things that tying with tying in different experiences together into one around a 30 minute video.
‫I hope you enjoy it.
‫Database logs are a critical component of any dbmr system in order to ensure durability and crash recovery,
‫if you will.
‫So logs like I'm referring to logs such as the right ahead log or wall for short while.
‫That is the redo logs and the undo logs.
‫These are the only three that I know.
‫There must be other type of logs that are there for specific implementation, but I think these are
‫the three most popular.
‫In fact, you can argue that the wall is actually identical to the redo logs.
‫You know, and I'm going to talk about all of this in this episode of the back end engineering show.
‫It's been a while.
‫Welcome to the back of the engineering show with your host, Hussein Nasr.
‫And.
‫If we really think about durability.
‫In database system.
‫You.
‫You really need to think about.
‫How do you persist?
‫Data.
‫Right.
‫Well, you might say I say, okay, you have tables, you have data structures on those tables such
‫as indexes, indexes, sequences, constraints, stuff like that.
‫And these live in files because that's why we have today file system.
‫So we have to work with files.
‫You might argue that no, we can work with block storage directly, but most generic implementations
‫work with files and there are pros and cons to that.
‫Right.
‫And I like to always think about this, you know.
‫Think about.
‫There is not only one way to do things and never pigeonhole your thing into one thing.
‫You know, always look outside the box, as cheesy as it may sound, you know, trying to look outside
‫the box and then understand why do you.
‫Do you say the things you say?
‫You know, so things live in files and we're working with data files.
‫So table live in a data file indexes live in a data file.
‫And this is up to you as the database implementer to whether to put these to into one file or put them
‫in separate files.
‫There are pros and cons for both cases, and whether you put all indexes in one file or put each index
‫in one file in its own file, it's all really depends on the implementation.
‫And that and this is something we never think about as back in engineers like because hey, it's behind
‫black box and the database does this thing what is good to understand how database is implemented because
‫is the database is just a program at the end of the day, right.
‫It's not really.
‫Well, what I was about to say rocket science, but.
‫You can argue it is as complex as rocket science.
‫Database engineer.
‫So.
‫When when I make a change as a transaction, I begin my transaction, I start changing my table.
‫The changes that I make to my table will trigger side behavior to update indexes, and these indexes
‫need to be updated as a result.
‫And the tables need to be updated as a result.
‫The tables consist of pages and the pages are touched and getting dirty.
‫And at the end of the day, we we going to say, commit my changes.
‫I just made a bunch of changes.
‫Go ahead and commit them.
‫Now if I say commit my changes.
‫What does that mean?
‫It means logically, I want anything that's high, high level speak.
‫Everything that I wrote, I want it to be there forever.
‫I want to see it next time I read.
‫And this means I want it to be durable.
‫That means if the database shut down after I successfully committed.
‫And that's another thing.
‫What does it mean to successfully commit?
‫It means I receive a successful synchronous response from my commit operation.
‫So I say, yup.
‫All done when you tell me.
‫Yup.
‫All done.
‫I assume that even if I shut you that down in that second, if I unplug you, I come back.
‫Everything that I wrote should be there.
‫So if we go back to this commit operation for a minute and ask yourself, how is this actually implemented?
‫How can I implement it?
‫Let's not get pigeonholed again with implementation.
‫One way you would implement it, you say, okay, any time I write, if I begin my transaction, I start
‫updating a row and inserting a new row and I doing all this changes and I update the indexes.
‫I am going to make all these changes in memory because you see, if I want to update row number seven,
‫I need to fetch the page where row number seven lives and update the page in memory.
‫That's what I'm going to do.
‫That's why I'm implementation.
‫I'm going to write in memory.
‫Then I'm going to insert a new row on the same page in memory, and then I'm going to insert another
‫row.
‫But this table is cluster.
‫So the pages at the end at the tail of the table.
‫So I need to fetch that page, fetch a put in the pool, the buffer pool, the memory, and then write
‫to it.
‫And I keep writing to memory, writing to my writing memory and that's fine because these changes are
‫dirty and I didn't commit right now if I say if the client tells me to commit, I'll take everything
‫that I have in memory and I literally just flush it to disk.
‫That means I am overwriting the same location where the page existed in the file with my changes.
‫And you can see if you make a lot of changes, the commit operation will be slow.
‫Right this implementation naturally, because all of this stuff is your memory and you have to take
‫time to persist these changes to disk.
‫Try, try, try as you as you ride them to disk.
‫You're taking a hit, you're going to the disk and writing these different pages and that might take
‫a while to write all this.
‫And the problem here is not the time, per se of this implementation.
‫The problem is what happened.
‫If I if you wrote half of these pages and the database crash in the middle of your commit.
‫That is dangerous.
‫Why?
‫Because you just flushed something and you changed the presentation of the table for a transaction that
‫was half --.
‫It was half committed.
‫And what does it mean to have committed?
‫Well, it doesn't mean anything.
‫I have committed the transaction is a rolled back transaction is a bad transaction and should not be
‫considered.
‫That's rule number one and asset transactions at home, etc..
‫So that's a problem.
‫That implementation sucks, right?
‫But it was so good because my rights were so fast, right?
‫Because I am writing.
‫All my changes are in memory.
‫But that sucks.
‫So what people did, what computer scientists did.
‫We said, okay, computer science said this.
‫What if.
‫As I am writing these dirty changes, I keep a log, an actual journal.
‫Of what exactly changed?
‫Not the whole thing.
‫Because remember, when you pull a page of six kilobyte, a kilobyte page depending on the SSD size
‫and the database page size and how they agree on that.
‫And you change.
‫One bite.
‫Or he changed 32 bits or whatever the size of the role you changed and you said flush.
‫There is no flush one.
‫But when it comes to disk.
‫Not until we get byte address ability on on SSDs and hard drives.
‫It doesn't exist today youif the minimum size is called and people argue about this.
‫It's called a page.
‫I believe in SSD.
‫Sometimes it's called double lock, sometimes it's called erasable unit and a database and hard disk
‫is called sector.
‫There is a specific size and we don't care what is called because names don't go anywhere.
‫To be honest, this is I've been in this business for a long time and everybody invents a name from
‫their --.
‫So you can invent your own name from your own -- and be satisfied with that.
‫But what what that technology does not allow us to write one, but because of physical limitation of
‫the disk, because it's not worth it to write one byte, I believe that's the reason.
‫So we write a bunch of them.
‫So if I change something that I have to write eight K, even if I change one thing, right.
‫And that's that's costly.
‫So that back to what computer scientist say, okay, because we have this limitation and instead of
‫I changed one thing, let's keep a log of that thing that changed.
‫That's a good idea.
‫And let's make sure that that log is immediately persisted to disk because that log.
‫That.
‫The Journal is the source of truth.
‫You change this role, A became B and RO.
‫Column seven in row six became this value and the string.
‫Hello becomes world.
‫You make changes you can't you just journal the changes and this is called the right ahead look and
‫it's called right ahead because you're writing ahead of time almost you're predicting.
‫Was going to change because that's that's that's what's going to change.
‫So.
‫If I write this now and I have this log and I make sure every write I do to the wall, to the right
‫ahead log is persisted to desk.
‫Then that's nice because I can keep my dirty pages in memory.
‫Now.
‫If I say commit.
‫I already written all the changes to the log.
‫I have a journal of every possible thing that my transaction did and all other transactions as well.
‫So I can replay this if I want to.
‫So now let's take this into consideration again.
‫You have a presentation of what the page looks like or the table looks like on disk.
‫You pulled it into memory and you start making the changes in memory.
‫You still make the changes to that page in memory, but you also take note of what these tiny changes,
‫just what changed into this wall.
‫So the wall will be so compact and it's also compressed and done all sorts of stuff to it.
‫And you flush the changes to disk, you keep those in memory.
‫You might say, Hey, you're not flushing this, it's okay not to flush it.
‫We're going to talk about it.
‫We're going to need to flush it eventually, but not immediately, because this are expensive.
‫These are large pages.
‫So now you keep all these changes.
‫And now you say commit.
‫Come the transaction.
‫What is the minimum amount of fork that the database needs to do to commit?
‫Well.
‫All you have to do is just commit.
‫Make sure all the walls committed.
‫And if the walls committed, we can persist.
‫The fact that this transaction is successfully committed and we can crash if we want.
‫You might say in the pages are old.
‫That's true.
‫The pages in the disk still is in its original representation, but the memory was the final one.
‫It was.
‫Everything we made.
‫The changes was in the memory.
‫We don't have to commit that.
‫We can try later, but we don't have to.
‫So let's take this consideration that I committed and I committed all the changes in the wall or the
‫right to ahead log and boom.
‫My database crashed but I committed it comes back up.
‫They detected that.
‫Well, the page now is completely out of sync with the wall.
‫Right.
‫Because the wall is ahead.
‫In this case, the write ahead log is literally ahead of the data files.
‫The data files was the old original one.
‫So the database knows this.
‫How does it know?
‫Because there are records of this and says, oh, wait a minute.
‫It starts up as, oh, wait a minute.
‫This is old.
‫I can't let people read this because nobody reads from the wall.
‫That's another question I got from the database course.
‫What if I can?
‫I read from the wall.
‫Can my transaction go to the wall or read it?
‫That's a bad idea.
‫Well, you can.
‫Let's not say anything.
‫It's bad idea.
‫It's just you can.
‫You can do anything you want.
‫You own the software.
‫If you're building it, you own the software.
‫You can write anything you do, you own it.
‫You can you can decide, hey, let's write from the let's read from the wall the changes.
‫But the work that you have to do is enormous.
‫And clients don't necessarily want to do that or they want to read a nice tucked in page and they want
‫to just crack the rows and read them.
‫They don't want to just figure things out because remember, this only has the changes.
‫You still need more stuff, so you'll end up reading multiple places.
‫So it can be done.
‫But it's hard.
‫So an Airbus crashed and stowed back and then it detected that there was a crash.
‫And the page that is on this is out of sync.
‫The wall is ahead of us.
‫Right.
‫The writer had.
‫Log is way ahead of us, so the wall is ahead.
‫So what are the debates do?
‫Text the page.
‫And then reads it into memory and says, Wait a minute.
‫Let me apply the changes in the wall to this page and then start redoing the changes because at one
‫point we did that changes.
‫This is a redo of these changes.
‫So you start redo these changes, applying these changes again, because this have been done at one
‫point.
‫Right.
‫But we lost it.
‫Now we're redoing it.
‫That's why the wall is also called the redo log.
‫So we're redoing these changes.
‫So take that beautiful old stale page, which is consistent, by the way, because at that moment of
‫time we didn't then apply that that, that, that, that all the changes that that transaction or other
‫transaction made were until we are done with the wall.
‫And now that page is freshly dirty with the latest changes.
‫So I can have clients read from this dirty page and I can safely flush this page to disk.
‫All right.
‫And now when you flush this page to disk.
‫You're you're now consistent with the war effectively.
‫You might say now.
‫Now, this is a very good point, right.
‫We talked about the data files on the indexes and we talked about the wall and how often should I flush
‫data files to disk?
‫It's up, really.
‫It's all of this is configurable for you as a DBA.
‫You know, there is something called the wall size.
‫How big the wall can get.
‫The wall shouldn't you think about shouldn't go infinity, right?
‫Because once I flush.
‫That dirty changes.
‫The wall can be perched.
‫Right.
‫Because those changes are already synched with the data files.
‫There is no point for me to keep these old walls.
‫All changes, because those changes has been completely the only purpose of the wall was in case of
‫a crash.
‫I can recover and redo the changes and if you did them and all the data files are in sync, why the
‫heck do you keep them around?
‫It's just wasted space.
‫That's why the wall is also presented sometimes as a circle.
‫And you.
‫You.
‫Whoa, whoa, whoa, whoa.
‫You're right.
‫I try change and change.
‫Changes, changes.
‫And once you end the end of the circle, that means it's time to flush the walls almost full.
‫Go and flush every data, file that page and memory down to disk.
‫And if the flushing was successful, go.
‫And first of all, you might say, Hussein, isn't this the same problem that you originally started
‫when he started this show?
‫You talked about that.
‫What happened if while you're flushing that page, the database crashed?
‫That's fine, because we know.
‫Right?
‫We know the moment where the the pages were consistent and we know that the wall is not whatever's in
‫the wall is the truth.
‫So you go back and you say, okay, from this point, that's when things were good.
‫That change is all garbage.
‫So you need to remove all these changes and then you have to reapply them again.
‫So that's that's another thing that the database do.
‫So in the case of a crash, so it's a complicated process and they take care of all of these situations.
‫So the that process that we talked about just now, where the flushing of these dirty pages so that
‫we can get rid of the wall is called check pointing and.
‫It really happens at the most random time because check pointing.
‫If you think about it is a data intensive, oil intensive operation.
‫Because now what I'm doing check pointing.
‫It depends really on the database implementation.
‫But I believe.
‫Things need to get paused during checkpoint because you really need to make sure that no new things
‫comes to the wall as you checkpoint.
‫You can you can you can argue that, hey, I'm going to put a checkpoint point on my wall.
‫This is where I'm checkpoint right now.
‫Write everything, flush everything to disk.
‫And then, yeah, the wall can continue to grow and transaction can keep coming in and you can implement
‫something like that.
‫Right.
‫With a little bit of finesse engineering I guess.
‫Right.
‫But yeah, I believe it's going to be a little bit complex.
‫But these checkpoints in operation and Chris warns about it, my SQL warns about it, every database
‫warns about, hey, just, just watch out for this because CPU ram disk activity will spike.
‫As checkpoint happens and this could happen at the most random places.
‫You might have a very high intensive workload.
‫Right.
‫That is happening at the same time as a checkpoint and that's will suffer as a result.
‫Do you want to suffer?
‫Well, the life is suffering, unfortunately.
‫So we all need to suffer.
‫Sometimes we cannot escape this, though.
‫But you have you can understand now that if this happens, right, this explains why sometimes it takes
‫a fraction of a millisecond.
‫Sometimes it takes 50 milliseconds out of nowhere.
‫Like, what is this?
‫Because the database might be doing something.
‫So what do you do?
‫Well, you try to make the wall as short as possible, right?
‫As small as possible.
‫If you make a wall as small as possible, then the checkpoint size will be smaller.
‫So the flushing will be more frequent.
‫The checkpoint thing won't be more frequent.
‫And.
‫In this case, you're only flushing certain amount of data as a given time, and that could be tolerable
‫and it will be almost consistent.
‫And you can argue, well, let's make the wall so large such that, yeah, I don't want to deal with
‫checkpoint point until I don't know, certain time, but if you kept the that for a long time that it
‫can also grow into other problems.
‫Everything is a trade off.
‫Unfortunately, I don't have solutions to any of this stuff.
‫It's just understanding that this exists and we have to deal with it all.
‫For World, for durability.
‫We are doing all of this for durability.
‫We want to be durable and we want to recover and be consistent in case of a crash.
‫If you can guarantee that you will never crash the room of the wall.
‫I dare you remove the wall.
‫So some say computer scientist built a wall and the DBA is.
‫Paid for it.
‫Get it?
‫No, that was a bad joke.
‫All right.
‫So we talked about redo logs, which is the wall the writer had log and and go go to the configuration
‫and you will get to see this plaster everywhere wall this wall that wall time wall this wall flush time.
‫What do you want, f sink or not, right?
‫Yeah.
‫Let's talk about this.
‫Actually, the f sink and the wall.
‫So you see, if you are building your database on top of an operating system.
‫You might say.
‫What kind of stupid statement is that, Hussein?
‫What else are you going to build your database on?
‫Hey, I have to be very specific, because you might build your own operating system.
‫That is a database.
‫Yeah, because you might.
‫You might.
‫You might build your own OS.
‫That happens to be a database without the bloat of the operating system.
‫But if you decide, like any database that is to build your database that lives on top of this bloat,
‫it's called the OS the general purpose.
‫Right.
‫OS Then you have to live with the rules of the OS which is Linux or Windows or Mac.
‫Or Temple Oaks.
‫Is that a horse then?
‫There is there is something that the operating system does because it's general purpose.
‫It doesn't trust.
‫Any app says, hey, all apps are stupid.
‫So if the if so.
‫So the apps tend to do some repetitive job.
‫They write to the same file multiple times in the same microsecond.
‫You know, if the operating system let that right go to disk immediately, your SSD will be dead effectively.
‫Right.
‫And in a few.
‫Because let's say.
‫Right.
‫Because like, how many times do you write save control as controls controllers?
‫Imagine all these rights going immediately to the right.
‫No.
‫What the system has is like it has a file system cache.
‫So if you write something, it says.
‫You really want, right?
‫Listen, let's just wait.
‫Let's buffer these rights so it it buffers these rights in memory and it keeps them in memory in the
‫cache, hoping that it might that same page will receive more rights because.
‫Same problem.
‫Right.
‫Because if I write one byte, I have to flush the whole disk.
‫It's just not worth it.
‫Not this whole page, right?
‫It's just not worth it.
‫So let's just wait for more.
‫Write that hopefully this dirty page receives so I can flush the whole page with.
‫With rich dirty rights.
‫The more rights the page receives and memory like, the better the economics of writing a page.
‫Because otherwise, if you write, if you change one character and that writes the desk, then he changed
‫another character in that writes the desk.
‫You're writing eight k, 8kkk, eight k, eight k.
‫Every microsecond, every millisecond that you're writing.
‫And that's too much, right?
‫So the operating system has the cache and waits for this cache to fail and then flush that cache to
‫disk.
‫Right.
‫So.
‫That's a problem.
‫We talked about the wall that writer had logged.
‫Right.
‫If I, if I.
‫If I told you to flush the wall, change that wall, I won't make it till the wall is actually persist.
‫Don't put it in the cash.
‫Don't try to be smart.
‫Don't try to be efficient operating system.
‫Go to desk.
‫I want a way to bypass this cache that you have.
‫And so that's why most databases in certain operations like the wall, because wall is critical.
‫Right.
‫It is tiny things.
‫But we need to flush them directly to disk.
‫Yeah.
‫And that's called F sync.
‫That option is called F sync.
‫So if if sync is enabled, which is the default, I believe, then.
‫No.
‫If Singh is disabled, right, which is off, that's the default.
‫Then it goes to the cash.
‫If Sink is enabled, they force the sink, make the change, go directly to disc bypass.
‫It's.
‫It's it's make it as a right through cash and punch through a hole through the cash.
‫So yeah, you can turn it off if you want and you get to get a little bit more speed, but in a danger
‫of losing your data.
‫I have that option off, by the way, in my testing data because I don't care.
‫It's a testing data and I load the data on a daily basis.
‫So if there is a crash, which is very highly unlikely, I just reload again.
‫And I did really not as much difference to be honest, but never mind.
‫So databases have this enabled most of the time, not necessarily.
‫Postgres doesn't have it enabled for like reading and writing pages because we know that it's okay,
‫let's write it to the cache and or operating system cache.
‫Let's read from the operating system cache.
‫That's fine.
‫So if you think about it, focus has almost two caches that cache in the buffer pool.
‫I think it's called the working memory.
‫Or maybe it's called the Beaufort War.
‫I forgot.
‫And then there is the operating system, cash itself.
‫So two layers of cash.
‫So yeah.
‫So that's something to watch out for.
‫So, yeah, database engineering is very complex.
‫So the final thing we're going to talk about is the undo log.
‫Hussein.
‫I wrote changes.
‫Right.
‫And by the way, not all databases have this and undo log thing.
‫Postgres doesn't because it does it differently.
‫The undo log was designed specifically.
‫For.
‫Give me the state of their raw.
‫Before it was changed.
‫I say, Hosain.
‫What?
‫Why?
‫Why are you doing this?
‫Well, if you are in a running transaction, a long running transaction, you're changing, changing,
‫changing, changing.
‫Right.
‫You're making changes directly to the page right in memory.
‫And you're writing the changes you made to the wall.
‫So now.
‫What happened to transactions that started before you?
‫Right.
‫Transaction have this thing that's called isolation level since they started.
‫Before you make this change and you still didn't comment.
‫Right in almost all isolation levels except read and committed.
‫Those transactions need the old state of the road before you changed it.
‫So it cannot read from the page that is dirty because it has the latest stuff.
‫It does not need the latest stuff.
‫It needs the old state.
‫So what the database do is they keep a record of the undo log, specifically how this row looked like
‫in its entirety, right in an old row.
‫In a specific log area that's called the Undo Log Oracle SQL Server, I believe.
‫I'm not 100% sure about SQL Server, but my sequel and Oracle have this model, which is the undo log.
‫Right.
‫Postgres doesn't because it uses versioning a version is that all postcards makes the changes in the
‫page.
‫Write it.
‫If you made an update, it's a new roll and postscript.
‫So the old row already lives, which is perfect, right?
‫I love this design much better if you think about it.
‫Again, it's all personal.
‫At the end of the day, preferences.
‫But now all transactions.
‫That want to read ROEs that have been changed.
‫Those rules don't exist on the page so that they have to do a little bit more extra work.
‫They have to go to the undo log, crack it open.
‫I don't know what that means.
‫Crack it open.
‫Just want to make sure that it's slower.
‫That's why I said that.
‫And then take the page, the row from the page, and then undo the changes that because the row is latest.
‫You want to undo the changes.
‫You want to apply changes to make it older, if you will.
‫Right.
‫So you want to undo these changes so that it goes back effectively?
‫To the old state so you can read it.
‫So a little bit more work.
‫For.
‫All their transactions or for that matter, newer transactions.
‫Right.
‫Because.
‫The problem is we have a transaction that did not commit.
‫We have a long running transaction.
‫That's why a long run transaction is is the worse for performance reasons because we the undo leg will
‫keep filling up and those transaction will have to go to the log and crack it open and apply the changes
‫and roll back, if you will, to read the old rules.
‫It has to do this all the time and you might say you can do all sorts of caching, but this is work
‫that the database has to do.
‫If you think about right, it's all work.
‫I'm saying, do you persist the undo log?
‫I believe you should.
‫Right.
‫Let's think this through.
‫You should do the same with the undo log as you do with the redo log, because the reading log tells
‫you what's the final state, right?
‫The undo log tells you the.
‫The older states.
‫Right.
‫And the correct way to do this is in case of a crash, you're going to have a bunch of wall, which
‫is the reader's log, and then you're going to have the undo logs and you get to have a state of the
‫page as it existed in desk.
‫So in case of a crash, you have the undo log, you have the redo log, you have the old page.
‫So what do you do?
‫The the correct order is take the page and whatever is in the world is the truth.
‫Apply all the world changes br apply all of them.
‫And then because you might have applied stuff from a transaction that have been rolled back, right?
‫Go and take the undo log and then redo, not redo, undo the changes up until the point where the transaction
‫has been rolled back.
‫All right.
‫So you're going to redo, redo and then undo, undo, undo.
‫And until you reach a consistent state, right.
‫That's one one implementation that I can think of.
‫Right.
‫Can you not persist undo log and only apply the redo logs for transaction that has committed?
‫I suppose you can, but then you have to differentiate committed transactions versus not committed transaction
‫in the wall.
‫Right.
‫Which I believe that information doesn't exist.
‫A wall has changes and it doesn't know if this is from this transaction versus that transaction.
‫I suppose you can store this information in another.
‫Data file containing the transactions that are being committed.
‫But it's just easier this way.
‫I don't know, guys.
‫This has been an episode of the back engineering show discussing the logs of the database, and pretty
‫sure I missed some of that stuff, but I believe that's the one of the most important things.
‫You know, logs undo log wall and the reader logs.
‫Can I see on the next one.
‫Hope you enjoyed this one.
‫Stay.
‫Awesome.
‫Goodbye.