size of table + toasted tables + indexes != pg_total_relation_size

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

 



Hi,
I'm trying to investigate some storage issues at one of my customers environments: 

One of the databases occupies too much storage (almost1T) : 
select t1.datname AS db_name,
       pg_size_pretty(pg_database_size(t1.datname)) as db_size
from pg_database t1
order by pg_database_size(t1.datname) desc

output : 
image.png

When I searched for the biggest objects in DB1 I saw that the total sum isnt even close to 100G :
select a.relname as table_name,pg_relation_size(a.oid, 'main')/1024/1024 as main_MB,
                pg_relation_size(a.oid, 'fsm')/1024/1024 as fsm_MB,
                pg_relation_size(a.oid, 'vm')/1024/1024 as vm_MB,
                pg_relation_size(a.oid, 'init')/1024/1024 as init_MB,
                pg_table_size(a.oid)/1024/1024 AS relation_size_mb,              pg_indexes_size(a.oid)/1024/1024 as indexes_MB,
                pg_total_relation_size(a.oid)/1024/1024 as total_size_MB from pg_class a where relkind in ('r','t')  order by relation_size_mb desc,total_size_MB desc limit 100;

image.png
pg_toast_17610 is the session`s toasted table
pg_toast_17315 is the attachment`s toasted table.

The local dba used to run vacuum full on a daily basis on those tables. As a result of that, every time autovacuum tried to run on those tables it stopped(saw it in the logs..).

Now, I thought that the pg_total_relation_size should include also all the dead tuples and the toasted tables. Why then the total_size of the  sessions table doesnt include the pg_toast_17610 ?

In addition, I saw from pg_stat_activity that the session table has about 198 dead tuples and the toasted table has about 83833 dead tuples. It seems that the autoanalyze has never run on the toasted table. 

Any idea what is the root cause then for the huge database size ? why the total size of the table doesnt include the toasted table ?

Thanks.

Aw
252 916

[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