Thread: Timeout error on pgstat

I have a lot (maybe 1 every 10 seconds) of this error WARNING:  pgstat wait timeout

Also this

ERROR:  canceling autovacuum task

In the pg_stat_activity show an autovacuum process over a very used table that runs about 1 hour and then this vacuum is cancelled (according to log)

I have Postgres 9.0.3 on a windows 2008 R2 running for about 1 year in same conditions, but this error is occurring about 1 week ago.

Thanks!!

Craig, those lines appear between pgstat timeout

ERROR:  canceling autovacuum task

CONTEXT:  automatic vacuum of table "XXX"

The table XXX is a table with about 200 insert p/ second. No update or delete.

The problem apparently is just with this table because there are others autovacuum running and working fin over others tables

The only difference is that this table XXX has about 5millions  of insert daily and all those 5millons are deleted in the night (cleanup process).

I have a lot (maybe 1 every 10 seconds) of this error WARNING:  pgstat wait timeout

A quick search suggests this can be due to excessive I/O. However, this thread:

http://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-td5078125.html

sounds very similar to your issue. I'm wondering if there's a bug lurking in there somewhere.

Was there any context to the `cancelling autovacuum task' message?

The current 9.0 release is 9.0.8, so you're missing a bunch of bug fixes.

http://www.postgresql.org/docs/current/static/release-9-0-8.html

Consider updating. You don't need to do a dump and reload or use pg_upgrade, since it's only a minor version update. Stop the DB, install the new binaries, start the DB.

However, I don't see any fixes related to the stats writer in the relnotes from the 9.0 series.

--
Craig Ringer

Maybe this can contribute…

When I run a query over this table XXX, and immediately try to cancel the query, the cancel never completes.

I found that this situation was fixed in last release (9.0.8)

·         Ensure sequential scans check for query cancel reasonably often (Merlin Moncure)

A scan encountering many consecutive pages that contain no live tuples would not respond to interrupts meanwhile

Maybe in the autovacuum happen something similar?

Craig, those lines appear between pgstat timeout

ERROR:  canceling autovacuum task

CONTEXT:  automatic vacuum of table "XXX"

The table XXX is a table with about 200 insert p/ second. No update or delete.

The problem apparently is just with this table because there are others autovacuum running and working fin over others tables

The only difference is that this table XXX has about 5millions  of insert daily and all those 5millons are deleted in the night (cleanup process).

I have a lot (maybe 1 every 10 seconds) of this error WARNING:  pgstat wait timeout

A quick search suggests this can be due to excessive I/O. However, this thread:

http://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-td5078125.html

sounds very similar to your issue. I'm wondering if there's a bug lurking in there somewhere.

Was there any context to the `cancelling autovacuum task' message?

The current 9.0 release is 9.0.8, so you're missing a bunch of bug fixes.

http://www.postgresql.org/docs/current/static/release-9-0-8.html

Consider updating. You don't need to do a dump and reload or use pg_upgrade, since it's only a minor version update. Stop the DB, install the new binaries, start the DB.

However, I don't see any fixes related to the stats writer in the relnotes from the 9.0 series.

--
Craig Ringer

Is probably that the cause of this behavior are many many dead tuples in the table.?

I have a lot (maybe 1 every 10 seconds) of this error WARNING:  pgstat wait timeout

Also this

ERROR:  canceling autovacuum task

In the pg_stat_activity show an autovacuum process over a very used table that runs about 1 hour and then this vacuum is cancelled (according to log)

I have Postgres 9.0.3 on a windows 2008 R2 running for about 1 year in same conditions, but this error is occurring about 1 week ago.

Thanks!!