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 footable file 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 this block 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.org
http://dalibo.com

Guillaume Lelarge <guillaume(at)lelarge(dot)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(at)lelarge(dot)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.org
http://dalibo.com

Guillaume Lelarge wrote:
> Tom Lane a écrit :
> > Guillaume Lelarge <guillaume(at)lelarge(dot)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(at)lelarge(dot)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.org
http://dalibo.com