From: Tom Lane <tgl@sss.pgh.pa.us>
To: jan.l.peterson@gmail.com
Cc: pgsql-performance@postgresql.org
Subject: Re: poor VACUUM performance on large tables
Date: Sun, 04 Sep 2005 19:01:10 -0400
Message-ID: <9274.1125874870@sss.pgh.pa.us> (raw)
In-Reply-To: <72e966b00509032316166ff0cf@mail.gmail.com>
References: <72e966b00509032316166ff0cf@mail.gmail.com>
Jan Peterson <jan.l.peterson@gmail.com> writes:
> We have been experiencing poor performance of VACUUM in our production
> database.
Which PG version, exactly?
> Everything works great until our rolling delete kicks in. Of course,
> we are doing periodic VACUUMS on all tables, with frequent VACUUMs on
> the more active tables. The problem arises when we start deleting the
> bulk data and have to VACUUM pg_largeobject and our other larger
> tables. We have seen VACUUM run for several hours (even tens of
> hours).
Plain VACUUM (not FULL) certainly ought not take that long. (If you're
using VACUUM FULL, the answer is going to be "don't do that".) What
maintenance_work_mem (or vacuum_mem in older releases) are you running
it under? Can you get VACUUM VERBOSE output from some of these cases
so we can see which phase(s) are eating the time? It'd also be
interesting to watch the output of vmstat or local equivalent --- it
might just be that your I/O capability is nearly saturated and VACUUM is
pushing the system over the knee of the response curve. If so, the
vacuum delay options of 8.0 would be worth experimenting with.
> Statistics are also skewed during this
> process and we have observed the planner choosing sequential scans on
> tables where it is obvious that an index scan would be more efficient.
That's really pretty hard to believe; VACUUM doesn't affect the
statistics until the very end. Can you give some specifics of how
the "statistics are skewed"?
regards, tom lane
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-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, jan.l.peterson@gmail.com
Subject: Re: poor VACUUM performance on large tables
In-Reply-To: <9274.1125874870@sss.pgh.pa.us>
* 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