From: John Scalia <jayknowsunix@gmail.com>
To: Mark Steben <mark.steben@drivedominion.com>
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: pgstat wait timeout encountered
Date: Mon, 09 Mar 2015 16:21:50 -0400
Message-ID: <54FE00DE.50205@gmail.com> (raw)
In-Reply-To: <CADyzmywG4YMRi9XLsXYQz=XjLYWRN_tzntL6Ly6s0cgdvcci9A@mail.gmail.com>
References: <CADyzmywG4YMRi9XLsXYQz=XjLYWRN_tzntL6Ly6s0cgdvcci9A@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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 <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/;
>
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jayknowsunix@gmail.com, mark.steben@drivedominion.com
Subject: Re: pgstat wait timeout encountered
In-Reply-To: <54FE00DE.50205@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox