pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedstatistics 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