Thread: Write-heavy pg_stats_collector on mostly idle server
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
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
>> 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
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
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
> 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
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. >