Re: vacuumdb not letting me connect to db

On 2021-02-06 12:54:11 -0600, Ron wrote:
> On 2/6/21 6:06 AM, Atul Kumar wrote:
>
> Hi Gavan,
>
> Thanks for providing the details, I need more clarification on this as how
> should I analyze that what should be ideal no. of connections should we set
> to avoid IO overhead based on the available hardware resources.
> How to do this calculation ?
>
>
>
> Run "iotop -o -u postgres", and then compare that with the total bandwidth
> available to the system.  If it's (even almost) saturated, then everything else
> will be starved.
>
> The "--jobs=" value should AT MOST be some *small* multiple of the number of
> CPUs (like 1x, 1.5x or maybe 2x if the core count is low, and nothing else is
> running on the system.

While this is generally good advice, Atul reported earlier in the thread
that CPU consumption is only 1 to 4 %. So the problem is clearly not
related to CPU usage, but very probably (as some have already noted) on
I/O. You could use a similar rule of thumb for disks ("a small multiple
of the number of disks") but with SSDs this might not be very accurate.

I think the only method to get to the optimal number of vacuums
that can be run in parallel is to determine it experimentally:
300 is clearly too high. So try 150, 75, 37, etc. For each number try to
do some other work - is the performance acceptable? If yes, note the
time until vacuum is finished. FInally among those where the performance
was acceptable choose the value which was fastest.

(Note: If you do this on the same database, subsequent runs will benefit
from work already done, so the take the results with a grain of salt).

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"