Re: pgstat wait timeout encountered

[John Scalia <jayknowsunix@xxxxxxxxx>]

Not to sound stupid, but two possible issues:

1) this is a really big table and
2) you have no index on the field "datname"

Possible?
Jay

On 3/9/2015 3:50 PM, Mark Steben wrote:

Good afternoon,

We have been sporadically receiving a warning:  pgstat wait timeout

in our production environment.  This has been always immediately followed by

a successful execution of the query.

EX:
 

Mar  6 00:31:29 ardbc01 postgres[42903]: [3-1] mavmail@10.93.156.42:prime_production:0WARNING:  pgstat wait timeout
Mar  6 00:31:29 ardbc01 postgres[42903]: [3-2] mavmail@10.93.156.42:prime_production:0STATEMENT:  SELECT * FROM pg_stat_database WHERE datname='prime_production';
Mar  6 00:31:29 ardbc01 postgres[42903]: [4-1] mavmail@10.93.156.42:prime_production:0LOG:  duration: 10137.143 ms  statement: SELECT * FROM pg_stat_database WHERE datname='prime_production';

and

mavmail@10.93.156.42:prime_production:0WARNING:  pgstat wait timeout
Mar  6 00:31:46 ardbc01 postgres[42903]: [6-2] mavmail@10.93.156.42:prime_production:0STATEMENT:  SELECT SUM(idx_blks_hit) AS hits, SUM(idx_blks_read) AS reads FROM pg_statio_user_indexes
Mar  6 00:31:46 ardbc01 postgres[42903]: [7-1] mavmail@10.93.156.42:prime_production:0LOG:  duration: 10144.601 ms  statement: SELECT SUM(idx_blks_hit) AS hits, SUM(idx_blks_read) AS reads FROM pg_statio_user_indexes

This is not confined to queries of pg stat infrastructure tables. In another database we can receive the same warning on a vacuum:

mavmail@10.93.156.45:mavmail WARNING:  pgstat wait timeout
Mar  6 01:16:44 ardbc01 postgres[66365]: [12-2] mavmail@10.93.156.45:mavmail STATEMENT:  VACUUM VERBOSE ANALYZE update_queues;
Mar  6 01:16:44 ardbc01 postgres[66365]: [13-1] mavmail@10.93.156.45:mavmail INFO:  vacuuming "public.update_queues"
Mar  6 01:16:44 ardbc01 postgres[66365]: [13-2] mavmail@10.93.156.45:mavmail STATEMENT:  VACUUM VERBOSE ANALYZE update_queues;
Mar  6 01:16:44 ardbc01 postgres[66365]: [14-1] mavmail@10.93.156.45:mavmail INFO:  index "uq_qid" now contains 0 row versions in 7 pages
Mar  6 01:16:44 ardbc01 postgres[66365]: [14-2] mavmail@10.93.156.45:mavmail DETAIL:  0 index row versions were removed.
Mar  6 01:16:44 ardbc01 postgres[66365]: [14-3] #0114 index pages have been deleted, 4 are currently reusable.
Mar  6 01:16:44 ardbc01 postgres[66365]: [14-4] #011CPU 0.00s/0.00u sec elapsed 0.00 sec.
Mar  6 01:16:44 ardbc01 postgres[66365]: [14-5] mavmail@10.93.156.45:mavmail STATEMENT:  VACUUM VERBOSE ANALYZE update_queues;
Mar  6 01:16:44 ardbc01 postgres[66365]: [15-1] mavmail@10.93.156.45:mavmail INFO:  "update_queues": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
Mar  6 01:16:44 ardbc01 postgres[66365]: [15-2] mavmail@10.93.156.45:mavmail DETAIL:  0 dead row versions cannot be removed yet.
Mar  6 01:16:44 ardbc01 postgres[66365]: [15-3] #011There were 0 unused item pointers.
Mar  6 01:16:44 ardbc01 postgres[66365]: [15-4] #0110 pages are entirely empty.
Mar  6 01:16:44 ardbc01 postgres[66365]: [15-5] #011CPU 0.00s/0.00u sec elapsed 0.00 sec.
Mar  6 01:16:44 ardbc01 postgres[66365]: [15-6] mavmail@10.93.156.45:mavmail STATEMENT:  VACUUM VERBOSE ANALYZE update_queues;

I did check older posts on pgadmin - one insight involved the possible inability of autovacuum to grab the latest stats as the stats collector might have gotten stuck in a loop and could not update the stats file.  That was from an older PSQL version and we don't employ autovacuum in these databases.

Any insights appreciated:

  postgres version: 9.2.5

  platform:
  Linux ardbc01 2.6.32-358.18.1.el6.x86_64 #1 SMP Wed Aug 28 17:19:38 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux

Thank you,

Mark Steben
 Database Administrator
@utoRevenue | Autobase 
  CRM division of Dominion Dealer Solutions 
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567

www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
 www.drivedominion.com