Re: pg_xlog size growing untill it fills the partition

The database is about 1TB large.
Thera are mainly 4 tables 2 with row sizes about 1000 bytes and 2 with rows
with about 200 bytes but contaniong also several usually large (the size of
an average webpage) text fields.
My initial setting was 16GB of shared buffers I tried with 2GB and did not
notice any clear difference.
I'll try with 128MB.

On Monday 07 October 2013 15:23:33 Marcin Mańk wrote:
> On Thu, Oct 3, 2013 at 11:56 PM, Michal TOMA <mt(at)sicoop(dot)com> wrote:
> > Now I have:
> > checkpoint_completion_target = 0.9
> > wal_buffers = 8MB
> > checkpoint_segments = 16
> > checkpoint_timeout = 20min
> > shared_buffers = 2GB
> > log_checkpoints = on
> >
> > This is what I can see in the log:
> > 2013-10-03 13:58:56 CEST LOG: checkpoint starting: xlog
> > 2013-10-03 13:59:56 CEST LOG: checkpoint complete: wrote 448 buffers
> > (0.2%); 0 transaction log file(s) added, 9 removed, 18 recycled;
> > write=39.144 s, s, sync=12102.311 s, total=12234.608 s; sync files=667,
> > longest=181.374 s, average=18.144 s
> > 2013-10-03 22:30:25 CEST LOG: checkpoint starting: xlog time
>
> From your logs, it seems that the writes are spread all over the (fairly
> large) database. Is that correct? What is the database size? What is the
> size of the working data set (i.e. the set of rows that are in use)?
>
> I heard of people having good results with setting a low value for
> shared_buffers (like 128MB) in a high write activity scenarios. Setting it
> that low would mean that checkpoints would have 16 times less to do.