Re: db size and tables size difference

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi All,
looking in more details on OS partitions sizwe  and each table space
corresponding to OS partitions will add up to  close to 6,5GB   for db size 
same result as SQL table size.
The issue may be   with pg_size_pretty()  results, I don't have details
knowledge of this function.
select pg_size_pretty(pg_database_size('db1'));
pg_size_pretty
----------------
12 GB 

Isabella


Isabella Ghiurea wrote:
> 
>  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@xxxxxxxxxxxxxx> 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-tp25531211p25531314.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


-- 
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux