Subject: Re: Long-running and non-finishing VACUUM ANALYZE on large
 table
To: Jan <pgsql.admin@j.mk-contact.de>, pgsql-admin@postgresql.org
References: <560EB550.5050501@j.mk-contact.de>
From: "Joshua D. Drake" <jd@commandprompt.com>
Organization: Command Prompt, Inc.
Message-ID: <560EB689.7040000@commandprompt.com>
Date: Fri, 2 Oct 2015 09:53:29 -0700
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.2.0
MIME-Version: 1.0
In-Reply-To: <560EB550.5050501@j.mk-contact.de>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

On 10/02/2015 09:48 AM, Jan wrote:
> Hi everyone,
>
> I'm currently running PostgreSQL 9.3.6 on Ubuntu 14.04 Server and have
> severe problems to get one large table properly vacuumed (16,743,000,000
> reltuples).

16B in a single table? I am not surprised you are having issues.


> That is, autovacuum on that table does not finish (even after six days
> it is still running). To narrow down the problem, I stopped autovacuum,
> temporarily disabled it (but ONLY on that particular table; not a
> permanent solution, I know) and started a manual VACUUM ANALYZE on that
> table via PGAdmin instead. In analogy to the autovacuum, the manually
> started process is also running virtually endless (until I terminate it
> after some days) but PGadmin's verbose output (see below) tells me at
> least that it (apparently) tries to process the same table over and over
> again. I'm definitely not a Postgres guru, but this can't be the
> expected behaviour, even on such a big table, right?Did I overlook
> something?

178,956,763 row versions to be removed.

I have no doubt that it is taking what seems like forever.

I strongly suggest you partition this beast.

JD

-- 
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin