Re: Moving pgstat.stat and pgstat.tmp - Mailing list pgsql-general

On Dec 5, 2007, at 7:50 AM, Robert Treat wrote:

> On Wednesday 05 December 2007 07:22, Alvaro Herrera wrote:
>> Robert Treat wrote:
>>> On Monday 03 December 2007 20:22, Erik Jones wrote:
>>>> Interesting.  If this is anything you'd like to look into I can
>>>> provide whatever diagnostic output you need (iostat, vmstat, dtrace
>>>> script outputs, etc...) but I do have to reiterate that we are an
>>>> extreme corner case due to out schema size.  For now, is
>>>> renaming the
>>>> #define'd paths for the stats file and temp file sufficient for
>>>> moving them?  Basically, we'd like to move them onto a RAM  disk to
>>>> give our disks a break.
>>>
>>> Yeah, we've noticed the same problem (pgstat is the most active
>>> file on
>>> the system... uncovered in much the same way... go solaris).
>>> Actually I
>>> was wondering if it could be done with symlinks, a la moving xlogs.
>>
>> Not really, because a new file is created and renamed in place
>> each time
>> it's going to be rewritten.  So the symlink would be lost in the
>> first
>> file rewrite.
>>
>
> Ah yeah, thats what I concluded back then.
>
>> The first idea that comes to mind is to make the path configurable
>> via
>> GUC, so the user could set it to be written to an in-memory
>> filesystem
>> (/tmp in Solaris?).
>
> Yep, thought of that to, though it was after feature freeze so I
> didn't
> propose it. Course if someone wants to sneak that in it would be
> cool :-)
>
>> But then I thought, why do we need it to be a file
>> at all?  Why not use a mmap'ed memory area or something like that,
>> and
>> only write it to a file on postmaster shutdown?  (Losing the file on
>> unclean shutdown is not a problem, because the file is removed
>> anyway.)
>
> I suppose you need some facility to spill to disk, so maybe being
> in a file is
> better? Seems it might not be in most cases... I wonder how big a
> memory
> space we (or Erik) need.

What I've done and tested on our test db server is to change lines 65
& 66 in pg_stat.c from

#define PGSTAT_STAT_FILENAME    "global/pgstat.stat"
#define PGSTAT_STAT_TMPFILE     "global/pgstat.tmp"

to

#define PGSTAT_STAT_FILENAME    "global/pg_stats/pgstat.stat"
#define PGSTAT_STAT_TMPFILE     "global/pg_stats/pgstat.tmp"

recompile and then create that pg_stats directory as a symlink to a
directory with a swapfs mounted in it.  Everything seems to be
kosher.  Of course, this adds a bit to our shutdown procedure in the
case where we're going to bounce the actual server in that we need to
make sure to copy the stats file(s) out of the swapfs directory in
order to preserve stats in that case (and back in afterwards, of
course).

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com