Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[DrakoRod]

You're right I have forgotten to say, the OS is RHEL 7.

Actually I'm reading about.

Thanks!





-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions 
--
View this message in context: 
http://www.postgresql-archive.org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968923p5969564.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[Melvin Davidson]

On Fri, Jun 30, 2017 at 11:36 AM, DrakoRod  wrote:

> > Do you control the app?
>
> Nop Just I know how it's developed.
>
> > The app has a pooling component and you still are having problems, have
> > you looked at what the pooler is actually doing?
>
> As far as I know, the wildfly's jdbc pool. No really I don't know what are
> doing. I suspect that problem is that in DAO's not are closing the sessions
> or not beginning transactions properly. I going to ask them send me the
> logfile or I'll could verify the pool behavior.
>
> > Not sure what the above means. Are you saying the application you refer
> > to above has a history of not correctly closing connections or are you
> > talking in general terms about applications interacting with databases.
>
> Sorry, it's not like that, just was a comment, The problem I have is with a
> specific application.
>
> > I've attached two files that may be helpful to you.
>
> Melvin , Thanks for the scripts! I owe one!
>
> I have another question, I've was reading about the lock_timeout, Somehow
> this parameter will help or could affect all the behaviour?
>
> Thanks!
>
>
>
>
>
>
> -
> Dame un poco de fe, eso me bastará.
> Rozvo Ware Solutions
> --
> View this message in context: http://www.postgresql-archive.
> org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-
> tp5968923p5969552.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

>I've was reading about the lock_timeout, Somehow this parameter will help
or could affect all the behaviour?
This affects all behavior and will abort statements that wait too long to
get locks.

https://www.postgresql.org/docs/9.4/static/runtime-config-client.html

lock_timeout (integer)

Abort any statement that waits longer than the specified number of
milliseconds while attempting to acquire a lock on a table, index, row, or
other database object. The time limit applies separately to each lock
acquisition attempt. The limit applies both to explicit locking requests
(such as LOCK TABLE, or SELECT FOR UPDATE without NOWAIT) and to
implicitly-acquired locks. If log_min_error_statement is set to ERROR or
lower, the statement that timed out will be logged. A value of zero (the
default) turns this off.

Unlike statement_timeout, this timeout can only occur while waiting for
locks. Note that if statement_timeout is nonzero, it is rather pointless to
set lock_timeout to the same or larger value, since the statement timeout
would always trigger first.

*Setting lock_timeout in postgresql.conf is not recommended because it
would affect all sessions.*


*You are probably much better off using tcp_keepalives... , providing your
system supports it. I don't remember if you ever gave us *


*the O/S.*



*Have a look at them. You might possibly be able to use them to force
disconnect after a set amount of inactivity
time.https://www.postgresql.org/docs/current/static/runtime-config-connection.html
*
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[DrakoRod]

> Do you control the app?

Nop Just I know how it's developed. 

> The app has a pooling component and you still are having problems, have 
> you looked at what the pooler is actually doing? 

As far as I know, the wildfly's jdbc pool. No really I don't know what are
doing. I suspect that problem is that in DAO's not are closing the sessions
or not beginning transactions properly. I going to ask them send me the
logfile or I'll could verify the pool behavior.

> Not sure what the above means. Are you saying the application you refer 
> to above has a history of not correctly closing connections or are you 
> talking in general terms about applications interacting with databases. 

Sorry, it's not like that, just was a comment, The problem I have is with a
specific application. 

> I've attached two files that may be helpful to you. 

Melvin , Thanks for the scripts! I owe one! 

I have another question, I've was reading about the lock_timeout, Somehow
this parameter will help or could affect all the behaviour?

Thanks! 






-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions 
--
View this message in context: 
http://www.postgresql-archive.org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968923p5969552.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[Melvin Davidson]

On Thu, Jun 29, 2017 at 7:30 PM, Adrian Klaver 
wrote:

> On 06/29/2017 10:03 AM, DrakoRod wrote:
>
>> To expand information, the application are written in Grails on wildfly
>> with
>> pool connections.
>>
>
> Do you control the app?
>
> The app has a pooling component and you still are having problems, have
> you looked at what the pooler is actually doing?
>
>
>> I didn't have time to check pg_locks with detail, I'll configure the
>> connections logs to monitoring those.
>>
>> I can't close connections on the application side. How I close connections
>> on the database side? With pg_terminate_backend, pg_cancel_backend or
>> exists
>> other function? I didn't want terminate backends because all connections
>> state was active.
>>
>
> https://www.postgresql.org/docs/9.4/static/functions-admin.
> html#FUNCTIONS-ADMIN-SIGNAL
>
> "pg_cancel_backend(pid int) boolean Cancel a backend's current
> query. You can execute this against another backend that has exactly the
> same role as the user calling the function. In all other cases, you must be
> a superuser.
>
> ...The process ID of an active backend can be found from the pid column of
> the pg_stat_activity view, ..."
>
>
>> I refer only to "idle" because almost in every database that I've saw the
>> application doesn't close correctly the connections. If are "idle in
>> transaction" is not normal.
>>
>
> Not sure what the above means. Are you saying the application you refer to
> above has a history of not correctly closing connections or are you talking
> in general terms about applications interacting with databases.
>
>
>> Your right Adrian, I need to know why the connections are not closing
>> properly.
>>
>> I can't apply idle_in_transation_session_timeout because the version of
>> PostgreSQL is 9.4.4 and the paramater not yet i'ts included. But sounds
>> good
>> the upgrade.
>>
>> Thanks for your help!
>>
>>
>>
>>
>> -
>> Dame un poco de fe, eso me bastará.
>> Rozvo Ware Solutions
>> --
>> View this message in context: http://www.postgresql-archive.
>> org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968
>> 923p5969262.html
>> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>>
>>
>>
>
>
>
> *DrakoRod,*
>
> *If you are concerned that the application is leaving connections 'idle in
> transaction', I've attached two files that may be helpful to you. *
>
> *The first is a SQL query -> current_queries.sql  'idle in transaction'
> will appear in the "state" column. *
>
>
> *CAVEAT EMPTOR! in PG 9.6 the "CASE WHEN waiting ..."  needs to be
> commented out..*
> *The second is a Linux script "kill_long_idles.sh" that will kill any
> connection that is *
> *'idle in transaction' for longer than max_time. Currently it is set to 30
> minutes, but you can adjust to*
>
> * your desire. Just run it from a root cron job.*
>
> *Don't forget to   chmod +x  **kill_long_idles.sh*
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.
SELECT backend_start as be_start,
   datname,
   pid as pid,
   client_addr,
   usename as user,
   state,
   query,
   CASE WHEN waiting = TRUE
THEN 'BLOCKED'
ELSE 'no'
END as waiting,
   query_start,
   current_timestamp - query_start as duration 
  FROM pg_stat_activity
 WHERE pg_backend_pid() <> pid
ORDER BY 1, 
 datname,
 query_start;



kill_long_idles.sh
Description: Bourne shell script

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[Adrian Klaver]

On 06/29/2017 10:03 AM, DrakoRod wrote:

To expand information, the application are written in Grails on wildfly with
pool connections.


Do you control the app?

The app has a pooling component and you still are having problems, have 
you looked at what the pooler is actually doing?




I didn't have time to check pg_locks with detail, I'll configure the
connections logs to monitoring those.

I can't close connections on the application side. How I close connections
on the database side? With pg_terminate_backend, pg_cancel_backend or exists
other function? I didn't want terminate backends because all connections
state was active.


https://www.postgresql.org/docs/9.4/static/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL

"pg_cancel_backend(pid int) 	boolean 	Cancel a backend's current query. 
You can execute this against another backend that has exactly the same 
role as the user calling the function. In all other cases, you must be a 
superuser.


...The process ID of an active backend can be found from the pid column 
of the pg_stat_activity view, ..."




I refer only to "idle" because almost in every database that I've saw the
application doesn't close correctly the connections. If are "idle in
transaction" is not normal.


Not sure what the above means. Are you saying the application you refer 
to above has a history of not correctly closing connections or are you 
talking in general terms about applications interacting with databases.




Your right Adrian, I need to know why the connections are not closing
properly.

I can't apply idle_in_transation_session_timeout because the version of
PostgreSQL is 9.4.4 and the paramater not yet i'ts included. But sounds good
the upgrade.

Thanks for your help!




-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions
--
View this message in context: 
http://www.postgresql-archive.org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968923p5969262.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[David G. Johnston]

On Thu, Jun 29, 2017 at 10:03 AM, DrakoRod  wrote:

> I can't close connections on the application side. How I close connections
> on the database side? With pg_terminate_backend, pg_cancel_backend or
> exists
> other function? I didn't want terminate backends because all connections
> state was active.
>
> I refer only to "idle" because almost in every database that I've saw the
> application doesn't close correctly the connections. If are "idle in
> transaction" is not normal.
>

​If you have lots of permanently maintained client sessions ​and cannot
change the client behavior you either need to try and get transaction
pooling working in a proxy pooling layer and point that clients to that OR
increase the number of simultaneous sessions your server will allow to some
number larger than the maximum possible concurrently connected clients.
And hope the server can handle the extra load.

David J.

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[DrakoRod]

To expand information, the application are written in Grails on wildfly with
pool connections.

I didn't have time to check pg_locks with detail, I'll configure the
connections logs to monitoring those.

I can't close connections on the application side. How I close connections
on the database side? With pg_terminate_backend, pg_cancel_backend or exists
other function? I didn't want terminate backends because all connections
state was active.

I refer only to "idle" because almost in every database that I've saw the
application doesn't close correctly the connections. If are "idle in
transaction" is not normal. 

Your right Adrian, I need to know why the connections are not closing
properly.

I can't apply idle_in_transation_session_timeout because the version of
PostgreSQL is 9.4.4 and the paramater not yet i'ts included. But sounds good
the upgrade. 

Thanks for your help! 




-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions 
--
View this message in context: 
http://www.postgresql-archive.org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968923p5969262.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[PT]

On Tue, 27 Jun 2017 16:16:53 -0700 (MST)
DrakoRod  wrote:

> Yep, the real problem was all connections are used up. A ps command showed
> this:
> 
> postgres  1172 23340  1 13:00 ?00:01:23 postgres: dbsomething
> dbsomething 8.8.8.1[34024] PARSE waiting
> postgres  1527 23340  3 13:07 ?00:02:47 postgres: dbsomething
> dbsomething 8.8.8.2[49193] PARSE waiting
> postgres  1869 23340  1 13:13 ?00:01:05 postgres: dbsomething
> dbsomething 8.8.8.1[34209] PARSE waiting
> postgres  1963 23340  0 13:15 ?00:00:23 postgres: dbsomething
> dbsomething 8.8.8.1[34244] PARSE waiting
> postgres  2408 23340  2 13:23 ?00:01:31 postgres: dbsomething
> dbsomething 8.8.8.3[38324] PARSE waiting
> postgres  2442 23340  3 13:23 ?00:02:19 postgres: dbsomething
> dbsomething 8.8.8.3[38359] PARSE waiting
> postgres  2526 23340  2 13:25 ?00:01:39 postgres: dbsomething
> dbsomething 8.8.8.2[49994] PARSE waiting
> postgres  2533 23340  2 13:25 ?00:02:00 postgres: dbsomething
> dbsomething 8.8.8.4[58916] PARSE waiting
> postgres  2616 23340  2 13:26 ?00:01:28 postgres: dbsomething
> dbsomething 8.8.8.3[38496] PARSE waiting
> postgres  2632 23340  3 13:27 ?00:02:09 postgres: dbsomething
> dbsomething 8.8.8.2[50088] idle in transaction
> postgres  2644 23340  0 13:27 ?00:00:25 postgres: dbsomething
> dbsomething 8.8.8.4[58999] PARSE waiting
> postgres  2787 23340  0 13:30 ?00:00:16 postgres: dbsomething
> dbsomething 8.8.8.5[57944] PARSE waiting
> postgres  2815 23340  1 13:31 ?00:00:52 postgres: dbsomething
> dbsomething 8.8.8.2[50263] PARSE waiting
> postgres  2822 23340  0 13:31 ?00:00:29 postgres: dbsomething
> dbsomething 8.8.8.4[59158] PARSE waiting
> postgres  2825 23340  1 13:31 ?00:00:47 postgres: dbsomething
> dbsomething 8.8.8.4[59161] PARSE waiting
> postgres  2826 23340  0 13:31 ?00:00:11 postgres: dbsomething
> dbsomething 8.8.8.4[59163] PARSE waiting
> postgres  2876 23340  0 13:32 ?00:00:26 postgres: dbsomething
> dbsomething 8.8.8.1[34469] PARSE waiting
> postgres  2888 23340  0 13:32 ?00:00:36 postgres: dbsomething
> dbsomething 8.8.8.3[38729] PARSE waiting
> postgres  2911 23340  0 13:33 ?00:00:11 postgres: dbsomething
> dbsomething 8.8.8.2[50352] PARSE waiting
> postgres  2912 23340  0 13:33 ?00:00:36 postgres: dbsomething
> dbsomething 8.8.8.2[50353] PARSE waiting
> postgres  2916 23340  0 13:33 ?00:00:30 postgres: dbsomething
> dbsomething 8.8.8.3[38750] PARSE waiting
> postgres  2922 23340  0 13:33 ?00:00:33 postgres: dbsomething
> dbsomething 8.8.8.4[59238] PARSE waiting
> postgres  2927 23340  1 13:33 ?00:00:38 postgres: dbsomething
> dbsomething 8.8.8.4[59242] PARSE waiting
> postgres  3012 23340  0 13:35 ?00:00:03 postgres: dbsomething
> dbsomething 8.8.8.2[50439] PARSE waiting
> postgres  3017 23340  0 13:35 ?00:00:01 postgres: dbsomething
> dbsomething 8.8.8.3[38833] PARSE waiting
> postgres  3018 23340  0 13:35 ?00:00:27 postgres: dbsomething
> dbsomething 8.8.8.3[38834] PARSE waiting
> postgres  3020 23340  0 13:35 ?00:00:24 postgres: dbsomething
> dbsomething 8.8.8.4[59318] PARSE waiting
> postgres  3026 23340  0 13:35 ?00:00:04 postgres: dbsomething
> dbsomething 8.8.8.4[59323] PARSE waiting
> postgres  3033 23340  0 13:35 ?00:00:15 postgres: dbsomething
> dbsomething 8.8.8.4[59328] PARSE waiting
> 
> 
> When I ran *SELECT * FROM pg_stat_activity*, the state in all queries was
> active and most were SELECTs, then the server did not open new connections.
> I canceled many queries (only SELECTs) and server back to normal.
> 
> I understand that the principal problem probably are the application, of
> that I'm sure, but in the process debug. The best way to avoid or "fix" this
> are with connections pool like pgbouncer? How is the most secure way to
> return connections without restart service?

There are various timeout settings that can be configured:
https://www.postgresql.org/docs/9.6/static/runtime-config-client.html
idle_in_transation_session_timeout is probably the one you want to
enable.

It's likely that your application developers will start to complain about
database "errors" once you enable that, as connections will get killed and
cause errors on the application. You'll need to work to educate your
developers on how to fix their application so the situation stops happening.

-- 
PT 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[Adrian Klaver]

On 06/27/2017 04:16 PM, DrakoRod wrote:

Yep, the real problem was all connections are used up. A ps command showed
this:

postgres  1172 23340  1 13:00 ?00:01:23 postgres: dbsomething
dbsomething 8.8.8.1[34024] PARSE waiting
postgres  1527 23340  3 13:07 ?00:02:47 postgres: dbsomething
dbsomething 8.8.8.2[49193] PARSE waiting


Hmm, the above is new one to me. Some searching found this:

https://www.postgresql.org/message-id/1282602153-sup-6272%40alvh.no-ip.org

"It means the parse phase is waiting for a lock.  You can see exactly
what it's waiting for by looking at pg_locks "WHERE NOT GRANTED"."

If you have not already, you might want to log 
connections/disconnections for more insight:


https://www.postgresql.org/docs/9.6/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT



When I ran *SELECT * FROM pg_stat_activity*, the state in all queries was
active and most were SELECTs, then the server did not open new connections.
I canceled many queries (only SELECTs) and server back to normal.

I understand that the principal problem probably are the application, of
that I'm sure, but in the process debug. The best way to avoid or "fix" this
are with connections pool like pgbouncer? How is the most secure way to
return connections without restart service?


Close the connection.



I never had this problem, the idle connections is the normal in almost every
database I managed, but this is new for me.


FYI there is a difference between 'idle' connections and 'idle in 
transaction', not sure which one you are referring to. See below for 
more info:


https://www.postgresql.org/docs/9.6/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

The bottom line is connections are resources that need to be managed. To 
better able to do that is going to require some detective work to 
determine what is generating the connections and for what purpose.




Thanks for your help!




-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[DrakoRod]

Yep, the real problem was all connections are used up. A ps command showed
this:

postgres  1172 23340  1 13:00 ?00:01:23 postgres: dbsomething
dbsomething 8.8.8.1[34024] PARSE waiting
postgres  1527 23340  3 13:07 ?00:02:47 postgres: dbsomething
dbsomething 8.8.8.2[49193] PARSE waiting
postgres  1869 23340  1 13:13 ?00:01:05 postgres: dbsomething
dbsomething 8.8.8.1[34209] PARSE waiting
postgres  1963 23340  0 13:15 ?00:00:23 postgres: dbsomething
dbsomething 8.8.8.1[34244] PARSE waiting
postgres  2408 23340  2 13:23 ?00:01:31 postgres: dbsomething
dbsomething 8.8.8.3[38324] PARSE waiting
postgres  2442 23340  3 13:23 ?00:02:19 postgres: dbsomething
dbsomething 8.8.8.3[38359] PARSE waiting
postgres  2526 23340  2 13:25 ?00:01:39 postgres: dbsomething
dbsomething 8.8.8.2[49994] PARSE waiting
postgres  2533 23340  2 13:25 ?00:02:00 postgres: dbsomething
dbsomething 8.8.8.4[58916] PARSE waiting
postgres  2616 23340  2 13:26 ?00:01:28 postgres: dbsomething
dbsomething 8.8.8.3[38496] PARSE waiting
postgres  2632 23340  3 13:27 ?00:02:09 postgres: dbsomething
dbsomething 8.8.8.2[50088] idle in transaction
postgres  2644 23340  0 13:27 ?00:00:25 postgres: dbsomething
dbsomething 8.8.8.4[58999] PARSE waiting
postgres  2787 23340  0 13:30 ?00:00:16 postgres: dbsomething
dbsomething 8.8.8.5[57944] PARSE waiting
postgres  2815 23340  1 13:31 ?00:00:52 postgres: dbsomething
dbsomething 8.8.8.2[50263] PARSE waiting
postgres  2822 23340  0 13:31 ?00:00:29 postgres: dbsomething
dbsomething 8.8.8.4[59158] PARSE waiting
postgres  2825 23340  1 13:31 ?00:00:47 postgres: dbsomething
dbsomething 8.8.8.4[59161] PARSE waiting
postgres  2826 23340  0 13:31 ?00:00:11 postgres: dbsomething
dbsomething 8.8.8.4[59163] PARSE waiting
postgres  2876 23340  0 13:32 ?00:00:26 postgres: dbsomething
dbsomething 8.8.8.1[34469] PARSE waiting
postgres  2888 23340  0 13:32 ?00:00:36 postgres: dbsomething
dbsomething 8.8.8.3[38729] PARSE waiting
postgres  2911 23340  0 13:33 ?00:00:11 postgres: dbsomething
dbsomething 8.8.8.2[50352] PARSE waiting
postgres  2912 23340  0 13:33 ?00:00:36 postgres: dbsomething
dbsomething 8.8.8.2[50353] PARSE waiting
postgres  2916 23340  0 13:33 ?00:00:30 postgres: dbsomething
dbsomething 8.8.8.3[38750] PARSE waiting
postgres  2922 23340  0 13:33 ?00:00:33 postgres: dbsomething
dbsomething 8.8.8.4[59238] PARSE waiting
postgres  2927 23340  1 13:33 ?00:00:38 postgres: dbsomething
dbsomething 8.8.8.4[59242] PARSE waiting
postgres  3012 23340  0 13:35 ?00:00:03 postgres: dbsomething
dbsomething 8.8.8.2[50439] PARSE waiting
postgres  3017 23340  0 13:35 ?00:00:01 postgres: dbsomething
dbsomething 8.8.8.3[38833] PARSE waiting
postgres  3018 23340  0 13:35 ?00:00:27 postgres: dbsomething
dbsomething 8.8.8.3[38834] PARSE waiting
postgres  3020 23340  0 13:35 ?00:00:24 postgres: dbsomething
dbsomething 8.8.8.4[59318] PARSE waiting
postgres  3026 23340  0 13:35 ?00:00:04 postgres: dbsomething
dbsomething 8.8.8.4[59323] PARSE waiting
postgres  3033 23340  0 13:35 ?00:00:15 postgres: dbsomething
dbsomething 8.8.8.4[59328] PARSE waiting


When I ran *SELECT * FROM pg_stat_activity*, the state in all queries was
active and most were SELECTs, then the server did not open new connections.
I canceled many queries (only SELECTs) and server back to normal.

I understand that the principal problem probably are the application, of
that I'm sure, but in the process debug. The best way to avoid or "fix" this
are with connections pool like pgbouncer? How is the most secure way to
return connections without restart service?

I never had this problem, the idle connections is the normal in almost every
database I managed, but this is new for me.

Thanks for your help! 




-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions 
--
View this message in context: 
http://www.postgresql-archive.org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968923p5968960.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[David G. Johnston]

On Tue, Jun 27, 2017 at 3:41 PM, Melvin Davidson 
wrote:

> *His problem is NOT 'idle in transaction' per se. It is all connections
> are used up.*
> *Hence the need for pg_bouncer for connection pooling.*
>
>
Whether pg_bouncer provides a viable solution is just as big an unknown as
whether "idle in transaction" is the biggest contributor to the problem.
If all of them are idle in transaction then pg_bouncer is powerless to
help.  If they are generally just long-lived sessions and only a few stay
in transaction then transaction pooling mode may help.

David J.

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[Adrian Klaver]

On 06/27/2017 03:41 PM, Melvin Davidson wrote:



On Tue, Jun 27, 2017 at 6:32 PM, Adrian Klaver 


*His problem is NOT 'idle in transaction' per se. It is all connections 
are used up.*


Not following. The 'idle in transaction' queries are coming in through a 
connection so having them around is keeping a connection open. Dealing 
with them will help with the connection count. Using pg_bouncer is an 
option, but if you have a process that is not properly closing 
transactions/connections you could get into an arms race between that 
process and the size of your connection pool.



*Hence the need for pg_bouncer for connection pooling.*
--
*Melvin Davidson*




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[PT]

On Tue, 27 Jun 2017 18:41:25 -0400
Melvin Davidson  wrote:

> On Tue, Jun 27, 2017 at 6:32 PM, Adrian Klaver 
> wrote:
> 
> > On 06/27/2017 01:10 PM, DrakoRod wrote:
> >
> >> Hi folks.
> >>
> >> Today I had a problem with production's database PostgreSQL version
> >> 9.4.4.9.
> >> The server have max_connections set to 200, but today I reviewed
> >> pg_stat_activity and saw 199 active connections, obviously the server
> >> rejected any new connection and the production stopped.
> >>
> >> I saw another posts with a similar problems, but this was because the
> >> pg_xlog was full or disk does'nt write, but the directory and disk  had no
> >> problems.
> >>
> >> I just canceled some SELECTs querys and the server returned to normality.
> >> Now a monitoring activity of server and I can see some backends like this:
> >>
> >> postgres  9737 23340  2 14:55 ?00:00:15 postgres: dbname user
> >> 8.8.8.8[37082] idle in transaction
> >> postgres  9741 23340  9 14:55 ?00:00:47 postgres: dbname user
> >> 8.8.8.8[54286] idle in transaction
> >>
> >> Any suggestions?
> >>
> >
> > https://www.postgresql.org/docs/9.4/static/monitoring-stats.
> > html#PG-STAT-ACTIVITY-VIEW
> >
> > SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';
> >
> > To get more detail on what is holding these transactions open.
> >
> 
> *His problem is NOT 'idle in transaction' per se. It is all connections are
> used up.*
> *Hence the need for pg_bouncer for connection pooling.*

That assessment lacks enough information to be substantiated.

One of the things I've frequently seen happen is applications written with
ORMs will create a new connection because the existing connections in the
pool are already in a transaction. If his application is not properly
committing transactions, an additional pooler layer will not improve on
the problem. Hence, what he needs to do first is gather more information and
understand exactly what's going on.

Of course, if usage has just scaled up to the point where he doesn't have
any free connections, then your assessment might be correct. But he hasn't
provided enough information to be sure of that.

Regardless, lots of "idle in transaction" connections that stick around a
long time is a clear sign of application bugs. If they're not the cause
of his immediate problem, they will be the cause of problems at some point,
so he might as well track them down and fix them.

-- 
PT 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[Melvin Davidson]

On Tue, Jun 27, 2017 at 6:32 PM, Adrian Klaver 
wrote:

> On 06/27/2017 01:10 PM, DrakoRod wrote:
>
>> Hi folks.
>>
>> Today I had a problem with production's database PostgreSQL version
>> 9.4.4.9.
>> The server have max_connections set to 200, but today I reviewed
>> pg_stat_activity and saw 199 active connections, obviously the server
>> rejected any new connection and the production stopped.
>>
>> I saw another posts with a similar problems, but this was because the
>> pg_xlog was full or disk does'nt write, but the directory and disk  had no
>> problems.
>>
>> I just canceled some SELECTs querys and the server returned to normality.
>> Now a monitoring activity of server and I can see some backends like this:
>>
>> postgres  9737 23340  2 14:55 ?00:00:15 postgres: dbname user
>> 8.8.8.8[37082] idle in transaction
>> postgres  9741 23340  9 14:55 ?00:00:47 postgres: dbname user
>> 8.8.8.8[54286] idle in transaction
>>
>> Any suggestions?
>>
>
> https://www.postgresql.org/docs/9.4/static/monitoring-stats.
> html#PG-STAT-ACTIVITY-VIEW
>
> SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';
>
> To get more detail on what is holding these transactions open.
>
>
>>
>>
>> -
>> Dame un poco de fe, eso me bastará.
>> Rozvo Ware Solutions
>> --
>> View this message in context: http://www.postgresql-archive.
>> org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968923.html
>> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

*His problem is NOT 'idle in transaction' per se. It is all connections are
used up.*
*Hence the need for pg_bouncer for connection pooling.*
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[Adrian Klaver]

On 06/27/2017 01:10 PM, DrakoRod wrote:

Hi folks.

Today I had a problem with production's database PostgreSQL version 9.4.4.9.
The server have max_connections set to 200, but today I reviewed
pg_stat_activity and saw 199 active connections, obviously the server
rejected any new connection and the production stopped.

I saw another posts with a similar problems, but this was because the
pg_xlog was full or disk does'nt write, but the directory and disk  had no
problems.

I just canceled some SELECTs querys and the server returned to normality.
Now a monitoring activity of server and I can see some backends like this:

postgres  9737 23340  2 14:55 ?00:00:15 postgres: dbname user
8.8.8.8[37082] idle in transaction
postgres  9741 23340  9 14:55 ?00:00:47 postgres: dbname user
8.8.8.8[54286] idle in transaction

Any suggestions?


https://www.postgresql.org/docs/9.4/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';

To get more detail on what is holding these transactions open.





-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions
--
View this message in context: 
http://www.postgresql-archive.org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968923.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[David G. Johnston]

On Tue, Jun 27, 2017 at 1:10 PM, DrakoRod  wrote:

> postgres  9741 23340  9 14:55 ?00:00:47 postgres: dbname user
> 8.8.8.8[54286] idle in transaction
>
> Any suggestions?
>

​There is a serious lack of information provided here but "idle in
transaction" sessions are generally problematic (in particular they
continue to hold locks) and can only be fixed at the source - by fixing
code or user behavior.

David J.

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[Melvin Davidson]

On Tue, Jun 27, 2017 at 4:10 PM, DrakoRod  wrote:

> Hi folks.
>
> Today I had a problem with production's database PostgreSQL version
> 9.4.4.9.
> The server have max_connections set to 200, but today I reviewed
> pg_stat_activity and saw 199 active connections, obviously the server
> rejected any new connection and the production stopped.
>
> I saw another posts with a similar problems, but this was because the
> pg_xlog was full or disk does'nt write, but the directory and disk  had no
> problems.
>
> I just canceled some SELECTs querys and the server returned to normality.
> Now a monitoring activity of server and I can see some backends like this:
>
> postgres  9737 23340  2 14:55 ?00:00:15 postgres: dbname user
> 8.8.8.8[37082] idle in transaction
> postgres  9741 23340  9 14:55 ?00:00:47 postgres: dbname user
> 8.8.8.8[54286] idle in transaction
>
> Any suggestions?
>
>
>
> -
> Dame un poco de fe, eso me bastará.
> Rozvo Ware Solutions
> --
> View this message in context: http://www.postgresql-archive.
> org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968923.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


*Are you asking how to track down the user using up all the connection?
With the information you provided that cannot. be down.*



*If you are asking how to prevent problems in the future, then install
Pg_Bouncer and use that to pool connections.https://pgbouncer.github.io/
*
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.