[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Fwd: [PERFORM] Delay in converting logs from ready state to done state
From:       Samir Magar <samirmagar8 () gmail ! com>
Date:       2016-10-12 7:16:35
Message-ID: CAA=to3jK3BScxcG9vnnP-Q4JVr_O7Eg+tbHR9Y-OXz7C=doxMg () mail ! gmail ! com
[Download RAW message or body]

*Hello Julien,*

*Thank you for your prompt response!*
*we have changed the checkpoint_segment to 170 and use pigz for the pg_xlog
compress.*
*It is working very well now.*

*Thanks again!!*

*Regards,*
*Samir Magar*

On Wed, Oct 12, 2016 at 11:33 AM, Julien Rouhaud <julien.rouhaud@dalibo.com>
wrote:

> 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
>

[Attachment #3 (text/html)]

<div dir="ltr"><div class="gmail_quote"><div dir="ltr"><i><b \
style="background-color:rgb(255,255,255)"><font color="#000000">Hello  <span \
style="font-size:12.8px">Julien,</span></font></b></i><div><span \
style="font-size:12.8px;background-color:rgb(255,255,255)"><i><b><font \
color="#000000"><br></font></b></i></span></div><div><i><b \
style="background-color:rgb(255,255,255)"><font color="#000000">Thank you for your \
prompt response!</font></b></i></div><div><i><b \
style="background-color:rgb(255,255,255)"><font color="#000000">we have changed the  \
<span style="font-size:12.8px">checkpoint_segment to 170 and use pigz for the pg_xlog \
compress.</span></font></b></i></div><div><i><b \
style="background-color:rgb(255,255,255)"><font color="#000000">It is working very \
well now.</font></b></i></div><div><i><b \
style="background-color:rgb(255,255,255)"><font \
color="#000000"><br></font></b></i></div><div><i><b \
style="background-color:rgb(255,255,255)"><font color="#000000">Thanks \
again!!</font></b></i></div><div><i><b \
style="background-color:rgb(255,255,255)"><font \
color="#000000"><br></font></b></i></div><div><i><b \
style="background-color:rgb(255,255,255)"><font \
color="#000000">Regards,</font></b></i></div><div><i><b \
style="background-color:rgb(255,255,255)"><font color="#000000">Samir \
Magar</font></b></i></div></div><div class="HOEnZb"><div class="h5"><div \
class="gmail_extra"><br><div class="gmail_quote">On Wed, Oct 12, 2016 at 11:33 AM, \
Julien Rouhaud <span dir="ltr">&lt;<a href="mailto:julien.rouhaud@dalibo.com" \
target="_blank">julien.rouhaud@dalibo.com</a>&gt;</span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><span>On 12/10/2016 07:26, Samir Magar wrote:<br> &gt; \
Team,<br> &gt;<br>
&gt; we are seeing delay in converting logs from ready state to done state in<br>
&gt; pg_xlog archive status.<br>
&gt;<br>
&gt; we have seen pg_xlog generated 2000 per hour and it is getting archived<br>
&gt; 1894. So the speed at which the archiving is done is too slow as compare<br>
&gt; to the pg_xlog generation<br>
&gt;<br>
&gt; So our pg_xlog directory keeps filling regularly. What should be the<br>
&gt; real cause here?<br>
&gt;<br>
&gt; We cannot see any specific error on pg_log   except no space left on device.<br>
&gt;<br>
&gt;<br>
&gt;<br>
&gt; current setting:<br>
&gt;<br>
&gt; wal_level = archive<br>
&gt;<br>
&gt; archive_mode = on<br>
&gt;<br>
&gt; max_wal_senders = 3<br>
&gt;<br>
&gt; archive_command = &#39;gzip &lt; %p &gt; /pgarchive/%f&#39;<br>
&gt;<br>
<br>
</span>You could use pigz which is parallel, that could speed up compression.<br>
<br>
&gt; checkpoint_segments = 3<br>
&gt;<br>
<br>
this is way to low. If you generate 2000 WAL per hour, you should<br>
configure it to something like 170 (or 5 min average if 2000 is a<br>
spike).   It&#39;ll perform less checkpoint and also generate less WALs.<br>
<span class="m_-7781476202425134922HOEnZb"><font color="#888888"><br>
<br>
--<br>
Julien Rouhaud<br>
<a href="http://dalibo.com" rel="noreferrer" target="_blank">http://dalibo.com</a> - \
<a href="http://dalibo.org" rel="noreferrer" \
target="_blank">http://dalibo.org</a><br> </font></span></blockquote></div><br></div>
</div></div></div><br></div>



[prev in list] [next in list] [prev in thread] [next in thread]