From: Alvaro Herrera <alvherre@commandprompt.com>
To: kuopo <spkuo@cs.nctu.edu.tw>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: autovacuum blocks the operations of other manual vacuum
Date: Sun, 21 Nov 2010 13:25:37 -0300
Message-ID: <1290356427-sup-9213@alvh.no-ip.org> (raw)
In-Reply-To: <AANLkTi=us2bK6QQOzNvwAZepjHcXcWZLr8J8cE-p6op1@mail.gmail.com>
References: <AANLkTimNrG6JA6MbMb52T5CVt+hi_hsD_fW6NRDkARgH@mail.gmail.com>
<1289921093-sup-1911@alvh.no-ip.org>
<AANLkTimYQ40v+ryYFRkQ7ENdwuQOxGUv3j7sLLjDwd38@mail.gmail.com>
<1290217475-sup-5126@alvh.no-ip.org>
<AANLkTi=us2bK6QQOzNvwAZepjHcXcWZLr8J8cE-p6op1@mail.gmail.com>
Excerpts from kuopo's message of dom nov 21 11:15:52 -0300 2010:
> In my experiment, I need about 1~3 min to finish the analyze operation
> on the big table (which depends on the value of vacuum_cost_delay). I
> am not surprised because this table is a really big one (now, it has
> over 200M records).
Okay. You may want to consider lowering the statistics size for all the
column in that table; that would reduce analyze time, at the cost of
possibly worsening the plans for that table, depending on how irregular
the distribution is. See ALTER TABLE / SET STATISTICS in the
documentation, and the default_statistics_target parameter in
postgresql.conf.
> However, the most of my concerns is the behavior of analyze/vacuum.
> You mentioned that the analyze-only operation cannot be optimized as
> the same way on optimizing vacuum. Does that mean the analyze
> operation on a table would unavoidably affect the vacuum proceeded on
> another one?
That's correct. I think you can run VACUUM ANALYZE, and it would do
both things at once; AFAIK this is also optimized like VACUUM is, but I
admit I'm not 100% sure (and I can't check right now).
> If this is a normal reaction for an analyze operation,
> maybe I should try to lower vacuum_cost_delay or use more powerful
> hardware to minimize the interfered period. So, the pages for the
> small table would not increase quickly.
I think it would make sense to have as low a cost_delay as possible for
this ANALYZE. (Note you can change it locally with a SET command; no
need to touch postgresql.conf. So you can change it when you analyze
just this large table).
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
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: alvherre@commandprompt.com, spkuo@cs.nctu.edu.tw
Subject: Re: autovacuum blocks the operations of other manual vacuum
In-Reply-To: <1290356427-sup-9213@alvh.no-ip.org>
* 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