pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_xlog size
4+ messages / 3 participants
[nested] [flat]
* pg_xlog size
@ 2011-03-15 15:09 Tech Madhu <technimadhu@gmail.com>
2011-03-16 17:27 ` Re: pg_xlog size Euler Taveira de Oliveira <euler@timbira.com>
2011-03-17 03:09 ` Re: pg_xlog size Fujii Masao <masao.fujii@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Tech Madhu @ 2011-03-15 15:09 UTC (permalink / raw)
To: pgsql-performance
hi all,
Setup:
Sparc, Solaris 10, Postgres 9.0.2, using streaming replication and hot
standby. 1 master 1 slave
Everything works fine (w.r.t replication), but the pg_xlog size grows
continuously, though i had no operations going on. Also the archiving to the
other side filled up the other side FS.
ls -l /var/postgres/data/pg_xlog | wc -l
103
At start, there were only 15 files. The max_wal_segments is 32, but not sure
why iam seeing 103 files. Also the archiving dir size doubled (w.r.t number
of files archived). and filled up the filesystem.
I manually logged into postgres and run checkpoint; did not see any file
reduction
Pasting some of the relevant conf values
The checkpoint_segments is commented out, so default to 3 segments right?
postgresql.conf
-----------------------
wal_level = hot_standby
checkpoint_warning = 30s
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB
each
#checkpoint_timeout = 5min # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 -
1.0
archive_mode = on # allows archiving to be done
# (change requires restart)
archive_command = 'cp %p /var/postgres/walfiles/%f' # this dir
is NFS mounted dir on the slave node
archive_timeout = 30
max_wal_senders = 5
wal_keep_segments = 32
hot_standby = on
track_counts = on
autovacuum = on
-----------------------------------------
I want to ensure it only keeps up to certain number of files, not keep on
growing and filling up my filesystem and requiring manual intervention.
Appreciate any tips or pointers to documentation. Thanks in advance. I
looked in the archives, but one user had this problem because he had the
wal_keep_segments set to 300 (whereas i have it only at 32)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_xlog size
2011-03-15 15:09 pg_xlog size Tech Madhu <technimadhu@gmail.com>
@ 2011-03-16 17:27 ` Euler Taveira de Oliveira <euler@timbira.com>
2011-03-16 21:27 ` Re: pg_xlog size Tech Madhu <technimadhu@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Euler Taveira de Oliveira @ 2011-03-16 17:27 UTC (permalink / raw)
To: Tech Madhu <technimadhu@gmail.com>; +Cc: pgsql-performance
Em 15-03-2011 12:09, Tech Madhu escreveu:
[This is not a performance question, next time post at the appropriate list,
that is -general]
> Everything works fine (w.r.t replication), but the pg_xlog size grows
> continuously, though i had no operations going on. Also the archiving to
> the other side filled up the other side FS.
> ls -l /var/postgres/data/pg_xlog | wc -l
> 103
Did you consider using pg_archivecleanup [1]?
> At start, there were only 15 files. The max_wal_segments is 32, but not
> sure why iam seeing 103 files. Also the archiving dir size doubled
> (w.r.t number of files archived). and filled up the filesystem.
> I manually logged into postgres and run checkpoint; did not see any file
> reduction
>
max_wal_segments [2] is *not* related to archiving activity.
[1] http://www.postgresql.org/docs/9.0/static/pgarchivecleanup.html
[2]
http://www.postgresql.org/docs/9.0/static/runtime-config-wal.html#GUC-WAL-KEEP-SEGMENTS
--
Euler Taveira de Oliveira
http://www.timbira.com/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_xlog size
2011-03-15 15:09 pg_xlog size Tech Madhu <technimadhu@gmail.com>
2011-03-16 17:27 ` Re: pg_xlog size Euler Taveira de Oliveira <euler@timbira.com>
@ 2011-03-16 21:27 ` Tech Madhu <technimadhu@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tech Madhu @ 2011-03-16 21:27 UTC (permalink / raw)
To: Euler Taveira de Oliveira <euler@timbira.com>; +Cc: pgsql-performance
Thank you. I had pg_archivecleanup added in recovery.conf, but on second
look had a typo in the archive dir path. After this change in recovery.conf
and postgres restart, its fine now. Once my archive dir got cleaned up , i
noticed my /var/postgres/data/pg_xlog dir on master also got cleaned up
On Wed, Mar 16, 2011 at 1:27 PM, Euler Taveira de Oliveira <
euler@timbira.com> wrote:
> Em 15-03-2011 12:09, Tech Madhu escreveu:
>
> [This is not a performance question, next time post at the appropriate
> list, that is -general]
>
>
> Everything works fine (w.r.t replication), but the pg_xlog size grows
>> continuously, though i had no operations going on. Also the archiving to
>> the other side filled up the other side FS.
>> ls -l /var/postgres/data/pg_xlog | wc -l
>> 103
>>
> Did you consider using pg_archivecleanup [1]?
>
>
> At start, there were only 15 files. The max_wal_segments is 32, but not
>> sure why iam seeing 103 files. Also the archiving dir size doubled
>> (w.r.t number of files archived). and filled up the filesystem.
>> I manually logged into postgres and run checkpoint; did not see any file
>> reduction
>>
>> max_wal_segments [2] is *not* related to archiving activity.
>
>
> [1] http://www.postgresql.org/docs/9.0/static/pgarchivecleanup.html
> [2]
> http://www.postgresql.org/docs/9.0/static/runtime-config-wal.html#GUC-WAL-KEEP-SEGMENTS
>
>
> --
> Euler Taveira de Oliveira
> http://www.timbira.com/
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_xlog size
2011-03-15 15:09 pg_xlog size Tech Madhu <technimadhu@gmail.com>
@ 2011-03-17 03:09 ` Fujii Masao <masao.fujii@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Fujii Masao @ 2011-03-17 03:09 UTC (permalink / raw)
To: Tech Madhu <technimadhu@gmail.com>; +Cc: pgsql-performance
On Wed, Mar 16, 2011 at 12:09 AM, Tech Madhu <technimadhu@gmail.com> wrote:
> hi all,
>
> Setup:
> Sparc, Solaris 10, Postgres 9.0.2, using streaming replication and hot
> standby. 1 master 1 slave
>
> Everything works fine (w.r.t replication), but the pg_xlog size grows
> continuously, though i had no operations going on. Also the archiving to the
> other side filled up the other side FS.
> ls -l /var/postgres/data/pg_xlog | wc -l
> 103
> At start, there were only 15 files. The max_wal_segments is 32, but not sure
> why iam seeing 103 files. Also the archiving dir size doubled (w.r.t number
> of files archived). and filled up the filesystem.
> I manually logged into postgres and run checkpoint; did not see any file
> reduction
>
> Pasting some of the relevant conf values
> The checkpoint_segments is commented out, so default to 3 segments right?
>
> postgresql.conf
> -----------------------
> wal_level = hot_standby
> checkpoint_warning = 30s
> #checkpoint_segments = 3 # in logfile segments, min 1, 16MB
> each
> #checkpoint_timeout = 5min # range 30s-1h
> #checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 -
> 1.0
>
> archive_mode = on # allows archiving to be done
> # (change requires restart)
> archive_command = 'cp %p /var/postgres/walfiles/%f' # this dir
> is NFS mounted dir on the slave node
> archive_timeout = 30
Since the setting "archive_timeout" makes the server create new WAL
segment file for each 30 seconds (even if there is no write transaction),
the size of pg_xlog directory continuously grows up.
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-03-17 03:09 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-03-15 15:09 pg_xlog size Tech Madhu <technimadhu@gmail.com>
2011-03-16 17:27 ` Euler Taveira de Oliveira <euler@timbira.com>
2011-03-16 21:27 ` Tech Madhu <technimadhu@gmail.com>
2011-03-17 03:09 ` Fujii Masao <masao.fujii@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox