Re: Location for pgstat.stat

Per this thread:
http://archives.postgresql.org/pgsql-general/2007-12/msg00255.php

it was pretty much (again, IIRC) concluded that we want "some better
way" to transfer the stats data.

But pending that we have that, how about we just move it into it's own
subdirectory? AFAICS that would be a simple change of two #defines
moving it from "global/pgstat.stat" to "pgstat/pgstat.stat" or something
like that. Might also need some code to create the directory if it
doesn't exist, but that shouldn't be hard.

This would make it possible to symlink or mount that directory off to a
ramdrive (for example).

It's not a perfect solution, but it would at least give a better tool
than we have today, no?

//Magnus

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> But pending that we have that, how about we just move it into it's own
> subdirectory?
> This would make it possible to symlink or mount that directory off to a
> ramdrive (for example).

Hmm ... that would almost certainly result in the stats being lost over
a system shutdown. How much do we care?

regards, tom lane

Tom Lane wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> But pending that we have that, how about we just move it into it's own
>> subdirectory?
>> This would make it possible to symlink or mount that directory off to a
>> ramdrive (for example).
>
> Hmm ... that would almost certainly result in the stats being lost over
> a system shutdown. How much do we care?

Only for those who put it on a ramdrive. The default, unless you
move/sync it off, would still be the same as it is today. While not
perfect, the performance difference of going to a ramdrive might easily
be enough to offset that in some cases, I think.

//Magnus

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> Tom Lane wrote:
>> Hmm ... that would almost certainly result in the stats being lost over
>> a system shutdown. How much do we care?

> Only for those who put it on a ramdrive. The default, unless you
> move/sync it off, would still be the same as it is today. While not
> perfect, the performance difference of going to a ramdrive might easily
> be enough to offset that in some cases, I think.

Well, what I was wondering about is whether it'd be worth adding logic
to copy the file to/from a "safer" location at startup/shutdown.

regards, tom lane

Tom Lane wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> Tom Lane wrote:
>>> Hmm ... that would almost certainly result in the stats being lost over
>>> a system shutdown. How much do we care?
>
>> Only for those who put it on a ramdrive. The default, unless you
>> move/sync it off, would still be the same as it is today. While not
>> perfect, the performance difference of going to a ramdrive might easily
>> be enough to offset that in some cases, I think.
>
> Well, what I was wondering about is whether it'd be worth adding logic
> to copy the file to/from a "safer" location at startup/shutdown.

Oh, I see. I should think more before I answer sometimes :-)

Not sure. I guess my own personal concern would be how badly is
autovacuum affected by having to start off a blank set of stats? Any
other uses I have I think are capable of dealing with reset-to-zero states.

//Magnus

Magnus Hagander wrote:

> Not sure. I guess my own personal concern would be how badly is
> autovacuum affected by having to start off a blank set of stats? Any
> other uses I have I think are capable of dealing with reset-to-zero states.

Well, it doesn't :-) No database or table will be processed until stat
entries are created, and then I think it will first wait until enough
activity gathers to take any actions at all.

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

Alvaro Herrera wrote:
> Magnus Hagander wrote:
>
>> Not sure. I guess my own personal concern would be how badly is
>> autovacuum affected by having to start off a blank set of stats? Any
>> other uses I have I think are capable of dealing with reset-to-zero states.
>
> Well, it doesn't :-) No database or table will be processed until stat
> entries are created, and then I think it will first wait until enough
> activity gathers to take any actions at all.

That's not actualliy not affected, but it does seem like it wouldn't be
a very big issue. If one table was just about to be vacuumed or
analyzed, this would just push it up to twice the threshold, right?

//Magnus

On Tue, 1 Jul 2008, Tom Lane wrote:

> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> Tom Lane wrote:
>>> Hmm ... that would almost certainly result in the stats being lost over
>>> a system shutdown. How much do we care?
>
>> Only for those who put it on a ramdrive. The default, unless you
>> move/sync it off, would still be the same as it is today. While not
>> perfect, the performance difference of going to a ramdrive might easily
>> be enough to offset that in some cases, I think.
>
> Well, what I was wondering about is whether it'd be worth adding logic
> to copy the file to/from a "safer" location at startup/shutdown.

Anyone who needs fast stats storage enough that they're going to symlink
it to RAM should be perfectly capable of scripting server startup/shutdown
to shuffle that to/from a more permanent location. Compared to the admin
chores you're likely to encounter before reaching that scale it's a pretty
easy job, and it's not like losing that data file is a giant loss in any
case. The only thing I could see putting into the server code to help
support this situation is rejecting an old stats file and starting from
scratch instead if they restored a previous version after a crash that
didn't save an updated copy.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> Alvaro Herrera wrote:
>> Well, it doesn't :-) No database or table will be processed until stat
>> entries are created, and then I think it will first wait until enough
>> activity gathers to take any actions at all.

> That's not actualliy not affected, but it does seem like it wouldn't be
> a very big issue. If one table was just about to be vacuumed or
> analyzed, this would just push it up to twice the threshold, right?

Except you could lather, rinse, repeat indefinitely.

The stats system started out with the idea that the stats were
disposable, but I don't really think that's an acceptable behavior
today. We don't even have stats_reset_on_server_start anymore.

It doesn't seem to me that it'd be hard to support two locations for the
stats file --- it'd just take another parameter to the read and write
routines. pgstat.c already knows the difference between a normal write
and a shutdown write ...

regards, tom lane

Tom Lane wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> Alvaro Herrera wrote:
>>> Well, it doesn't :-) No database or table will be processed until stat
>>> entries are created, and then I think it will first wait until enough
>>> activity gathers to take any actions at all.
>
>> That's not actualliy not affected, but it does seem like it wouldn't be
>> a very big issue. If one table was just about to be vacuumed or
>> analyzed, this would just push it up to twice the threshold, right?
>
> Except you could lather, rinse, repeat indefinitely.

Yeha, but if you do that, you certainly have other problems as well....

> The stats system started out with the idea that the stats were
> disposable, but I don't really think that's an acceptable behavior
> today. We don't even have stats_reset_on_server_start anymore.

Good point.

> It doesn't seem to me that it'd be hard to support two locations for the
> stats file --- it'd just take another parameter to the read and write
> routines. pgstat.c already knows the difference between a normal write
> and a shutdown write ...

Right. Should it be removed from the permanent location when the server
starts? Otherwise, if it crashes, we'll pick up the old, stale, version
of the file since it didn't have a chance to get saved away. Better to
start from an empty file, or to start from one that has old data in it?

//Magnus

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> Tom Lane wrote:
>> It doesn't seem to me that it'd be hard to support two locations for the
>> stats file --- it'd just take another parameter to the read and write
>> routines. pgstat.c already knows the difference between a normal write
>> and a shutdown write ...

> Right. Should it be removed from the permanent location when the server
> starts?

Yes, I would say so. There are two possible exit paths: normal shutdown
(where we'd write a new file) and crash. In a crash we'd wish to delete
the file anyway for fear that it's corrupted.

Startup: read permanent file, then delete it.

Post-crash: remove any permanent file (same as now)

Shutdown: write permanent file.

Normal stats collector write: write temp file.

Backend stats fetch: read temp file.

regards, tom lane

On Jul 1, 2008, at 3:02 PM, Tom Lane wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> Alvaro Herrera wrote:
>>> Well, it doesn't :-) No database or table will be processed
>>> until stat
>>> entries are created, and then I think it will first wait until
>>> enough
>>> activity gathers to take any actions at all.
>
>> That's not actualliy not affected, but it does seem like it
>> wouldn't be
>> a very big issue. If one table was just about to be vacuumed or
>> analyzed, this would just push it up to twice the threshold, right?
>
> Except you could lather, rinse, repeat indefinitely.
>
> The stats system started out with the idea that the stats were
> disposable, but I don't really think that's an acceptable behavior
> today. We don't even have stats_reset_on_server_start anymore.
>
> It doesn't seem to me that it'd be hard to support two locations
> for the
> stats file --- it'd just take another parameter to the read and write
> routines. pgstat.c already knows the difference between a normal
> write
> and a shutdown write ...

Leaving the realm of "an easy change", what about periodically (once
a minute?) writing stats to a real table? That means we should never
have to suffer corrupted or lost stats on a crash. Along the same
lines, perhaps we can just keep updates in shared memory instead of
in a file, since that's proven to cause issues for some people.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Decibel! <decibel(at)decibel(dot)org> writes:
> Leaving the realm of "an easy change", what about periodically (once
> a minute?) writing stats to a real table?

The ensuing vacuum overhead seems a sufficient reason why not.

regards, tom lane

Tom Lane wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> Tom Lane wrote:
>>> It doesn't seem to me that it'd be hard to support two locations for the
>>> stats file --- it'd just take another parameter to the read and write
>>> routines. pgstat.c already knows the difference between a normal write
>>> and a shutdown write ...
>
>> Right. Should it be removed from the permanent location when the server
>> starts?
>
> Yes, I would say so. There are two possible exit paths: normal shutdown
> (where we'd write a new file) and crash. In a crash we'd wish to delete
> the file anyway for fear that it's corrupted.
>
> Startup: read permanent file, then delete it.
>
> Post-crash: remove any permanent file (same as now)
>
> Shutdown: write permanent file.
>
> Normal stats collector write: write temp file.
>
> Backend stats fetch: read temp file.

Attached is a patch that implements this. I went with the option of just
storing it in a temporary directory that can be symlinked, and not
bothering with a GUC for it. Comments? (documentation updates are also
needed, but I'll wait with those until I hear patch comments :-P)

//Magnus

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> Attached is a patch that implements this. I went with the option of just
> storing it in a temporary directory that can be symlinked, and not
> bothering with a GUC for it. Comments? (documentation updates are also
> needed, but I'll wait with those until I hear patch comments :-P)

Looks alright in a fast once-over (I didn't test it). Two comments:
Treating the directory as something to create in initdb means you'll
need to bump catversion when you apply it. I'm not sure where you are
planning to document, but there should at least be a mention in the
"database physical layout" chapter, since that's supposed to enumerate
all the subdirectories of $PGDATA.

regards, tom lane

Tom Lane wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> Attached is a patch that implements this. I went with the option of just
>> storing it in a temporary directory that can be symlinked, and not
>> bothering with a GUC for it. Comments? (documentation updates are also
>> needed, but I'll wait with those until I hear patch comments :-P)
>
> Looks alright in a fast once-over (I didn't test it).

That's what I was after. I tested it myself, obviously :-) Not promising
zero bugs, but I was looking for the comment on the approach. So thanks!

> Two comments:
> Treating the directory as something to create in initdb means you'll
> need to bump catversion when you apply it.

Yeah, i meant to do that as part of the commit. But thanks for the
reminder anyway!

> I'm not sure where you are
> planning to document, but there should at least be a mention in the
> "database physical layout" chapter, since that's supposed to enumerate
> all the subdirectories of $PGDATA.

I'm putting it under "configuring the statistics collector". And I'll
add a directory in that section - had missed that.

//Magnus