temporary statistics option at initdb time

On Saturday 09 August 2008 21:31:28 Euler Taveira de Oliveira wrote:

Hi,

After the Magnus patch [1], that make it possible store statistics files
at another (RAM-based) disk, I was thinking that would be useful to add
an option at initdb time to do the symlink as we already do with xlog.
Maybe it could be documented in monitoring.sgml too. Comments?

[1] http://archives.postgresql.org/pgsql-hackers/2008-08/msg00176.php

I find Magnus's approach to be admin un-friendly, and your patch a
re-enforcement of that feeling. Forcing people to fall back on the OS tools
(which may not be terribly good on systems like win32) when we could provide
a consistent way for postgres to handle this itself seems backwards. I
believe this would be much simpler for DBA's if we simply give them a GUC for
stat file location, and allow them to set the location that way. Ideally this
could be done as PGC_SIGHUP, and a change to the location would move the
file "on-the-fly" as they say. (There might be practical limitation to making
that work, but it would certainly be simpler for admins, imho)

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

Robert Treat wrote:

On Saturday 09 August 2008 21:31:28 Euler Taveira de Oliveira wrote:

Hi,

After the Magnus patch [1], that make it possible store statistics files
at another (RAM-based) disk, I was thinking that would be useful to add
an option at initdb time to do the symlink as we already do with xlog.
Maybe it could be documented in monitoring.sgml too. Comments?

[1] http://archives.postgresql.org/pgsql-hackers/2008-08/msg00176.php

I find Magnus's approach to be admin un-friendly, and your patch a
re-enforcement of that feeling. Forcing people to fall back on the OS tools
(which may not be terribly good on systems like win32) when we could provide
a consistent way for postgres to handle this itself seems backwards. I
believe this would be much simpler for DBA's if we simply give them a GUC for
stat file location, and allow them to set the location that way. Ideally this
could be done as PGC_SIGHUP, and a change to the location would move the
file "on-the-fly" as they say. (There might be practical limitation to making
that work, but it would certainly be simpler for admins, imho)

Well, the general enthusiasm for this feature at all wasn't very high,
so I implemented in the least invasive way. It would certainly be
possible to do it as a GUC, and not all that much more work.

I don't think it'd be that hard to handle the SIGHUP case - just have
the stats collector start writing it in the new location the next time
it writes it out, and backends will start reading from there. There's a
short window where the backends would get no data, but I think that's
quite acceptable..

//Magnus

Robert Treat escreveu:

On Saturday 09 August 2008 21:31:28 Euler Taveira de Oliveira wrote:

Hi,

After the Magnus patch [1], that make it possible store statistics files
at another (RAM-based) disk, I was thinking that would be useful to add
an option at initdb time to do the symlink as we already do with xlog.
Maybe it could be documented in monitoring.sgml too. Comments?

[1] http://archives.postgresql.org/pgsql-hackers/2008-08/msg00176.php

I find Magnus's approach to be admin un-friendly, and your patch a
re-enforcement of that feeling. Forcing people to fall back on the OS tools
(which may not be terribly good on systems like win32) when we could provide
a consistent way for postgres to handle this itself seems backwards. I
believe this would be much simpler for DBA's if we simply give them a GUC for
stat file location, and allow them to set the location that way. Ideally this
could be done as PGC_SIGHUP, and a change to the location would move the
file "on-the-fly" as they say. (There might be practical limitation to making
that work, but it would certainly be simpler for admins, imho)

How many times will you change the pg_stat_tmp directory? IMHO it is
something that we don't frequently change. I agree that GUC is a
DBA-friendly-option but we have to deal with (i) stop stats collector
(ii) remove the old contents? (iii) start it at another location. I'm
not saying that is _so_ difficult to do but I could live with
pg_stat_tmp symlink option.

--
Euler Taveira de Oliveira
http://www.timbira.com/

On Aug 12, 2008, at 12:27 PM, Magnus Hagander wrote:

I don't think it'd be that hard to handle the SIGHUP case - just have
the stats collector start writing it in the new location the next time
it writes it out, and backends will start reading from there.
There's a
short window where the backends would get no data, but I think that's
quite acceptable..

I disagree. While we don't guarantee stats are absolutely up-to-date,
or atomic I don't think that gives license for them to just magically
not exist sometimes.

Would it really be that hard to have the system copy the file out
before telling all the other backends of the change?

BTW, it would be nice if it would actually remove the old file, but
that sounds like a lot more work...
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Decibel! <decibel@decibel.org> writes:

I disagree. While we don't guarantee stats are absolutely up-to-date,
or atomic I don't think that gives license for them to just magically
not exist sometimes.

Would it really be that hard to have the system copy the file out
before telling all the other backends of the change?

Well, there is no (zero, zilch, nada) use-case for changing this setting
on the fly. Why not make it a "frozen at postmaster start" GUC? Seems
like that gets all the functionality needed and most of the ease of use.

regards, tom lane

Tom Lane wrote:

Decibel! <decibel@decibel.org> writes:

I disagree. While we don't guarantee stats are absolutely up-to-date,
or atomic I don't think that gives license for them to just magically
not exist sometimes.

Would it really be that hard to have the system copy the file out
before telling all the other backends of the change?

Well, there is no (zero, zilch, nada) use-case for changing this setting
on the fly. Why not make it a "frozen at postmaster start" GUC? Seems
like that gets all the functionality needed and most of the ease of use.

Oh, there is a use-case. If you run your system and then only afterwards
realize the I/O from the stats file is high enough to be an issue, and
want to change it.

That said, I'm not sure the use-case is anywhere near common enough to
put a lot of code into it.

But I can certainly look at making it a startup GUC. As you say, that'll
solve *most* of the cases.

//Magnus

Magnus Hagander wrote:

Tom Lane wrote:

Decibel! <decibel@decibel.org> writes:

I disagree. While we don't guarantee stats are absolutely up-to-date,
or atomic I don't think that gives license for them to just magically
not exist sometimes.
Would it really be that hard to have the system copy the file out
before telling all the other backends of the change?

Well, there is no (zero, zilch, nada) use-case for changing this setting
on the fly. Why not make it a "frozen at postmaster start" GUC? Seems
like that gets all the functionality needed and most of the ease of use.

Oh, there is a use-case. If you run your system and then only afterwards
realize the I/O from the stats file is high enough to be an issue, and
want to change it.

That said, I'm not sure the use-case is anywhere near common enough to
put a lot of code into it.

But I can certainly look at making it a startup GUC. As you say, that'll
solve *most* of the cases.

Here's a patch that implements the simple case making it a
PGC_POSTMASTER variable. Is this good enough for people? ;-)

//Magnus

On Aug 13, 2008, at 4:12 AM, Magnus Hagander wrote:

Tom Lane wrote:

Decibel! <decibel@decibel.org> writes:

I disagree. While we don't guarantee stats are absolutely up-to-
date,
or atomic I don't think that gives license for them to just
magically
not exist sometimes.

Would it really be that hard to have the system copy the file out
before telling all the other backends of the change?

Well, there is no (zero, zilch, nada) use-case for changing this
setting
on the fly. Why not make it a "frozen at postmaster start" GUC?
Seems
like that gets all the functionality needed and most of the ease
of use.

Oh, there is a use-case. If you run your system and then only
afterwards
realize the I/O from the stats file is high enough to be an issue, and
want to change it.

That said, I'm not sure the use-case is anywhere near common enough to
put a lot of code into it.

Something to keep in mind as PG is used to build larger systems
'further up the enterprise'... for us to bounce a database at work
costs us a LOT in lost revenue. I don't want to go into specifics,
but it's more than enough to buy a very nice car. :) That's why I
asked how hard it'd be to do this on the fly.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Decibel! wrote:

On Aug 13, 2008, at 4:12 AM, Magnus Hagander wrote:

Tom Lane wrote:

Decibel! <decibel@decibel.org> writes:

I disagree. While we don't guarantee stats are absolutely up-to-date,
or atomic I don't think that gives license for them to just magically
not exist sometimes.

Would it really be that hard to have the system copy the file out
before telling all the other backends of the change?

Well, there is no (zero, zilch, nada) use-case for changing this setting
on the fly. Why not make it a "frozen at postmaster start" GUC? Seems
like that gets all the functionality needed and most of the ease of use.

Oh, there is a use-case. If you run your system and then only afterwards
realize the I/O from the stats file is high enough to be an issue, and
want to change it.

That said, I'm not sure the use-case is anywhere near common enough to
put a lot of code into it.

Something to keep in mind as PG is used to build larger systems 'further
up the enterprise'... for us to bounce a database at work costs us a LOT
in lost revenue. I don't want to go into specifics, but it's more than
enough to buy a very nice car. :) That's why I asked how hard it'd be to
do this on the fly.

Well, it's doable, but fairly hard.

But you can do it the symlink way without shutting it down, I think. :-)

//Magnus

Magnus Hagander wrote:

Decibel! wrote:

On Aug 13, 2008, at 4:12 AM, Magnus Hagander wrote:

Tom Lane wrote:

Decibel! <decibel@decibel.org> writes:

I disagree. While we don't guarantee stats are absolutely up-to-date,
or atomic I don't think that gives license for them to just magically
not exist sometimes.
Would it really be that hard to have the system copy the file out
before telling all the other backends of the change?

Well, there is no (zero, zilch, nada) use-case for changing this setting
on the fly. Why not make it a "frozen at postmaster start" GUC? Seems
like that gets all the functionality needed and most of the ease of use.

Oh, there is a use-case. If you run your system and then only afterwards
realize the I/O from the stats file is high enough to be an issue, and
want to change it.

That said, I'm not sure the use-case is anywhere near common enough to
put a lot of code into it.

Something to keep in mind as PG is used to build larger systems 'further
up the enterprise'... for us to bounce a database at work costs us a LOT
in lost revenue. I don't want to go into specifics, but it's more than
enough to buy a very nice car. :) That's why I asked how hard it'd be to
do this on the fly.

Well, it's doable, but fairly hard.

But you can do it the symlink way without shutting it down, I think. :-)

Actually, I think maybe not so hard. Attached is a patch that fixes
this. It's done by keeping the old filename around. When you change the
path, the stats collector will start writing the new file the next time
it writes something (which should be max 0.5 seconds later if something
is happening). The backends will immediately try to read from the new
filename, but if that one is not found, they will switch to reading the
old filename. This obviously fails if you change the temp directory
twice in less than half a second, but I really don't see a use-case for
that...

Or did I miss something here? :-)

//Magnus

Magnus Hagander <magnus@hagander.net> writes:

Actually, I think maybe not so hard. Attached is a patch that fixes
this. It's done by keeping the old filename around. When you change the
path, the stats collector will start writing the new file the next time
it writes something (which should be max 0.5 seconds later if something
is happening). The backends will immediately try to read from the new
filename, but if that one is not found, they will switch to reading the
old filename. This obviously fails if you change the temp directory
twice in less than half a second, but I really don't see a use-case for
that...

I think this is introducing complication and race conditions to solve a
problem that no one will really care about. Just let people change the
filename at SIGHUP and document that doing that on-the-fly may cause
stats queries to fail for a short interval.

regards, tom lane

Tom Lane wrote:

Magnus Hagander <magnus@hagander.net> writes:

Actually, I think maybe not so hard. Attached is a patch that fixes
this. It's done by keeping the old filename around. When you change the
path, the stats collector will start writing the new file the next time
it writes something (which should be max 0.5 seconds later if something
is happening). The backends will immediately try to read from the new
filename, but if that one is not found, they will switch to reading the
old filename. This obviously fails if you change the temp directory
twice in less than half a second, but I really don't see a use-case for
that...

I think this is introducing complication and race conditions to solve a
problem that no one will really care about. Just let people change the
filename at SIGHUP and document that doing that on-the-fly may cause
stats queries to fail for a short interval.

Ok, I'll do it that way.

//Magnus

Magnus Hagander <magnus@hagander.net> writes:

Tom Lane wrote:

I think this is introducing complication and race conditions to solve a
problem that no one will really care about. Just let people change the
filename at SIGHUP and document that doing that on-the-fly may cause
stats queries to fail for a short interval.

Ok, I'll do it that way.

BTW, it might be worth tweaking the stats collector to dump stats
immediately after responding to SIGHUP, just to narrow this window
as much as possible.

[ squint... ] Actually, it looks like the stats collector SIG_IGNores
SIGHUP? That can't be right (any more) can it?

regards, tom lane

Tom Lane wrote:

Magnus Hagander <magnus@hagander.net> writes:

Tom Lane wrote:

I think this is introducing complication and race conditions to solve a
problem that no one will really care about. Just let people change the
filename at SIGHUP and document that doing that on-the-fly may cause
stats queries to fail for a short interval.

Ok, I'll do it that way.

BTW, it might be worth tweaking the stats collector to dump stats
immediately after responding to SIGHUP, just to narrow this window
as much as possible.

Pah, that is too obvious :-)
Yeah, should've thought of that, will make that happen.

[ squint... ] Actually, it looks like the stats collector SIG_IGNores
SIGHUP? That can't be right (any more) can it?

It did, until after my patch that was applied a couple of minutes ago.
After the patch, it cares about SIGHUP.

//Magnus

I wrote:

[ squint... ] Actually, it looks like the stats collector SIG_IGNores
SIGHUP? That can't be right (any more) can it?

Oh, never mind, I see my hour-old CVS pull is obsolete ...

regards, tom lane