Re: db size and tables size difference - Mailing list pgsql-admin
| From | Isabella Ghiurea |
|---|---|
| Subject | Re: db size and tables size difference |
| Date | |
| Msg-id | 25531211.post@talk.nabble.com Whole thread Raw |
| In response to | db size and tables size difference (Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca>) |
| Responses |
Re: db size and tables size difference
Re: db size and tables size difference |
| List | pgsql-admin |
Hi All,
Tom, please see bellow are the results for the modified query with ORDER
BY
select pg_size_pretty(pg_database_size('db1')); ---->
pg_size_pretty
----------------
12 GB
SELECT 'the table size without table space restrictions';
SELECT nspname || '.' || relname AS
"relation",pg_size_pretty(pg_total_relation_size(nspname || '.' || relname))
AS "s
ize"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
ORDER BY pg_total_relation_size(nspname || '.' || relname) DESC
LIMIT 1000;
------------------------------------------------------+------------
caom.spatialentity | 3216 MB
caom.artifact | 2150 MB
caom.plane | 677 MB
caom.positionsample | 219 MB
caom.simpleobservation | 202 MB
caom.artifact_i1 | 171 MB
caom.spatialentity_i1 | 162 MB
caom.temporalentity | 86 MB
caom.plane_psi2 | 86 MB
caom.spectralentity | 73 MB
caom.metric | 70 MB
caom.plane_energy_i1 | 67 MB
caom.plane_time_i1 | 58 MB
caom.plane_position_i2 | 48 MB
caom.polarizationentity | 33 MB
caom.simpleobservation_i2 | 25 MB
caom.plane_psi1 | 23 MB
caom.metric_i2 | 18 MB
caom.metric_i1 | 18 MB
caom.plane_i1 | 15 MB
caom.plane_position_i3 | 15 MB
caom.plane_polar_i1 | 15 MB
caom.plane_time_i2 | 15 MB
caom.plane_energy_i2 | 15 MB
caom.plane_i2 | 15 MB
caom.simpleobservation_i1 | 12 MB
caom.temporalentity_i1 | 9496 kB
caom.spectralentity_i1 | 4384 kB
caom.polarizationentity_i1 | 4368 kB
caom.harvestskip | 2056 kB
pg_catalog.pg_depend | 1008 kB
pg_catalog.pg_proc | 880 kB
caom.harveststate | 856 kB
pg_catalog.pg_attribute | 648 kB
caom.positionhole | 584 kB
pg_catalog.pg_statistic | 576 kB
caom.plane_phi2 | 496 kB
caom.harvestskip_i1 | 480 kB
pg_catalog.pg_proc_proname_args_nsp_index | 328 kB
pg_catalog.pg_operator | 296 kB
pg_catalog.pg_description | 280 kB
pg_catalog.pg_depend_depender_index | 264 kB
pg_catalog.pg_depend_reference_index | 264 kB
pg_catalog.pg_rewrite | 256 kB
pg_catalog.pg_attribute_relid_attnam_index | 240 kB
pg_toast.pg_toast_2618 | 160 kB
pg_catalog.pg_type | 144 kB
pg_catalog.pg_class | 136 kB
pg_catalog.pg_amop | 128 kB
pg_catalog.pg_operator_oprname_l_r_n_index | 112 kB
pg_catalog.pg_description_o_c_o_index | 96 kB
pg_toast.pg_toast_2619 | 96 kB
pg_catalog.pg_constraint | 80 kB
pg_catalog.pg_conversion | 80 kB
pg_catalog.pg_amproc | 72 kB
pg_catalog.pg_attribute_relid_attnum_index | 72 kB
pg_catalog.pg_proc_oid_index | 72 kB
pg_catalog.pg_opclass | 72 kB
pg_catalog.pg_trigger | 56 kB
pg_catalog.pg_type_typname_nsp_index | 56 kB
pg_catalog.pg_index | 56 kB
information_schema.sql_features | 48 kB
pg_catalog.pg_cast | 48 kB
pg_catalog.pg_class_relname_nsp_index | 48 kB
pg_catalog.pg_database | 48 kB
pg_catalog.pg_authid | 48 kB
pg_catalog.pg_ts_config_map | 48 kB
pg_catalog.pg_opfamily | 48 kB
pg_catalog.pg_language | 40 kB
pg_catalog.pg_shdepend | 40 kB
pg_catalog.pg_ts_parser | 40 kB
pg_catalog.pg_ts_template | 40 kB
pg_catalog.pg_auth_members | 40 kB
pg_catalog.pg_ts_config | 40 kB
pg_catalog.pg_ts_dict | 40 kB
pg_catalog.pg_operator_oid_index | 40 kB
pg_catalog.pg_tablespace | 40 kB
pg_catalog.pg_namespace | 40 kB
pg_catalog.pg_am | 40 kB
pg_toast.pg_toast_45054 | 32 kB
pg_catalog.pg_conversion_name_nsp_index | 32 kB
pg_catalog.pg_shdescription | 32 kB
tap_schema.tables | 32 kB
pg_catalog.pg_statistic_relid_att_index | 32 kB
pg_catalog.pg_amop_fam_strat_index | 32 kB
tap_schema.schemas | 32 kB
pg_catalog.pg_amproc_fam_proc_index | 32 kB
pg_catalog.pg_ts_config_map_index | 32 kB
tap_schema.columns | 32 kB
caom.plane_phi1 | 32 kB
pg_catalog.pg_amop_oid_index | 32 kB
pg_catalog.pg_amop_opr_fam_index | 32 kB
pg_catalog.pg_opclass_am_name_nsp_index | 32 kB
caom.deletedplane | 24 kB
pg_catalog.pg_pltemplate | 24 kB
pg_catalog.pg_attrdef | 24 kB
tap_schema.keys | 24 kB
caom.deletedspatialentity | 24 kB
caom.deletedpolarizationentity | 24 kB
caom.compositeobservation | 24 kB
caom.deletedtemporalentity | 24 kB
caom.deletedartifact | 24 kB
caom.deletedspectralentity | 24 kB
pg_catalog.pg_aggregate | 24 kB
information_schema.sql_implementation_info | 16 kB
information_schema.sql_languages | 16 kB
pg_catalog.pg_enum | 16 kB
pg_catalog.pg_ts_config_oid_index | 16 kB
pg_catalog.pg_trigger_oid_index | 16 kB
tap_schema.schemas_pkey | 16 kB
pg_catalog.pg_constraint_conrelid_index | 16 kB
pg_catalog.pg_conversion_default_index | 16 kB
pg_catalog.pg_namespace_oid_index | 16 kB
pg_catalog.pg_namespace_nspname_index | 16 kB
pg_catalog.pg_cast_source_target_index | 16 kB
pg_catalog.pg_constraint_contypid_index | 16 kB
information_schema.sql_packages | 16 kB
pg_catalog.pg_aggregate_fnoid_index | 16 kB
pg_catalog.pg_rewrite_oid_index | 16 kB
pg_toast.pg_toast_2618_index | 16 kB
pg_catalog.pg_trigger_tgconstrname_index | 16 kB
pg_catalog.pg_trigger_tgrelid_tgname_index | 16 kB
information_schema.sql_parts | 16 kB
pg_catalog.pg_authid_rolname_index | 16 kB
pg_catalog.pg_authid_oid_index | 16 kB
pg_catalog.pg_auth_members_role_member_index | 16 kB
pg_catalog.pg_auth_members_member_role_index | 16 kB
information_schema.sql_sizing | 16 kB
tap_schema.keys_pkey | 16 kB
tap_schema.columns_pkey | 16 kB
caom.deletedplane_i1 | 16 kB
pg_catalog.pg_database_datname_index | 16 kB
pg_catalog.pg_database_oid_index | 16 kB
caom.deletedpolarizationentity_i1 | 16 kB
pg_catalog.pg_shdescription_o_c_index | 16 kB
pg_catalog.pg_language_name_index | 16 kB
pg_catalog.pg_language_oid_index | 16 kB
pg_catalog.pg_index_indrelid_index | 16 kB
caom.timesample | 16 kB
pg_catalog.pg_am_name_index | 16 kB
pg_toast.pg_toast_2619_index | 16 kB
pg_catalog.pg_constraint_conname_nsp_index | 16 kB
pg_catalog.pg_index_indexrelid_index | 16 kB
pg_catalog.pg_am_oid_index | 16 kB
pg_catalog.pg_pltemplate_name_index | 16 kB
pg_catalog.pg_shdepend_depender_index | 16 kB
pg_catalog.pg_shdepend_reference_index | 16 kB
pg_catalog.pg_tablespace_oid_index | 16 kB
pg_catalog.pg_tablespace_spcname_index | 16 kB
pg_toast.pg_toast_45054_index | 16 kB
caom.deletedtemporalentity_i1 | 16 kB
pg_catalog.pg_ts_dict_oid_index | 16 kB
caom.deletedspectralentity_i1 | 16 kB
caom.deletedspatialentity_i1 | 16 kB
caom.deletedartifact_i1 | 16 kB
pg_catalog.pg_constraint_oid_index | 16 kB
pg_catalog.pg_opfamily_am_name_nsp_index | 16 kB
pg_catalog.pg_opfamily_oid_index | 16 kB
pg_catalog.pg_opclass_oid_index | 16 kB
pg_catalog.pg_amproc_oid_index | 16 kB
pg_catalog.pg_class_oid_index | 16 kB
pg_catalog.pg_rewrite_rel_rulename_index | 16 kB
pg_catalog.pg_ts_config_cfgname_index | 16 kB
tap_schema.tables_pkey | 16 kB
Thank you
Isabella
Hi All,
Please, see more info my env: PG 8.3.6 on RHE5-64bits.
1. there are more than one schemas, but the size of the tables is close to
30-40kB, see some samples
schemaname | tablename | size_pretty | total_size_pretty
------------+-------------+-------------+-------------------
tap_schema | tables | 8192 bytes | 32 kB
tap_schema | columns | 8192 bytes | 32 kB
tap_schema | schemas | 8192 bytes | 32 kB
tap_schema | keys | 8192 bytes | 24 kB
tap_schema | key_columns | 8192 bytes | 8192 bytes
2. There are no BLOB's data type in db at this time :
cvodb=# select * from pg_largeobject;
loid | pageno | data
------+--------+------
(0 rows)
3. As Tom suggested , I excluded the table space restriction and changed
to pg_total_relation_size my original SQL :
SELECT 'the table size without table space restrictions';
SELECT nspname || '.' || relname AS
"relation",pg_size_pretty(pg_total_relation_size(nspname || '.' || relname))
AS "s
ize"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
ORDER BY pg_relation_size(nspname || '.' || relname) DESC
LIMIT 20;
the table size without table space restrictions
(1 row)
relation | size
---------------------------+---------
caom.spatialentity | 3216 MB
caom.artifact | 2150 MB
caom.plane | 677 MB
caom.artifact_i1 | 171 MB
caom.simpleobservation | 202 MB
caom.spatialentity_i1 | 162 MB
caom.positionsample | 219 MB
caom.plane_psi2 | 86 MB
caom.temporalentity | 86 MB
caom.spectralentity | 73 MB
caom.plane_energy_i1 | 67 MB
caom.plane_time_i1 | 58 MB
caom.plane_position_i2 | 48 MB
caom.metric | 70 MB
caom.polarizationentity | 33 MB
caom.simpleobservation_i2 | 25 MB
caom.plane_psi1 | 23 MB
caom.metric_i2 | 18 MB
caom.metric_i1 | 18 MB
caom.plane_position_i3 | 15 MB
(20 rows)
4. Where are the rest of 5,5 GB been used ? How can I get the system
catalog correct size ?
Thank you,
Isabella
Tom Lane wrote:
>
> Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca> writes:
> > I'm trying to understand why there are GB's difference when checking
> > for db size using pg_size_pretty() and querying for tables + indexes
> > size. .
>
> You are not counting everything --- the total DB size is clearly 12GB,
> so the question is where are the other 5.5GB? Your first query shows
> that schema caom accounts for 6+GB, but the second one does not prove
> that schema caom contains all the big hogs. My guesses are:
>
> 1. Toast tables for tables that aren't in caom --- you used
> pg_relation_size not pg_total_relation_size, and excluded toast
> tables, so you are missing those.
>
> 2. pg_largeobject ... got any large objects?
>
> 3. Bloat in other system catalogs. 5GB of catalog bloat would be
> pretty awful, but maybe that's what it is.
>
> Try that last query without the namespace restrictions.
>
>
> regards, tom lane
Hi Pg Admin list.
I'm trying to understand why there are GB's difference when checking for
db size using pg_size_pretty() and querying for tables + indexes size. .
The sum of tables +index sizes is showing as aprox 6.5GB and
pg_size_pretty(dbname) is coming as 12GB, this are the results after a full
vacuum and reindexdb, also the sum of OS db files size is ~ 6.5GB. Any tips
what I'm missing : are some "unallocated" db pages or anything else ?
select pg_size_pretty(pg_database_size('db1'));
pg_size_pretty
----------------
12 GB
(1 row)
*** Check for tables size :
SELECT schemaname, tablename,
pg_size_pretty(size) AS size_pretty,
pg_size_pretty(total_size) AS total_size_pretty
FROM (SELECT *,
pg_relation_size(schemaname||'.'||tablename) AS
size,pg_total_relation_size(schemaname||'.'||tablename) AS
total_size FROM pg_tables) AS TABLES where schemaname='caom'
ORDER BY total_size DESC;
schemaname | tablename | size_pretty | total_size_pretty
------------+-----------------------------+-------------+-------------------
caom | spatialentity | 3053 MB | 3216 MB
caom | artifact | 1979 MB | 2150 MB
caom | plane | 413 MB | 677 MB
caom | positionsample | 110 MB | 219 MB
caom | simpleobservation | 165 MB | 202 MB
caom | temporalentity | 77 MB | 86 MB
caom | spectralentity | 68 MB | 73 MB
caom | metric | 33 MB | 70 MB
caom | polarizationentity | 29 MB | 33 MB
caom | harvestskip | 1576 kB | 2056 kB
caom | harveststate | 840 kB | 856 kB
caom | positionhole | 48 kB | 584 kB
*** OR : check for the biggest tables+index size:
SELECT ' Top 20 biggest tables and indexes'
;
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_relation_size(nspname || '.' || relname)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND nspname !~ '^pg_toast'
AND pg_relation_size(nspname || '.' || relname)>0
ORDER BY pg_relation_size(nspname || '.' || relname) DESC
LIMIT 20;
(1 row)
relation | size
---------------------------+---------
caom.spatialentity | 3053 MB
caom.artifact | 1979 MB
caom.plane | 413 MB
caom.artifact_i1 | 171 MB
caom.simpleobservation | 165 MB
caom.spatialentity_i1 | 162 MB
caom.positionsample | 110 MB
caom.plane_psi2 | 86 MB
caom.temporalentity | 77 MB
caom.spectralentity | 68 MB
caom.plane_energy_i1 | 67 MB
caom.plane_time_i1 | 58 MB
caom.plane_position_i2 | 48 MB
caom.metric | 33 MB
caom.polarizationentity | 29 MB
caom.simpleobservation_i2 | 25 MB
caom.plane_psi1 | 23 MB
caom.metric_i2 | 18 MB
Thank you
Isabella
--
View this message in context:
http://www.nabble.com/Re%3A--ADMIN--db-size-and-tables-size-difference-tp25531211p25531211.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
pgsql-admin by date: