Обсуждение: Where to configure pg_xlog file-size?

Hi,

I am using postgres-8.3 on an embedded ARM9 system.
Works pretty well, except for stoarge consumptions.

The actual table data is rather small, but postgres creates 2 16mb
files in pg_xlog:
root@mesrv:/var/lib/postgresql/8.3/main# ls -la pg_xlog/
total 32820
-rw-------  1 postgres postgres 16777216 2010-04-12 15:00
000000010000000000000006
-rw-------  1 postgres postgres 16777216 2010-04-11 23:42
000000010000000000000007

Is there anything I can do to lower the size of those two files?
What are reasonable values for smaller databases, and if it can be
changed, what impact would it have on the system?

Thanks, Clemens

Clemens Eisserer wrote on 12.04.2010 23:25:
> Hi,
>
> I am using postgres-8.3 on an embedded ARM9 system.
> Works pretty well, except for stoarge consumptions.
>
> The actual table data is rather small, but postgres creates 2 16mb
> files in pg_xlog:
> root@mesrv:/var/lib/postgresql/8.3/main# ls -la pg_xlog/
> total 32820
> -rw-------  1 postgres postgres 16777216 2010-04-12 15:00
> 000000010000000000000006
> -rw-------  1 postgres postgres 16777216 2010-04-11 23:42
> 000000010000000000000007
>
> Is there anything I can do to lower the size of those two files?
> What are reasonable values for smaller databases, and if it can be
> changed, what impact would it have on the system?
>
> Thanks, Clemens
>

Those are checkpoint segments.

I don't think you change the size of the files, but you should be able to limit that to one file.

http://www.postgresql.org/docs/8.3/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-CHECKPOINTS

Although I have no idea about the impact regarding performance. But I guess if you don't have too many writes it might
actuallybe OK. 

Thomas

Clemens Eisserer <linuxhippy@gmail.com> writes:
> Is there anything I can do to lower the size of those two files?

Well, if you were using 8.4 you could fool with configure's
--with-wal-segsize option.  Since you're not, look into
src/include/pg_config_manual.h.  In either case, expect to do a
full recompile and initdb after changing it.

> What are reasonable values for smaller databases, and if it can be
> changed, what impact would it have on the system?

I don't believe any serious effort has been made to quantify that;
the knob is there but you're on your own to figure out how hard to
twist it.

            regards, tom lane

Hello again,

> Well, if you were using 8.4 you could fool with configure's
> --with-wal-segsize option.  Since you're not, look into
> src/include/pg_config_manual.h.  In either case, expect to do a
> full recompile and initdb after changing it.
Good to know - I plan to upgrade to 8.4/9.0 anyway.
I am currently using pre-built binaries but don't want to touch the OS,
so no way arround compiling PG in the long term ;)

> I don't believe any serious effort has been made to quantify that;
> the knob is there but you're on your own to figure out how hard to
> twist it.
Ok, understood :)

Thanks, Clemens