Skip site navigation (1) Skip section navigation (2)

Re: Too many WAL(s) despite low transaction

From: Selva manickaraja <mavles78(at)gmail(dot)com>
To: Stephen Frost <sfrost(at)snowman(dot)net>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Too many WAL(s) despite low transaction
Date: 2011-04-01 02:33:24
Message-ID: AANLkTinoAVz9qH=fV=qNxMyZnzBXsweDgGK4hQCEebZr@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
Where you mentioned "after the reload" I suppose you meant restart right?

About compressing you mentioned iirc, but how do I use it? are there any
examples. I read about pg_compress before. Is that same?

The configuration file shows that autovacuum=on and track_count=on to be
commented out. That means that it is not running right? If that's the case,
just uncommenting it now should get it working right?

OK, I'm going to hold on to the VACUUM FREEZE ANALYZE for time being.

Yes Stephen you have been extraordinarily helpful.

I will wait for your reply.


On Fri, Apr 1, 2011 at 10:22 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> * Selva manickaraja (mavles78(at)gmail(dot)com) wrote:
> > 1. Set archive_timeout = 20m (Does the change require db restart to take
> > effect?)
>
> I *think* it can be changed with just a reload, but I'm not 100% sure.
> Check your logs after doing the reload, it'll complain if it isn't able
> to change that parameter on reload.
>
> 20m sounds reasonable, still would recommend compressing the WALs if
> they're likely to be less than full (less than 16M of data in 20m).
>
> > 2. Set  autovacuum=on and track_count=on (Does the change require db
> restart
> > to take effect?)
> >     Does that mean we are running autovacuum?
>
> This is the default, so unless you changed the default, yes, it's
> already running.
>
> > 3. Run VACUUM FREEZE ANALYZE since bulk loading was done earlier. (Can
> this
> > be done while the db is active and on production?)
>
> Yes, you can freeze records while the DB is running (erm, I don't know
> that you can run it w/o the DB running..).  I don't know that I'd jump
> to running it right away though, unless you know that you need it...?
>
> > All 3 steps is to lower the WAL files that are being shipped out.
>
> Uhh, the only option that's going to affect that is the first one..
>
> > Is this a workable action to achieve the result required?
>
> You probably just need to change archive_timeout and reload the
> database.  Well, you also need to go read the documentation, but that's
> beside the point.
>
> > Please assist.
>
> Uhm, pretty sure I have been?
>
>        Thanks,
>
>                Stephen
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (GNU/Linux)
>
> iEYEARECAAYFAk2VNtEACgkQrzgMPqB3kig1ugCeMqz9PWDozSYpfVsJh4SxzitJ
> EKAAmQFPiVurdCDNxW5YEKE4JICHHUFq
> =mJol
> -----END PGP SIGNATURE-----
>
>

In response to

Responses

pgsql-admin by date

Next:From: Stephen FrostDate: 2011-04-01 02:35:55
Subject: Re: Too many WAL(s) despite low transaction
Previous:From: Stephen FrostDate: 2011-04-01 02:22:09
Subject: Re: Too many WAL(s) despite low transaction

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group