pg_xlog Concern

On 18/05/15 13:44, Sachin Srivastava wrote:

But currently my pg_xlog size is 60 GB and there are 3740 WAL file in
this folder and in Last week this was 400 GB(pg_xlog folder) and WAL
file were approx. 30000. Due to archiving pg_xlog folder size is
decreasing now but it’s taking one week to come in normal size.

Any chance you have unfinished transactions running for a week?

pg_stat_activity should be able to tell you that.

Torsten

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,

Any kind of slowness on your archive directory may cause the archiving
process fall behind thus accumulating segment files in your cluster's
pg_xlog directory.

I assume that you are on PostgreSQL 9.4. Could you please check your
archiver status by typing "select * from pg_catalog.pg_stat_archiver;" in
psql ? If the last_archived_wal column's value is not so close to your
current xlog location, then it probably means a slow write speed on your
archive path compared to your pg_xlog path.

You can check your current xlog file by typing "select
pg_xlogfile_name(pg_current_xlog_location());" in psql.

If you are not on PostgreSQL 9.4, you can check your archiver status by
typing "ps -ef | grep archiver" in your shell.

I've also assumed that you are not using replication slots.

As far as I know, long running transactions (just sitting in idle) won't
affect pg_xlog directory size. Correct me if I'm wrong.

Hope that helps.

Have a nice day.

Koray

On Mon, May 18, 2015 at 5:00 PM, Torsten Förtsch <torsten.foertsch@gmx.net>
wrote:

On Mon, May 18, 2015 at 9:44 PM, Sachin Srivastava <ssr.teleatlas@gmail.com>
wrote:

Hi,

I have done below changes in my postgresql.conf.

*Checkpoint_segments= 200*

and

*checkpoint_timeout= 5min*

I am also doing archiving and below changes in my postgresql.conf.

*Wal_level=archive*

*archive_command= cp %p /dbkup/momacpp_213_live/%f*

*archive_mode=on*

Achieving is going smoothly in /*dbkup/momacpp_213_live* folder.

But currently my pg_xlog size is 60 GB and there are 3740 WAL file in this
folder and in Last week this was 400 GB(pg_xlog folder) and WAL file were
approx. 30000. Due to archiving pg_xlog folder size is decreasing now but
it’s taking one week to come in normal size.

*I have 2 Question*:

*First:* When I have given *checkpoint segments=200 (As per my knowledge
WAL file should be generated 200*2+3=403 only)* but why it’s generating
too much file. MY each WAL file is 16 MB.

What do you see in the "archive_status" directory ?
Other way around could be, if you have log_checkpoints parameter set to
"on", then checkpoints information will be logged into the postgresql
logfile. This will help you understand how checkpoints are behaving.

*Second:* Why pg_xlog size is increasing too much it should be only
(403*16 MB = 6448 MB) and if Production team is not entering data in bulk
and if normal production is going then it’s size remain same as per logic.

Do you mean to say, pg_xlog is not getting cleared forever ? or is it
getting cleared periodically and you are uncertain about the behaviour ?

How I handle this case (pg_xlog folder size) when Production people
entering the data in bulk, kindly suggest. I am missing something in my
postgresql.conf and somewhere else.

What is the *archive_timeout* value you have ?

Regards,
Venkata Balaji N

Fujitsu Australia

Hello,

First: When I have given checkpoint segments=200 (As per my knowledge WAL

file should be generated >200*2+3=403 only) but why it’s generating too much
file. MY each WAL file is 16 MB.

The formula is
(2 + checkpoint_completion_target) * checkpoint_segments + 1
or
checkpoint_segments + wal_keep_segments + 1 files

So what is your checkpoint_completion_target and wal_keep_segments?
regards
Sameer

--
View this message in context: http://postgresql.nabble.com/pg-xlog-Concern-tp5849713p5850100.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

------------------

"MY each WAL file is 16 MB."

-------------------
Are you saying that you want WAL segments to be smaller? WAL file segments are always the same size, which can be set with --with-WAL-segsize at build time but not at runtime.

Regards
Mathew

Sent from my iPhone

(I'm not going to try to fix the top-posting)

I've seen what I think you're describing on several occasions.

What we basically discovered is very simple: disks have a top speed and
you can't exceed it.

If I understand you correctly, you see the # of wal segments grow very
large during and shortly after bulk data changes, then they shrink back
down to what you'd expect, but grow again during the next bulk data
change.

Essentially, what we discovered was happening was that we were doing
bulk data manipulations at about 100% the speed of the disk subsystem.
As a result, there was no additional capacity for the wal archiving to
copy files with. Archiving still runs, but it's much slowed down, just
like any other disk activity when the disks are very busy. Since
PostgreSQL thinks your data is important, it won't discard wal files
that have not yet been archived, so they stick around until it can
catch up.

If I'm diagnosing your situation correclty, you have a few options:

1) Just accept it, that's what we decided since the bulk operations
only happened about once a month and the problem cleared up in a
few hours.
2) Faster disks: move to SSDs or a better RAID controller or whatever
it takes to make the disks fast enough not to have the problem.
3) Move the wal and/or the wal archive directories onto a different disk
subsystem, which essentially increases the speed of the disks through
"divide and conquer". You many not even need new hardware to accomplish
this -- if you have enough disks you might benefit from rearranging
how they're organized in the RAID controller. Of course, you'll have
to back up and restore the system to do so.
4) Change the application that does the bulk loading to throttle itself
so it doesn't overload the disks, which will then allow wal archiving
to keep up through the process.

Of course, each of these solutions has its benefits and drawbacks, so
you'll have to decide which is right for you.

On Mon, 18 May 2015 17:34:21 +0300
Koray Eyidoğan <korayey@gmail.com> wrote:

Hi,

Any kind of slowness on your archive directory may cause the archiving
process fall behind thus accumulating segment files in your cluster's
pg_xlog directory.

I assume that you are on PostgreSQL 9.4. Could you please check your
archiver status by typing "select * from pg_catalog.pg_stat_archiver;" in
psql ? If the last_archived_wal column's value is not so close to your
current xlog location, then it probably means a slow write speed on your
archive path compared to your pg_xlog path.

You can check your current xlog file by typing "select
pg_xlogfile_name(pg_current_xlog_location());" in psql.

If you are not on PostgreSQL 9.4, you can check your archiver status by
typing "ps -ef | grep archiver" in your shell.

I've also assumed that you are not using replication slots.

As far as I know, long running transactions (just sitting in idle) won't
affect pg_xlog directory size. Correct me if I'm wrong.

Hope that helps.

Have a nice day.

Koray

On Mon, May 18, 2015 at 5:00 PM, Torsten Förtsch <torsten.foertsch@gmx.net>
wrote:

On 18/05/15 13:44, Sachin Srivastava wrote:

But currently my pg_xlog size is 60 GB and there are 3740 WAL file in
this folder and in Last week this was 400 GB(pg_xlog folder) and WAL
file were approx. 30000. Due to archiving pg_xlog folder size is
decreasing now but it’s taking one week to come in normal size.

Any chance you have unfinished transactions running for a week?

pg_stat_activity should be able to tell you that.

Torsten

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
PT <wmoran@potentialtech.com>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, May 20, 2015 at 4:12 PM, Sachin Srivastava <ssr.teleatlas@gmail.com>
wrote:

Dear Venkata,

I have not set this parameter "archive_timeout"

I think that is also the reason.

What value I'll assign to this parameter if my *Checkpoint_segments= 200 *and
*checkpoint_timeout= 5min, *kindly confirm.

Before we get on to that, can you please let us know what is archive_status
directory saying. Do you see any files there ? if yes, what is there
extension ?
We need to first investigate why pg_xlog is growing, is it because of long
running transactions or archiving is not working as expected.
It is the checkpoint process which cleans up the WAL files from pg_xlog, if
that is not happening

Regards,
Venkata Balaji N

Fujitsu Australia