Thread: We want to monitor total size of database

We want to monitor total size of database

From

Yumiko Izumi

Date:

28 December 2005, 01:42:05

Hello.

We want to monitor the following items with respect to PostgreSQL.
*Total Size of database
*Size of used space of the entire database
*Size of free space of the entire database
*Size of fragmentation area of the entire database

But the following items are getting monitored in PostgreSQL7.3.8.
("Pgstattuple" is used for monitoring)
*Total of sizes of all the tables in database
*Total of used space by all the tables in database
*Total of free space of all the tables in database
*Total of fragmentation area of all the tables in database

In short, in 7.3.8 we are not able to acquire the information on the space
related to
index but the information of the partial area of the database is monitored.

Therefore, we are in search of the method to monitor the space of the entire
database.

Our question is:
Is there any method available in PostgreSQL8.1.0 for monitoring the space of
the entire database ?

Best Regards,

Re: We want to monitor total size of database

From

Larry Rosenman

Date:

28 December 2005, 13:00:12

On Dec 27 2005, Yumiko Izumi wrote:

> Hello.
>
> We want to monitor the following items with respect to PostgreSQL.
> *Total Size of database
> *Size of used space of the entire database
> *Size of free space of the entire database
> *Size of fragmentation area of the entire database
>
> But the following items are getting monitored in PostgreSQL7.3.8.
> ("Pgstattuple" is used for monitoring)
> *Total of sizes of all the tables in database
> *Total of used space by all the tables in database
> *Total of free space of all the tables in database
> *Total of fragmentation area of all the tables in database
>
> In short, in 7.3.8 we are not able to acquire the information on the
> space related to index but the information of the partial area of the
> database is monitored.
>
> Therefore, we are in search of the method to monitor the space of the
> entire database.
>
> Our question is: Is there any method available in PostgreSQL8.1.0 for
> monitoring the space of the entire database ?

I believe that the pgstat* functions were improved in 8.1 to include
this information.

--
Larry Rosenman, Database Support Engineer, E-Mail:
Larry.Rosenman@pervasive.com Pervasive Software, 12365B Riata Trace
Parkway, Austin, TX 78727 Office: 512-231-6173

Re: We want to monitor total size of database

From

Jaime Casanova

Date:

28 December 2005, 13:22:02

On 12/27/05, Yumiko Izumi <izumi-yumiko@scnet.co.jp> wrote:
> Hello.
>
> We want to monitor the following items with respect to PostgreSQL.
> *Total Size of database
> *Size of used space of the entire database
> *Size of free space of the entire database
> *Size of fragmentation area of the entire database
>
> But the following items are getting monitored in PostgreSQL7.3.8.
> ("Pgstattuple" is used for monitoring)
> *Total of sizes of all the tables in database
> *Total of used space by all the tables in database
> *Total of free space of all the tables in database
> *Total of fragmentation area of all the tables in database
>
> In short, in 7.3.8 we are not able to acquire the information on the space
> related to
> index but the information of the partial area of the database is monitored.
>
> Therefore, we are in search of the method to monitor the space of the entire
> database.
>
> Our question is:
> Is there any method available in PostgreSQL8.1.0 for monitoring the space of
> the entire database ?
>
> Best Regards,
>
>

yes... in 8.1.x there are specific functions to do this...
http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Re: We want to monitor total size of database

From

Yumiko Izumi

Date:

05 January 2006, 03:00:48

Thank you for a reply.

> yes... in 8.1.x there are specific functions to do this...
> http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE

We understand that the following is acquirable in
pg_total_relation_size().
*Total Size of database

However, now, the following is unacquirable.
*Size of used space of the entire database
*Size of free space of the entire database
*Size of fragmentation area of the entire database

Is there any method of acquiring the above?

Thanks.

Re: We want to monitor total size of database

From

Jaime Casanova

Date:

05 January 2006, 05:17:05

On 1/4/06, Yumiko Izumi <izumi-yumiko@scnet.co.jp> wrote:
> Thank you for a reply.
>
> > yes... in 8.1.x there are specific functions to do this...
> >
> http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE
>
> We understand that the following is acquirable in
> pg_total_relation_size().
> *Total Size of database
>

no, this will give you the size of a table and all its indexes and toast data...

> However, now, the following is unacquirable.
> *Size of used space of the entire database

pg_database_size(name)

or you mean the size of all databases?

select sum(pg_database_size(datname)) from pg_database;

> *Size of free space of the entire database

free space? all available in disk

> *Size of fragmentation area of the entire database
>

VACUUM FULL is used to avoid fragmentation as much as possible... so i
suppose there is a way to know that...

> Is there any method of acquiring the above?
>
> Thanks.
>
>




--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Re: We want to monitor total size of database

From

Yumiko Izumi

Date:

05 January 2006, 05:52:45

Thank you for a reply.

And I am sorry as explanation was lacking.

> > We understand that the following is acquirable in
> > pg_total_relation_size().
> > *Total Size of database
> >
>
> no, this will give you the size of a table and all its indexes and toast data...
>
> > However, now, the following is unacquirable.
> > *Size of used space of the entire database
>
> pg_database_size(name)
>
> or you mean the size of all databases?
>
> select sum(pg_database_size(datname)) from pg_database;
>
> > *Size of free space of the entire database
>
> free space? all available in disk
>
> > *Size of fragmentation area of the entire database
> >
>
> VACUUM FULL is used to avoid fragmentation as much as possible... so i
> suppose there is a way to know that...
>

We understand that the size of the whole database is acquirable
by carrying out pg_total_relation_size() to all the tables in a database.

For example, in pgstattuple(), a result is outputted as follows.
*Size of the table
*Used space by the table
*Free space of the table
*Fragmentation area of the table

testDB=# select * from pgstattuple('test1');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent |
free_space| free_percent 

-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
     49152 |           9 |      4072 |          8.28 |                8 |            3240|               6.59 |
41244|        83.91 
(1 row)

Although only the size of a database is acquirable in pg_database_size(),
since we want to know used space by the database, free space of the
database, and fragmentation area of the database, we use pgstattuple(),
and are totaling and using the result.

However, neither space of the index nor the toasted space
is included in the result of pgstattuple().

We want to know whether there is any method of acquiring the same
information as pgstattuple() also including space of the index
or the toasted space to the result in V8.1.0.

Thanks.