Обсуждение: Standby server lagging behind

<div class="Section1"><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Verdana","sans-serif"">Hi</span><pclass="MsoNormal"><span
style="font-size:10.0pt;font-family:"Verdana","sans-serif""> </span><pclass="MsoNormal"><span
style="font-size:10.0pt;font-family:"Verdana","sans-serif"">Weare running postgres8.3.9 and have enabled log shipping
replication.Our standby server is about 1000 files BEHIND – everything I’ve read indicates that the standby server
shouldbe very close behind the live server – at the rate we’re going it will take 8 hours to apply all the logs to
standby!How can I speed up the recovery process on the standby server?</span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Verdana","sans-serif""> </span><pclass="MsoNormal"><span
style="font-size:10.0pt;font-family:"Verdana","sans-serif"">Someinfo</span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Verdana","sans-serif"">Liveserver –16CPU, 80G Ram</span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Verdana","sans-serif"">wewrite about 2to3 16MB WAL files per minute</span><p
class="MsoNormal"><spanstyle="font-size:10.0pt;font-family:"Verdana","sans-serif"">These get copied to the standby so
itsall there, just not being applied fast enough. </span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Verdana","sans-serif""> </span><pclass="MsoNormal"><span
style="font-size:10.0pt;font-family:"Verdana","sans-serif""> </span><pclass="MsoNormal"><span
style="font-size:10.0pt;font-family:"Verdana","sans-serif"">Anyadvice would be most welcome</span><p
class="MsoNormal"><spanstyle="font-size:10.0pt;font-family:"Verdana","sans-serif""> </span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Verdana","sans-serif"">Ta</span><span
style="font-size:10.0pt;font-family:Wingdings">J</span><spanstyle="font-size: 
10.0pt;font-family:"Verdana","sans-serif""></span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Verdana","sans-serif""> </span><pclass="MsoNormal"><span
style="font-size:10.0pt;font-family:"Verdana","sans-serif""> </span><pclass="MsoNormal"><span
style="font-size:10.0pt;font-family:"Verdana","sans-serif";
color:blue">Huda Booley<br /> DBA | CareerJunction | Better jobs. More often.<br /> Web:<u>
www.careerjunction.co.za</u>|Email: <a href="mailto:huda@careerjunction.co.za"> <span
style="color:blue">huda@careerjunction.co.za</span></a><br/> Phone: +27 21 818 8635 | Mobile: +27 82 9587818 | Fax: +27
21818 8609</span><span style="font-size:12.0pt;font-family:"Times New Roman","serif";color:blue"> </span></div><br
/><hr/><font color="Gray" face="Verdana" size="1">Disclaimer<br /> This message contains information intended solely
forthe addressee, which is confidential or private in nature. If you are not the intended recipient, you may not
peruse,use, disseminate, distribute or copy this message or any file attached to this message. Any such unauthorised
use,is prohibited and may be unlawful. If you have received this message in error, please notify the sender immediately
bye-mail, facsimile or telephone and thereafter delete the original message from your machine.<br /> Furthermore, the
informationcontained in this message, and any attachments thereto, is for information purposes only and may contain the
personalviews and opinions of the author, which are not necessarily the views and opinions of CareerJunction or its
subsidiariesand associated companies. CareerJunction therefore does not accept liability for any claims, loss or
damagesof whatsoever nature, arising as a result of the reliance on such information by anyone.<br /> Whilst all
reasonablesteps are taken to ensure the accuracy and integrity of information transmitted electronically and to
preservethe confidentiality thereof, CareerJunction accepts no liability or responsibility whatsoever if information or
datais, for whatever reason, incorrect, corrupted or does not reach its intended destination.<br /></font> 

"Huda Booley (huda@careerjunction.co.za)"
<Huda@careerjunction.co.za> wrote:
> These get copied to the standby so its all there, just not being
> applied fast enough.
If the files are there and are not applying fast enough, it's
probably because on the source machine you can have multiple
connections submitting data modifications, keeping multiple CPUs and
spindles busy all at the same time.  When playing back the WAL files
on the warm standby, you have one connection which has a harder time
keeping a lot of resources busy.
I think I have some recall of improvements to that in the 8.4
release, so an upgrade might help.  Other than that, I think it's
mostly a matter of your hardware and OS.  Make sure you have a RAID
controller with battery backed cache configured for write-back,
spread the database across as many spindles as you can, don't worry
so much about how *many* CPUs you have as how *fast* they are.
I've never actually had this problem, so take my advice on it with
a grain of salt.
-Kevin

On Fri, Jan 22, 2010 at 2:37 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
>
> I think I have some recall of improvements to that in the 8.4
> release, so an upgrade might help.

There was a standalone program which you can call from your recovery
script to prefetch the blocks needed to replay the next log file
before starting to apply it. I find this message but there may be a
more recent version if you search:
http://archives.postgresql.org/pgsql-hackers/2008-11/msg01807.php

Otherwise I think there were some performance improvements to replay
but I don't recall which versions which improvements were in.

-- 
greg