Re: vacuumdb not letting me connect to db

On 6 Feb 2021, at 3:37, Ron wrote:

> On 2/5/21 10:22 AM, Rob Sargent wrote:
>>
>>
>> On 2/5/21 9:11 AM, Ron wrote:
>>> Obviously... don't use 300 threads.
>>>
>> No, no Ron.  Clearly the answer is more CPUs
>
> I hope you're being sarcastic.
>
A reasonable conjecture… though there is the consideration that 300 
CPU intensive tasks spread across a given number of CPUs is going to 
waste some resources with context switching., i.e., need more CPUs :)

Basically if there is plenty of wait time for I/O completion then CPU 
task switching can get more total work done.  So far so obvious. In this 
thread I can see where it is disappointing to have a system considered 
capable of 700 connections getting saturated by a “mere” 300 
threads. But this is only a “problem” if connections are equated to 
threads. PG max connection count is about external users having access 
to resources needed to get a task done. Like all resource allocations 
this relies on estimated average usage, i.e., each connection only asks 
for a lot of CPU in brief bursts and then the result is transmitted with 
a time lag before the connection makes another CPU demand. The system 
designer should use estimations about usage and load to budget and 
configure the system, and, monitor it all against actual performance in 
the real world. Of course estimates are a standing request for outliers 
and the system will show stress under an unexpected load.

So far I have not seen an analysis of where the bottle neck has 
occurred: CPU RAM HD and/or the data bus connecting these. Some of these 
hardware resources maxed out to the extent the system would not 
immediately pick up an additional work unit. As I see it OP started 300 
CPU intensive tasks on hardware intended for 700 connections. If the 
connection count was designed with say 50% CPU intensive time per 
connection you would expect this hardware to be fully saturated with 300 
CPU intensive tasks. More than that, doing the task with 300 threads 
would probably take longer than (say) 200 threads as the increased CPU 
context swapping time is just wasted effort.

OP now has a choice: decrease threads or (seriously) upgrade the 
hardware. We in the gallery would love to see a plot of total time to 
completion as a function of threads invoked (50-300 increments of 50) 
assuming the starting conditions are the same :)

Gavan Schneider
——
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a 
well-known solution to every human problem — neat, plausible, and 
wrong.
— H. L. Mencken, 1920