Subject: Re: Delay in converting logs from ready state to done state
To: Samir Magar <samirmagar8@gmail.com>, pgsql-performance@postgresql.org
References: 
 <CAA=to3jfR91csHz70eXZOTf_9WYzVHKWE2HG4EuZZPRO-XV4Gw@mail.gmail.com>
From: Julien Rouhaud <julien.rouhaud@dalibo.com>
Message-ID: <ec7967ee-10bb-d0fc-e27d-a0e2200652c9@dalibo.com>
Date: Wed, 12 Oct 2016 08:03:53 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.2.0
MIME-Version: 1.0
In-Reply-To: 
 <CAA=to3jfR91csHz70eXZOTf_9WYzVHKWE2HG4EuZZPRO-XV4Gw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 12/10/2016 07:26, Samir Magar wrote:
> Team,
> 
> we are seeing delay in converting logs from ready state to done state in
> pg_xlog archive status.
> 
> we have seen pg_xlog generated 2000 per hour and it is getting archived
> 1894. So the speed at which the archiving is done is too slow as compare
> to the pg_xlog generation
> 
> So our pg_xlog directory keeps filling regularly. What should be the
> real cause here?
> 
> We cannot see any specific error on pg_log  except no space left on device.
> 
>  
> 
> current setting:
> 
> wal_level = archive
> 
> archive_mode = on
> 
> max_wal_senders = 3
> 
> archive_command = 'gzip < %p > /pgarchive/%f'
> 

You could use pigz which is parallel, that could speed up compression.

> checkpoint_segments = 3
> 

this is way to low. If you generate 2000 WAL per hour, you should
configure it to something like 170 (or 5 min average if 2000 is a
spike).  It'll perform less checkpoint and also generate less WALs.


-- 
Julien Rouhaud
http://dalibo.com - http://dalibo.org


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