Message-ID: <53d097b669514cd2628678149a87f85b7ef3db5f.camel@cybertec.at>
Subject: Re: autovacuum big table taking hours and sometimes seconds
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>, Jeff Janes
	 <jeff.janes@gmail.com>
Cc: David Rowley <david.rowley@2ndquadrant.com>,
	pgsql-performance@lists.postgresql.org
Date: Fri, 15 Feb 2019 09:06:13 +0100
In-Reply-To: 
 <CA+t6e1nuSWXDNCo=0=dS0or6WS5bZn3Aax_=sV2+oqvCSWg2hA@mail.gmail.com>
References: 
	<CA+t6e1n9CDKmzAU=ksOV56+5NUQwmUTh5oKsdexkbCx5ioceBw@mail.gmail.com>
	 <95c2859e3b9b80597a5bf23788b567ac586d068c.camel@cybertec.at>
	 <CAKJS1f-jT7ZuNbwdMEaoWXbR5N1WT77=TOvy+zDkTFBORQUHtg@mail.gmail.com>
	 <CA+t6e1m7V82uCaRePRpHS5FByj+Lj__dVMQ+n0eYBFw0Wur62w@mail.gmail.com>
	 <CAKJS1f_yfxBSUN5tv1zD7Y6Pg767qrS-uSMPY3HuSUL9UWVWXw@mail.gmail.com>
	 <CA+t6e1kH8KtHDEWHVnGq3As1+E7Wut0qk9M-W5G+0L-6EVYgrA@mail.gmail.com>
	 <CAMkU=1w2UD5vUh4QDADfiXF-PKkFHQASwZJM30KAX6Dj2=T02g@mail.gmail.com>
	 <CA+t6e1nuSWXDNCo=0=dS0or6WS5bZn3Aax_=sV2+oqvCSWg2hA@mail.gmail.com>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

Mariel Cherkassky wrote:
> Lets focus for example on one of the outputs :
> postgresql-Fri.log:2019-02-08 05:05:53 EST  24776  LOG:  automatic vacuum of table "myDB.pg_toast.pg_toast_1958391": index scans: 8
> postgresql-Fri.log-	pages: 2253 removed, 13737828 remain
> postgresql-Fri.log-	tuples: 21759258 removed, 27324090 remain
> postgresql-Fri.log-	buffer usage: 15031267 hits, 21081633 misses, 19274530 dirtied
> postgresql-Fri.log-	avg read rate: 2.700 MiB/s, avg write rate: 2.469 MiB/s
> 
> The cost_limit is set to 200 (default) and the cost_delay is set to 20ms. 
> The calculation I did : (1*15031267+10*21081633+20*19274530)/200*20/1000 = 61133.8197 seconds ~ 17H
> So autovacuum was laying down for 17h ? I think that I should increase the cost_limit to max specifically on the toasted table. What do you think ? Am I wrong here ?

Increasing cost_limit or reducing cost_delay improves the situation.

cost_delay = 0 makes autovacuum as fast as possible.

Yours,
Laurenz Albe