DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com;
 h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
 b=UbwHNR6FQQfxepaFNUVyswwFgJ2YlrTaXSJFm1hhU26A8fgQaXhPIv45nqqGDnkpE5tb33cvlMHLhJAtpvdcH5yG6Xl59NVrLSeRYSeL77tOcQM28So4hzj9BK+sC3LHM2XqJ4ZuI+BqvOGFT1XSNVZvtrE/iewIOEIaDB66Gsc=;
References: <E59C57F2AE0F8641B5B428292D88479F26D5E1CB@ATLPXCH02MB.uss.net>
 <1386969850.61933.YahooMailNeo@web162903.mail.bf1.yahoo.com>
 <E59C57F2AE0F8641B5B428292D88479F26D5E384@ATLPXCH02MB.uss.net>
Message-ID: <1386973904.10081.YahooMailNeo@web162903.mail.bf1.yahoo.com>
Date: Fri, 13 Dec 2013 14:31:44 -0800 (PST)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: AUTOVACUUM after purging a lot of records
To: Jeni Fifrick <jfifrick@incomm.com>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
In-Reply-To: <E59C57F2AE0F8641B5B428292D88479F26D5E384@ATLPXCH02MB.uss.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Jeni Fifrick <jfifrick@incomm.com> wrote:

> I think, I'll let autovaccum run and check it tomorrow.

Seems reasonable.

> So, what you're saying is if I run the "VACUUM ANALYZE" while the
> autovacuum still running, the autovacuum will be cancelled, right?

Yes.

> And, I need to set the maintenance_work_mem in the session before
> executing the 'VACUUM ANALYZE transactionlog'. Is this a correct
> syntax?

You might want to connect with psql and issue these statements:

SET maintenance_work_mem =3D '2GB';
VACUUM ANALYZE transactionlog;

> Regarding the configuration, all the memory related are still
> with default value.

> We did change the max_connections to be 2000. BUT, so far, our
> max connection is around 500.

Well, work_mem is the limit on memory used by each node (processing
step) of every query that is running.=A0 A good rule of thumb is to
assume one such allocation per allowed connection, and keep that to
25% of machine RAM.=A0 So 64GB * 0.25 / 2000 would be 8MB.=A0 If you
could drop max connections, you could boost work_mem proportionally
without risking blowing out all of your OS cache (or even
swapping).

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin