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
From: Jan <pgsql.admin@j.mk-contact.de>
Message-ID: <5614649D.6050906@j.mk-contact.de>
Date: Wed, 7 Oct 2015 02:17:33 +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:
> Jan <pgsql.admin@j.mk-contact.de> writes:
>> I'm still not getting the math behind it. The below tuple stats show a
>> dead tuple count of 63,187,655 whereas the PGadmin output (see my
>> initial e-mail) reported the message "scanned index protein_hsps_pkey to
>> remove 178956753 row versions" nine times before I cancelled it. That
>> is, if one multiplies 178,956,753 by 9 it yields 1,610,610,777 (dead)
>> rows. But the latter number is much higher than the above 63m rows? Do I
>> compare the wrong numbers?
> There's something awfully wacky about that.  I suspect that pgstattuple
> is somehow giving wrong answers, but I don't see any plausible theory
> as to why.
>
>> Some more background: the whole database was recently migrated to a new
>> database server and thus restored from a dump file. That is, the table
>> 'protein_hsps' and its index were build from scratch. Since then, the
>> only operations on that table were some column type conversions (e.g.,
>> integer to smallint, double to real). Data-wise, the only operations
>> were UPDATES on a single boolean column by adding precalculated values
>> (true/false) to EACH row in the database (~ 16bn rows). These UPDATEs
>> were most likely the cause for the (huge) number of dead tuples (0.32%,
>> see above), weren't they?
> 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.

Exactly, I altered the column types first. (That had been on my list for 
a long time, and I used the planned database server downtime for these 
optimizations.) After that, back in production mode, the aforementioned 
UPDATEs were applied (not at once, but in batches of rows). That is, I 
also assume 50% dead tuples right now.

> 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.

I protocolled the exact execution times of each ALTER statement 
(unfortunately not available right now because I'm at home) and these 
took roundabout 1-2 days each.
Now I will go for the VACUUM FULL and will report back here once it's 
done. *fingers crossed*

Many thanks for your time!

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