Re: pgstat wait timeout encountered

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(at)10(dot)93(dot)156(dot)42:prime_production:0WARNING: pgstat wait timeout
> Mar 6 00:31:29 ardbc01 postgres[42903]: [3-2] mavmail(at)10(dot)93(dot)156(dot)42:prime_production:0STATEMENT: SELECT * FROM pg_stat_database WHERE datname='prime_production';
> Mar 6 00:31:29 ardbc01 postgres[42903]: [4-1] mavmail(at)10(dot)93(dot)156(dot)42:prime_production:0LOG: duration: 10137.143 ms statement: SELECT * FROM pg_stat_database WHERE
> datname='prime_production';
>
> and
>
> mavmail(at)10(dot)93(dot)156(dot)42:prime_production:0WARNING: pgstat wait timeout
> Mar 6 00:31:46 ardbc01 postgres[42903]: [6-2] mavmail(at)10(dot)93(dot)156(dot)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(at)10(dot)93(dot)156(dot)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(at)10(dot)93(dot)156(dot)45:mavmail WARNING: pgstat wait timeout
> Mar 6 01:16:44 ardbc01 postgres[66365]: [12-2] mavmail(at)10(dot)93(dot)156(dot)45:mavmail STATEMENT: VACUUM VERBOSE ANALYZE update_queues;
> Mar 6 01:16:44 ardbc01 postgres[66365]: [13-1] mavmail(at)10(dot)93(dot)156(dot)45:mavmail INFO: vacuuming "public.update_queues"
> Mar 6 01:16:44 ardbc01 postgres[66365]: [13-2] mavmail(at)10(dot)93(dot)156(dot)45:mavmail STATEMENT: VACUUM VERBOSE ANALYZE update_queues;
> Mar 6 01:16:44 ardbc01 postgres[66365]: [14-1] mavmail(at)10(dot)93(dot)156(dot)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(at)10(dot)93(dot)156(dot)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(at)10(dot)93(dot)156(dot)45:mavmail STATEMENT: VACUUM VERBOSE ANALYZE update_queues;
> Mar 6 01:16:44 ardbc01 postgres[66365]: [15-1] mavmail(at)10(dot)93(dot)156(dot)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(at)10(dot)93(dot)156(dot)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(at)10(dot)93(dot)156(dot)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 <http://www.autorevenue.com/> | Autobase <http://www.autobase.net/>
> 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 <http://www.fb.com/DominionDealerSolutions>
> www.twitter.com/DominionDealer <http://www.twitter.com/DominionDealer>
> www.drivedominion.com <http://www.autorevenue.com/>
>
>
>
>
>