From: MichaelDBA <MichaelDBA@sqlexec.com>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
Cc: dangal <danielito.gallo@gmail.com>
Cc: PostgreSQL mailing lists <pgsql-performance@postgresql.org>
Subject: Re: autovacuum big table taking hours and sometimes seconds
Date: Wed, 06 Feb 2019 09:11:40 -0500
Message-ID: <5C5AEB1C.9070301@sqlexec.com> (raw)
In-Reply-To: <CA+t6e1m=DRX5wji5fZy--b=uZOMnvjTeDqxFjvRqbHN56u0qoQ@mail.gmail.com>
References: <CA+t6e1n9CDKmzAU=ksOV56+5NUQwmUTh5oKsdexkbCx5ioceBw@mail.gmail.com>
<95c2859e3b9b80597a5bf23788b567ac586d068c.camel@cybertec.at>
<CAKJS1f-jT7ZuNbwdMEaoWXbR5N1WT77=TOvy+zDkTFBORQUHtg@mail.gmail.com>
<1549460174579-0.post@n3.nabble.com>
<CA+t6e1m=DRX5wji5fZy--b=uZOMnvjTeDqxFjvRqbHN56u0qoQ@mail.gmail.com>
Hi all,
In the myriad of articles written about autovacuum tuning, I really like
this article by Tomas Vondra of 2ndQuadrant:
https://blog.2ndquadrant.com/autovacuum-tuning-basics/
It is a concise article that touches on all the major aspects of
autovacuuming tuning: thresholds, scale factors, throttling, etc.
Regards and happy vacuuming to yas!
Michael Vitale
> Mariel Cherkassky <mailto:mariel.cherkassky@gmail.com>
> Wednesday, February 6, 2019 8:41 AM
> which one you mean ? I changed the threshold and the scale for the
> specific table...
>
> dangal <mailto:danielito.gallo@gmail.com>
> Wednesday, February 6, 2019 8:36 AM
> Would it be nice to start changing those values found in the default
> postgres.conf so low?
>
>
>
> --
> Sent from:
> http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
>
> David Rowley <mailto:david.rowley@2ndquadrant.com>
> Wednesday, February 6, 2019 8:05 AM
>
> Going by the block hits/misses/dirtied and the mentioned vacuum times,
> it looks like auto-vacuum is set to the standard settings and if so it
> spent about 100% of its time sleeping on the job.
>
> It might be a better idea to consider changing the vacuum settings
> globally rather than just for one table.
>
> Running a vacuum_cost_limit of 200 is likely something you'd not want
> to ever do with modern hardware... well maybe unless you just bought
> the latest Raspberry PI, or something. You should be tuning that
> value to something that runs your vacuums to a speed you're happy with
> but leaves enough IO and CPU for queries running on the database.
>
> If you see that all auto-vacuum workers are busy more often than not,
> then they're likely running too slowly and should be set to run more
> quickly.
>
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: MichaelDBA@sqlexec.com, mariel.cherkassky@gmail.com, danielito.gallo@gmail.com
Subject: Re: autovacuum big table taking hours and sometimes seconds
In-Reply-To: <5C5AEB1C.9070301@sqlexec.com>
* 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