pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWrite-heavy pg_stats_collector on mostly idle server
7+ messages / 4 participants
[nested] [flat]
* Write-heavy pg_stats_collector on mostly idle server
@ 2011-02-04 19:05 Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 7+ messages in thread
From: Josh Berkus @ 2011-02-04 19:05 UTC (permalink / raw)
To: pgsql-performance
All,
Seeing an issue which is new on me. On a mostly idle PostgreSQL server,
the stats collector is rewriting the entire stats file twice per second.
Version: 8.4.4
Server: Ubuntu, kernel 2.6.32
Server set up: ApacheMQ server. 25 databases, each of which hold 2-3
tables.
Filesystem: Ext4, defaults
Active connections: around 15
Autovacuum settings: defaults
Symptoms: on a server which gets around 20 reads and 15 writes per
minute, we are seeing average 500K/second writes by the stats collector
to pg_stat.tmp. pg_stat.tmp is around 270K.
An strace of the stats collector process shows that the stats collector
is, in fact, rewriting the entire stats file twice per second.
Anyone seen anything like this before?
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Write-heavy pg_stats_collector on mostly idle server
@ 2011-02-05 22:15 Cédric Villemain <cedric.villemain.debian@gmail.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 7+ messages in thread
From: Cédric Villemain @ 2011-02-05 22:15 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
2011/2/4 Josh Berkus <josh@agliodbs.com>:
> All,
>
> Seeing an issue which is new on me. On a mostly idle PostgreSQL server,
> the stats collector is rewriting the entire stats file twice per second.
>
> Version: 8.4.4
> Server: Ubuntu, kernel 2.6.32
> Server set up: ApacheMQ server. 25 databases, each of which hold 2-3
> tables.
> Filesystem: Ext4, defaults
> Active connections: around 15
> Autovacuum settings: defaults
>
> Symptoms: on a server which gets around 20 reads and 15 writes per
> minute, we are seeing average 500K/second writes by the stats collector
> to pg_stat.tmp. pg_stat.tmp is around 270K.
>
> An strace of the stats collector process shows that the stats collector
> is, in fact, rewriting the entire stats file twice per second.
>
> Anyone seen anything like this before?
>
it is the expected behavior, IIRC
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Write-heavy pg_stats_collector on mostly idle server
@ 2011-02-07 22:58 Josh Berkus <josh@agliodbs.com>
parent: Cédric Villemain <cedric.villemain.debian@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Josh Berkus @ 2011-02-07 22:58 UTC (permalink / raw)
To: Cédric Villemain <cedric.villemain.debian@gmail.com>; +Cc: pgsql-performance
>> Anyone seen anything like this before?
>>
>
> it is the expected behavior, IIRC
OK. It just seems kind of pathological for stats file writing to be 10X
the volume of data writing. I see why it's happening, but I think it's
something we should fix.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Write-heavy pg_stats_collector on mostly idle server
@ 2011-02-07 23:12 Joshua D. Drake <jd@commandprompt.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 7+ messages in thread
From: Joshua D. Drake @ 2011-02-07 23:12 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Cédric Villemain <cedric.villemain.debian@gmail.com>; pgsql-performance
On Mon, 2011-02-07 at 14:58 -0800, Josh Berkus wrote:
> >> Anyone seen anything like this before?
> >>
> >
> > it is the expected behavior, IIRC
>
> OK. It just seems kind of pathological for stats file writing to be 10X
> the volume of data writing. I see why it's happening, but I think it's
> something we should fix.
I don't think it is expected. As I recall, it is something we fixed a
couple of major versions back (8.2?). It used to be that stats would
write every 500ms. We changed that to when they are asked for (via a
select from the table or something). Specifically because it could cause
this type of problem.
Am I thinking of something else?
I remember going back and forth with tgl about this, tgl?
JD
>
> --
> -- Josh Berkus
> PostgreSQL Experts Inc.
> http://www.pgexperts.com
>
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Write-heavy pg_stats_collector on mostly idle server
@ 2011-02-07 23:24 Cédric Villemain <cedric.villemain.debian@gmail.com>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 7+ messages in thread
From: Cédric Villemain @ 2011-02-07 23:24 UTC (permalink / raw)
To: jd@commandprompt.com; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
2011/2/8 Joshua D. Drake <jd@commandprompt.com>:
> On Mon, 2011-02-07 at 14:58 -0800, Josh Berkus wrote:
>> >> Anyone seen anything like this before?
>> >>
>> >
>> > it is the expected behavior, IIRC
>>
>> OK. It just seems kind of pathological for stats file writing to be 10X
>> the volume of data writing. I see why it's happening, but I think it's
>> something we should fix.
>
> I don't think it is expected. As I recall, it is something we fixed a
> couple of major versions back (8.2?). It used to be that stats would
> write every 500ms. We changed that to when they are asked for (via a
> select from the table or something). Specifically because it could cause
> this type of problem.
>
> Am I thinking of something else?
>
> I remember going back and forth with tgl about this, tgl?
Ooops.
It looks like you are right, see ./src/backend/postmaster/pgstat.c
3c2313f4 (Tom Lane 2008-11-03 01:17:08 +0000 2926)
if (last_statwrite < last_statrequest)
70d75697 (Magnus Hagander 2008-08-05 12:09:30 +0000 2927)
pgstat_write_statsfile(false);
>
> JD
>
>>
>> --
>> -- Josh Berkus
>> PostgreSQL Experts Inc.
>> http://www.pgexperts.com
>>
>
> --
> PostgreSQL.org Major Contributor
> Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
> Consulting, Training, Support, Custom Development, Engineering
> http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
>
>
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Write-heavy pg_stats_collector on mostly idle server
@ 2011-02-08 01:39 Josh Berkus <josh@agliodbs.com>
parent: Cédric Villemain <cedric.villemain.debian@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Josh Berkus @ 2011-02-08 01:39 UTC (permalink / raw)
To: Cédric Villemain <cedric.villemain.debian@gmail.com>; +Cc: jd@commandprompt.com; pgsql-performance
> Ooops.
> It looks like you are right, see ./src/backend/postmaster/pgstat.c
>
> 3c2313f4 (Tom Lane 2008-11-03 01:17:08 +0000 2926)
> if (last_statwrite < last_statrequest)
> 70d75697 (Magnus Hagander 2008-08-05 12:09:30 +0000 2927)
> pgstat_write_statsfile(false);
This is a different issue. This is happening because we have a bunch of
databases (25 to 35) and as a result autovacuum is requesting the stats
file rather frequently. And autovacuum for whatever reason won't accept
a stats file more than 10ms old, so it pretty much rewrites the stats
file on every request.
At least, that's my reading of it after poking around and talking to
Gierth.
It seems like that 10ms window for autovac is way too small.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Write-heavy pg_stats_collector on mostly idle server
@ 2011-02-08 08:41 Jakub Ouhrabka <kuba@comgate.cz>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 7+ messages in thread
From: Jakub Ouhrabka @ 2011-02-08 08:41 UTC (permalink / raw)
To: pgsql-performance; josh@agliodbs.com
Hi Josh,
it's "known" issue, see this thread:
http://archives.postgresql.org/pgsql-hackers/2010-02/thrd6.php#01290
HTH,
Kuba
Dne 8.2.2011 2:39, Josh Berkus napsal(a):
>
>> Ooops.
>> It looks like you are right, see ./src/backend/postmaster/pgstat.c
>>
>> 3c2313f4 (Tom Lane 2008-11-03 01:17:08 +0000 2926)
>> if (last_statwrite< last_statrequest)
>> 70d75697 (Magnus Hagander 2008-08-05 12:09:30 +0000 2927)
>> pgstat_write_statsfile(false);
>
> This is a different issue. This is happening because we have a bunch of
> databases (25 to 35) and as a result autovacuum is requesting the stats
> file rather frequently. And autovacuum for whatever reason won't accept
> a stats file more than 10ms old, so it pretty much rewrites the stats
> file on every request.
>
> At least, that's my reading of it after poking around and talking to
> Gierth.
>
> It seems like that 10ms window for autovac is way too small.
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2011-02-08 08:41 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-02-04 19:05 Write-heavy pg_stats_collector on mostly idle server Josh Berkus <josh@agliodbs.com>
2011-02-05 22:15 ` Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-02-07 22:58 ` Josh Berkus <josh@agliodbs.com>
2011-02-07 23:12 ` Joshua D. Drake <jd@commandprompt.com>
2011-02-07 23:24 ` Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-02-08 01:39 ` Josh Berkus <josh@agliodbs.com>
2011-02-08 08:41 ` Jakub Ouhrabka <kuba@comgate.cz>
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