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=IU21nspvTa3fDQh3Zt2oCXfUwnaF3EgsW7esr6eNx+AHmd50FNucA1EqtJHw2lgKjmQ8qM1xp+kmRzUGCNv7QK4C6dLwqgjTDaqtFtWZDmYcdzKFOBriprMsHIXAKc+4tnJupIVw9UbYEaoQNb/ESz/UBo/xqCrkaoGmLetLn30=;
References: <E59C57F2AE0F8641B5B428292D88479F26D5E1CB@ATLPXCH02MB.uss.net>
Message-ID: <1386969850.61933.YahooMailNeo@web162903.mail.bf1.yahoo.com>
Date: Fri, 13 Dec 2013 13:24:10 -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: <E59C57F2AE0F8641B5B428292D88479F26D5E1CB@ATLPXCH02MB.uss.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Jeni Fifrick <jfifrick@incomm.com> wrote:

> Is it normal for vacumm to run that long (23 hour)?

It can be.=C2=A0 The autovacuum workers are "paced" so that their
resource usage is limited.=C2=A0 This spreads out the work, to minimize
impact on foreground processes.=C2=A0 In some cases the default
configuration is not aggressive enough to keep up overall, but that
should be what you look at, not whether a particular worker takes
days on a particular table -- that in itself doesn't indicate a
problem.

> Is there a way to verify whether the vacuum process is running
> properly and making any progress?

Use OS tools (like top or ps) to check whether the process is still
consuming resources.

> Can I increase the maintenance_work_mem parameter to make the
> existing process faster?

That is one of many settings which are often tuned.=C2=A0 If you really
want to cause that table to finish quickly, you could just start a
VACUUM ANALYZE command for that table.=C2=A0 The autovacuum run will
cancel to make room for it, and the command default to running full
speed rather than pacing its work.=C2=A0 You could set
maintenance_work_mem or cost-based pacing for the command in the
session before running the command to optimize it or pace it.

> Do you think it=E2=80=99s better to do VACUUM FULL, consider the amount
> of records deleted?

That depends on how much data is left and whether you can afford to
have the table exclusively locked while its data is copied and
re-indexed.

> Any other input/recommendation you can give us?

If you expect to purge sets of data like that, and you know what
purge group a row belongs in at the time it is inserted, you should
probably look at partitioning.=C2=A0 There's no faster way to drop
millions of rows from a table than to drop the partition they are
in.

> We=E2=80=99re using Vpostgres version 9.1.3

If you want to stay with 9.1 for now, you should upgrade to the
latest minor release.

http://www.postgresql.org/support/versioning/

> on CentOS 6.3. 12 cores and 64 GB memory.
> But, we=E2=80=99re using default setup.

With a machine that size, you definitely should tune several
settings.=C2=A0 The defaults are geared more toward being able to
"install and go" on a low-end laptop.=C2=A0 Serious servers need tuning.

If you really are at the default configuration (including
max_connections), the following would almost certainly be
improvements:

shared_buffers =3D 8GB
effective_cache_size =3D 32GB
work_mem =3D 160MB
maintenance_work_mem =3D 2GB
wal_buffers =3D 32MB
checkpoint_completion_target =3D 0.9
checkpoint_segments =3D 64
autovacuum_vacuum_cost_limit =3D 800
cpu_tuple_cost =3D 0.03

Depending on the size of your active data set, you may also want to
reduce random_page_cost; but you didn't provide enough information
to know that.=C2=A0 That's hardly a complete list, but it should help
with performance on most servers and workloads with the RAM and
cores you describe.

--
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