From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Selva manickaraja <mavles78@gmail.com>
To: Stephen Frost <sfrost@snowman.net>
Cc: pgsql-admin@postgresql.org
Subject: Re: Too many WAL(s) despite low transaction
Date: Fri, 01 Apr 2011 09:15:55 -0500
Message-ID: <4D9597CB020000250003C0FE@gw.wicourts.gov> (raw)
In-Reply-To: <AANLkTini7zev+CyTLMGFFsaZucxCR35rBsgg_Znj8F-7@mail.gmail.com>
References: <AANLkTi=Vc91VMA1_w3e6yV0gmAL1xgbogj9p1fW3zmQ=@mail.gmail.com>
<20110401004240.GA4116@tamriel.snowman.net>
<AANLkTik9S-bB0nhAzUWED0vKS_JgF3RkZd0JPNq-C-Yn@mail.gmail.com>
<20110401010336.GB4116@tamriel.snowman.net>
<AANLkTi=U_2opbk3Kg223=++V9EfK5KGw-M-28Ec0dxCe@mail.gmail.com>
<20110401013853.GC4116@tamriel.snowman.net>
<AANLkTini7zev+CyTLMGFFsaZucxCR35rBsgg_Znj8F-7@mail.gmail.com>
Selva manickaraja <mavles78@gmail.com> wrote:
> Since the production database is running, I plan to do now is
> this
>
> 1. Set archive_timeout = 20m (Does the change require db restart
> to take effect?)
You'd better make that '20min'. Reload should suffice.
> 2. Set autovacuum=on and track_count=on (Does the change require
> db restart to take effect?)
Reload should suffice.
> Does that mean we are running autovacuum?
Autovacuum is always there to prevent transaction ID wraparound, but
you really should be using it with at least the default
aggressiveness in almost all environments.
> 3. Run VACUUM FREEZE ANALYZE since bulk loading was done earlier.
> (Can this be done while the db is active and on production?)
It can be done while the server is running, but be prepared for a
massive WAL volume and a likely-noticeable performance impact. We
usually do our bulk loads with archiving off and run VACUUM FREEZE
ANALYZE before turning archiving on and letting the users in.
Otherwise, since everything in a bulk load will have the same xmin,
or very close to that, autovacuum can kick in and tank performance
and glut the WAL streaming for a long time at some unpredictable
moment -- although that moment is most likely to be in the midst of
high transaction volume.
> Is this a workable action to achieve the result required?
Those are all sound like good ideas, given your situation.
As mentioned elsewhere on the thread, WAL file compression can help
quite a bit. That's especially true if you filter the WAL through
pg_clearxlogtail or pg_lesslog before compression. The issue there
is that WAL files are reused without clearing the tail of the file,
so if you don't use one of these programs before compression a file
which is switched out based on time, before it's full, will probably
take just as much space as a full file. With pg_clearxlogtail an
"empty" WAL file will gzip to 16KB (three orders of magnitude better
than uncompressed). I haven't used pg_lesslog but it might do even
better.
-Kevin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, mavles78@gmail.com, sfrost@snowman.net
Subject: Re: Too many WAL(s) despite low transaction
In-Reply-To: <4D9597CB020000250003C0FE@gw.wicourts.gov>
References: <AANLkTi=Vc91VMA1_w3e6yV0gmAL1xgbogj9p1fW3zmQ=@mail.gmail.com> <20110401004240.GA4116@tamriel.snowman.net> <AANLkTik9S-bB0nhAzUWED0vKS_JgF3RkZd0JPNq-C-Yn@mail.gmail.com> <20110401010336.GB4116@tamriel.snowman.net> <AANLkTi=U_2opbk3Kg223=++V9EfK5KGw-M-28Ec0dxCe@mail.gmail.com> <20110401013853.GC4116@tamriel.snowman.net> <AANLkTini7zev+CyTLMGFFsaZucxCR35rBsgg_Znj8F-7@mail.gmail.com> <4D9597CB020000250003C0FE@gw.wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox