Re: vacuumdb not letting me connect to db

[Atul Kumar]

ok, How do I resolve it ?

Any suggestions ?











On 2/5/21, Ron  wrote:
> Your problem screams "IO saturation".
>
> On 2/4/21 12:07 PM, Atul Kumar wrote:
>> There is no error message, when I try to connect the database while
>> running vacuumdb with 300 jobs, it gets stuck.
>>
>> On Thursday, February 4, 2021, Ravi Krishna > > wrote:
>>
>> >The CPU and RAM are normal even on 300 jobs ( only 1-4% of
>> consumption)
>> >but I don’t understand one thing here that if max_connections is set
>> to 700 then
>> >why I am not able to connect the db. As the running jobs (300) are
>> lesser than
>> >half of max_connections.
>>
>> Please paste the error message
>>
>
> --
> Angular momentum makes the world go 'round.
>

RE: cant connect to localhost:5432 (but unix socket ok)

[Jain, Ankit]

Did you try modifying the firewall settings ?

Add postgres service to the firewall. If that doesn’t work, explicitly open the 
port 5433 in the firewall.

From: Joao Miguel Ferreira 
Sent: Thursday, February 4, 2021 10:08 AM
To: Tom Lane 
Cc: dep...@depesz.com; pgsql-general 
Subject: Re: cant connect to localhost:5432 (but unix socket ok)


CAUTION: This email originated from outside of Snap-on. Do not click on links 
or open attachments unless you have validated the sender, even if it is a known 
contact. Contact the sender by phone to validate the contents.


On Thu, Feb 4, 2021 at 3:04 PM Joao Miguel Ferreira 
mailto:joao.miguel.c.ferre...@gmail.com>> 
wrote:


On Thu, Feb 4, 2021 at 3:02 PM Joao Miguel Ferreira 
mailto:joao.miguel.c.ferre...@gmail.com>> 
wrote:
Hi Tom

On Thu, Feb 4, 2021 at 2:50 PM Tom Lane 
mailto:t...@sss.pgh.pa.us>> wrote:
Joao Miguel Ferreira 
mailto:joao.miguel.c.ferre...@gmail.com>> 
writes:
> On Thu, Feb 4, 2021 at 2:26 PM hubert depesz lubaczewski 
> mailto:dep...@depesz.com>>
> wrote:
>>> My database is not listening on TCP/localhost, desptite it is listening
 on the unix socket. How can I investigate this?

> it's on 5433:

Hmm, something odd there, because a port number mismatch should have
resulted in psql failing to connect via unix socket either.  Maybe
you have more than one active postmaster?

"ps xauwww | grep postgres" shows only one postgres process (and a few vaccum 
related)

here is the full list:

root@deb10tp:~# ps xauww | grep postgres
postgres   825  0.0  0.1 213472 14980 ?S09:59   0:01 
/usr/lib/postgresql/11/bin/postgres -D /var/lib/postgresql/11/main -c 
config_file=/etc/postgresql/11/main/postgresql.conf
postgres   847  0.0  0.0 213572  5660 ?Ss   09:59   0:00 postgres: 
11/main: checkpointer
postgres   848  0.0  0.0 213472  3808 ?Ss   09:59   0:00 postgres: 
11/main: background writer
postgres   849  0.0  0.0 213472  3688 ?Ss   09:59   0:00 postgres: 
11/main: walwriter
postgres   850  0.0  0.0 214012  5628 ?Ss   09:59   0:01 postgres: 
11/main: autovacuum launcher
postgres   852  0.0  0.4 102172 34612 ?Ss   09:59   0:10 postgres: 
11/main: stats collector
postgres   853  0.0  0.0 213880  4260 ?Ss   09:59   0:00 postgres: 
11/main: logical replication launcher
root  9652  0.0  0.0   6208   884 pts/1S+   15:06   0:00 grep postgres
root@deb10tp:~#





yes, I see your point. makes sense. the unix socket is actually also on 5433

root@deb10tp:~# grep -nr 543 /etc/postgresql
/etc/postgresql/11/main/postgresql.conf:63:port = 5433
root@deb10tp:~# find /var/run/postgresql/ | grep 543
/var/run/postgresql/.s.PGSQL.5433
/var/run/postgresql/.s.PGSQL.5433.lock
root@deb10tp:~#


Anyway, given these settings, "psql -p 5433 -h localhost" should
connect.  If you still get "connection refused" then you need to
look at the kernel firewall (packet filter) settings.

yes, with "-p 5433" I can connect


regards, tom lane

thanks

Re: updating(column) Porting from Oracle Trigger to PostgreSQL trigger

[Laurenz Albe]

On Fri, 2021-02-05 at 10:57 +0530, Jagmohan Kaintura wrote:
> When we do an implementation from Oracle to PostgreSQL in trigger for  clause 
> like :
>  updating(column_name) ==>   (TG_OP='UPDATE' and OLD.column_name IS 
> DISTINCT FROM NEW.column_name)
> But this condition would mostly not be sufficient when we are updating any 
> column and with similar value somehow. 
> Like : column_name OLD values is 2 and New values updates is 2. 
> 
> In PG it would become (TG_OP="UPDATE' and 2 is DISTINCT FROM 2), Overall it 
> becomes FALSE , so from conversion from ORACLE to PostgreSQL it doesn't give 
> a clear picture for exact implementation for
> these UPDATING clause. 
> 
> Now why I brought up this as I got into one of the implementations which made 
> me crazy to implement. 
> 
> Scenario :
> Column c3 is right not updating to the same value of column, for some bypass 
> implementation.
> 
> update table 
> c1 = 'abc',
> c2 ='xyz',
> c3=c3;
> 
> 
> Inside trigger we have implementation like :
> 
> Oracle:
> --THis block is being placed at the start of Trigger to bypass all other 
> trigger blocks whenever we are updating column c3 , along with other columns.
> IF UPDATING('C3') THEN 
>RETURN;
> END IF;
> 
> If this column c3 is not placed in the update clause, then trigger would go 
> through and execute other statements.
> 
> PostgreSQL:
> We use the same method as :
> IF TC_OP='UPDATE' and OLD.c3 is DISTINCT FROM NEW.C3 THEN 
>RETURN NEW;
> END IF;

There is no way to determine inside the trigger function which columns were
mentioned in the SET clause of the UPDATE statement.

But you can do that in CREATE TRIGGER:

  CREATE TRIGGER ... BEFORE UPDATE OF (c3) ON mytable ...

Of course that might mean that you cannot do conditional processing
in the trigger function, but you have to define several triggers
(which could perhaps share a trigger function).

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: cant connect to localhost:5432 (but unix socket ok)

[Joao Miguel Ferreira]

Hello Jain

On Fri, Feb 5, 2021 at 1:16 PM Jain, Ankit  wrote:

> Did you try modifying the firewall settings ?
>
>
>
> Add postgres service to the firewall. If that doesn’t work, explicitly
> open the port 5433 in the firewall.
>
>
> Anyway, given these settings, "psql -p 5433 -h localhost" should
> connect.  If you still get "connection refused" then you need to
> look at the kernel firewall (packet filter) settings.
>
>
>
> yes, with "-p 5433" I can connect
>
>
>
> It was not necessary to change firewall settings because I was able to
connect to the database with the "-p 5433" option

and all is fine now

thanks

Re: vacuumdb not letting me connect to db

[Ron]

Obviously... don't use 300 threads.

On 2/5/21 2:15 AM, Atul Kumar wrote:

ok, How do I resolve it ?

Any suggestions ?

On 2/5/21, Ron  wrote:

Your problem screams "IO saturation".

On 2/4/21 12:07 PM, Atul Kumar wrote:

There is no error message, when I try to connect the database while
running vacuumdb with 300 jobs, it gets stuck.

On Thursday, February 4, 2021, Ravi Krishna mailto:rkrishna...@aol.com>> wrote:

 >The CPU and RAM are normal even on 300 jobs ( only 1-4% of
consumption)
 >but I don’t understand one thing here that if max_connections is set
 to 700 then
 >why I am not able to connect the db. As the running jobs (300) are
 lesser than
 >half of max_connections.

 Please paste the error message


--
Angular momentum makes the world go 'round.



--
Angular momentum makes the world go 'round.

Re: vacuumdb not letting me connect to db

[Rob Sargent]

On 2/5/21 9:11 AM, Ron wrote:

Obviously... don't use 300 threads.


No, no Ron.  Clearly the answer is more CPUs

Re: vacuumdb not letting me connect to db

[Ron]

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.

Re: vacuumdb not letting me connect to db

[Gavan Schneider]

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