Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 42B202E008B for ; Mon, 28 Jan 2008 16:38:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 86374-02 for ; Mon, 28 Jan 2008 16:37:51 -0400 (AST) Received: from bay0-omc3-s24.bay0.hotmail.com (bay0-omc3-s24.bay0.hotmail.com [65.54.246.224]) by postgresql.org (Postfix) with ESMTP id 065FE2E0069 for ; Mon, 28 Jan 2008 16:37:59 -0400 (AST) Received: from BAY116-W32 ([64.4.38.132]) by bay0-omc3-s24.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.3959); Mon, 28 Jan 2008 12:37:59 -0800 Message-ID: Content-Type: multipart/alternative; boundary="_fc3a666b-f3d3-479d-95a1-100fb9b6f3c8_" X-Originating-IP: [204.132.58.105] From: dx k9 To: posgres support Subject: Zen and art if WAL and dump file mainteance Date: Mon, 28 Jan 2008 15:37:58 -0500 Importance: Normal MIME-Version: 1.0 X-OriginalArrivalTime: 28 Jan 2008 20:37:59.0012 (UTC) FILETIME=[AB5BC240:01C861ED] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/266 X-Sequence-Number: 28040 --_fc3a666b-f3d3-479d-95a1-100fb9b6f3c8_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, We are trying to confirm our understanding of WAL. Thank you in advance fo= r any comments and help. We have version 8.24. We noticed that when our nightly maintenance of the WAL folders kicked off = *** - see below. Production processing was "KILLED". We were in the pr= ocess of rekeying MANY existing tables. We were doing heavy processing(which we normally don't do at the time of ma= intenance - adding fields, table creation and filling tables going on. Du= ring this time, archiving was enabled. ## Our data is set up like this A /sprj/sqldumps_Linux.pgsql_serverX is our storage for the WAL B /sqldata_Linux.pgsql_serverX is another computer/folder where the actual = data is (pgdata). We are trying to figure out why maintenance on A (zipping up & removing fil= es) would have an affect on production processing (killed it) on pgdata - B= . We are thinking we should have turned off the nightly maintenance at this p= oint, but we are wondering why and trying to improve our process logic. We= were thinking that our zipping and deleting activity on A would not have an affect on B. And it never has before, alth= ough we never had heavy processing at that time before. Since, the archives don't happen too often, until the 36 logfile segments l= ocally gets filled up, then one gets pushed out. However, our clean up pro= cess is running from 11:30 - 12:32 in this case. In our case, the first script wasn't finished zipping causing the second re= move script to fail. *** #This is what happened on A - nightly maintenance of dumps folder *** Mayb= e there is a better way to do this and we are missing something here 30 19,23 * * * find /maintenance/NFSmntdd.dump001/sqldumps_Linux.pgsql_* -t= ype f -mmin +10 ! -name \*.gz -size +0c -exec gzip {} \;=20 30 0 * * * find /maintenance/NFSmntdd.dump001/sqldumps_Linux.pgsql_* -type = f -mmin +20 -exec rm {} \ # This is what was happening on B #--------------------------------------------------------------------------= - # WRITE AHEAD LOG #--------------------------------------------------------------------------= - # - Settings - #fsync =3D on # turns forced synchronization on= or off #wal_sync_method =3D fsync # the default is the first option # supported by the operating system= : # open_datasync # fdatasync # fsync # fsync_writethrough # open_sync #full_page_writes =3D on # recover from partial page write= s #wal_buffers =3D 64kB # min 32kB wal_buffers =3D 128kB # min 32kB # (change requires restart) #commit_delay =3D 0 # range 0-100000, in microseconds #commit_siblings =3D 5 # range 1-1000 # - Checkpoints - #checkpoint_segments =3D 3 # in logfile segments, min 1, 16M= B each checkpoint_segments =3D 36 # in logfile segments, min 1, 16M= B each #checkpoint_timeout =3D 5min # range 30s-1h #checkpoint_warning =3D 30s # 0 is off # - Archiving - #archive_command =3D '' # command to use to archive a logfile seg= ment archive_command =3D 'cp "%p" /sprj/sqldumps_Linux.pgsql_serverx/"%f"' = # command to use to archive a logfile segment archive_timeout =3D 0 # force a logfile segment switch after th= is # many seconds; 0 is off _________________________________________________________________ Shed those extra pounds with MSN and The Biggest Loser! http://biggestloser.msn.com/= --_fc3a666b-f3d3-479d-95a1-100fb9b6f3c8_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi,

We are trying to confirm our understan= ding of WAL.  Thank you in advance for any comments and help.  We= have version 8.24.

We noticed that when our nightly maintenance of = the WAL folders kicked off    *** - see below.  Production p= rocessing was "KILLED".  We were in the process of rekeying  MANY= existing tables.
We were doing heavy processing(which we normally don't= do at the time of maintenance -  adding fields, table creation and fi= lling tables going on.  During this time, archiving was enabled.
## Our data is set up like this
A /sprj/sqldumps_Linux.pgsql_serverX i= s our storage for the WAL
B /sqldata_Linux.pgsql_serverX is another comp= uter/folder where the actual data is (pgdata).

We are trying to figu= re out why maintenance on A (zipping up & removing files) would have an= affect on production processing (killed it) on pgdata - B.

We are t= hinking we should have turned off the nightly maintenance at this point, bu= t we are wondering why and trying to improve our process logic.  We we= re thinking that our zipping and deleting
activity on A would not have a= n affect on B.  And it never has before, although we never had heavy p= rocessing at that time before.
Since, the archives don't happen too ofte= n, until the 36 logfile segments locally gets filled up, then one gets push= ed out.  However, our clean up process is running from 11:30 - 12:32 i= n this case.
In our case, the first script wasn't finished zipping causi= ng the second remove script to fail.

***
#This is what happened o= n A - nightly maintenance of dumps folder ***  Maybe there is a better= way to do this and we are missing something here
30 19,23 * * * find /m= aintenance/NFSmntdd.dump001/sqldumps_Linux.pgsql_* -type f -mmin +10 ! -nam= e \*.gz -size +0c -exec gzip {} \;
30 0 * * * find /maintenance/NFSmntd= d.dump001/sqldumps_Linux.pgsql_* -type f -mmin +20 -exec rm {} \

# This is what was happening on B
#------------------------------------= ---------------------------------------
# WRITE AHEAD LOG
#----------= -----------------------------------------------------------------

# = - Settings -

#fsync =3D on       =             &nb= sp;         # turns forced synchron= ization on or off
#wal_sync_method =3D fsync    &nbs= p;           # the defaul= t is the first option
        &n= bsp;            = ;            &n= bsp;      # supported by the operating system:
=             &nb= sp;            =             &nb= sp;  #   open_datasync
     &nbs= p;            &= nbsp;           &nbs= p;         #   fdatasync<= br>            =             &nb= sp;            =    #   fsync
      &nb= sp;            =             &nb= sp;        #   fsync_writethro= ugh
           &n= bsp;            = ;            &n= bsp;   #   open_sync
#full_page_writes =3D on &= nbsp;           &nbs= p;    # recover from partial page writes
#wal_buffers =3D= 64kB           &nbs= p;         # min 32kB
wal_buffer= s =3D 128kB          &nbs= p;          # min 32kB
&nbs= p;            &= nbsp;           &nbs= p;            &= nbsp; # (change requires restart)
#commit_delay =3D 0   &= nbsp;           &nbs= p;       # range 0-100000, in microseconds#commit_siblings =3D 5        &nbs= p;           # range 1-10= 00

# - Checkpoints -

#checkpoint_segments =3D 3  &n= bsp;            = ; # in logfile segments, min 1, 16MB each
checkpoint_segments =3D 36&nbs= p;            &= nbsp;  # in logfile segments, min 1, 16MB each
#checkpoint_timeout = =3D 5min           &= nbsp;  # range 30s-1h
#checkpoint_warning =3D 30s   =             # 0 is o= ff

# - Archiving -

#archive_command =3D ''   &= nbsp;       # command to use to archive a log= file segment
archive_command =3D 'cp "%p" /sprj/sqldumps_Linux.pgsql_ser= verx/"%f"'         # command to use= to archive a logfile segment
archive_timeout =3D 0   &nb= sp;         # force a logfile segme= nt switch after this
        &nb= sp;            =            # many seconds= ; 0 is off


Shed those extra pounds with MSN and The Biggest = Loser! Learn more.= = --_fc3a666b-f3d3-479d-95a1-100fb9b6f3c8_--