From: Jan <pgsql.admin@j.mk-contact.de>
Subject: Re: Long-running and non-finishing VACUUM ANALYZE on large
 table
To: Tom Lane <tgl@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>
Cc: pgsql-admin@postgresql.org
Message-ID: <561922F9.3000902@j.mk-contact.de>
Date: Sat, 10 Oct 2015 16:38:49 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.3.0
MIME-Version: 1.0
In-Reply-To: <9857.1444175592@sss.pgh.pa.us>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


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