Re: pgstat wait timeout

Hi,

I am having a scenario where I get consistent warnings in the pglog folder:

2011-12-11 00:00:03 JST WARNING: pgstat wait timeout
2011-12-11 00:00:14 JST WARNING: pgstat wait timeout
2011-12-11 00:00:24 JST WARNING: pgstat wait timeout
2011-12-11 00:00:31 JST WARNING: pgstat wait timeout
2011-12-11 00:00:44 JST WARNING: pgstat wait timeout
2011-12-11 00:00:52 JST WARNING: pgstat wait timeout
2011-12-11 00:01:03 JST WARNING: pgstat wait timeout
2011-12-11 00:01:11 JST WARNING: pgstat wait timeout
.
.
.

This is impacting database performance.

The issue persists even when I use the database minimally.

I have tried fine-tuning the Auto-vacuum parameters:

Change the parameter autovacuum_vacuum_cost_delay to 40ms ::: Issue is
reproduced 4 hours after this change

Change the parameter autovacuum_max_workers to 20 ::: I got the warning
message 2 times at times 17:20:12 and 17:20:20. After this, no warnings for
5 hours. Then I tried:

Change the parameter autovacuum_vacuum_cost_delay to: 60ms, Change the
parameter autovacuum_max_workers to: 10::: I got the warning message 2 times
at times 17:20:12 and 17:20:20. After this, no warnings for 5 hours

Any Ideas?

--
View this message in context: http://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-tp5078125p5078125.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On 15 Prosinec 2011, 17:55, pratikchirania wrote:
> Hi,
>
> I am having a scenario where I get consistent warnings in the pglog
> folder:
>
> 2011-12-11 00:00:03 JST WARNING: pgstat wait timeout
> 2011-12-11 00:00:14 JST WARNING: pgstat wait timeout
> 2011-12-11 00:00:24 JST WARNING: pgstat wait timeout
> 2011-12-11 00:00:31 JST WARNING: pgstat wait timeout
> 2011-12-11 00:00:44 JST WARNING: pgstat wait timeout
> 2011-12-11 00:00:52 JST WARNING: pgstat wait timeout
> 2011-12-11 00:01:03 JST WARNING: pgstat wait timeout
> 2011-12-11 00:01:11 JST WARNING: pgstat wait timeout
>
> This is impacting database performance.

It's rather a sign that the I/O is overloaded, although in some cases it
may actually be the cause.

> The issue persists even when I use the database minimally.

Yes, because the file is written periodically - twice a second IIRC. If
the file is large, this may be an issue. What is the pgstat.stat size
(should be in data/global).

> I have tried fine-tuning the Auto-vacuum parameters:

Autovacuum has nothing to do with this.

> Any Ideas?

Move the file to a RAM drive - there's even a config parameter
'stats_temp_directory' to do that. See
http://www.postgresql.org/docs/9.1/static/runtime-config-statistics.html

Tomas

On Thu, Dec 15, 2011 at 18:13, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
> On 15 Prosinec 2011, 17:55, pratikchirania wrote:
>> Hi,
>>
>> I am having a scenario where I get consistent warnings in the pglog
>> folder:
>>
>> 2011-12-11 00:00:03 JST WARNING:  pgstat wait timeout
>> 2011-12-11 00:00:14 JST WARNING:  pgstat wait timeout
>> 2011-12-11 00:00:24 JST WARNING:  pgstat wait timeout
>> 2011-12-11 00:00:31 JST WARNING:  pgstat wait timeout
>> 2011-12-11 00:00:44 JST WARNING:  pgstat wait timeout
>> 2011-12-11 00:00:52 JST WARNING:  pgstat wait timeout
>> 2011-12-11 00:01:03 JST WARNING:  pgstat wait timeout
>> 2011-12-11 00:01:11 JST WARNING:  pgstat wait timeout
>>
>> This is impacting database performance.
>
> It's rather a sign that the I/O is overloaded, although in some cases it
> may actually be the cause.
>
>> The issue persists even when I use the database minimally.
>
> Yes, because the file is written periodically - twice a second IIRC. If
> the file is large, this may be an issue. What is the pgstat.stat size
> (should be in data/global).

That was only true prior to 8.4. As of 8.4 it's only written when
necessary, which is usually a lot less than twice / second.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On 15 Prosinec 2011, 18:19, Magnus Hagander wrote:
> On Thu, Dec 15, 2011 at 18:13, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
>> On 15 Prosinec 2011, 17:55, pratikchirania wrote:
>>> Hi,
>>>
>>> I am having a scenario where I get consistent warnings in the pglog
>>> folder:
>>>
>>> 2011-12-11 00:00:03 JST WARNING:  pgstat wait timeout
>>> 2011-12-11 00:00:14 JST WARNING:  pgstat wait timeout
>>> 2011-12-11 00:00:24 JST WARNING:  pgstat wait timeout
>>> 2011-12-11 00:00:31 JST WARNING:  pgstat wait timeout
>>> 2011-12-11 00:00:44 JST WARNING:  pgstat wait timeout
>>> 2011-12-11 00:00:52 JST WARNING:  pgstat wait timeout
>>> 2011-12-11 00:01:03 JST WARNING:  pgstat wait timeout
>>> 2011-12-11 00:01:11 JST WARNING:  pgstat wait timeout
>>>
>>> This is impacting database performance.
>>
>> It's rather a sign that the I/O is overloaded, although in some cases it
>> may actually be the cause.
>>
>>> The issue persists even when I use the database minimally.
>>
>> Yes, because the file is written periodically - twice a second IIRC. If
>> the file is large, this may be an issue. What is the pgstat.stat size
>> (should be in data/global).
>
> That was only true prior to 8.4. As of 8.4 it's only written when
> necessary, which is usually a lot less than twice / second.

Thanks for the correction. Nevertheless, it would be useful to know what
is the size of the file and what is the I/O usage.

Pratik, can you post the size of the pgstat.stat file and post a few lines
of "iostat -x 1" collected when the "pgstat wait timeout" happens?

Tomas

Size of pgstat.stat file: 86KB

I did not understand the second part. Where do I get "iostat -x 1" message?
(Its not present in any file in the pg_log folder)

I am using postgres 9.0.1

--
View this message in context: http://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-tp5078125p5078391.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On 15 Prosinec 2011, 19:42, pratikchirania wrote:
> Size of pgstat.stat file: 86KB

That's pretty small.

> I did not understand the second part. Where do I get "iostat -x 1"
> message?
> (Its not present in any file in the pg_log folder)

iostat is not part of PostgreSQL, it's a tool used to display various I/O
metrics in Linux (and Unix in general). What OS are you using?

It seems the I/O subsystem is so busy it can't write the pgstat.stat on
time, so a warning is printed. You need to find out why the I/O is so
overutilized.

> I am using postgres 9.0.1

That's way too old. Upgrade to 9.0.6.

Tomas

OS: I am using Windows server 2003

Version upgrade: hmm.. Is there any fix/change related to this issue in
9.0.6?
If yes, I will upgrade in next scheduled downtime (I am using this as
production server)...

postgres queries are very occasionly used (a set of calls once in 30
minutes).. so I guess I am not calling my DB component heavily.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-tp5078125p5086379.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On Mon, Dec 19, 2011 at 10:02 AM, pratikchirania <pratik(dot)chirania(at)hp(dot)com> wrote:
> Version upgrade: hmm.. Is there any fix/change related to this issue in
> 9.0.6?

You could read the release notes for those minor version upgrades.

Based on a quick look through the commit logs, and a quick grep of
release-9-0.sgml, I don't think so.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 12/19/2011 11:45 AM, Robert Haas wrote:
> On Mon, Dec 19, 2011 at 10:02 AM, pratikchirania<pratik(dot)chirania(at)hp(dot)com> wrote:
>> Version upgrade: hmm.. Is there any fix/change related to this issue in
>> 9.0.6?
> You could read the release notes for those minor version upgrades.
>
> Based on a quick look through the commit logs, and a quick grep of
> release-9-0.sgml, I don't think so.

Would this be alleviated by setting stats_temp_dir to point to a ramdisk?

cheers

andrew

>Would this be alleviated by setting stats_temp_dir to point to a ramdisk?

I am not aware how to do this. I am using a windows server OS.
The conf file has the entry : #stats_temp_directory = 'pg_stat_tmp'

What do I change it to? Please elucidate.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-tp5078125p5088497.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On 12/20/2011 05:13 AM, pratikchirania wrote:
>> Would this be alleviated by setting stats_temp_dir to point to a ramdisk?
> I am not aware how to do this. I am using a windows server OS.
> The conf file has the entry : #stats_temp_directory = 'pg_stat_tmp'
>
> What do I change it to? Please elucidate.
>

On Windows it appears you need third party software for a ramdisk.
Search google for info.

cheers

andrew

I have installed RAMdisk and pointed the parameter:

#stats_temp_directory = 'B:\pg_stat_tmp'
I also tried #stats_temp_directory = 'B:/pg_stat_tmp'

But, still there is no file created in the RAM disk.
The previous stat file is touched even after the change is made. (I have
restarted the service after effecting the change)

--
View this message in context: http://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-tp5078125p5119436.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On 4 Leden 2012, 13:17, pratikchirania wrote:
> I have installed RAMdisk and pointed the parameter:
>
> #stats_temp_directory = 'B:\pg_stat_tmp'
> I also tried #stats_temp_directory = 'B:/pg_stat_tmp'
>
> But, still there is no file created in the RAM disk.
> The previous stat file is touched even after the change is made. (I have
> restarted the service after effecting the change)

You have to remove the '#' at the beginning, this way it's commented out.

Tomas

Thanks, i missed that.

After doing these changes, following is the observation:

1. The size of the pgstat file is 86KB. Last edited was when i moved the
file location to RAMdisk.
2. The issue persists. I am seeing continuous logs:

2012-01-05 00:00:06 JST WARNING: pgstat wait timeout
2012-01-05 00:00:14 JST WARNING: pgstat wait timeout
2012-01-05 00:00:26 JST WARNING: pgstat wait timeout
.
.
.
2012-01-05 15:36:25 JST WARNING: pgstat wait timeout
2012-01-05 15:36:37 JST WARNING: pgstat wait timeout
2012-01-05 15:36:45 JST WARNING: pgstat wait timeout

--
View this message in context: http://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-tp5078125p5121894.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Hi,

Any ideas on this?

--
View this message in context: http://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-tp5078125p5165651.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Hi,

I Disabled autovacuuming and the warnings stopped logging.
After enabling Autovacuuming, the warnings again started logging.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-tp5078125p5444033.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.