Re: vacuumdb not letting me connect to db

[Atul Kumar]

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 ?

Note: even during 300 threads, my RAM utilisation is totally normal.


Regards
Atul






On Saturday, February 6, 2021, Gavan Schneider 
wrote:

> 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
>

Re: vacuumdb not letting me connect to db

[Gavan Schneider]

On 6 Feb 2021, at 23:06, Atul Kumar wrote:

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 analysis properly is outside my expertise and likely off 
topic for this list. But you can get an overall idea doing what I 
suggested: measure the performance (i.e., total time to completion) when 
using different numbers of threads). In an ideal world the time will get 
less as you invoke more threads. But there will be a point where there 
is a bottle neck (or some resource hits its limit) and the time will be 
more or less the same no matter how many threads.



How to do this calculation ?

That’s likely too hard. Measurement will tell you what you need to 
know. Once you know how many threads it takes to saturate you will know 
you need to use a lesser number. This may, or may not, get you to 
revisit the max 700 connections setting but once you know there is a 
performance limit, and you decide it’s not good enough, then you can 
ask the hardware people for help.



Note: even during 300 threads, my RAM utilisation is totally normal.

 That’s not unexpected. The CPU activity should be using the data 
held in RAM not spending all those cycles allocating RAM.


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

Re: vacuumdb not letting me connect to db

[Gmail]

> On Feb 5, 2021, at 9:37 AM, 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.
> 
> -- 
> Angular momentum makes the world go 'round.
> 
Ron, I was being sarcastic, but I’ve gone over the thread and I do not see any 
mention of the number of cores on the server.  If it has a single quad core 
chip then the queue for each processor could be on average 75 deep at the 
outset, all of them anxiously awaiting data.  -j connections are processes, 
correct? Not threads.
Atul, if you list the tools you know and have used for watching system 
performance and then others can perhaps suggest alternatives (or re-interpret 
results)

Re: vacuumdb not letting me connect to db

[Ron]

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.



Note: even during 300 threads, my RAM utilisation is totally normal.


Regards
Atul






On Saturday, February 6, 2021, Gavan Schneider > wrote:


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



--
Angular momentum makes the world go 'round.

Should pgAdmin 3 be saved?

[Gabriel Martin]

Hello,

I know that since the launch of pgAdmin 4 in 2016, pgAdmin has been using a
web-based model, as announced as early as 2014. You only need to do a
little research on the Web to understand all the reasons behind this
choice, which I do not seek to question.

However, I wonder if it would be relevant to keep pgAdmin 3 alive. I'm not
so much talking about improving the interface of pgAdmin 3, but simply
about keeping the software functional in new systems. I tried to install it
in the latest versions of Linux Mint and Ubuntu, but the software keeps
bugging, which it didn't do in older versions of the same systems 5 or 6
years ago.

Do you think there's a possibility that people could fix pgAdmin 3, even if
it means making a fork or whatever? Because, although pgAdmin 4 and other
tools such as OmniDB and DBeaver are probably excellent, some people, for
various reasons, might want to continue using pgAdmin 3 in 2021. I'm not an
expert in the field, but I'm thinking that maybe someone here might be able
to save this nice little application software from disappearing by
investing a few days to maintain it.

Thank you for your time,

GM

Re: Should pgAdmin 3 be saved?

[Tom Lane]

Gabriel Martin  writes:
> Do you think there's a possibility that people could fix pgAdmin 3, even if
> it means making a fork or whatever?

You'd probably be better advised to ask this on the pgadmin project lists.

Certainly, anybody who cares to fork and maintain pgAdmin 3 could do so.
It's a matter of finding people with the time, interest, and technical
ability.

regards, tom lane

Re: Should pgAdmin 3 be saved?

[Ian Lawrence Barwick]

2021年2月7日(日) 4:07 Gabriel Martin :

> Hello,
>
> I know that since the launch of pgAdmin 4 in 2016, pgAdmin has been using
> a web-based model, as announced as early as 2014. You only need to do a
> little research on the Web to understand all the reasons behind this
> choice, which I do not seek to question.
>
> However, I wonder if it would be relevant to keep pgAdmin 3 alive. I'm not
> so much talking about improving the interface of pgAdmin 3, but simply
> about keeping the software functional in new systems. I tried to install it
> in the latest versions of Linux Mint and Ubuntu, but the software keeps
> bugging, which it didn't do in older versions of the same systems 5 or 6
> years ago.
>
> Do you think there's a possibility that people could fix pgAdmin 3, even
> if it means making a fork or whatever? Because, although pgAdmin 4 and
> other tools such as OmniDB and DBeaver are probably excellent, some people,
> for various reasons, might want to continue using pgAdmin 3 in 2021. I'm
> not an expert in the field, but I'm thinking that maybe someone here might
> be able to save this nice little application software from disappearing by
> investing a few days to maintain it.
>

FWIW there is a fork here which is making some attempt to keep it up-to-date
with recent PostgreSQL versions:

https://github.com/AbdulYadi/pgadmin3

Regards

Ian Barwick

-- 
EnterpriseDB: https://www.enterprisedb.com

Postgres NOSQL Benchmarking

[Rushikesh socha]

Hi, Any suggestion on how to do PostgreSQL benchmarking for Document Store
database(Nosql/jsonb). I found this
https://github.com/EnterpriseDB/pg_nosql_benchmark while searching google.
Please advise if there anything latest available.

Thanks.