Обсуждение: question regarding full_page_write
I can't clearly understand what FULL_PAGE_WRITE parameter is stand for.
Documentation suggest that If I make it OFF, then I have the chance for DB crash.
Can anyone please tell me how it could be happened?
Documentation suggest that If I make it OFF, then I have the chance for DB crash.
Can anyone please tell me how it could be happened?
AI Rumman wrote: > I can't clearly understand what FULL_PAGE_WRITE parameter is stand for. > Documentation suggest that If I make it OFF, then I have the chance > for DB crash. > Can anyone please tell me how it could be happened? The database writes to disk in 8K blocks. If you can be sure that your disk drives and operating system will always write in 8K blocks, you can get a performance improvement from turning full_page_writes off. But if you do that, and it turns out that when the power is interrupted your disk setup will actually do partial writes of less than 8K, your database can get corrupted. Your system needs to ensure that when a write happens, either the whole thing goes to disk, or none of it does. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
2011/2/17 Greg Smith <greg@2ndquadrant.com>: > AI Rumman wrote: >> >> I can't clearly understand what FULL_PAGE_WRITE parameter is stand for. >> Documentation suggest that If I make it OFF, then I have the chance for DB >> crash. >> Can anyone please tell me how it could be happened? > > The database writes to disk in 8K blocks. If you can be sure that your disk > drives and operating system will always write in 8K blocks, you can get a > performance improvement from turning full_page_writes off. But if you do > that, and it turns out that when the power is interrupted your disk setup > will actually do partial writes of less than 8K, your database can get > corrupted. Your system needs to ensure that when a write happens, either > the whole thing goes to disk, or none of it does. Sorry for the late reply, but I was investigating this option in postgresql.conf and saw this mail. My question regarding your answer is, why is it important for the first page after a checkpoint and not on other page writes? -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador
On 08/22/2011 05:07 PM, Martín Marqués wrote: > My question regarding your answer is, why is it important for the > first page after a checkpoint and not on other page writes? > The first time a page is written after a checkpoint, when full_page_writes is on, the entire 8K page is written out to disk at that point. The idea is that if the page is corrupted in any way by a partial write, you can restore it to a known good state again by using this version. After that copy, though, additional modifications to the page only need to save the delta of what changed, at the row level. If there's a crash, during recovery the full page image will be written, then the series of deltas, ending up with the same data as was intended. This whole mechanism resets again each time a checkpoint finishes, and the full page writes start all over again. One of the main purposes of checkpoints are to move forward the pointer of how far back crash recovery needs to replay from. Starting each new checkpoint over again, with a full copy of all the data modified going into the WAL, it is part of that logic. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
El día 22 de agosto de 2011 18:39, Greg Smith <greg@2ndquadrant.com> escribió: > On 08/22/2011 05:07 PM, Martín Marqués wrote: >> >> My question regarding your answer is, why is it important for the >> first page after a checkpoint and not on other page writes? >> > > The first time a page is written after a checkpoint, when full_page_writes > is on, the entire 8K page is written out to disk at that point. The idea is > that if the page is corrupted in any way by a partial write, you can restore > it to a known good state again by using this version. After that copy, > though, additional modifications to the page only need to save the delta of > what changed, at the row level. If there's a crash, during recovery the > full page image will be written, then the series of deltas, ending up with > the same data as was intended. > > This whole mechanism resets again each time a checkpoint finishes, and the > full page writes start all over again. One of the main purposes of > checkpoints are to move forward the pointer of how far back crash recovery > needs to replay from. Starting each new checkpoint over again, with a full > copy of all the data modified going into the WAL, it is part of that logic. Still something missing (for me :-)): Checkpoint happens, logs get flushed to disk, WAL segments get archived if archive is on, and then the are recycled. Now a new transaction get commited, and at least 1 page has to go to WAL (those are 8Kb). full_page_writes garantees all 8Kb are written or nothing. After this thansaction comes another, but here you don't need the garantee of all 8Kb in WAL (you say because deltas are enough). Why aren't deltas good enough for the first 8Kb? Is there other information in the first 8Kb that make those more important? -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador
On 08/24/2011 11:12 AM, Martín Marqués wrote: > Why aren't deltas good enough for the first 8Kb? Is there other > information in the first 8Kb that make those more important? > The fundamental problem is what's called a torn page. You write out a 8K page; only part of it actually makes it to disk; the server crashes. What you now have on disk is completely unpredictable. It's a mix of the old new page, but what portion of each you got, that's almost random. You cannot fix it with any delta. The only way to make sure it's back into a good state is to write an entire 8K page, a known correct copy, from some point in time. The way this is done in PostgreSQL, one of those is written out to the WAL the first time any page is touched after a checkpoint. Those become the known copy good to recover from any torn page problem. Then database recovery only has to replay activity since that checkpoint marker to fix all torn pages. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us