From: tv@fuzzy.cz
To: pgsql-performance <pgsql-performance@postgresql.org>
Cc: kuopo <spkuo@cs.nctu.edu.tw>
Subject: Re: autovacuum blocks the operations of other manual vacuum
Date: Sat, 20 Nov 2010 05:43:18 +0100
Message-ID: <a6493ebdc86cb37c0dce4d904220dbf2.squirrel@sq.gransy.com> (raw)
In-Reply-To: <1290217475-sup-5126@alvh.no-ip.org>
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>
> Excerpts from kuopo's message of jue nov 18 04:10:36 -0300 2010:
>> However, when I analyze the table A, the autovacuum or vacuum on the
>> table B cannot find any removable row version (the number of
>> nonremoveable row versions and pages keeps increasing). After the
>> analysis finishes, the search operations on the table B is still
>> inefficient. If I call full vacuum right now, then I can have quick
>> response time of the search operations on the table B again.
Hi, I don't know how to fix the long VACUUM/ANALYZE, but have you tried to
minimize the growth using HOT?
HOT means that if you update only columns that are not indexed, and if the
update can fit into the same page (into an update chain), this would not
create a dead row.
Are there any indexes on the small table? How large is it? You've
mentioned there are about 2049 rows - that might be just a few pages so
the indexes would not be very efficient anyway.
Try to remove the indexes, and maybe create the table with a smaller
fillfactor (so that there is more space for the updates).
That should be much more efficient and the table should not grow.
You can see if HOT works through pg_stat_all_tables view (columns
n_tup_upd and n_tup_hot_upd).
regards
Tomas
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: tv@fuzzy.cz, spkuo@cs.nctu.edu.tw
Subject: Re: autovacuum blocks the operations of other manual vacuum
In-Reply-To: <a6493ebdc86cb37c0dce4d904220dbf2.squirrel@sq.gransy.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