pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedsize of table + toasted tables + indexes != pg_total_relation_size
5+ messages / 3 participants
[nested] [flat]
* size of table + toasted tables + indexes != pg_total_relation_size
@ 2018-11-04 10:14 Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Mariel Cherkassky @ 2018-11-04 10:14 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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: 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: 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
Attachments:
image.png (image/png, 13.1K) download
view image
image.png (image/png, 4.4K) download
view image
* Re: size of table + toasted tables + indexes != pg_total_relation_size
@ 2018-11-04 12:12 Andrew Gierth <andrew@tao11.riddles.org.uk>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Andrew Gierth @ 2018-11-04 12:12 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
>>>>> "Mariel" == Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
Mariel> output :
Mariel> image.png
In future please use plain text for query output, not screenshots
Mariel> When I searched for the biggest objects in DB1 I saw that the
Mariel> total sum isnt even close to 100G :
You should probably check whether there are files in the db's directory
that do not correspond to the filenode of any table (use
select pg_relation_filenode(oid) from pg_class; to get a list of
filenodes)
Crashes (or other unclean shutdown) in the middle of doing a vacuum full
or other table-rewriting operation is known to leave orphaned files
around in the data dir.
Mariel> Now, I thought that the pg_total_relation_size should include
Mariel> also all the dead tuples and the toasted tables.
It does, and your results agree with that. Perhaps you missed that
pg_table_size() includes the toast table too?
Mariel> In addition, I saw from pg_stat_activity that the session table
Mariel> has about 198 dead tuples and the toasted table has about 83833
Mariel> dead tuples. It seems that the autoanalyze has never run on the
Mariel> toasted table.
There's nothing in a toast table that needs analyzing.
--
Andrew (irc:RhodiumToad)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: size of table + toasted tables + indexes != pg_total_relation_size
@ 2018-11-04 15:09 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Andrew Gierth <andrew@tao11.riddles.org.uk>
0 siblings, 1 reply; 5+ messages in thread
From: Mariel Cherkassky @ 2018-11-04 15:09 UTC (permalink / raw)
To: andrew@tao11.riddles.org.uk; +Cc: pgsql-admin@lists.postgresql.org
>
>
> In future please use plain text for query output, not screenshots
> ------------------------------------------
>
> Mariel> When I searched for the biggest objects in DB1 I saw that the
> Mariel> total sum isnt even close to 100G :
>
> You should probably check whether there are files in the db's directory
> that do not correspond to the filenode of any table (use
> select pg_relation_filenode(oid) from pg_class; to get a list of
> filenodes)
>
* and If i'll find some, is it safe just to delete them ?*
> -------------------------------------------------------------
> Crashes (or other unclean shutdown) in the middle of doing a vacuum full
> or other table-rewriting operation is known to leave orphaned files
> around in the data dir.
>
> Mariel> Now, I thought that the pg_total_relation_size should include
> Mariel> also all the dead tuples and the toasted tables.
>
> It does, and your results agree with that. Perhaps you missed that
> pg_table_size() includes the toast table too?
>
* Yeah, that is exactly what I missed, thanks ;)*
>
> Mariel> In addition, I saw from pg_stat_activity that the session table
> Mariel> has about 198 dead tuples and the toasted table has about 83833
> Mariel> dead tuples. It seems that the autoanalyze has never run on the
> Mariel> toasted table.
>
> There's nothing in a toast table that needs analyzing.
> Why not ? So when you analyze a table it will analyze also the pg_toast ?
> For example you would want to know how many records there are in that
> toast..
> --
> Andrew (irc:RhodiumToad)
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: size of table + toasted tables + indexes != pg_total_relation_size
@ 2018-11-04 16:47 Andrew Gierth <andrew@tao11.riddles.org.uk>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Andrew Gierth @ 2018-11-04 16:47 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
>>>>> "Mariel" == Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
>> You should probably check whether there are files in the db's
>> directory that do not correspond to the filenode of any table (use
>> select pg_relation_filenode(oid) from pg_class; to get a list of
>> filenodes)
Mariel> * and If i'll find some, is it safe just to delete them ?*
You need to check that they're not recent (i.e. could not belong to
table creations/rewrites that are still in progress, since those would
not be visible in pg_class).
Also make sure you're looking in the right database (using
pg_relation_filepath instead may make this clearer).
Remember that a given filenode/filepath also generates names with
suffixes for forks (e.g. _vm) and segments (.1, .2 etc) - those need to
be kept for valid filenodes and deleted along with orphaned ones.
Naturally, if you get this wrong you will break your database beyond
easy recovery, so BE CAREFUL. As an extra check, you could look at the
times of the files and check against your server logs to see if there
were indeed any crashes or unclean restarts of pg around those times; if
you have a lot of apparently orphaned files you should investigate why.
--
Andrew (irc:RhodiumToad)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: size of table + toasted tables + indexes != pg_total_relation_size
@ 2018-11-04 19:49 Rui DeSousa <rui@crazybean.net>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Rui DeSousa @ 2018-11-04 19:49 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> On Nov 4, 2018, at 5:14 AM, Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:
>
> local dba used to run vacuum full on a daily basis
Shouldn’t be doing vacuum fulls; that’s just bad db management. Autovacuum/vacuum and normal turnover rate should be enough; otherwise you server is mostly not sized correctly.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2018-11-04 19:49 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-11-04 10:14 size of table + toasted tables + indexes != pg_total_relation_size Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-11-04 12:12 ` Andrew Gierth <andrew@tao11.riddles.org.uk>
2018-11-04 15:09 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-11-04 16:47 ` Andrew Gierth <andrew@tao11.riddles.org.uk>
2018-11-04 19:49 ` Rui DeSousa <rui@crazybean.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox