From: Kevin Grittner <kgrittn@ymail.com>
To: Jeni Fifrick <jfifrick@incomm.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: AUTOVACUUM after purging a lot of records
Date: Fri, 13 Dec 2013 14:31:44 -0800 (PST)
Message-ID: <1386973904.10081.YahooMailNeo@web162903.mail.bf1.yahoo.com> (raw)
In-Reply-To: <E59C57F2AE0F8641B5B428292D88479F26D5E384@ATLPXCH02MB.uss.net>
References: <E59C57F2AE0F8641B5B428292D88479F26D5E1CB@ATLPXCH02MB.uss.net>
<1386969850.61933.YahooMailNeo@web162903.mail.bf1.yahoo.com>
<E59C57F2AE0F8641B5B428292D88479F26D5E384@ATLPXCH02MB.uss.net>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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 = '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. A good rule of thumb is to
assume one such allocation per allowed connection, and keep that to
25% of machine RAM. So 64GB * 0.25 / 2000 would be 8MB. 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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 using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: kgrittn@ymail.com, jfifrick@incomm.com
Subject: Re: AUTOVACUUM after purging a lot of records
In-Reply-To: <1386973904.10081.YahooMailNeo@web162903.mail.bf1.yahoo.com>
* 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