Thread: 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@fuzzy.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@fuzzy.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.chirania@hp.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.chirania@hp.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.