Thread: shared_buffers, wal_buffers, WAL files, data files
Hi, First, I'm not sure this mail should go to this mailing list. As it refers to source code (mainly src/backend/postmaster/bgwriter.c and src/backend/access/transam/xlog.c), I sent it here. I apologize if I'm wrong. I'm a bit puzzled by the different informations I can read on the documentation, on the source files and other README files from the source package. I'm actually using 8.2.5 source files. I try to answer a simple question : what happens when I do a simple "INSERT" on a just started PostgreSQL server. # pg_ctl start # psql foobase [...] foobase=# CREATE TABLE footable (i int4); CREATE TABLE foobase=# INSERT INTO footable (i) VALUES (1); INSERT 0 1 From what I understand with the INSERT statement, here is what happens :* backend loads first (and only) block from footablefile into a shared buffer* it modifies this block on the shared buffer, and sets it as dirty After checkpoint_timeout seconds or after a manual CHECKPOINT (there's also some other ways to have a checkpoint but in my simple example, these two seem to be the most probable ones) :* bgwriter runs CreateCheckPoint* it will, beyond other things, flush thisblock from shared buffer to disk, fsync, sets the block as non-dirty... ... and here is my first question : flushes to the current XLOG file or to footable data file ? I think it is to XLOG file but I don't find where in the code the content of WAL files is flushed to data files. If you know where I can look to get this information, I'm really interested. From other parts of the documentation, it seems checkpoints are also in charge of flushing WAL files to data files. I've read something like this: "a checkpoint writes dirty shared buffers, sync dirty kernel buffers, recycle WAL files." Also, because of the checkpoint_segments GUC. And I don't find where wal_buffers are used in all this process. One last thing (wrt this mail), I think I understand what full_page_writes does but I wonder what this sentence means : "Writes full pages to WAL when first modified after a checkpoint." ? (it's the last part that I don't understand) Thanks for any pointers. Regards. -- Guillaume.http://www.postgresqlfr.orghttp://dalibo.com
Guillaume Lelarge <guillaume@lelarge.info> writes:
> I try to answer a simple question : what happens when I do a simple
> "INSERT" on a just started PostgreSQL server.
> From what I understand with the INSERT statement, here is what happens :
> * backend loads first (and only) block from footable file into a shared
> buffer
> * it modifies this block on the shared buffer, and sets it as dirty
Right, and it also makes a WAL log entry about this action.
> After checkpoint_timeout seconds or after a manual CHECKPOINT (there's
> also some other ways to have a checkpoint but in my simple example,
> these two seem to be the most probable ones) :
> * bgwriter runs CreateCheckPoint
> * it will, beyond other things, flush this block from shared buffer to
> disk, fsync, sets the block as non-dirty...
Right.
> ... and here is my first question : flushes to the current XLOG file or
> to footable data file ?
To the data file. The WAL entry got flushed to disk during (or before)
commit of the INSERT transaction.
> I think it is to XLOG file but I don't find
> where in the code the content of WAL files is flushed to data files.
There's no such thing as "flushing WAL to data". During normal
operation the WAL is write-only; we never look at it again once
we've made a log entry.
> From other parts of the documentation, it seems checkpoints are also in
> charge of flushing WAL files to data files.
No, they're just in charge of removing no-longer-needed sections of WAL.
Once we've checkpointed a particular data file change, there's no need
to keep around the part of the WAL that logged that change.
regards, tom lane
Tom Lane a écrit : > Guillaume Lelarge <guillaume@lelarge.info> writes: >> I try to answer a simple question : what happens when I do a simple >> "INSERT" on a just started PostgreSQL server. > >> From what I understand with the INSERT statement, here is what happens : >> * backend loads first (and only) block from footable file into a shared >> buffer >> * it modifies this block on the shared buffer, and sets it as dirty > > Right, and it also makes a WAL log entry about this action. > The WAL log entry is made on the wal buffers (in memory). As soon as this statement is commited (in my example, it's right now, but in a BEGIN ... COMMIT statement, at COMMIT time), the wal buffer is flushed on WAL files. It can be flushed before if wal buffer is not big enough to contain all the current transactions. Am I right ? Thanks for your answer, I think I better understand the complete process. Regards. -- Guillaume.http://www.postgresqlfr.orghttp://dalibo.com
Guillaume Lelarge wrote: > Tom Lane a écrit : > > Guillaume Lelarge <guillaume@lelarge.info> writes: > >> I try to answer a simple question : what happens when I do a simple > >> "INSERT" on a just started PostgreSQL server. > > > >> From what I understand with the INSERT statement, here is what happens : > >> * backend loads first (and only) block from footable file into a shared > >> buffer > >> * it modifies this block on the shared buffer, and sets it as dirty > > > > Right, and it also makes a WAL log entry about this action. > > > > The WAL log entry is made on the wal buffers (in memory). As soon as > this statement is commited (in my example, it's right now, but in a > BEGIN ... COMMIT statement, at COMMIT time), the wal buffer is flushed > on WAL files. It can be flushed before if wal buffer is not big enough > to contain all the current transactions. Am I right ? That's correct. WAL buffers are obviously shared; when one transaction commits it will flush not only its own entries, but also those that any other transaction could have written. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J "Endurecerse, pero jamás perder la ternura" (E. Guevara)
Alvaro Herrera a écrit : > Guillaume Lelarge wrote: >> Tom Lane a écrit : >>> Guillaume Lelarge <guillaume@lelarge.info> writes: >>>> I try to answer a simple question : what happens when I do a simple >>>> "INSERT" on a just started PostgreSQL server. >>>> From what I understand with the INSERT statement, here is what happens : >>>> * backend loads first (and only) block from footable file into a shared >>>> buffer >>>> * it modifies this block on the shared buffer, and sets it as dirty >>> Right, and it also makes a WAL log entry about this action. >>> >> The WAL log entry is made on the wal buffers (in memory). As soon as >> this statement is commited (in my example, it's right now, but in a >> BEGIN ... COMMIT statement, at COMMIT time), the wal buffer is flushed >> on WAL files. It can be flushed before if wal buffer is not big enough >> to contain all the current transactions. Am I right ? > > That's correct. WAL buffers are obviously shared; when one transaction > commits it will flush not only its own entries, but also those that any > other transaction could have written. > OK, thanks. I'll probably have more questions but I need to think a bit more about all your answers. Anyways, thanks. Regards. -- Guillaume.http://www.postgresqlfr.orghttp://dalibo.com