From: Kevin Grittner <kgrittn@mail.com>
To: Baptiste LHOSTE <blhoste@alaloop.com>
Cc: pgsql-admin@postgresql.org,"Sylvain CAILLET" <scaillet@alaloop.com>
Subject: Re: Autovacuum issues with truncate and create index ...
Date: Thu, 20 Dec 2012 17:46:12 -0500
Message-ID: <20121220224612.14720@gmx.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Baptiste LHOSTE wrote:
>> Was the blocking you described occurring at the time you
>> captured this? It doesn't seem to be showing any problem.
>
> Yes indeed. We have noticed that any process seems to be in
> waiting situation but :
> - before the autovacuum process starts to work on the both kind
> of tables, truncate and index creation take less than 2
> seconds
> - after the autovacuum process starts to work on the both kind
> of tables, truncate and index creation never end
Just so we know how to interpret that, how many minutes, hours, or
days did you wait to see whether it would ever end?
> We have to stop our process, then reset the autovacuum thresold
> for second kind of tables, then restart our process.
>
> Is it possible that the fact that statistics of postgresql are
> not up-to-date could explain this behavior ?
> Is it possible that the autovacuum process does not stop itself
> when we perform a truncate or a create index ?
At the time you captured data from pg_stat_activity and pg_locks,
there were three autovacuum processes active, all running ANALYZE,
and eight TRUNCATE commands active on "normal" connections. All the
TRUNCATE statements started in the same second. One of the ANALYZE
tasks started about a minute and a half before that, the other two
started about a minute after the TRUNCATE statements. All are on
different tables, and no heavyweight locking is showing up.
I've heard enough reports of behavior like this to believe that
there is some sort of bug here, but the nature of it is not
apparent. We could really use more clues.
If it doesn't cause too much pain to let it get into this state for
a few minutes, it might help diagnose the issue if you could start
`vmstat 1` before you let it get into this state, and capture `ps
aux | postgres`, pg_stat_activity, and pg_locks at intervals while
it is in this state. Looking at all of the above might suggest a
cause. If we can find the cause, we can almost certainly fix it.
-Kevin
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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-admin@postgresql.org
Cc: kgrittn@mail.com, blhoste@alaloop.com, scaillet@alaloop.com
Subject: Re: Autovacuum issues with truncate and create index ...
In-Reply-To: <20121220224612.14720@gmx.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