pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
statistics buffer is full
14+ messages / 8 participants
[nested] [flat]

* statistics buffer is full
@ 2004-02-08 22:39  Bjoern Metzdorf <bm@turtle-entertainment.de>
  0 siblings, 1 reply; 14+ messages in thread

From: Bjoern Metzdorf @ 2004-02-08 22:39 UTC (permalink / raw)
  To: pgsql-admin

Hi,

one of our heavy loaded postgresql servers regularly gives

LOG:  statistics buffer is full

Sometimes those messages come when there is a load peak on the server, 
so it comes to my mind that it may be possible to improve performance by 
tweaking postgresql.conf to get rid of that full buffers.

We run 7.4.1 on Linux 2.4 (Dual Xeon 2,4, 3 GB RAM). pg_autovacuum is 
running.

postgresql.conf (tweaked parameters only):

shared_buffers = 20000
sort_mem = 16384
fsync = false
wal_buffers = 256
checkpoint_segments = 6
log_timestamp = true
stats_start_collector = true
stats_row_level = true

Any hints?

Regards,
Bjoern




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: statistics buffer is full
@ 2004-02-09 03:23  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Bjoern Metzdorf <bm@turtle-entertainment.de>
  0 siblings, 0 replies; 14+ messages in thread

From: Tom Lane @ 2004-02-09 03:23 UTC (permalink / raw)
  To: Bjoern Metzdorf <bm@turtle-entertainment.de>; +Cc: pgsql-admin

Bjoern Metzdorf <bm@turtle-entertainment.de> writes:
> one of our heavy loaded postgresql servers regularly gives
> LOG:  statistics buffer is full

You could try increasing PGSTAT_RECVBUFFERSZ in include/pgstat.h
(this will require recompiling src/backend/postmaster/pgstat.c
to take effect).  I'm not convinced it will help; it may just allow
the stats collector to fall further behind.  But if your problem
is load spikes it might help.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* statistics buffer is full
@ 2004-12-08 21:15  Bjoern Metzdorf <bm@turtle-entertainment.de>
  0 siblings, 0 replies; 14+ messages in thread

From: Bjoern Metzdorf @ 2004-12-08 21:15 UTC (permalink / raw)
  To: pgsql-admin

Hi,

just wanted to share some experience with the curious log message: 
"statistics buffer is full".

Some time ago we suffered from bad performance on our database server. 
We were running 7.4.x. I saw this strange log message and Tom told me a 
way to raise this specific buffer. After raising it the box worked fine.

Now we are running 7.4.6 and I still have to patch the source, as I had 
to do with all 7.4.x versions.

See attached patch for details. We began raising from 1024 to 2048 then 
to 4096. After a while the messages appeared again and now we are at 
16384 and the messages have gone again.

Tom: Do you know of any other bad impact this raising might have?

All: If you suffer from bad performance, check your logs and raise this 
buffer.

Our specs:

Debian Woody, Kernel 2.6
Postgresql 7.4.6
Dual Xeon 3.2 with HT enabled
Mylex eXtremeRAID 2000 with 4 drives RAID 10

Regards,
Bjoern

Attachments:

pg_buffers_full-7.4.6.patch.txt (text/plain, 396B) download --- src/include/pgstat.h~ 2004-12-08 16:28:12.000000000 +0100 +++ src/include/pgstat.h 2004-12-08 16:28:12.000000000 +0100 @@ -65,7 +65,7 @@ * Amount of space reserved in pgstat_recvbuffer(). * ---------- */ -#define PGSTAT_RECVBUFFERSZ ((int) (1024 * sizeof(PgStat_Msg))) +#define PGSTAT_RECVBUFFERSZ ((int) (16384 * sizeof(PgStat_Msg))) /* ----------
^ permalink raw reply [nested|flat] 14+ messages in thread

* Statistics Buffer is full
@ 2005-05-06 13:11  Pallav Kalva <pkalva@deg.cc>
  0 siblings, 2 replies; 14+ messages in thread

From: Pallav Kalva @ 2005-05-06 13:11 UTC (permalink / raw)
  To: pgsql-admin

Hi Everybody,


    I got this logged in my log file last night on my production 
database while doing vacuumdb with analyze option , is this normal ? 
does it affect the system anyway ? is there anyway to get rid of these 
messages ?

2005-05-06 00:31:03 EDT%%% LOG:  statistics buffer is full
2005-05-06 00:31:15 EDT%%% LOG:  statistics buffer is full
2005-05-06 00:31:15 EDT%%% LOG:  statistics buffer is full
2005-05-06 00:31:15 EDT%%% LOG:  statistics buffer is full
2005-05-06 00:31:15 EDT%%% LOG:  statistics buffer is full


Thanks!
Pallav




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Statistics Buffer is full
@ 2005-05-06 14:03  Bricklen Anderson <BAnderson@PresiNET.com>
  parent: Pallav Kalva <pkalva@deg.cc>
  1 sibling, 0 replies; 14+ messages in thread

From: Bricklen Anderson @ 2005-05-06 14:03 UTC (permalink / raw)
  To: Pallav Kalva <pkalva@deg.cc>; +Cc: pgsql-admin

Pallav Kalva wrote:
> Hi Everybody,
> 
> 
>    I got this logged in my log file last night on my production database 
> while doing vacuumdb with analyze option , is this normal ? does it 
> affect the system anyway ? is there anyway to get rid of these messages ?
> 
> 2005-05-06 00:31:03 EDT%%% LOG:  statistics buffer is full
> 2005-05-06 00:31:15 EDT%%% LOG:  statistics buffer is full
> 2005-05-06 00:31:15 EDT%%% LOG:  statistics buffer is full
> 2005-05-06 00:31:15 EDT%%% LOG:  statistics buffer is full
> 2005-05-06 00:31:15 EDT%%% LOG:  statistics buffer is full
> 
> 
> Thanks!
> Pallav
> 
I frequently see the same message in my logs when doing a bulk load (6-8 million 
  rows). I'm interested in knowing what the message is as well.



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Statistics Buffer is full
@ 2005-05-06 16:38  Bjoern Metzdorf <bm@turtle-entertainment.de>
  parent: Pallav Kalva <pkalva@deg.cc>
  1 sibling, 0 replies; 14+ messages in thread

From: Bjoern Metzdorf @ 2005-05-06 16:38 UTC (permalink / raw)
  To: Pallav Kalva <pkalva@deg.cc>; +Cc: pgsql-admin

this applies to 7.x only:

raise PGSTAT_RECVBUFFERSZ in /src/include/pgstat.h (i.e. raise 1024 to 
16384)

Performance drops will go away then (at least in my case here).

Regards
Bjoern


Pallav Kalva wrote:
> Hi Everybody,
> 
> 
>    I got this logged in my log file last night on my production database 
> while doing vacuumdb with analyze option , is this normal ? does it 
> affect the system anyway ? is there anyway to get rid of these messages ?
> 
> 2005-05-06 00:31:03 EDT%%% LOG:  statistics buffer is full
> 2005-05-06 00:31:15 EDT%%% LOG:  statistics buffer is full
> 2005-05-06 00:31:15 EDT%%% LOG:  statistics buffer is full
> 2005-05-06 00:31:15 EDT%%% LOG:  statistics buffer is full
> 2005-05-06 00:31:15 EDT%%% LOG:  statistics buffer is full
> 
> 
> Thanks!
> Pallav
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* statistics buffer is full
@ 2006-11-09 02:46  Jeff Frost <jeff@frostconsultingllc.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Jeff Frost @ 2006-11-09 02:46 UTC (permalink / raw)
  To: pgsql-admin

What does the message 'statistics buffer is full' mean?  And if I saw lots of 
these in my logs what sort of bad things would happen?  Presumably this is 
related to the planner statistics?  Would I expect to have bad plans if this 
happens and how would I resolve it?  This is what I found in the source, but 
I'm not really sure what's going on here:

Part of:

pgstat_recvbuffer(void)
<snip>
                 /*
                  * As long as we have buffer space we add the socket to the read
                  * descriptor set.
                  */
                 if (msg_have <= (int) (PGSTAT_RECVBUFFERSZ - sizeof(PgStat_Msg))
)
                 {
                         FD_SET(pgStatSock, &rfds);
                         maxfd = pgStatSock;
                         overflow = false;
                 }
                 else
                 {
                         if (!overflow)
                         {
                                 ereport(LOG,
                                                 (errmsg("statistics buffer is full")));
                                 overflow = true;
                         }
                 }


-- 
Jeff Frost, Owner 	<jeff@frostconsultingllc.com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 650-780-7908	FAX: 650-649-1954



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: statistics buffer is full
@ 2006-11-09 03:27  Joshua D. Drake <jd@commandprompt.com>
  parent: Jeff Frost <jeff@frostconsultingllc.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Joshua D. Drake @ 2006-11-09 03:27 UTC (permalink / raw)
  To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-admin

On Wed, 2006-11-08 at 18:46 -0800, Jeff Frost wrote:
> What does the message 'statistics buffer is full' mean?  And if I saw lots of 
> these in my logs what sort of bad things would happen?  Presumably this is 
> related to the planner statistics?  Would I expect to have bad plans if this 
> happens and how would I resolve it?  This is what I found in the source, but 
> I'm not really sure what's going on here:

Usually it means that you have stats_command_string turned on :)

> 
> Part of:
> 
> pgstat_recvbuffer(void)
> <snip>
>                  /*
>                   * As long as we have buffer space we add the socket to the read
>                   * descriptor set.
>                   */
>                  if (msg_have <= (int) (PGSTAT_RECVBUFFERSZ - sizeof(PgStat_Msg))
> )
>                  {
>                          FD_SET(pgStatSock, &rfds);
>                          maxfd = pgStatSock;
>                          overflow = false;
>                  }
>                  else
>                  {
>                          if (!overflow)
>                          {
>                                  ereport(LOG,
>                                                  (errmsg("statistics buffer is full")));
>                                  overflow = true;
>                          }
>                  }
> 
> 
-- 

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate






^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: statistics buffer is full
@ 2006-11-09 03:29  Jeff Frost <jeff@frostconsultingllc.com>
  parent: Joshua D. Drake <jd@commandprompt.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Jeff Frost @ 2006-11-09 03:29 UTC (permalink / raw)
  To: Joshua D. Drake <jd@commandprompt.com>; +Cc: pgsql-admin

On Wed, 8 Nov 2006, Joshua D. Drake wrote:

> On Wed, 2006-11-08 at 18:46 -0800, Jeff Frost wrote:
>> What does the message 'statistics buffer is full' mean?  And if I saw lots of
>> these in my logs what sort of bad things would happen?  Presumably this is
>> related to the planner statistics?  Would I expect to have bad plans if this
>> happens and how would I resolve it?  This is what I found in the source, but
>> I'm not really sure what's going on here:
>
> Usually it means that you have stats_command_string turned on :)

hah!  Does that mean it's just the buffering for pg_stat_activity and not for 
planner statistics?  That doesn't make me feel too bad in this case.


-- 
Jeff Frost, Owner 	<jeff@frostconsultingllc.com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 650-780-7908	FAX: 650-649-1954



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: statistics buffer is full
@ 2006-11-09 03:43  Joshua D. Drake <jd@commandprompt.com>
  parent: Jeff Frost <jeff@frostconsultingllc.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Joshua D. Drake @ 2006-11-09 03:43 UTC (permalink / raw)
  To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-admin

On Wed, 2006-11-08 at 19:29 -0800, Jeff Frost wrote:
> On Wed, 8 Nov 2006, Joshua D. Drake wrote:
> 
> > On Wed, 2006-11-08 at 18:46 -0800, Jeff Frost wrote:
> >> What does the message 'statistics buffer is full' mean?  And if I saw lots of
> >> these in my logs what sort of bad things would happen?  Presumably this is
> >> related to the planner statistics?  Would I expect to have bad plans if this
> >> happens and how would I resolve it?  This is what I found in the source, but
> >> I'm not really sure what's going on here:
> >
> > Usually it means that you have stats_command_string turned on :)
> 
> hah!  Does that mean it's just the buffering for pg_stat_activity and not for 
> planner statistics?  That doesn't make me feel too bad in this case.

Yeah it is nothing to worry about, however turning it off is certainly a
performance benefit.

Joshua D. Drake


> 
> 
-- 

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate






^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: statistics buffer is full
@ 2006-11-09 05:46  Jeff Frost <jeff@frostconsultingllc.com>
  parent: Joshua D. Drake <jd@commandprompt.com>
  0 siblings, 3 replies; 14+ messages in thread

From: Jeff Frost @ 2006-11-09 05:46 UTC (permalink / raw)
  To: Joshua D. Drake <jd@commandprompt.com>; +Cc: pgsql-admin

On Wed, 8 Nov 2006, Joshua D. Drake wrote:

>> hah!  Does that mean it's just the buffering for pg_stat_activity and not for
>> planner statistics?  That doesn't make me feel too bad in this case.
>
> Yeah it is nothing to worry about, however turning it off is certainly a
> performance benefit.
>

So, then the next question: is that error because the command string was too 
long or because there were too many command strings in the buffer at once 
(i.e. it got backed up a bit and filled)?

It would be a drag to be without pg_stat_activity on this system.  The 
performance detriment is lessened in 8.2, correct?

-- 
Jeff Frost, Owner 	<jeff@frostconsultingllc.com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 650-780-7908	FAX: 650-649-1954



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: statistics buffer is full
@ 2006-11-09 06:59  Joshua D. Drake <jd@commandprompt.com>
  parent: Jeff Frost <jeff@frostconsultingllc.com>
  2 siblings, 0 replies; 14+ messages in thread

From: Joshua D. Drake @ 2006-11-09 06:59 UTC (permalink / raw)
  To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-admin

On Wed, 2006-11-08 at 21:46 -0800, Jeff Frost wrote:
> On Wed, 8 Nov 2006, Joshua D. Drake wrote:
> 
> >> hah!  Does that mean it's just the buffering for pg_stat_activity and not for
> >> planner statistics?  That doesn't make me feel too bad in this case.
> >
> > Yeah it is nothing to worry about, however turning it off is certainly a
> > performance benefit.
> >
> 
> So, then the next question: is that error because the command string was too 
> long or because there were too many command strings in the buffer at once 
> (i.e. it got backed up a bit and filled)?

This one I can't answer.

> 
> It would be a drag to be without pg_stat_activity on this system.  The 
> performance detriment is lessened in 8.2, correct?

Yes my understanding is work was done on 8.2 to help eliviate the issue.

Sincerely,

Joshua D. Drake

> 
-- 

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate






^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: statistics buffer is full
@ 2006-11-09 07:21  Jim C. Nasby <jim@nasby.net>
  parent: Jeff Frost <jeff@frostconsultingllc.com>
  2 siblings, 0 replies; 14+ messages in thread

From: Jim C. Nasby @ 2006-11-09 07:21 UTC (permalink / raw)
  To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-admin

On Wed, Nov 08, 2006 at 09:46:48PM -0800, Jeff Frost wrote:
> On Wed, 8 Nov 2006, Joshua D. Drake wrote:
> 
> >>hah!  Does that mean it's just the buffering for pg_stat_activity and not 
> >>for
> >>planner statistics?  That doesn't make me feel too bad in this case.
> >
> >Yeah it is nothing to worry about, however turning it off is certainly a
> >performance benefit.
> >
> 
> So, then the next question: is that error because the command string was 
> too long or because there were too many command strings in the buffer at 
> once (i.e. it got backed up a bit and filled)?

IIRC, (in 8.1) the same amount of data is shuffled through the stats
system for every command, so it'd be an issue of too many messages.

> It would be a drag to be without pg_stat_activity on this system.  The 
> performance detriment is lessened in 8.2, correct?

Correct. Performance of updating the info for ps was also improved,
iirc.
-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: statistics buffer is full
@ 2006-11-09 12:14  Alvaro Herrera <alvherre@commandprompt.com>
  parent: Jeff Frost <jeff@frostconsultingllc.com>
  2 siblings, 0 replies; 14+ messages in thread

From: Alvaro Herrera @ 2006-11-09 12:14 UTC (permalink / raw)
  To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-admin

Jeff Frost wrote:
> On Wed, 8 Nov 2006, Joshua D. Drake wrote:
> 
> >>hah!  Does that mean it's just the buffering for pg_stat_activity and not 
> >>for
> >>planner statistics?  That doesn't make me feel too bad in this case.
> >
> >Yeah it is nothing to worry about, however turning it off is certainly a
> >performance benefit.
> >
> 
> So, then the next question: is that error because the command string was 
> too long or because there were too many command strings in the buffer at 
> once (i.e. it got backed up a bit and filled)?

Yeah, it got backed up.  It's an UDP socket and it's having trouble
keeping up.  Disabling stats_command_string removes the higher producer
of "statistics" traffic, and the price you pay for it is that queries
don't show up in pg_stat_activity.  You can still see the tags in ps
though, and of course you'll have them in the log if you enabled that.

> It would be a drag to be without pg_stat_activity on this system.  The 
> performance detriment is lessened in 8.2, correct?

Yes, by not using the UDP socket to transmit the command string in the
first place.  They are now just stored in memory, which is considerably
faster.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.



^ permalink  raw  reply  [nested|flat] 14+ messages in thread


end of thread, other threads:[~2006-11-09 12:14 UTC | newest]

Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-08 22:39 statistics buffer is full Bjoern Metzdorf <bm@turtle-entertainment.de>
2004-02-09 03:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-12-08 21:15 statistics buffer is full Bjoern Metzdorf <bm@turtle-entertainment.de>
2005-05-06 13:11 Statistics Buffer is full Pallav Kalva <pkalva@deg.cc>
2005-05-06 14:03 ` Re: Statistics Buffer is full Bricklen Anderson <BAnderson@PresiNET.com>
2005-05-06 16:38 ` Re: Statistics Buffer is full Bjoern Metzdorf <bm@turtle-entertainment.de>
2006-11-09 02:46 statistics buffer is full Jeff Frost <jeff@frostconsultingllc.com>
2006-11-09 03:27 ` Joshua D. Drake <jd@commandprompt.com>
2006-11-09 03:29   ` Jeff Frost <jeff@frostconsultingllc.com>
2006-11-09 03:43     ` Joshua D. Drake <jd@commandprompt.com>
2006-11-09 05:46       ` Jeff Frost <jeff@frostconsultingllc.com>
2006-11-09 06:59         ` Joshua D. Drake <jd@commandprompt.com>
2006-11-09 07:21         ` Jim C. Nasby <jim@nasby.net>
2006-11-09 12:14         ` Alvaro Herrera <alvherre@commandprompt.com>

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