Обсуждение: pg_xlog content

Hi,

    When I initialize database, pg_xlog direcory takes almost 17 MB size
    Why pg_xlog/WAL takes 17 MB of size while there is no transaction
occurred ?

    What is the content of pg_xlog , If it's only wal file it's must be
blank after on write of transaction on database.

regards,
Siddharth

Siddharth Shah wrote:
> Hi,
>
>    When I initialize database, pg_xlog direcory takes almost 17 MB size
>    Why pg_xlog/WAL takes 17 MB of size while there is no transaction
> occurred ?
>
>    What is the content of pg_xlog , If it's only wal file it's must be
> blank after on write of transaction on database.

It's the WAL. Files will be 16MB in size and you will have several (I
think 3 is the minimum). If you have a large update that can increase.
See the checkpoint_segments setting if you think that's something you'll
do frequently.

If you are using WAL-based replication you could have lots if the
archive command keeps failing.

--
  Richard Huxton
  Archonet Ltd

Richard Huxton wrote:
> Siddharth Shah wrote:
>
>> Hi,
>>
>>    When I initialize database, pg_xlog direcory takes almost 17 MB size
>>    Why pg_xlog/WAL takes 17 MB of size while there is no transaction
>> occurred ?
>>
>>    What is the content of pg_xlog , If it's only wal file it's must be
>> blank after on write of transaction on database.
>>
>
> It's the WAL. Files will be 16MB in size and you will have several (I
> think 3 is the minimum). If you have a large update that can increase.
> See the checkpoint_segments setting if you think that's something you'll
> do frequently.
>
> If you are using WAL-based replication you could have lots if the
> archive command keeps failing.
>
>
    I am not using wall based replication ? I no not have frequent &
long transaction , need of checkpoints
Just want to get what data resides in pg_xlog that it takes 16MB without
any transactions.

-    Siddharth

Siddharth Shah wrote:
>    I am not using wall based replication ? I no not have frequent & long
> transaction , need of checkpoints
> Just want to get what data resides in pg_xlog that it takes 16MB without
> any transactions.

The files are created 16MB long, it is faster to overwrite that than to
keep extending a shorter file. Since the WAL is written to with every
transaction it needs to be fast.

--
  Richard Huxton
  Archonet Ltd

Siddharth Shah wrote:
>     I am not using wall based replication ? I no not have frequent &
> long transaction , need of checkpoints
> Just want to get what data resides in pg_xlog that it takes 16MB without
> any transactions.

It contains the transaction log files.

The files are precreated with a size of 16MB and filled with log entries.
There will normally be several of those files around.

Since your main objective is to keep size small, you can change
the default of 16MB by setting XLOG_SEG_SIZE in src/include/pg_config_manual.h
to a smaller value.

Yours,
Laurenz Albe

Albe Laurenz wrote:
> Siddharth Shah wrote:
>
>>     I am not using wall based replication ? I no not have frequent &
>> long transaction , need of checkpoints
>> Just want to get what data resides in pg_xlog that it takes 16MB without
>> any transactions.
>>
>
> It contains the transaction log files.
>
> The files are precreated with a size of 16MB and filled with log entries.
> There will normally be several of those files around.
>
> Since your main objective is to keep size small, you can change
> the default of 16MB by setting XLOG_SEG_SIZE in src/include/pg_config_manual.h
> to a smaller value.
>
> Yours,
> Laurenz Albe
>
>
>
Thanks for info, will help me.
Yes, Objective is to reduce the size, Does setting XLOG_SEG_SIZE to
lower size make any difference in performance.
Is there any content other than log that that postgres refers from pg_xlog

regards,
Siddharth