Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1grNvb-0001CP-3L for pgsql-performance@arkaria.postgresql.org; Wed, 06 Feb 2019 14:11:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1grNvZ-0008DB-Ot for pgsql-performance@arkaria.postgresql.org; Wed, 06 Feb 2019 14:11:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1grNvZ-0008D4-HW for pgsql-performance@lists.postgresql.org; Wed, 06 Feb 2019 14:11:49 +0000 Received: from mout.perfora.net ([74.208.4.194]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1grNvW-0000hm-LR for pgsql-performance@postgresql.org; Wed, 06 Feb 2019 14:11:49 +0000 Received: from [192.168.1.101] ([98.169.208.73]) by mrelay.perfora.net (mreueus003 [74.208.5.2]) with ESMTPSA (Nemesis) id 0M5vg7-1h2Sed0J2H-00xsgA; Wed, 06 Feb 2019 15:11:42 +0100 Message-ID: <5C5AEB1C.9070301@sqlexec.com> Date: Wed, 06 Feb 2019 09:11:40 -0500 From: MichaelDBA User-Agent: Postbox 5.0.25 (Windows/20180328) MIME-Version: 1.0 To: Mariel Cherkassky CC: dangal , PostgreSQL mailing lists Subject: Re: autovacuum big table taking hours and sometimes seconds References: <95c2859e3b9b80597a5bf23788b567ac586d068c.camel@cybertec.at> <1549460174579-0.post@n3.nabble.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------090707030409080109040202" X-Provags-ID: V03:K1:Y60qBQDSBuAMHdFtppmLavP068UOZy1CTUlmCmO3k5e5fOrqWvC bSjzqBC8t7DAJuIlgXaKLS6ZqrDvVncBV+uq++sstbt2HYchulmJTO3pJY9sHsg1bnymozC YwTkz+Bvi3qfyYWoMOhyVFDKurijPR+LZ66xRXOPaJmPlWU9MKROvMY3hhEOG6/G2NDOENM flrGhSAdG4woh2+teHUUg== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:C9A0MP8mW64=:wHbtxUEElTzWcpt2WZ1t1z AN8rWwDNVGVxdIQ4mnVwW1YNVbziTC2V1lcy3ddUZWzBnojUwkK0HiW4iRdtr1/fGuzYbmxqM d7gQK9eHeSRsOYurFLU0J+ElhOfhmkBvXqUzfiS5Ya7B2PJT/1tZ2ll39PpCAxnyUA0H5GRnF VrXGh/ZfKCetis1xv4iWlasx+1oC863y4fdxLHoNe3ixhIOcVE0a3iM2skCxBTsseSrsJnDx3 zPzeX4Za9iScNLAj8J0aF599o7fQMnU5a+4BCP1BVmg3kfolzP4WFj7Bnxgnu1ySFt10nGpez DoyIbLR/V8rJY3q+X6gKAl6DvZtVYpOwmVk6E3wxlThoNYO+REr/CC/7LlbTeEEnDs324xkui MS0kQKBGQm9L5E2tzfhrb/TkgKOcIBotf4wwPSwu79eZZOjKhxLsqj2ppquFTVjdg15KJIILr 0YSs9mXxeqKzAAUPw9FfdimDhaZk8XssnY3TElbg/1KcgnobJ+YpanU7+DQvvdil73Q/XZ9Ir bPn98empzK1W65HknGKK8LDy/r+nw1xNf3hClnezYXBhapi2EBOeZqEqTpgUBoDaUs6CMkPWy g0y/GIVZDnwr8z9lvsPyi5dSQ83feyxV9bX1fveYxPis3VHaFWHDvO93qin+co34zCrIuXreB Yq7toSuXFi9Zt1y1bersreFlzUvX+KCsqtpvCVsHISYsRVQGDtw5lpwCIIpfSXd2RF83Iotw1 F+jMUS1TDIrMpW4rk66pCbf3IJsX7B/fs574k1dQn2suTPQfonwBc+Mb4lA= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------090707030409080109040202 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 > Wednesday, February 6, 2019 8:41 AM > which one you mean ? I changed the threshold and the scale for the > specific table... > > dangal > 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 > 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. > --------------090707030409080109040202 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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

Wednesday, February 6, 2019 8:41 AM
which one you mean ? I changed the threshold and the scale for the specific table...

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

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.


--------------090707030409080109040202--