Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 488891337999 for ; Thu, 31 Mar 2011 23:22:19 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 09458-04 for ; Fri, 1 Apr 2011 02:22:10 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from tamriel.snowman.net (tamriel.snowman.net [72.66.115.51]) by mail.postgresql.org (Postfix) with ESMTP id B069A13364DE for ; Thu, 31 Mar 2011 23:22:08 -0300 (ADT) Received: by tamriel.snowman.net (Postfix, from userid 1000) id 2B0EA229A7; Thu, 31 Mar 2011 22:22:09 -0400 (EDT) Date: Thu, 31 Mar 2011 22:22:09 -0400 From: Stephen Frost To: Selva manickaraja Cc: pgsql-admin@postgresql.org Subject: Re: Too many WAL(s) despite low transaction Message-ID: <20110401022209.GD4116@tamriel.snowman.net> References: <20110401004240.GA4116@tamriel.snowman.net> <20110401010336.GB4116@tamriel.snowman.net> <20110401013853.GC4116@tamriel.snowman.net> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="xv7XqFHmY6tBbSbJ" Content-Disposition: inline In-Reply-To: X-Editor: Vim http://www.vim.org/ X-Info: http://www.snowman.net X-Operating-System: Linux/2.6.32-5-amd64 (x86_64) X-Uptime: 22:15:44 up 57 days, 10:45, 13 users, load average: 0.01, 0.04, 0.00 User-Agent: Mutt/1.5.20 (2009-06-14) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/307 X-Sequence-Number: 37179 --xv7XqFHmY6tBbSbJ Content-Type: text/plain; charset=us-ascii Content-Disposition: inline * Selva manickaraja (mavles78@gmail.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 --xv7XqFHmY6tBbSbJ Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.10 (GNU/Linux) iEYEARECAAYFAk2VNtEACgkQrzgMPqB3kig1ugCeMqz9PWDozSYpfVsJh4SxzitJ EKAAmQFPiVurdCDNxW5YEKE4JICHHUFq =mJol -----END PGP SIGNATURE----- --xv7XqFHmY6tBbSbJ--