Message-ID: <cc23c226-e0be-47a7-bf6f-bcedd097a239@dalibo.com>
Date: Mon, 13 May 2024 17:14:53 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
From: =?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?= <frederic.yhuel@dalibo.com>
Subject: Re: New GUC autovacuum_max_threshold ?
To: Robert Haas <robertmhaas@gmail.com>,
 "Imseih (AWS), Sami" <simseih@amazon.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>,
 David Rowley <dgrowleyml@gmail.com>, Joe Conway <mail@joeconway.com>,
 Michael Banck <mbanck@gmx.net>, Laurenz Albe <laurenz.albe@cybertec.at>,
 Melanie Plageman <melanieplageman@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <c78950c4a37a29b62d4eede6ecc403ecdcd9eeb6.camel@cybertec.at>
 <d120db91-393e-4904-83df-d936eee56db4@dalibo.com>
 <d1af9a71e17f117e514d25bba2306ee845a9fde5.camel@cybertec.at>
 <662b6941.5d0a0220.ea153.4178@mx.google.com>
 <06b361a6-094c-4d78-b4c9-b102e270d97e@joeconway.com>
 <CA+TgmoZav6Mxv6qzPEZiAknzqdQ7Cq-oS1WJ6N-sPe5QyT2tiA@mail.gmail.com>
 <7c91df80-ef1b-426a-a2b3-e11ffb31bcc7@joeconway.com>
 <CA+Tgmoa9FzNLNtD-PYptJP1ApV=a57RwqOVypb0gL5uLzLtCFQ@mail.gmail.com>
 <CAApHDvo8DWyt4CWhF=NPeRstz_78SteEuuNDfYO7cjp=7YTK4g@mail.gmail.com>
 <CA+TgmoaCQ_GCwn3V8uXc+60oSJivY6ouiAqe81Z3h1kT6zMMYQ@mail.gmail.com>
 <20240507211702.GA2720371@nathanxps13>
 <0A799687-DE0A-4BFC-8900-E1D57EB6C211@amazon.com>
 <CA+TgmoYF3HqEUArbsqtCtP7YMJ62G3U8W1mSmZFZoCK8uBSi9g@mail.gmail.com>
Content-Language: fr, en-US
Autocrypt: addr=frederic.yhuel@dalibo.com; keydata=
 xjMEXn3bgxYJKwYBBAHaRw8BAQdA6tX5FT/n5ztMWIoBdl6k5avvu65fv6ryfVzIx/aH3V3N
 JEYuIFlodWVsIDxmcmVkZXJpYy55aHVlbEBkYWxpYm8uY29tPsKWBBMWCAA+FiEEi7OTyf6H
 ePS+C6n11XGWhN8zGNUFAmYFjIICGwMFCQln/O0FCwkIBwIGFQoJCAsCBBYCAwECHgECF4AA
 CgkQ1XGWhN8zGNU8jwD7BQf1+z8S62UH+jgUZPPbqqMaKQu2MNsJd7cV0M+wBMUBAM6Y4GmE
 YnxOPckuqhrTIA9O8fvWGNVJX1r0XjSs1DQLzjgEXn3bgxIKKwYBBAGXVQEFAQEHQCuh5cLQ
 AtEBXHa2Fmtp0kduBu7msM7qO/gaEcdjFUdiAwEIB8J+BBgWCAAmAhsMFiEEi7OTyf6HePS+
 C6n11XGWhN8zGNUFAmYFjIIFCQln/O0ACgkQ1XGWhN8zGNXsMAD/VYorW/1s3nWJpMOi8RNo
 8WZIXCG5Zm5NbKscHWTOm94A/RFjn7bP3dfnU4ioUkG+qY4iLE/t2fE1bd6XxP9BUHYH
In-Reply-To: <CA+TgmoYF3HqEUArbsqtCtP7YMJ62G3U8W1mSmZFZoCK8uBSi9g@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/cc23c226-e0be-47a7-bf6f-bcedd097a239%40dalibo.com>
Precedence: bulk


Le 09/05/2024 à 16:58, Robert Haas a écrit :
> As I see it, a lot of the lack of agreement up until now is people
> just not understanding the math. Since I think I've got the right idea
> about the math, I attribute this to other people being confused about
> what is going to happen and would tend to phrase it as: some people
> don't understand how catastrophically bad it will be if you set this
> value too low.

FWIW, I do agree with your math. I found your demonstration convincing. 
500000 was selected with the wet finger.

Using the formula I suggested earlier:

vacthresh = Min(vac_base_thresh + vac_scale_factor * reltuples, 
vac_base_thresh + vac_scale_factor * sqrt(reltuples) * 1000);

your table of 2.56 billion tuples will be vacuumed if there are
more than 10 million dead tuples (every 28 minutes).

If we want to stick with the simple formula, we should probably choose a 
very high default, maybe 100 million, as you suggested earlier.

However, it would be nice to have the visibility map updated more 
frequently than every 100 million dead tuples. I wonder if this could be 
decoupled from the vacuum process?