pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedMonitoring disk space from within the server
22+ messages / 7 participants
[nested] [flat]
* Monitoring disk space from within the server
@ 2019-11-08 13:24 Christoph Berg <myon@debian.org>
0 siblings, 3 replies; 22+ messages in thread
From: Christoph Berg @ 2019-11-08 13:24 UTC (permalink / raw)
To: pgsql-hackers
Monitoring the available disk space is the topmost thing on the
priority for PostgreSQL operation, yet this metric is not available
from the SQL level.
The attached patch implements a function pg_tablespace_statfs(tblspc)
to report disk space numbers per tablespace:
# select * from pg_tablespace_statfs('pg_default');
blocks │ bfree │ bavail │ files │ ffree
───────────┼──────────┼──────────┼──────────┼──────────
103179564 │ 20829222 │ 20815126 │ 26214400 │ 24426295
Open points:
* should these numbers be converted to bytes?
* the column names currently mirror the statfs() names and should
certainly be improved
* which of these columns add to \db+ output?
* possibly extend this (and \db) to pg_wal
Christoph
Attachments:
0001-Add-pg_tablespace_statfs-functions.patch (text/x-diff, 5.2K) download
From bcd0c16e4ce12d406e41e1a77cbc2cf781accc93 Mon Sep 17 00:00:00 2001
From: Christoph Berg <christoph.berg@credativ.de>
Date: Fri, 8 Nov 2019 14:12:35 +0100
Subject: [PATCH] Add pg_tablespace_statfs() functions
This exposes statfs() on tablespace directories on the SQL level,
allowing monitoring of free disk space from within the server.
---
src/backend/utils/adt/dbsize.c | 86 +++++++++++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 14 ++++++
2 files changed, 100 insertions(+)
diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
index a87e7214e9..7283fd19d9 100644
--- a/src/backend/utils/adt/dbsize.c
+++ b/src/backend/utils/adt/dbsize.c
@@ -12,19 +12,23 @@
#include "postgres.h"
#include <sys/stat.h>
+#include <sys/vfs.h>
#include "access/htup_details.h"
#include "access/relation.h"
#include "catalog/catalog.h"
#include "catalog/namespace.h"
#include "catalog/pg_authid.h"
+#include "catalog/pg_type.h"
#include "catalog/pg_tablespace.h"
#include "commands/dbcommands.h"
#include "commands/tablespace.h"
+#include "funcapi.h"
#include "miscadmin.h"
#include "storage/fd.h"
#include "utils/acl.h"
#include "utils/builtins.h"
+#include "utils/memutils.h"
#include "utils/numeric.h"
#include "utils/rel.h"
#include "utils/relfilenodemap.h"
@@ -263,6 +267,88 @@ pg_tablespace_size_name(PG_FUNCTION_ARGS)
}
+/*
+ * Return disk stats of tablespace. Returns -1 if the tablespace directory
+ * cannot be found.
+ */
+static Datum
+get_tablespace_statfs(Oid tblspcOid)
+{
+ char tblspcPath[MAXPGPATH];
+ AclResult aclresult;
+ struct statfs fst;
+ TupleDesc tupdesc;
+ Datum values[6];
+ bool isnull[6];
+ HeapTuple tuple;
+
+ /*
+ * User must be a member of pg_read_all_stats or have CREATE privilege for
+ * target tablespace, either explicitly granted or implicitly because it
+ * is default for current database.
+ */
+ if (tblspcOid != MyDatabaseTableSpace &&
+ !is_member_of_role(GetUserId(), DEFAULT_ROLE_READ_ALL_STATS))
+ {
+ aclresult = pg_tablespace_aclcheck(tblspcOid, GetUserId(), ACL_CREATE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, OBJECT_TABLESPACE,
+ get_tablespace_name(tblspcOid));
+ }
+
+ if (tblspcOid == DEFAULTTABLESPACE_OID)
+ snprintf(tblspcPath, MAXPGPATH, "base");
+ else if (tblspcOid == GLOBALTABLESPACE_OID)
+ snprintf(tblspcPath, MAXPGPATH, "global");
+ else
+ snprintf(tblspcPath, MAXPGPATH, "pg_tblspc/%u/%s", tblspcOid,
+ TABLESPACE_VERSION_DIRECTORY);
+
+ if (statfs(tblspcPath, &fst) < 0)
+ {
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not stat tablespace directory \"%s\": %m", tblspcPath)));
+ }
+
+ tupdesc = CreateTemplateTupleDesc(5);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "blocks", INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "bfree", INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 3, "bavail", INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 4, "files", INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 5, "ffree", INT8OID, -1, 0);
+ BlessTupleDesc(tupdesc);
+
+ memset(isnull, false, sizeof(isnull));
+ values[0] = Int64GetDatum((int64) fst.f_blocks);
+ values[1] = Int64GetDatum((int64) fst.f_bfree);
+ values[2] = Int64GetDatum((int64) fst.f_bavail);
+ values[3] = Int64GetDatum((int64) fst.f_files);
+ values[4] = Int64GetDatum((int64) fst.f_ffree);
+
+ tuple = heap_form_tuple(tupdesc, values, isnull);
+
+ PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
+}
+
+Datum
+pg_tablespace_statfs_oid(PG_FUNCTION_ARGS)
+{
+ Oid tblspcOid = PG_GETARG_OID(0);
+
+ PG_RETURN_DATUM(get_tablespace_statfs(tblspcOid));
+}
+
+Datum
+pg_tablespace_statfs_name(PG_FUNCTION_ARGS)
+{
+ Name tblspcName = PG_GETARG_NAME(0);
+ Oid tblspcOid = get_tablespace_oid(NameStr(*tblspcName), false);
+
+ PG_RETURN_DATUM(get_tablespace_statfs(tblspcOid));
+}
+
+
/*
* calculate size of (one fork of) a relation
*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 58ea5b982b..1f1398b46b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6914,6 +6914,20 @@
descr => 'total disk space usage for the specified tablespace',
proname => 'pg_tablespace_size', provolatile => 'v', prorettype => 'int8',
proargtypes => 'name', prosrc => 'pg_tablespace_size_name' },
+{ oid => '4191',
+ descr => 'disk stats for the specified tablespace',
+ proname => 'pg_tablespace_statfs', provolatile => 'v',
+ prorettype => 'record', proargtypes => 'oid',
+ proallargtypes => '{oid,int8,int8,int8,int8,int8}', proargmodes => '{i,o,o,o,o,o}',
+ proargnames => '{oid,blocks,bfree,bavail,files,ffree}',
+ prosrc => 'pg_tablespace_statfs_oid' },
+{ oid => '4192',
+ descr => 'disk stats for the specified tablespace',
+ proname => 'pg_tablespace_statfs', provolatile => 'v',
+ prorettype => 'record', proargtypes => 'name',
+ proallargtypes => '{name,int8,int8,int8,int8,int8}', proargmodes => '{i,o,o,o,o,o}',
+ proargnames => '{name,blocks,bfree,bavail,files,ffree}',
+ prosrc => 'pg_tablespace_statfs_name' },
{ oid => '2324', descr => 'total disk space usage for the specified database',
proname => 'pg_database_size', provolatile => 'v', prorettype => 'int8',
proargtypes => 'oid', prosrc => 'pg_database_size_oid' },
--
2.24.0.rc1
* Re: Monitoring disk space from within the server
@ 2019-11-08 13:32 Julien Rouhaud <rjuju123@gmail.com>
parent: Christoph Berg <myon@debian.org>
2 siblings, 2 replies; 22+ messages in thread
From: Julien Rouhaud @ 2019-11-08 13:32 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; +Cc: pgsql-hackers
On Fri, Nov 8, 2019 at 2:24 PM Christoph Berg <myon@debian.org> wrote:
>
> Monitoring the available disk space is the topmost thing on the
> priority for PostgreSQL operation, yet this metric is not available
> from the SQL level.
>
> The attached patch implements a function pg_tablespace_statfs(tblspc)
> to report disk space numbers per tablespace:
>
> # select * from pg_tablespace_statfs('pg_default');
> blocks │ bfree │ bavail │ files │ ffree
> ───────────┼──────────┼──────────┼──────────┼──────────
> 103179564 │ 20829222 │ 20815126 │ 26214400 │ 24426295
>
> Open points:
> * should these numbers be converted to bytes?
> * the column names currently mirror the statfs() names and should
> certainly be improved
> * which of these columns add to \db+ output?
> * possibly extend this (and \db) to pg_wal
Shouldn't we have something more generic, in hope that this eventually
get implemented on Windows? I'm also wondering if getting the fs
information is enough, as there might be quota.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Monitoring disk space from within the server
@ 2019-11-08 13:35 Christoph Berg <myon@debian.org>
parent: Julien Rouhaud <rjuju123@gmail.com>
1 sibling, 1 reply; 22+ messages in thread
From: Christoph Berg @ 2019-11-08 13:35 UTC (permalink / raw)
To: Julien Rouhaud <rjuju123@gmail.com>; +Cc: pgsql-hackers
Re: Julien Rouhaud 2019-11-08 <CAOBaU_YVGEnsnP1ufp42NiJ+WvPHRWBOsBOcaxWxsbXPN_sdNQ@mail.gmail.com>
> Shouldn't we have something more generic, in hope that this eventually
> get implemented on Windows? I'm also wondering if getting the fs
> information is enough, as there might be quota.
The name is certainly not a good pick, it's not meant to be a raw
statfs() wrapper but something more high-level. I just went with that
to have something working to start with.
How about these?
pg_tablespace_stats()
pg_tablespace_space()
pg_tablespace_disk_space()
Christoph
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Monitoring disk space from within the server
@ 2019-11-08 13:40 Christoph Berg <myon@debian.org>
parent: Julien Rouhaud <rjuju123@gmail.com>
1 sibling, 1 reply; 22+ messages in thread
From: Christoph Berg @ 2019-11-08 13:40 UTC (permalink / raw)
To: Julien Rouhaud <rjuju123@gmail.com>; +Cc: pgsql-hackers
Re: Julien Rouhaud 2019-11-08 <CAOBaU_YVGEnsnP1ufp42NiJ+WvPHRWBOsBOcaxWxsbXPN_sdNQ@mail.gmail.com>
> I'm also wondering if getting the fs
> information is enough, as there might be quota.
We could append the quotactl(Q_GETQUOTA) information as well, but I'm
not sure this has a sensible actual-users-to-noise ratio.
Christoph
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Monitoring disk space from within the server
@ 2019-11-08 13:48 Julien Rouhaud <rjuju123@gmail.com>
parent: Christoph Berg <myon@debian.org>
0 siblings, 1 reply; 22+ messages in thread
From: Julien Rouhaud @ 2019-11-08 13:48 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; +Cc: pgsql-hackers
On Fri, Nov 8, 2019 at 2:35 PM Christoph Berg <myon@debian.org> wrote:
>
> Re: Julien Rouhaud 2019-11-08 <CAOBaU_YVGEnsnP1ufp42NiJ+WvPHRWBOsBOcaxWxsbXPN_sdNQ@mail.gmail.com>
> > Shouldn't we have something more generic, in hope that this eventually
> > get implemented on Windows? I'm also wondering if getting the fs
> > information is enough, as there might be quota.
>
> The name is certainly not a good pick, it's not meant to be a raw
> statfs() wrapper but something more high-level. I just went with that
> to have something working to start with.
>
> How about these?
> pg_tablespace_stats()
> pg_tablespace_space()
> pg_tablespace_disk_space()
The related function on Windows is apparently GetDiskFreeSpaceA [1].
It'll probably be quite hard to get something consistent for most of
counters, so probably pg_tablespace_(disk_)space is the best name,
providing only total size and free size?
[1] https://docs.microsoft.com/en-us/windows/win32/api/fileapi/nf-fileapi-getdiskfreespacea
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Monitoring disk space from within the server
@ 2019-11-08 13:55 Julien Rouhaud <rjuju123@gmail.com>
parent: Christoph Berg <myon@debian.org>
0 siblings, 1 reply; 22+ messages in thread
From: Julien Rouhaud @ 2019-11-08 13:55 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; +Cc: pgsql-hackers
On Fri, Nov 8, 2019 at 2:40 PM Christoph Berg <myon@debian.org> wrote:
>
> Re: Julien Rouhaud 2019-11-08 <CAOBaU_YVGEnsnP1ufp42NiJ+WvPHRWBOsBOcaxWxsbXPN_sdNQ@mail.gmail.com>
> > I'm also wondering if getting the fs
> > information is enough, as there might be quota.
>
> We could append the quotactl(Q_GETQUOTA) information as well, but I'm
> not sure this has a sensible actual-users-to-noise ratio.
Well, having a quota is one of the few real reason to create a
tablespace so it's probably worth it, although I have to agree that I
seldom saw quota in production.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Monitoring disk space from within the server
@ 2019-11-08 13:58 Christoph Berg <myon@debian.org>
parent: Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Christoph Berg @ 2019-11-08 13:58 UTC (permalink / raw)
To: Julien Rouhaud <rjuju123@gmail.com>; +Cc: pgsql-hackers
Re: Julien Rouhaud 2019-11-08 <CAOBaU_Zu6RP6-mHyA_J9-xkxJe0tarTVqU9TFza+tCPKUxsjiA@mail.gmail.com>
> The related function on Windows is apparently GetDiskFreeSpaceA [1].
There's a link to GetDiskFreeSpaceExA() which seems much easier to use
because it accepts any directory on the drive in question:
https://docs.microsoft.com/en-us/windows/win32/api/fileapi/nf-fileapi-getdiskfreespaceexa
> It'll probably be quite hard to get something consistent for most of
> counters, so probably pg_tablespace_(disk_)space is the best name,
> providing only total size and free size?
So, how about:
pg_tablespace_disk_space -> total_bytes | free_bytes
The inode numbers are probably not very interesting in a PG tablespace
as we don't create that many files. Or do we think including these
counters (on UNIX) makes sense?
There's precedents for leaving fields NULL where not supported by the
OS, for example pg_stat_file() returns "change" on UNIX only, and
"creation" on Windows only.
Christoph
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Monitoring disk space from within the server
@ 2019-11-08 14:00 Christoph Berg <myon@debian.org>
parent: Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 0 replies; 22+ messages in thread
From: Christoph Berg @ 2019-11-08 14:00 UTC (permalink / raw)
To: Julien Rouhaud <rjuju123@gmail.com>; +Cc: pgsql-hackers
Re: Julien Rouhaud 2019-11-08 <CAOBaU_ay0FT6dFt61Pae77pHEu6sny3xM43L4i-pPi5kKkguxQ@mail.gmail.com>
> > We could append the quotactl(Q_GETQUOTA) information as well, but I'm
> > not sure this has a sensible actual-users-to-noise ratio.
>
> Well, having a quota is one of the few real reason to create a
> tablespace so it's probably worth it, although I have to agree that I
> seldom saw quota in production.
Given that PG deals badly with one tablespace being full (might work
in production, but if it's full during recovery, the whole server will
stop), I've never seen quotas being used.
Christoph
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Monitoring disk space from within the server
@ 2019-11-08 14:10 Julien Rouhaud <rjuju123@gmail.com>
parent: Christoph Berg <myon@debian.org>
0 siblings, 1 reply; 22+ messages in thread
From: Julien Rouhaud @ 2019-11-08 14:10 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; +Cc: pgsql-hackers
On Fri, Nov 8, 2019 at 2:58 PM Christoph Berg <myon@debian.org> wrote:
>
> Re: Julien Rouhaud 2019-11-08 <CAOBaU_Zu6RP6-mHyA_J9-xkxJe0tarTVqU9TFza+tCPKUxsjiA@mail.gmail.com>
> > The related function on Windows is apparently GetDiskFreeSpaceA [1].
>
> There's a link to GetDiskFreeSpaceExA() which seems much easier to use
> because it accepts any directory on the drive in question:
>
> https://docs.microsoft.com/en-us/windows/win32/api/fileapi/nf-fileapi-getdiskfreespaceexa
>
> > It'll probably be quite hard to get something consistent for most of
> > counters, so probably pg_tablespace_(disk_)space is the best name,
> > providing only total size and free size?
>
> So, how about:
>
> pg_tablespace_disk_space -> total_bytes | free_bytes
>
> The inode numbers are probably not very interesting in a PG tablespace
> as we don't create that many files. Or do we think including these
> counters (on UNIX) makes sense?
Agreed, inodes are probably not very useful there.
> There's precedents for leaving fields NULL where not supported by the
> OS, for example pg_stat_file() returns "change" on UNIX only, and
> "creation" on Windows only.
> [...]
> Given that PG deals badly with one tablespace being full (might work
> in production, but if it's full during recovery, the whole server will
> stop), I've never seen quotas being used.
I'm +1 on "pg_tablespace_disk_space -> total_bytes | free_bytes"
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Monitoring disk space from within the server
@ 2019-11-08 14:50 Tomas Vondra <tomas.vondra@2ndquadrant.com>
parent: Christoph Berg <myon@debian.org>
2 siblings, 1 reply; 22+ messages in thread
From: Tomas Vondra @ 2019-11-08 14:50 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; pgsql-hackers
On Fri, Nov 08, 2019 at 02:24:19PM +0100, Christoph Berg wrote:
>Monitoring the available disk space is the topmost thing on the
>priority for PostgreSQL operation, yet this metric is not available
>from the SQL level.
>
While I agree monitoring disk space is important, I think pretty much
every deployment already does that using some other monitoring tool
(which also monitors million other things).
Also, I wonder how universal / reliable this actually is, considering
the range of filesystems and related stuff (thin provisioning, quotas,
...) people use in production. I do recall a number of cases when "df"
was showing a plenty of free space, but one of the internal resources
for that particular filesystem was exhausted. I doubt it's desirable to
add all this knowledge into PostgreSQL.
It's not clear to me what issue this is actually meant to solve - it
provides data, which is nice, but it still needs to be fed to some
motinoring and alerting system. And every monitoring system has a good
plugin to collect this type of data, so why not to use that?
Surely, we can't rely on this for any internal logic - so why not to
provide this as an extension?
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Monitoring disk space from within the server
@ 2019-11-08 15:06 Christoph Berg <myon@debian.org>
parent: Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 2 replies; 22+ messages in thread
From: Christoph Berg @ 2019-11-08 15:06 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; +Cc: pgsql-hackers
Re: Tomas Vondra 2019-11-08 <20191108145025.d7pfcip6plufxiah@development>
> While I agree monitoring disk space is important, I think pretty much
> every deployment already does that using some other monitoring tool
> (which also monitors million other things).
There are plenty of deployments where that isn't true, either because
they aren't doing any monitoring, or (probably more commonly) because
the OS monitoring is done by the OS department and the DB department
doesn't have good access to the figures, and possibly not even any
shell access.
Offering the numbers on the database level would make monitoring
easier for these users, and also provide the numbers on the level
where they might be useful. ("Do I have enough disk space to load this
5GB dump now?")
> Also, I wonder how universal / reliable this actually is, considering
> the range of filesystems and related stuff (thin provisioning, quotas,
> ...) people use in production. I do recall a number of cases when "df"
> was showing a plenty of free space, but one of the internal resources
> for that particular filesystem was exhausted. I doubt it's desirable to
> add all this knowledge into PostgreSQL.
That might be partly true, e.g. btrfs traditionally didn't support
"df" but only "btrfs df". But this got fixed in the meantime, and just
because there are weird filesystems doesn't mean we shouldn't try to
support the normal case where statfs() just works.
> It's not clear to me what issue this is actually meant to solve - it
> provides data, which is nice, but it still needs to be fed to some
> motinoring and alerting system. And every monitoring system has a good
> plugin to collect this type of data, so why not to use that?
What's wrong with providing nice data? It doesn't hurt to have it.
And the cost of the implementation is low.
> Surely, we can't rely on this for any internal logic - so why not to
> provide this as an extension?
By the same argument you could also argue that \l+ should be an
extension because database size is optional to know.
I think this should be directly in core because it's useful to a wide
range of users.
Christoph
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Monitoring disk space from within the server
@ 2019-11-08 15:12 Christoph Berg <myon@debian.org>
parent: Christoph Berg <myon@debian.org>
1 sibling, 0 replies; 22+ messages in thread
From: Christoph Berg @ 2019-11-08 15:12 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; pgsql-hackers
Re: To Tomas Vondra 2019-11-08 <20191108150621.GL8017@msg.df7cb.de>
> I think this should be directly in core because it's useful to a wide
> range of users.
Also, I want to have it in \db+ in psql where users would actually be
looking for it.
Christoph
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Monitoring disk space from within the server
@ 2019-11-08 21:11 Tomas Vondra <tomas.vondra@2ndquadrant.com>
parent: Christoph Berg <myon@debian.org>
1 sibling, 0 replies; 22+ messages in thread
From: Tomas Vondra @ 2019-11-08 21:11 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; pgsql-hackers
On Fri, Nov 08, 2019 at 04:06:21PM +0100, Christoph Berg wrote:
>Re: Tomas Vondra 2019-11-08 <20191108145025.d7pfcip6plufxiah@development>
>> While I agree monitoring disk space is important, I think pretty much
>> every deployment already does that using some other monitoring tool
>> (which also monitors million other things).
>
>There are plenty of deployments where that isn't true, either because
>they aren't doing any monitoring, or (probably more commonly) because
>the OS monitoring is done by the OS department and the DB department
>doesn't have good access to the figures, and possibly not even any
>shell access.
>
It might sound a bit annoying, but I suspect deployments where the DBAs
does not have access to such basic system metrics have bigger issues and
giving them one particular piece of information is just a bandaid.
>Offering the numbers on the database level would make monitoring
>easier for these users, and also provide the numbers on the level
>where they might be useful. ("Do I have enough disk space to load this
>5GB dump now?")
>
>> Also, I wonder how universal / reliable this actually is, considering
>> the range of filesystems and related stuff (thin provisioning, quotas,
>> ...) people use in production. I do recall a number of cases when "df"
>> was showing a plenty of free space, but one of the internal resources
>> for that particular filesystem was exhausted. I doubt it's desirable to
>> add all this knowledge into PostgreSQL.
>
>That might be partly true, e.g. btrfs traditionally didn't support
>"df" but only "btrfs df". But this got fixed in the meantime, and just
>because there are weird filesystems doesn't mean we shouldn't try to
>support the normal case where statfs() just works.
>
Maybe, but if you suggest to show the information in \dn+ then we should
at least know how common / likely those issues are. Because if they are
anything but extremely uncommon, we'll get plenty of bogus bug reports
complaining about inaccurate information.
>> It's not clear to me what issue this is actually meant to solve - it
>> provides data, which is nice, but it still needs to be fed to some
>> motinoring and alerting system. And every monitoring system has a good
>> plugin to collect this type of data, so why not to use that?
>
>What's wrong with providing nice data? It doesn't hurt to have it.
>And the cost of the implementation is low.
>
My point was that there are other (better, already existing) ways to get
the data, and getting them through database means extra complexity (I do
agree it's not a lot of code at this point, though).
Of course, if your assumption is that using those other ways to get the
data is impossible, then sure - adding this makes sense.
>> Surely, we can't rely on this for any internal logic - so why not to
>> provide this as an extension?
>
>By the same argument you could also argue that \l+ should be an
>extension because database size is optional to know.
>
That's not really my argument, though. I'm not suggesting everything
"optional" should be in an extension, but that there's nothing forcing
us to make this directly part of the core.
And packaging stuff into extension has advantages too (independent dev
cycle, and so on).
>I think this should be directly in core because it's useful to a wide
>range of users.
>
I'm not convinced that's actually true. It might be, but I don't have
any data to support it (or vice versa).
cheers
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Monitoring disk space from within the server
@ 2019-11-09 01:56 Michael Paquier <michael@paquier.xyz>
parent: Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Michael Paquier @ 2019-11-09 01:56 UTC (permalink / raw)
To: Julien Rouhaud <rjuju123@gmail.com>; +Cc: Christoph Berg <myon@debian.org>; pgsql-hackers
On Fri, Nov 08, 2019 at 03:10:57PM +0100, Julien Rouhaud wrote:
> Agreed, inodes are probably not very useful there.
Total bytes and free bytes looks like a good first cut. Have you
looked at the portability of statfs() on other BSD flavors and
Solaris? I recall from a lookup at statvfs() that these may not be
present everywhere. I'd think that we can live with a configure
switch and complain with an error or a warning if we are out of
options on a given platform.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Monitoring disk space from within the server
@ 2019-11-09 13:33 Christoph Moench-Tegeder <cmt@burggraben.net>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 22+ messages in thread
From: Christoph Moench-Tegeder @ 2019-11-09 13:33 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Julien Rouhaud <rjuju123@gmail.com>; Christoph Berg <myon@debian.org>; pgsql-hackers
## Michael Paquier (michael@paquier.xyz):
> Total bytes and free bytes looks like a good first cut. Have you
> looked at the portability of statfs() on other BSD flavors and
> Solaris?
"The statfs() system call first appeared in 4.4BSD." (from the statfs(2)
manpage on FreeBSD). struct statfs differs between Linux and BSD, but
is "close enough" for this, the fields from the original patch are
present in both implementations.
Solaris does not have statfs() anymore. Instead, it has a statvfs()
which is "more or less equivalent" to the Linux statvfs(). On FreeBSD,
using statvfs() (it's available) is rather not recommended, from the
man page:
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 portable applications must not depend on
this.
That's funny, as statvfs() is in our beloved POSIX.1 since at least
2001 - current specs:
https://pubs.opengroup.org/onlinepubs/9699919799/functions/fstatvfs.html
Regards,
Christoph
--
Spare Space
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Monitoring disk space from within the server
@ 2019-11-11 20:11 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Christoph Berg <myon@debian.org>
2 siblings, 1 reply; 22+ messages in thread
From: Laurenz Albe @ 2019-11-11 20:11 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; pgsql-hackers
On Fri, 2019-11-08 at 14:24 +0100, Christoph Berg wrote:
> Monitoring the available disk space is the topmost thing on the
> priority for PostgreSQL operation, yet this metric is not available
> from the SQL level.
>
> The attached patch implements a function pg_tablespace_statfs(tblspc)
> to report disk space numbers per tablespace:
>
> # select * from pg_tablespace_statfs('pg_default');
> blocks │ bfree │ bavail │ files │ ffree
> ───────────┼──────────┼──────────┼──────────┼──────────
> 103179564 │ 20829222 │ 20815126 │ 26214400 │ 24426295
>
> Open points:
> * should these numbers be converted to bytes?
> * the column names currently mirror the statfs() names and should
> certainly be improved
> * which of these columns add to \db+ output?
> * possibly extend this (and \db) to pg_wal
Will this work on Windows?
A quick web search seems to indicate that Windows has no statfs(2).
What's more is that the Linux man page says that statfs(2) is
Linux-specific.
I think that if we have such a feature (which I think would be useful)
should be available for all operating systems supported by PostgreSQL.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Monitoring disk space from within the server
@ 2019-11-12 01:46 Michael Paquier <michael@paquier.xyz>
parent: Christoph Moench-Tegeder <cmt@burggraben.net>
0 siblings, 1 reply; 22+ messages in thread
From: Michael Paquier @ 2019-11-12 01:46 UTC (permalink / raw)
To: Christoph Moench-Tegeder <cmt@burggraben.net>; +Cc: Julien Rouhaud <rjuju123@gmail.com>; Christoph Berg <myon@debian.org>; pgsql-hackers
On Sat, Nov 09, 2019 at 02:33:49PM +0100, Christoph Moench-Tegeder wrote:
> "The statfs() system call first appeared in 4.4BSD." (from the statfs(2)
> manpage on FreeBSD). struct statfs differs between Linux and BSD, but
> is "close enough" for this, the fields from the original patch are
> present in both implementations.
> Solaris does not have statfs() anymore. Instead, it has a statvfs()
> which is "more or less equivalent" to the Linux statvfs(). On FreeBSD,
> using statvfs() (it's available) is rather not recommended, from the
> man page:
> 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 portable applications must not depend on
> this.
> That's funny, as statvfs() is in our beloved POSIX.1 since at least
> 2001 - current specs:
> https://pubs.opengroup.org/onlinepubs/9699919799/functions/fstatvfs.html
Thanks for looking at that. The point of FreeBSD is interesting to
know. So this basically would leave us with the following hierarchy
to grab the data:
1) statfs()
2) statvfs()
3) Windows-specific implementation
4) Complain if nothing is present
For the free space, then we just need (f_bsize * f_bfree), and the
total is (f_blocks * f_bsize).
Any opinions?
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Monitoring disk space from within the server
@ 2019-11-12 01:47 Michael Paquier <michael@paquier.xyz>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 22+ messages in thread
From: Michael Paquier @ 2019-11-12 01:47 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Christoph Berg <myon@debian.org>; pgsql-hackers
On Mon, Nov 11, 2019 at 09:11:35PM +0100, Laurenz Albe wrote:
> Will this work on Windows?
> A quick web search seems to indicate that Windows has no statfs(2).
It won't. We are actually discussing the compatibility aspects and
the minimal data set we could grab in a different part of the thread.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Monitoring disk space from within the server
@ 2019-11-12 08:47 Daniel Gustafsson <daniel@yesql.se>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 2 replies; 22+ messages in thread
From: Daniel Gustafsson @ 2019-11-12 08:47 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Christoph Moench-Tegeder <cmt@burggraben.net>; Julien Rouhaud <rjuju123@gmail.com>; Christoph Berg <myon@debian.org>; pgsql-hackers
> On 12 Nov 2019, at 02:46, Michael Paquier <michael@paquier.xyz> wrote:
> Any opinions?
I agree with Tomas upthread that it's unclear whether this needs to be in core.
There are many system parameters a database admin is likely to be interested
in, diskspace being just one of them (albeit a very important one for many
reasons), and there is nothing that makes the SQL interface (or postgres core
for that matter) particularly more suited for this job than other existing
tools.
Why is SQL level crucial for this?
cheers ./daniel
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Monitoring disk space from within the server
@ 2019-11-12 09:03 Michael Paquier <michael@paquier.xyz>
parent: Daniel Gustafsson <daniel@yesql.se>
1 sibling, 0 replies; 22+ messages in thread
From: Michael Paquier @ 2019-11-12 09:03 UTC (permalink / raw)
To: Daniel Gustafsson <daniel@yesql.se>; +Cc: Christoph Moench-Tegeder <cmt@burggraben.net>; Julien Rouhaud <rjuju123@gmail.com>; Christoph Berg <myon@debian.org>; pgsql-hackers
On Tue, Nov 12, 2019 at 09:47:47AM +0100, Daniel Gustafsson wrote:
> I agree with Tomas upthread that it's unclear whether this needs to be in core.
> There are many system parameters a database admin is likely to be interested
> in, diskspace being just one of them (albeit a very important one for many
> reasons), and there is nothing that makes the SQL interface (or postgres core
> for that matter) particularly more suited for this job than other existing
> tools.
>
> Why is SQL level crucial for this?
Because this makes the monitoring experience easier from a remote
perspective. FWIW, I have cases it would have been useful to monitor
out the physical amount of space available with the amount of space
covered by bloated tables for a given set of tablespaces through a
single source.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Monitoring disk space from within the server
@ 2019-11-12 09:04 Christoph Berg <myon@debian.org>
parent: Daniel Gustafsson <daniel@yesql.se>
1 sibling, 0 replies; 22+ messages in thread
From: Christoph Berg @ 2019-11-12 09:04 UTC (permalink / raw)
To: Daniel Gustafsson <daniel@yesql.se>; +Cc: Michael Paquier <michael@paquier.xyz>; Christoph Moench-Tegeder <cmt@burggraben.net>; Julien Rouhaud <rjuju123@gmail.com>; pgsql-hackers
Re: Daniel Gustafsson 2019-11-12 <7A3B9BB6-BEA0-466E-98A9-B4DD8F04830E@yesql.se>
> I agree with Tomas upthread that it's unclear whether this needs to be in core.
> There are many system parameters a database admin is likely to be interested
> in, diskspace being just one of them (albeit a very important one for many
> reasons), and there is nothing that makes the SQL interface (or postgres core
> for that matter) particularly more suited for this job than other existing
> tools.
>
> Why is SQL level crucial for this?
Because the figure is interesting to users as well. They will usually
not have any access to monitoring, and checking if they can load this
extra 10 GB dataset is a good use case.
This is about providing the numbers in the place where they are
needed. Of course admins can just go elsewhere to look it up (and
probably will), but I think now there's a usability gap for people who
just have SQL access.
Christoph
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Monitoring disk space from within the server
@ 2019-11-12 09:07 Julien Rouhaud <rjuju123@gmail.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 0 replies; 22+ messages in thread
From: Julien Rouhaud @ 2019-11-12 09:07 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Christoph Berg <myon@debian.org>; pgsql-hackers
On Tue, Nov 12, 2019 at 2:48 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Mon, Nov 11, 2019 at 09:11:35PM +0100, Laurenz Albe wrote:
> > Will this work on Windows?
> > A quick web search seems to indicate that Windows has no statfs(2).
>
> It won't. We are actually discussing the compatibility aspects and
> the minimal data set we could grab in a different part of the thread.
For the record I already mentioned Windows specificity in [1] and
GetDiskFreeSpaceA [2] looks like the function to use on windows.
[1] https://www.postgresql.org/message-id/CAOBaU_Zu6RP6-mHyA_J9-xkxJe0tarTVqU9TFza%2BtCPKUxsjiA%40mail.g...
[2] https://docs.microsoft.com/en-us/windows/win32/api/fileapi/nf-fileapi-getdiskfreespacea
^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2019-11-12 09:07 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-11-08 13:24 Monitoring disk space from within the server Christoph Berg <myon@debian.org>
2019-11-08 13:32 ` Julien Rouhaud <rjuju123@gmail.com>
2019-11-08 13:35 ` Christoph Berg <myon@debian.org>
2019-11-08 13:48 ` Julien Rouhaud <rjuju123@gmail.com>
2019-11-08 13:58 ` Christoph Berg <myon@debian.org>
2019-11-08 14:10 ` Julien Rouhaud <rjuju123@gmail.com>
2019-11-09 01:56 ` Michael Paquier <michael@paquier.xyz>
2019-11-09 13:33 ` Christoph Moench-Tegeder <cmt@burggraben.net>
2019-11-12 01:46 ` Michael Paquier <michael@paquier.xyz>
2019-11-12 08:47 ` Daniel Gustafsson <daniel@yesql.se>
2019-11-12 09:03 ` Michael Paquier <michael@paquier.xyz>
2019-11-12 09:04 ` Christoph Berg <myon@debian.org>
2019-11-08 13:40 ` Christoph Berg <myon@debian.org>
2019-11-08 13:55 ` Julien Rouhaud <rjuju123@gmail.com>
2019-11-08 14:00 ` Christoph Berg <myon@debian.org>
2019-11-08 14:50 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-11-08 15:06 ` Christoph Berg <myon@debian.org>
2019-11-08 15:12 ` Christoph Berg <myon@debian.org>
2019-11-08 21:11 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-11-11 20:11 ` Laurenz Albe <laurenz.albe@cybertec.at>
2019-11-12 01:47 ` Michael Paquier <michael@paquier.xyz>
2019-11-12 09:07 ` Julien Rouhaud <rjuju123@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox