Checking Database Object Sizes and Disk Space in Greenplum using gp_toolkit schema views
Post date: Jan 21, 2014 2:20:39 AM
The following views calculate the total size of an object across all primary segments (mirrors are not included in the size calculations).
1. gp_size_of_all_table_indexes
2. gp_size_of_database
3. gp_size_of_index
4. gp_size_of_partition_and_indexes_disk
5. gp_size_of_schema_disk
6. gp_size_of_table_and_indexes_disk
7. gp_size_of_table_and_indexes_licensing
8. gp_size_of_table_disk
9. gp_size_of_table_uncompressed
10. gp_disk_free
1. gp_size_of_all_table_indexes
gpadmin=# \d gp_toolkit.gp_size_of_all_table_indexes
View "gp_toolkit.gp_size_of_all_table_indexes"
Column | Type | Modifiers
-----------------+---------+-----------
soatioid | oid |
soatisize | numeric |
soatischemaname | name |
soatitablename | name |
View definition:
SELECT soati.soatioid, soati.soatisize, fn.fnnspname AS soatischemaname, fn.fnrelname AS soatitablename
FROM ( SELECT ti.tireloid AS soatioid, sum(pg_relation_size(ti.tiidxoid)) AS soatisize
FROM gp_toolkit.gp_table_indexes ti
GROUP BY ti.tireloid) soati
JOIN gp_toolkit.__gp_fullname fn ON soati.soatioid = fn.fnoid;
This view shows the total size of all indexes for a table. This view is accessible to all users, however non-superusers will only be able to see relations that they have permission to access.
soatioid=>The object ID of the table
soatisize=>The total size of all table indexes in bytes
soatischemaname=>The schema name
soatitablename=>The table name
2. gp_size_of_database
gpadmin=# \d gp_toolkit.gp_size_of_database
View "gp_toolkit.gp_size_of_database"
Column | Type | Modifiers
-------------+--------+-----------
sodddatname | name |
sodddatsize | bigint |
View definition:
SELECT pg_database.datname AS sodddatname, pg_database_size(pg_database.oid) AS sodddatsize
FROM pg_database
WHERE pg_database.datname <> 'template0'::name AND pg_database.datname <> 'template1'::name AND pg_database.datname <> 'postgres'::name;
This view shows the total size of a database. This view is accessible to all users, however non-superusers will only be able to see databases that they have permission to access.
sodddatname=>The name of the database
sodddatsize=>The size of the database in bytes
3. gp_size_of_index
gpadmin=# \d gp_toolkit.gp_size_of_index
View "gp_toolkit.gp_size_of_index"
Column | Type | Modifiers
--------------------+--------+-----------
soioid | oid |
soitableoid | oid |
soisize | bigint |
soiindexschemaname | name |
soiindexname | name |
soitableschemaname | name |
soitablename | name |
View definition:
SELECT soi.soioid, soi.soitableoid, soi.soisize, fnidx.fnnspname AS soiindexschemaname, fnidx.fnrelname AS soiindexname, fntbl.fnnspname AS soitableschemaname, fntbl.fnrelname AS soitablename
FROM ( SELECT pgi.indexrelid AS soioid, pgi.indrelid AS soitableoid, pg_relation_size(pgi.indexrelid) AS soisize
FROM pg_index pgi
JOIN gp_toolkit.__gp_user_data_tables_readable ut ON pgi.indrelid = ut.autoid) soi
JOIN gp_toolkit.__gp_fullname fnidx ON soi.soioid = fnidx.fnoid
JOIN gp_toolkit.__gp_fullname fntbl ON soi.soitableoid = fntbl.fnoid;
This view shows the total size of an index. This view is accessible to all users, however non-superusers will only be able to see relations that they have permission to access.
soioid=>The object ID of the index
soitableoid=>The object ID of the table to which the index belongs
soisize=>The size of the index in bytes
soiindexschemaname=>The name of the index schema
soiindexname=>The name of the index
soitableschemaname=>The name of the table schema
soitablename=>The name of the table
4. gp_size_of_partition_and_indexes_disk
gpadmin=# \d gp_toolkit.gp_size_of_partition_and_indexes_disk
View "gp_toolkit.gp_size_of_partition_and_indexes_disk"
Column | Type | Modifiers
----------------------------+---------+-----------
sopaidparentoid | oid |
sopaidpartitionoid | oid |
sopaidpartitiontablesize | bigint |
sopaidpartitionindexessize | numeric |
sopaidparentschemaname | name |
sopaidparenttablename | name |
sopaidpartitionschemaname | name |
sopaidpartitiontablename | name |
View definition:
SELECT sopaid.sopaidparentoid, sopaid.sopaidpartitionoid, sopaid.sopaidpartitiontablesize, sopaid.sopaidpartitionindexessize, fnparent.fnnspname AS sopaidparentschemaname, fnparent.fnrelname AS sopaidparenttablename, fnpart.fnnspname AS sopaidpartitionschemaname, fnpart.fnrelname AS sopaidpartitiontablename
FROM ( SELECT pgp.parrelid AS sopaidparentoid, pgpr.parchildrelid AS sopaidpartitionoid, sotd.sotdsize + sotd.sotdtoastsize + sotd.sotdadditionalsize AS sopaidpartitiontablesize, COALESCE(soati.soatisize, 0::numeric) AS sopaidpartitionindexessize
FROM pg_partition pgp
JOIN pg_partition_rule pgpr ON pgp.oid = pgpr.paroid
JOIN gp_toolkit.gp_size_of_table_disk sotd ON sotd.sotdoid = pgpr.parchildrelid
LEFT JOIN gp_toolkit.gp_size_of_all_table_indexes soati ON soati.soatioid = pgpr.parchildrelid) sopaid
JOIN gp_toolkit.__gp_fullname fnparent ON sopaid.sopaidparentoid = fnparent.fnoid
JOIN gp_toolkit.__gp_fullname fnpart ON sopaid.sopaidpartitionoid = fnpart.fnoid;
This view shows the size on disk of partitioned child tables and their indexes. This view is accessible to all users, however non-superusers will only be able to see relations that they have permission to access.
sopaidparentoid=>The object ID of the parent table
sopaidpartitionoid=>The object ID of the partition table
sopaidpartitiontablesize=>The partition table size in bytes
sopaidpartitionindexessize=>The total size of all indexes on this partition
Sopaidparentschemaname=>The name of the parent schema
Sopaidparenttablename=>The name of the parent table
Sopaidpartitionschemaname=>The name of the partition schema
sopaidpartitiontablename=>The name of the partition table
5. gp_size_of_schema_disk
gpadmin=# \d gp_toolkit.gp_size_of_schema_disk
View "gp_toolkit.gp_size_of_schema_disk"
Column | Type | Modifiers
---------------------+---------+-----------
sosdnsp | name |
sosdschematablesize | numeric |
sosdschemaidxsize | numeric |
View definition:
SELECT un.aunnspname AS sosdnsp, COALESCE(sum(sotaid.sotaidtablesize), 0::numeric) AS sosdschematablesize, COALESCE(sum(sotaid.sotaididxsize), 0::numeric) AS sosdschemaidxsize
FROM gp_toolkit.gp_size_of_table_and_indexes_disk sotaid
JOIN gp_toolkit.__gp_fullname fn ON sotaid.sotaidoid = fn.fnoid
RIGHT JOIN gp_toolkit.__gp_user_namespaces un ON un.aunnspname = fn.fnnspname
GROUP BY un.aunnspname;
This view shows schema sizes for the public schema and the user-created schemas in the current database. This view is accessible to all users, however non-superusers will be able to see only the schemas that they have permission to access.
sosdnsp=>The name of the schema
sosdschematablesize=>The total size of tables in the schema in bytes
sosdschemaidxsize=>The total size of indexes in the schema in bytes
6. gp_size_of_table_and_indexes_disk
gpadmin=# \d gp_toolkit.gp_size_of_table_and_indexes_disk
View "gp_toolkit.gp_size_of_table_and_indexes_disk"
Column | Type | Modifiers
------------------+---------+-----------
sotaidoid | oid |
sotaidtablesize | bigint |
sotaididxsize | numeric |
sotaidschemaname | name |
sotaidtablename | name |
View definition:
SELECT sotaid.sotaidoid, sotaid.sotaidtablesize, sotaid.sotaididxsize, fn.fnnspname AS sotaidschemaname, fn.fnrelname AS sotaidtablename
FROM ( SELECT sotd.sotdoid AS sotaidoid, sotd.sotdsize + sotd.sotdtoastsize + sotd.sotdadditionalsize AS sotaidtablesize,
CASE
WHEN soati.soatisize IS NULL THEN 0::numeric
ELSE soati.soatisize
END AS sotaididxsize
FROM gp_toolkit.gp_size_of_table_disk sotd
LEFT JOIN gp_toolkit.gp_size_of_all_table_indexes soati ON sotd.sotdoid = soati.soatioid) sotaid
JOIN gp_toolkit.__gp_fullname fn ON sotaid.sotaidoid = fn.fnoid;
This view shows the size on disk of tables and their indexes. This view is accessible to all users, however non-superusers will only be able to see relations that they have permission to access.
sotaidoid=>The object ID of the parent table
sotaidtablesize=>The disk size of the table
sotaididxsize=>The total size of all indexes on the table
sotaidschemaname=>The name of the schema
sotaidtablename=>The name of the table
7. gp_size_of_table_and_indexes_licensing
gpadmin=# \d gp_toolkit.gp_size_of_table_and_indexes_licensing
View "gp_toolkit.gp_size_of_table_and_indexes_licensing"
Column | Type | Modifiers
-----------------------------+------------------+-----------
sotailoid | oid |
sotailtablesizedisk | bigint |
sotailtablesizeuncompressed | double precision |
sotailindexessize | numeric |
sotailschemaname | name |
sotailtablename | name |
View definition:
SELECT sotail.sotailoid, sotail.sotailtablesizedisk, sotail.sotailtablesizeuncompressed, sotail.sotailindexessize, fn.fnnspname AS sotailschemaname, fn.fnrelname AS sotailtablename
FROM ( SELECT sotu.sotuoid AS sotailoid, sotaid.sotaidtablesize AS sotailtablesizedisk, sotu.sotusize AS sotailtablesizeuncompressed, sotaid.sotaididxsize AS sotailindexessize
FROM gp_toolkit.gp_size_of_table_uncompressed sotu
JOIN gp_toolkit.gp_size_of_table_and_indexes_disk sotaid ON sotu.sotuoid = sotaid.sotaidoid) sotail
JOIN gp_toolkit.__gp_fullname fn ON sotail.sotailoid = fn.fnoid;
This view shows the total size of tables and their indexes for licensing purposes. The use of this view requires superuser permissions.
sotailoid=>The object ID of the table
sotailtablesizedisk=>The total disk size of the table
sotailtablesizeuncompressed=>If the table is a compressed append-optimized table, shows the uncompressed table size in bytes.
sotailindexessize=>The total size of all indexes in the table
sotailschemaname=>The schema name
sotailtablename=>The table name
8. gp_size_of_table_disk
gpadmin=# \d gp_toolkit.gp_size_of_table_disk
View "gp_toolkit.gp_size_of_table_disk"
Column | Type | Modifiers
--------------------+--------+-----------
sotdoid | oid |
sotdsize | bigint |
sotdtoastsize | bigint |
sotdadditionalsize | bigint |
sotdschemaname | name |
sotdtablename | name |
View definition:
SELECT sotd.sotdoid, sotd.sotdsize, sotd.sotdtoastsize, sotd.sotdadditionalsize, fn.fnnspname AS sotdschemaname, fn.fnrelname AS sotdtablename
FROM ( SELECT udtr.autoid AS sotdoid, pg_relation_size(udtr.autoid) AS sotdsize,
CASE
WHEN udtr.auttoastoid > 0::oid THEN pg_total_relation_size(udtr.auttoastoid)
ELSE 0::bigint
END AS sotdtoastsize,
CASE
WHEN ao.segrelid IS NOT NULL AND ao.segrelid > 0::oid THEN pg_total_relation_size(ao.segrelid)
ELSE 0::bigint
END +
CASE
WHEN ao.blkdirrelid IS NOT NULL AND ao.blkdirrelid > 0::oid THEN pg_total_relation_size(ao.blkdirrelid)
ELSE 0::bigint
END AS sotdadditionalsize
FROM ( SELECT __gp_user_data_tables_readable.autnspname, __gp_user_data_tables_readable.autrelname, __gp_user_data_tables_readable.autrelkind, __gp_user_data_tables_readable.autreltuples, __gp_user_data_tables_readable.autrelpages, __gp_user_data_tables_readable.autrelacl, __gp_user_data_tables_readable.autoid, __gp_user_data_tables_readable.auttoastoid, __gp_user_data_tables_readable.autrelstorage
FROM gp_toolkit.__gp_user_data_tables_readable
WHERE __gp_user_data_tables_readable.autrelstorage <> 'x'::"char") udtr
LEFT JOIN pg_appendonly ao ON udtr.autoid = ao.relid) sotd
JOIN gp_toolkit.__gp_fullname fn ON sotd.sotdoid = fn.fnoid;
This view shows the size of a table on disk. This view is accessible to all users, however non-superusers will only be able to see tables that they have permission to access.
sotdoid=>The object ID of the table
sotdsize=>The total size of the table in bytes (main relation, plus oversized (toast) attributes, plus additional storage objects for AO tables).
sotdtoastsize=>The size of the TOAST table (oversized attribute storage), if there is one.
sotdadditionalsize=>Reflects the segment and block directory table sizes for append-optimized (AO) tables.
sotdschemaname=>The schema name
sotdtablename=>The table name
9. gp_size_of_table_uncompressed
gpadmin=# \d gp_toolkit.gp_size_of_table_uncompressed
View "gp_toolkit.gp_size_of_table_uncompressed"
Column | Type | Modifiers
----------------+------------------+-----------
sotuoid | oid |
sotusize | double precision |
sotuschemaname | name |
sotutablename | name |
View definition:
SELECT sotu.sotuoid, sotu.sotusize, fn.fnnspname AS sotuschemaname, fn.fnrelname AS sotutablename
FROM ( SELECT sotd.sotdoid AS sotuoid,
CASE
WHEN iao.iaotype THEN
CASE
WHEN pg_relation_size(sotd.sotdoid) = 0 THEN 0::double precision
ELSE pg_relation_size(sotd.sotdoid)::double precision *
CASE
WHEN get_ao_compression_ratio(sotd.sotdoid) = (-1)::double precision THEN NULL::double precision
ELSE get_ao_compression_ratio(sotd.sotdoid)
END
END
ELSE sotd.sotdsize::double precision
END + sotd.sotdtoastsize::double precision + sotd.sotdadditionalsize::double precision AS sotusize
FROM gp_toolkit.gp_size_of_table_disk sotd
JOIN gp_toolkit.__gp_is_append_only iao ON sotd.sotdoid = iao.iaooid) sotu
JOIN gp_toolkit.__gp_fullname fn ON sotu.sotuoid = fn.fnoid;
This view shows the uncompressed table size for append-optimized (AO) tables. Otherwise, the table size on disk is shown. The use of this view requires superuser permissions.
sotuoid=>The object ID of the table
sotusize=>The uncomressed size of the table in bytes if it is a compressed AO table. Otherwise, the table size on disk.
sotuschemaname=>The schema name
sotutablename=>The table name
10. gp_disk_free
gpadmin=# \d gp_toolkit.gp_disk_free
External table "gp_toolkit.gp_disk_free"
Column | Type | Modifiers
------------+---------+-----------
dfsegment | integer |
dfhostname | text |
dfdevice | text |
dfspace | bigint |
Type: readable
Encoding: UTF8
Format type: csv
Format options: delimiter ',' null '' escape '"' quote '"'
Command: python -c "from gppylib.commands import unix; df=unix.DiskFree.get_disk_free_info_local('token','$GP_SEG_DATADIR'); print '%s, %s, %s, %s' % ('$GP_SEGMENT_ID', unix.getLocalHostname(), df[0], df[3])"
Execute on: all segments
This external table runs the df (disk free) command on the active segment hosts and reports back the results. Inactive mirrors are not included in the calculation. The use of this external table requires superuser permissions.
dfsegment=>The content id of the segment (only active segments are shown)
dfhostname=>The hostname of the segment host
dfdevice=>The device name
dfspace=>Free disk space in the segment file system in kilobytes