Autovacuum doesn't work if the table has large number of records

On Saturday, April 13, 2013, Ascot Moss wrote:

> Hi Jeff,
>
> Thanks for your reply.
>
>
> Yes, it took a while to complete the autovacuum.
>
> I checked it again this morning:
>
> last_autovacuum: 2013-04-14 06:17:02.9464+08
>
> last_autoanalyze: 2013-04-13 20:27:12.396048+08
>
> ...
>
>
> I also checked the pg_log files, the autovacuum somehow was delayed by
> "pgstat wait timeout" , during 1:10am to 05:41am this morning (over 4.5
> hours).
>
> I think this would impact the DB performance.
>

Those warnings are not directly related to your vacuum performance,
although they do indicate your system is under some IO stress. Whether
that stress is slowing down the vacuum, or caused by the vacuum, or
irrelevant to it, is hard to say. Was the system idle other than the
vacuum? What indexes do you have on the table? What is your IO subsystem
like?

...

> I am new to PostgreSQL, can anyone advise me how to handle "pgstat wait
> timeout"?
>

If they occur during maintenance/refactoring/bulk loading, just ignore
them. If they occur a lot during normal operations, you probably need
better hardware. Maybe separating the pg_xlog from the rest of the IO if
you haven't already. (You could also move the stats file to a ramdisk, but
that is probably treating the symptom more than the problem)

Cheers,

Jeff