From: Jan <pgsql.admin@j.mk-contact.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org
Subject: Re: Long-running and non-finishing VACUUM ANALYZE on large table
Date: Sat, 10 Oct 2015 16:38:49 +0200
Message-ID: <561922F9.3000902@j.mk-contact.de> (raw)
In-Reply-To: <9857.1444175592@sss.pgh.pa.us>
References: <560EB550.5050501@j.mk-contact.de>
<23918.1443806166@sss.pgh.pa.us>
<56145236.2030805@j.mk-contact.de>
<9857.1444175592@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
On 10/07/2015 01:53 AM, Tom Lane wrote:
> Such an UPDATE should have left the table 50% dead tuples, since every
> row would leave behind a dead version. On the other hand, an ALTER
> COLUMN TYPE operation should rewrite the whole table and leave no dead
> tuples behind. No matter which one you did last, it doesn't square with
> 0.32% dead tuples.
>
> My best guess at this point is that what you did last is an UPDATE,
> so you have 50% dead tuples, and for some reason pgstattuple is not
> telling you the truth about that. But the VACUUM is showing reality.
>
> How long did those UPDATEs and ALTER TABLEs take? If an ALTER seemed
> tolerable then maybe what you want to do is VACUUM FULL, which would
> be roughly the same cost.
Tom, as you predicted, the VACUUM FULL finished relatively quickly
yesterday after about 31h. Here is the verbose output:
INFO: vacuuming "public.protein_hsps"
INFO: "protein_hsps": found 63187655 removable, 11353611882
nonremovable row versions in 181253461 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU 2814.17s/8479.90u sec elapsed 15451.26 sec.
Total query runtime: 114969739 ms.
Here is the output from pgstattuple (before / after vacuum):
-- SELECT * FROM pgstattuple('protein_hsps');
--
-- yielded:
table_len 1484828352512 / 958853496832
tuple_count 11353611882 / 11353611882
tuple_len 874228114914 / 874228114914
tuple_percent 58.88 / 91.17
dead_tuple_count 63187655 / 0
dead_tuple_len 4810998304 / 0
dead_tuple_percent 0.32 / 0
free_space 495246133064 / 1872767456
free_percent 33.35 / 0.2
And the table sizes (before / after vacuum):
-- SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_relation_size(C.oid)) AS "size"
-- FROM pg_class C LEFT JOIN pg_namespace N ON N.oid = C.relnamespace
-- WHERE relname IN ('protein_hsps', 'protein_hsps_clustidx_on_origin',
'protein_hsps_pkey')
--
-- yielded:
public.protein_hsps 1383 GB / 893 GB
public.protein_hsps_clustidx_on_origin 499 GB / 238 GB
public.protein_hsps_pkey 494 GB / 238 GB
The only thing which I currently not understand is the pgstattuple
output, which tells me that there is 0.2% free space left. Actually
there are 7.3T (i.e., 71%) left on the device so this is either a wrong
display or this value refers to something other than the free disk space
on the drive.
Again, many thanks for the advice!
Kind regards,
Jan
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: pgsql.admin@j.mk-contact.de, tgl@sss.pgh.pa.us
Subject: Re: Long-running and non-finishing VACUUM ANALYZE on large table
In-Reply-To: <561922F9.3000902@j.mk-contact.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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