pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: David Rowley <david.rowley@2ndquadrant.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: autovacuum big table taking hours and sometimes seconds
Date: Fri, 15 Feb 2019 09:06:13 +0100
Message-ID: <53d097b669514cd2628678149a87f85b7ef3db5f.camel@cybertec.at> (raw)
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>

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





view thread (21+ messages)

Message-ID: <53d097b669514cd2628678149a87f85b7ef3db5f.camel@cybertec.at>
Permalink:  ../53d097b669514cd2628678149a87f85b7ef3db5f.camel@cybertec.at/
Also on:    postgresql.org/message-id/53d097b669514cd2628678149a87f85b7ef3db5f.camel@cybertec.at

 · 

reply
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-performance@postgresql.org
  Cc: laurenz.albe@cybertec.at, mariel.cherkassky@gmail.com, jeff.janes@gmail.com, david.rowley@2ndquadrant.com, pgsql-performance@lists.postgresql.org
  Subject: Re: autovacuum big table taking hours and sometimes seconds
  In-Reply-To: <53d097b669514cd2628678149a87f85b7ef3db5f.camel@cybertec.at>

* 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