Re: AUTOVACUUM after purging a lot of records

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