Thread: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting
On Tue, Jun 27, 2017 at 4:10 PM, DrakoRod <drakoflames@hotmail.com> 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-2222-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/
--
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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On Tue, Jun 27, 2017 at 6:32 PM, Adrian Klaver <adrian.klaver@aklaver.com> 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-2222-PARSE-waiting-tp5968 923.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Adrian Klaver
adrian.klaver@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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On Thu, Jun 29, 2017 at 7:30 PM, Adrian Klaver <adrian.klaver@aklaver.com> 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-2222-PARSE-waiting-tp5968 923p5969262.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.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.CAVEAT EMPTOR! in PG 9.6 the "CASE WHEN waiting ..." needs to be commented out..The first is a SQL query -> current_queries.sql 'idle in transaction' will appear in the "state" column.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.
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.klaver@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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Attachment
On Fri, Jun 30, 2017 at 11:36 AM, DrakoRod <drakoflames@hotmail.com> 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-2222-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
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
--
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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.