Thread: autovacuum recommendations for Large tables
Hi,
On Tuesday, November 17, 2020, Olivier Gautherot <ogautherot@gautherot.net> wrote:
Could you help me by explaining in simple words, as I am new to postgres.
What value of which parameter should I set and why.
I only have this one big table in the database of size 3113 GB with rows 7661353111.
Right Now the autovacuum setting for that table is set to
{autovacuum_enabled=true,
{autovacuum_enabled=true,
Please help.
Regards
Atul
On Tuesday, November 17, 2020, Olivier Gautherot <ogautherot@gautherot.net> wrote:
Hi Atul,I would start with a factor of 0.00001 (10 parts per million) and explore down to 0.000001.I did some massive updates on a partition with 12 millions rows and my factor was 0.001.Depending on the number of big tables you have in the database, you may wish to reduce the number of parallel workers.On Mon, Nov 16, 2020 at 8:20 PM Atul Kumar <akumar14871@gmail.com> wrote:Ok,
Right Now the autovacuum setting for that table is set to
{autovacuum_enabled=true,autovacuum_vacuum_scale_ factor=0.2,autovacuum_analyze_ scale_factor=0.2}
So Please suggest, how much should i set atleast, to avoid increasing
in dead tuple.
Regards,
Atul
On 11/16/20, Olivier Gautherot <ogautherot@gautherot.net> wrote:
> Hi Atul,
>
> Le lun. 16 nov. 2020 à 18:38, Atul Kumar <akumar14871@gmail.com> a écrit :
>
>> Hi,
>>
>> I have a large table having no. of live tuples approx 7690798868 and
>> no. of dead tuples approx 114917737.
>>
>> So Please share autovacuum tuning recommendations for this table so
>> that our time can be spent better than repeatedly vacuuming large
>> tables.
>>
>
> Vacuuming will affect you when it has a lot of work to do. I would try to
> trigger an autovacuum every 10,000 insert/update to minimize the impact.
> You can play with the parameter autovacuum_vacuum_scale_factor for that
> table.
>
> Good luck
> Olivier
>
Hi Atul,
On Tue, Nov 17, 2020 at 12:05 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Mon, Nov 16, 2020 at 3:57 PM Atul Kumar <akumar14871@gmail.com> wrote:I only have this one big table in the database of size 3113 GB with rows 7661353111.Right Now the autovacuum setting for that table is set to
{autovacuum_enabled=true,autovacuum_vacuum_scale_factor=0.2,autovacuum_analyze_scale_factor=0.2}auto-vacuum doesn't care directly about absolute size, it cares about change (relative to absolute size in many cases, hence the scale factors).David J.
David is correct.
If it helps, I put together a few thoughts and own experience on a blog:
Hope you find it useful.
--
Olivier Gautherot
Hi Rob,
On Tue, Nov 17, 2020 at 10:24 PM Rob Sargent <robjsargent@gmail.com> wrote:
If it helps, I put together a few thoughts and own experience on a blog:Hope you find it useful.--That URL does not work for me (not even https://sites.google.com/gautherot.net)
Thanks for the heads up - it looks like Google won't publish the site outside of my domain.
I moved it to the old sites: https://sites.google.com/a/gautherot.net/postgresql-tips/vacuum
Let's hope it works this time.
Cheers
Olivier