Обсуждение: fsutil ideas

As part of integrating PostgreSQL into our production environment, we're
working on monitoring software, to provide the same kinds of status
reporting and alerts we have implemented for our outgoing commercial
database product.  One of the things we show on our "big board" is
impending failure conditions, so that they can be resolved before actual
failure occurs.  One key component of this is free space monitoring.
Since PostgreSQL uses available free disk space from the operating
system, we needed methods to query this through the database.  (We
looked at getting it directly from the OS, but there are many reasons it
makes more sense to go through functions in the database to get it.)

Peter Brant, a consultant working with us, has written code which is
working for this under both Linux and Windows.  I've been trying to
package it up in PostgreSQL standard format for submission to the
community, but I'm not sure how to handle a few things.  For Linux, he
used statvfs.  There doesn't seem to be anything in mingw to support
these functions, so he used methods declared in windows.h, which compile
fine under mingw gcc and run fine on our Windows systems.  My concern is
that he has about 100 lines of code which differ between the Linux and
Windows versions, which exceeds anything I've seen in current PostgreSQL
#if statements in my code browsing.  (Not that I've looked at every
line, of course.)

So, my questions:

(1)  Did I miss something regarding mingw support for statvfs?

(2)  If not, is it acceptable for a source file to contain that much
#if code for Windows?

I should probably also ask a tertiary question.  His implementation
reports space in 1K increments though int values.  This effectively
limits the maximum space which will be handled to 2 TB, which I'm told
would be enough to cover our organization for the next ten years, but I
suspect that the PostgreSQL community would prefer to see this handled a
different way.

(3)  What data type should be used to report total space and free space
for each volume?

Thanks in advance for your responses.

-Kevin

P.S.  To make it a bit more concrete, an example of the usage of these
methods, against a local instance on my workstation, is attached.

Kevin Grittner wrote:

>(2)  If not, is it acceptable for a source file to contain that much
>#if code for Windows?
>
>  
>

Large chunks of platform-specific code usually go in src/port

100 lines for a WIN32 piece would not be out of order there.

If that proves difficult, let us see the mods and we can advise better.

cheers

andrew

Kevin Grittner wrote:

> So, my questions:
> 
> (1)  Did I miss something regarding mingw support for statvfs?
> 
> (2)  If not, is it acceptable for a source file to contain that much
> #if code for Windows?
> 
> I should probably also ask a tertiary question.  His implementation
> reports space in 1K increments though int values.  This effectively
> limits the maximum space which will be handled to 2 TB, which I'm told
> would be enough to cover our organization for the next ten years, but I
> suspect that the PostgreSQL community would prefer to see this handled a
> different way.
> 
> (3)  What data type should be used to report total space and free space
> for each volume?
> 
> Thanks in advance for your responses.
> 

No really answering your questions, but (if you haven't already) you 
might want to consider creating a view that extracts all the interesting 
stuff from the file_system() function - so that potential users can just 
'\d' it to determine what it provides.

Also, not sure if its really important, but the man entry from statvfs 
on FreeBSD is a little disconcerting:

<quote>
SYNOPSIS     #include <sys/statvfs.h>
     int     statvfs(const char * restrict path, struct statvfs * restrict buf);
     int     fstatvfs(int fd, struct statvfs *buf);

DESCRIPTION     The statvfs() and fstatvfs() functions fill the structure pointed 
to by     buf with garbage.  This garbage will occasionally bear resemblance to     file system statistics, but
portableapplications must not depend on     this.  Applications must pass a pathname or file descriptor which 
 
refers     to a file on the file system in which they are interested.
     The statvfs structure contains the following members:

</quote>

I haven't tried out any code that uses it, so not sure if the 'garbage' 
comment is valid.

regards

Mark

Kevin Grittner wrote:
> Peter Brant, a consultant working with us, has written code which is
> working for this under both Linux and Windows.  [...] For Linux, he
> used statvfs.

statvfs(2) is standardized, but doesn't seem portable: it isn't 
available on OSX 10.3, NetBSD 2.0 or OpenBSD, for example. Perhaps you 
can fallback to statfs(2) when it is available?

This seems an area where providing consistent cross-platform behavior 
might be difficult. Do we actually need this functionality inside the 
DBMS in the first place?

> (2)  If not, is it acceptable for a source file to contain that much
> #if code for Windows?

Let's see the code first...

> (3)  What data type should be used to report total space and free space
> for each volume?

int64?

-Neil

Neil Conway <neilc@samurai.com> writes:
> Do we actually need this functionality inside the 
> DBMS in the first place?

I think that is the $64 question.  My immediate instinct is "no".
See the knock-down-drag-out fights we had last summer about whether
to expose any filesystem access in built-in standard functions at all.
There will be what the Supreme Court would call "strict scrutiny"
concerning the need for this, possible security risks, etc.
        regards, tom lane

On Thu, Feb 23, 2006 at 11:32:05PM -0500, Tom Lane wrote:
> Neil Conway <neilc@samurai.com> writes:
> > Do we actually need this functionality inside the 
> > DBMS in the first place?
> 
> I think that is the $64 question.  My immediate instinct is "no".
> See the knock-down-drag-out fights we had last summer about whether
> to expose any filesystem access in built-in standard functions at all.
> There will be what the Supreme Court would call "strict scrutiny"
> concerning the need for this, possible security risks, etc.

Isn't this something that could be accomplished entirely within a
function? I suppose it might have to be an untrusted language, but that
still seems cleaner than putting it in the backend. Plus, ISTM that
something like perl is more likely to have a cross-platform means of
accomplishing this.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

The code in question is written as C extension functions.  I think we
were thinking it might be something for contrib (although perhaps that
would be too much of an official blessing too?)

Pete

>>> "Jim C. Nasby" <jnasby@pervasive.com> 02/24/06 8:04 am >>>
Isn't this something that could be accomplished entirely within a
function? I suppose it might have to be an untrusted language, but
that
still seems cleaner than putting it in the backend. Plus, ISTM that
something like perl is more likely to have a cross-platform means of
accomplishing this.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com 
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

>>> On Thu, Feb 23, 2006 at  8:43 pm, in message
<43FE72DB.8070701@samurai.com>,
Neil Conway <neilc@samurai.com> wrote: 
> Kevin Grittner wrote:
>> Peter Brant, a consultant working with us, has written code which
is
>> working for this under both Linux and Windows.  [...] For Linux, he
>> used statvfs.
> 
> statvfs(2) is standardized, but doesn't seem portable: it isn't 
> available on OSX 10.3, NetBSD 2.0 or OpenBSD, for example. Perhaps
you 
> can fallback to statfs(2) when it is available?
> 
> This seems an area where providing consistent cross- platform
behavior 
> might be difficult. Do we actually need this functionality inside the

> DBMS in the first place?

It sounds like we should probably just shelve the idea of sharing this
code.  It is very useful to us, since it allows a single database
connection to monitor the health of the database and detect impending
failure.  Having our monitoring software need to know the operating
system of each database, with security to log into the OS of each
machine, would be far worse from a security standpoint, more fragile,
and more work than adding these functions to the database.  But no
response so far has indicated any interest in it from any other quarter,
and it sounds like the portability issues are more than we want to deal
with here.

In response to a few other posts -- it is implemented with three
functions, which I was bundling to target the contrib level of
submission.  One function returns a list of volumes (i.e., drive letters
in Windows, mount points in Linux), one returns available space for a
given volume, and one returns total space for a given volume.

Since we have what we need to get our work done, and the community at
large doesn't seem interested, I'll shelve the idea of submitting
anything.

Thanks for the responses.

-Kevin

> > This seems an area where providing consistent cross- platform
> behavior 
> > might be difficult. Do we actually need this functionality inside the
> 
> > DBMS in the first place?
> 
> It sounds like we should probably just shelve the idea of sharing this
> code.  It is very useful to us, since it allows a single database
> connection to monitor the health of the database and detect impending
> failure.  Having our monitoring software need to know the operating
> system of each database, with security to log into the OS of each
> machine, would be far worse from a security standpoint, more fragile,
> and more work than adding these functions to the database.  But no

Why don't you have a process on the machine update a set of values in
the database that you then read from remote?

You don't need to know the free diskspace in real time. A 2 minute old
value is probably just as good.


-- 

Kevin Grittner wrote:

>Since we have what we need to get our work done, and the community at
>large doesn't seem interested, I'll shelve the idea of submitting
>anything.
>
>  
>

I think you have misinterpreted. By all means share the code. Put it on 
your website or start a pgfoundry project. Even if it doesn't go into 
the core code some people might very well be interested to use it.

cheers

andrew

>>> On Fri, Feb 24, 2006 at  9:34 am, in message
<1140795253.5092.122.camel@home>,
Rod Taylor <pg@rbt.ca> wrote: 
> 
> You don't need to know the free diskspace in real time. A 2 minute
old
> value is probably just as good.

Not really, this sort of monitoring has kept us from crashing under our
old database product when a poorly written query starts filling
available space by populating a temporary table.  A green light turns
orange (indicating impending failure) on a big board which monitors the
health of about 200 servers and about 3000 workstations.  A support
person drills down through the "LED" on a version of the "big board" in
their browser, which shows the supporting detail.  A runaway query can
be identified and killed, protecting the rest of the users.  A two
minute delay could cause an embarrassing interruption in service for
hundreds of our users.

Your suggestion would work for the gradual "outgrowing" of space, but
we need to cover both.

-Kevin

On Fri, 2006-02-24 at 09:48 -0600, Kevin Grittner wrote:
> >>> On Fri, Feb 24, 2006 at  9:34 am, in message
> <1140795253.5092.122.camel@home>,
> Rod Taylor <pg@rbt.ca> wrote: 
> > 
> > You don't need to know the free diskspace in real time. A 2 minute
> old
> > value is probably just as good.
> 
> Not really, this sort of monitoring has kept us from crashing under our
> old database product when a poorly written query starts filling
> available space by populating a temporary table.  A green light turns

I see. It is annoying that you cannot easily (takes a patch to PG
sources) segregate users temporary workspaces into per-user tablespaces
with filesystem quotas.

PostgreSQL seems to deal with out of diskspace situations pretty well
when it impacts a tablespace (global stuff like WAL or subtransactions
have issues -- but they grow slowly) as far as only interrupting service
for the individual actions that ran out.

You may wish to look at funding toggles that can configure the maximum
memory usage and maximum temporary diskspace (different tablespaces with
filesystem quotas) on a per user basis similar to the statement_timeout
limitations in place today.


I'm curious as to how you monitor for total transaction time length to
ensure that vacuum is able to do its thing, particularly when the
transaction is active (not IDLE).

-- 

>>> On Fri, Feb 24, 2006 at 10:57 am, in message
<1140800266.5092.144.camel@home>,
Rod Taylor <pg@rbt.ca> wrote: 
> 
> PostgreSQL seems to deal with out of diskspace situations pretty
well
> when it impacts a tablespace (global stuff like WAL or
subtransactions
> have issues --  but they grow slowly) as far as only interrupting
service
> for the individual actions that ran out.

We haven't used tablespace features yet, as 3 of the 4 databases
running PostgreSQL so far are on Windows.  We have run out of space a
couple times, and it seems like it handles it well in terms of not
corrupting the database, and resuming OK once some space is freed.  The
messages are not that clear -- some sort of generic I/O write error, as
I recall, instead of "out of disk space" being clearly stated.

> You may wish to look at funding toggles that can configure the
maximum
> memory usage and maximum temporary diskspace (different tablespaces
with
> filesystem quotas) on a per user basis similar to the
statement_timeout
> limitations in place today.

That wouldn't help because the vast majority of the work is done
through a middle tier which uses a connection pool shared by all users. 
It does take some human review and judgment to ensure that a query which
is running long and/or using a lot of temp table space is really a
problem as opposed to one of our larger legitimate processes.

> I'm curious as to how you monitor for total transaction time length
to
> ensure that vacuum is able to do its thing, particularly when the
> transaction is active (not IDLE).

We run a database vacuum nightly and review it the next day. 
(Something will need to be done to automate this with summaries and
exception lists when we get more than a few databases on PostgreSQL.  We
can't have a person reviewing 100 of these every day.)  We've not had
any nightly vacuum fail to finish.  They did start running a tad long
until we did some aggressive maintenance at one point.

Our autovacuum is configured with fairly aggressive parameters,
compared to the default; but, even so, only a few small tables with high
update rates normally reach the thresholds.  I haven't noticed the
autovacuum getting held up on these.

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> We haven't used tablespace features yet, as 3 of the 4 databases
> running PostgreSQL so far are on Windows.  We have run out of space a
> couple times, and it seems like it handles it well in terms of not
> corrupting the database, and resuming OK once some space is freed.  The
> messages are not that clear -- some sort of generic I/O write error, as
> I recall, instead of "out of disk space" being clearly stated.

Hm, this may be an issue of poor translation of Windows error codes;
AFAIR PG is pretty good these days about reporting the correct error
message on Unix.  Can you dig up the exact message you got, or
deliberately reproduce the problem to get a fresh copy?
        regards, tom lane

> > I'm curious as to how you monitor for total transaction time length
> to
> > ensure that vacuum is able to do its thing, particularly when the
> > transaction is active (not IDLE).
> 
> We run a database vacuum nightly and review it the next day. 

Ahh.. different issues again I guess.

I have a few structures that need to be vacuumed every 10 minutes or
less. If a transaction runs longer than 20 minutes (thus effectively
disabling vacuum for 20 minutes) I run into pretty big problems.

I watch for table bloat but I haven't figured out a nice way of tracking
down the postgresql process with the oldest transaction running short of
patching PostgreSQL to report the XID for a connection in
pg_stat_activity.

-- 

Rod Taylor <pg@rbt.ca> writes:
> I watch for table bloat but I haven't figured out a nice way of tracking
> down the postgresql process with the oldest transaction running short of
> patching PostgreSQL to report the XID for a connection in
> pg_stat_activity.

I don't think you need a patch --- try joining with pg_locks.
        regards, tom lane

On Fri, 2006-02-24 at 12:48 -0500, Tom Lane wrote:
> Rod Taylor <pg@rbt.ca> writes:
> > I watch for table bloat but I haven't figured out a nice way of tracking
> > down the postgresql process with the oldest transaction running short of
> > patching PostgreSQL to report the XID for a connection in
> > pg_stat_activity.
> 
> I don't think you need a patch --- try joining with pg_locks.

Ugh.. pg_locks is the first think I look at to see if something is
blocked that shouldn't be. Don't know why I didn't think of using it for
the XID value.

Thanks.

On Fri, 2006-02-24 at 19:12, Rod Taylor wrote:
> On Fri, 2006-02-24 at 12:48 -0500, Tom Lane wrote:
> > Rod Taylor <pg@rbt.ca> writes:
> > > I watch for table bloat but I haven't figured out a nice way of tracking
> > > down the postgresql process with the oldest transaction running short of
> > > patching PostgreSQL to report the XID for a connection in
> > > pg_stat_activity.

But I'm afraid that a long running transaction with many short queries
will not even show up in pg_stat_activity. So that's not a completely
reliable way of catching long running transactions... but it's true that
most of the time a long running query is the problem, and that is
catchable.

Cheers,
Csaba.

On Fri, 2006-02-24 at 19:20 +0100, Csaba Nagy wrote:
> On Fri, 2006-02-24 at 19:12, Rod Taylor wrote:
> > On Fri, 2006-02-24 at 12:48 -0500, Tom Lane wrote:
> > > Rod Taylor <pg@rbt.ca> writes:
> > > > I watch for table bloat but I haven't figured out a nice way of tracking
> > > > down the postgresql process with the oldest transaction running short of
> > > > patching PostgreSQL to report the XID for a connection in
> > > > pg_stat_activity.
> 
> But I'm afraid that a long running transaction with many short queries
> will not even show up in pg_stat_activity. So that's not a completely
> reliable way of catching long running transactions... but it's true that
> most of the time a long running query is the problem, and that is
> catchable.

The specific query may not show up but the process should appear in one
state or another.

That said, pg_locks would still show low XID (compared to the rest)
exists and that would probably be the culprit.
-- 

Ühel kenal päeval, R, 2006-02-24 kell 19:20, kirjutas Csaba Nagy:
> On Fri, 2006-02-24 at 19:12, Rod Taylor wrote:
> > On Fri, 2006-02-24 at 12:48 -0500, Tom Lane wrote:
> > > Rod Taylor <pg@rbt.ca> writes:
> > > > I watch for table bloat but I haven't figured out a nice way of tracking
> > > > down the postgresql process with the oldest transaction running short of
> > > > patching PostgreSQL to report the XID for a connection in
> > > > pg_stat_activity.
> 
> But I'm afraid that a long running transaction with many short queries
> will not even show up in pg_stat_activity. 

It will show as "<IDLE> in transaction"

The harder part would be knowing how long the queri has been running in
wallclock time, not in transactions.

---------------
Hannu

Oh, so does it actually involve any server modifications? Or can it just
go into pgfoundry?
On Fri, Feb 24, 2006 at 08:25:03AM -0600, Peter Brant wrote:
> The code in question is written as C extension functions.  I think we
> were thinking it might be something for contrib (although perhaps that
> would be too much of an official blessing too?)
> 
> Pete
> 
> >>> "Jim C. Nasby" <jnasby@pervasive.com> 02/24/06 8:04 am >>>
> Isn't this something that could be accomplished entirely within a
> function? I suppose it might have to be an untrusted language, but
> that
> still seems cleaner than putting it in the backend. Plus, ISTM that
> something like perl is more likely to have a cross-platform means of
> accomplishing this.
> -- 
> Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com 
> Pervasive Software      http://pervasive.com    work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

>>> On Fri, Feb 24, 2006 at  5:00 pm, in message
<20060224230030.GM2068@pervasive.com>, "Jim C. Nasby"
<jnasby@pervasive.com>
wrote: 
> Oh, so does it actually involve any server modifications? Or can it
just
> go into pgfoundry?

No server modifications.  I've got it bundled up as though it were
going to be under contrib, and I've applied for a pgfoundry project for
it.  Waiting for approval.

I've not dealt with pgfoundry before, so I don't know how much
modification, if any, is needed to set that up.  It's working for us now
by building in standard contrib fashion (from contrib/fsutil).  The
files:

-rw-r--r--  1 kgrittn users  318 Feb 24 11:39 Makefile
-rw-r--r--  1 kgrittn users  751 Feb 24 11:44 README.fsutil
-rw-r--r--  1 kgrittn users 7857 Feb 24 14:25 fsutil.c
-rw-r--r--  1 kgrittn users 2149 Feb 24 14:25 fsutil.h
-rw-r--r--  1 kgrittn users  440 Feb 24 12:54 fsutil.sql.in

Any pointers on setting up the project, or a URL to a page on that
topic, would be welcome.  I haven't stumbled across it yet; although,
once I know about it I'm sure it will seem obvious.

-Kevin

Kevin Grittner wrote:

> I've not dealt with pgfoundry before, so I don't know how much
> modification, if any, is needed to set that up.  It's working for us now
> by building in standard contrib fashion (from contrib/fsutil).  The
> files:

Having it build with PGXS would be a definite plus for ease of
installation.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

>>> On Fri, Feb 24, 2006 at  5:25 pm, in message
<20060224232500.GG9060@surnet.cl>,
Alvaro Herrera <alvherre@commandprompt.com> wrote: 
> 
> Having it build with PGXS would be a definite plus for ease of
> installation.

It does.