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