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
