public inbox for [email protected]  

help / color / mirror / Atom feed

From: Imseih (AWS), Sami <[email protected]>
To: Robert Haas <[email protected]>
Cc: Joe Conway <[email protected]>
Cc: Michael Banck <[email protected]>
Cc: Laurenz Albe <[email protected]>
Cc: Frédéric Yhuel <[email protected]>
Cc: Nathan Bossart <[email protected]>
Cc: Melanie Plageman <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: David Rowley <[email protected]>
Subject: Re: New GUC autovacuum_max_threshold ?
Date: Thu, 2 May 2024 16:01:23 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+TgmoZKwRQkjymNRogaMupyLNsH_oqyTGHWogwqJ4TfHbJpRg@mail.gmail.com>
References: <[email protected]>
	<CAAKRu_bevP35okEsQuT6dr5jbvD79Gva_-YO_ciBSchT4GDHSg@mail.gmail.com>
	<20240424195747.GA861150@nathanxps13>
	<CA+TgmoY4BENJyYcnU2eLFYZ73MZOb72WymNBH7vug6DtA+CZZw@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CA+TgmoZav6Mxv6qzPEZiAknzqdQ7Cq-oS1WJ6N-sPe5QyT2tiA@mail.gmail.com>
	<[email protected]>
	<CA+Tgmoa9FzNLNtD-PYptJP1ApV=a57RwqOVypb0gL5uLzLtCFQ@mail.gmail.com>
	<[email protected]>
	<CA+TgmoZKwRQkjymNRogaMupyLNsH_oqyTGHWogwqJ4TfHbJpRg@mail.gmail.com>

> And as far as that goes, I'd like you - and others - to spell out more
> precisely why you think 100 or 200 million tuples is too much. It
> might be, or maybe it is in some cases but not in others. To me,
> that's not a terribly large amount of data. Unless your tuples are
> very wide, it's a few tens of gigabytes. That is big enough that I can
> believe that you *might* want autovacuum to run when you hit that
> threshold, but it's definitely not *obvious* to me that you want
> autovacuum to run when you hit that threshold.


Vacuuming the heap alone gets faster the more I do it, thanks to the 
visibility map. However, the more indexes I have, and the larger ( in the TBs),
the indexes become, autovacuum workers will be 
monopolized vacuuming these indexes.


At 500k tuples, I am constantly vacuuming large indexes
and monopolizing autovacuum workers. At 100 or 200
million tuples, I will also monopolize autovacuum workers
as they vacuums indexes for many minutes or hours. 


At 100 or 200 million, the monopolization will occur less often, 
but it will still occur leading an operator to maybe have to terminate
the autovacuum an kick of a manual vacuum. 


I am not convinced a new tuple based threshold will address this, 
but I may also may be misunderstanding the intention of this GUC.


> To make that concrete: If the table is 10TB, do you want to vacuum to
> reclaim 20GB of bloat? You might be vacuuming 5TB of indexes to
> reclaim 20GB of heap space - is that the right thing to do? If yes,
> why?


No, I would not want to run autovacuum on 5TB indexes to reclaim 
a small amount of bloat.




Regards,


Sami

view thread (17+ messages)  latest in thread

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: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: New GUC autovacuum_max_threshold ?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox