pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedApplication hangs
3+ messages / 3 participants
[nested] [flat]
* Application hangs
@ 2016-01-22 03:47 Ankur Kaushik <ankurkaushik@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Ankur Kaushik @ 2016-01-22 03:47 UTC (permalink / raw)
To: pgsql-admin
Hi ,
We are facing problem in our Live server , Applications hangs after some
time
Please Let me know Is this database related or Application ?
Process list shows as below
postgres=# select count(*) from pg_stat_activity ;
count
-------
37
(1 row)
"*SELECT NULL AS TABLE_CAT*" and "*SHOW TRANSACTION ISOLATION LEVEL*",
Type query increases as
16384 | 15011 | idle | SELECT NULL AS TABLE_CAT, n.nspname AS
TABLE_SCHEM, c.relname AS TABLE_NAME, CASE n.nspname ~ '^pg_' OR n.nspname
= 'information_schema' WHEN true THEN CASE WHE
N n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE
c.relkind WHEN 'r' THEN 'SYSTEM TABLE' WHEN 'v' THEN 'SYSTEM VIEW'
WHEN 'i' THEN 'SYSTEM INDEX' ELSE NULL
END WHEN n.nspname = 'pg_toast' THEN CASE c.relkind WHEN 'r' THEN
'SYSTEM TOAST TABLE' WHEN 'i' THEN 'SYSTEM TOAST INDEX' ELSE NULL END
ELSE CASE c.relkind WHEN 'r' THEN 'TEMPOR
ARY TABLE' WHEN 'i' THEN 'TEMPORARY INDEX' WHEN 'S' THEN 'TEMPORARY
SEQUENCE' WHEN 'v' THEN 'TEMPORARY VIEW' ELSE NULL END END WHEN
false THEN CASE c.relkind WHEN 'r' THEN 'TAB
LE' WHEN 'i' THEN 'INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'v' THEN 'VIEW'
WHEN 'c' THEN 'TYPE' WHEN 'f' THEN 'FOREIGN TABLE' WHEN 'm' THEN
'MATERIALIZED VIEW' ELSE NULL END ELSE NULL
END AS TABLE_TYPE, d.description AS REMARKS FROM
pg_catalog.pg_namespace n, pg_catalog.pg_class c L
16384 | 15012 | idle | SELECT NULL AS TABLE_CAT, n.nspname AS
TABLE_SCHEM, c.relname AS TABLE_NAME, CASE n.nspname ~ '^pg_' OR n.nspname
= 'information_schema' WHEN true THEN CASE WHE
N n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE
c.relkind WHEN 'r' THEN 'SYSTEM TABLE' WHEN 'v' THEN 'SYSTEM VIEW'
WHEN 'i' THEN 'SYSTEM INDEX' ELSE NULL
END WHEN n.nspname = 'pg_toast' THEN CASE c.relkind WHEN 'r' THEN
'SYSTEM TOAST TABLE' WHEN 'i' THEN 'SYSTEM TOAST INDEX' ELSE NULL END
ELSE CASE c.relkind WHEN 'r' THEN 'TEMPOR
ARY TABLE' WHEN 'i' THEN 'TEMPORARY INDEX' WHEN 'S' THEN 'TEMPORARY
SEQUENCE' WHEN 'v' THEN 'TEMPORARY VIEW' ELSE NULL END END WHEN
false THEN CASE c.relkind WHEN 'r' THEN 'TAB
LE' WHEN 'i' THEN 'INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'v' THEN 'VIEW'
WHEN 'c' THEN 'TYPE' WHEN 'f' THEN 'FOREIGN TABLE' WHEN 'm' THEN
'MATERIALIZED VIEW' ELSE NULL END ELSE NULL
END AS TABLE_TYPE, d.description AS REMARKS FROM
pg_catalog.pg_namespace n, pg_catalog.pg_class c L
16384 | 15013 | idle | SELECT NULL AS TABLE_CAT, n.nspname AS
TABLE_SCHEM, c.relname AS TABLE_NAME, CASE n.nspname ~ '^pg_' OR n.nspname
= 'information_schema' WHEN true THEN CASE WHE
N n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE
c.relkind WHEN 'r' THEN 'SYSTEM TABLE' WHEN 'v' THEN 'SYSTEM VIEW'
WHEN 'i' THEN 'SYSTEM INDEX' ELSE NULL
END WHEN n.nspname = 'pg_toast' THEN CASE c.relkind WHEN 'r' THEN
'SYSTEM TOAST TABLE' WHEN 'i' THEN 'SYSTEM TOAST INDEX' ELSE NULL END
ELSE CASE c.relkind WHEN 'r' THEN 'TEMPOR
ARY TABLE' WHEN 'i' THEN 'TEMPORARY INDEX' WHEN 'S' THEN 'TEMPORARY
SEQUENCE' WHEN 'v' THEN 'TEMPORARY VIEW' ELSE NULL END END WHEN
false THEN CASE c.relkind WHEN 'r' THEN 'TAB
LE' WHEN 'i' THEN 'INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'v' THEN 'VIEW'
WHEN 'c' THEN 'TYPE' WHEN 'f' THEN 'FOREIGN TABLE' WHEN 'm' THEN
'MATERIALIZED VIEW' ELSE NULL END ELSE NULL
END AS TABLE_TYPE, d.description AS REMARKS FROM
pg_catalog.pg_namespace n, pg_catalog.pg_class c L
16384 | 15014 | idle | SELECT NULL AS TABLE_CAT, n.nspname AS
TABLE_SCHEM, c.relname AS TABLE_NAME, CASE n.nspname ~ '^pg_' OR n.nspname
= 'information_schema' WHEN true THEN CASE WHE
N n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE
c.relkind WHEN 'r' THEN 'SYSTEM TABLE' WHEN 'v' THEN 'SYSTEM VIEW'
WHEN 'i' THEN 'SYSTEM INDEX' ELSE NULL
END WHEN n.nspname = 'pg_toast' THEN CASE c.relkind WHEN 'r' THEN
'SYSTEM TOAST TABLE' WHEN 'i' THEN 'SYSTEM TOAST INDEX' ELSE NULL END
ELSE CASE c.relkind WHEN 'r' THEN 'TEMPOR
ARY TABLE' WHEN 'i' THEN 'TEMPORARY INDEX' WHEN 'S' THEN 'TEMPORARY
SEQUENCE' WHEN 'v' THEN 'TEMPORARY VIEW' ELSE NULL END END WHEN
false THEN CASE c.relkind WHEN 'r' THEN 'TAB
LE' WHEN 'i' THEN 'INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'v' THEN 'VIEW'
WHEN 'c' THEN 'TYPE' WHEN 'f' THEN 'FOREIGN TABLE' WHEN 'm' THEN
'MATERIALIZED VIEW' ELSE NULL END ELSE NULL
END AS TABLE_TYPE, d.description AS REMARKS FROM
pg_catalog.pg_namespace n, pg_catalog.pg_class c L
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Application hangs
@ 2016-02-01 13:00 jaime soler <jaime.soler@gmail.com>
parent: Ankur Kaushik <ankurkaushik@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: jaime soler @ 2016-02-01 13:00 UTC (permalink / raw)
To: Ankur Kaushik <ankurkaushik@gmail.com>; pgsql-admin
El vie, 22-01-2016 a las 09:17 +0530, Ankur Kaushik escribió:
>
> Hi ,
>
> We are facing problem in our Live server , Applications hangs after
> some time
>
> Please Let me know Is this database related or Application ?
>
> Process list shows as below
>
> postgres=# select count(*) from pg_stat_activity ;
> count
> -------
> 37
> (1 row)
>
>
> "SELECT NULL AS TABLE_CAT" and "SHOW TRANSACTION ISOLATION LEVEL",
> Type query increases as
>
>
>
> 16384 | 15011 | idle | SELECT NULL AS TABLE_CAT, n.nspname AS
> TABLE_SCHEM, c.relname AS TABLE_NAME, CASE n.nspname ~ '^pg_' OR
> n.nspname = 'information_schema' WHEN true THEN CASE WHE
> N n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN
> CASE c.relkind WHEN 'r' THEN 'SYSTEM TABLE' WHEN 'v' THEN 'SYSTEM
> VIEW' WHEN 'i' THEN 'SYSTEM INDEX' ELSE NULL
> END WHEN n.nspname = 'pg_toast' THEN CASE c.relkind WHEN 'r' THEN
> 'SYSTEM TOAST TABLE' WHEN 'i' THEN 'SYSTEM TOAST INDEX' ELSE NULL
> END ELSE CASE c.relkind WHEN 'r' THEN 'TEMPOR
> ARY TABLE' WHEN 'i' THEN 'TEMPORARY INDEX' WHEN 'S' THEN
> 'TEMPORARY SEQUENCE' WHEN 'v' THEN 'TEMPORARY VIEW' ELSE NULL
> END END WHEN false THEN CASE c.relkind WHEN 'r' THEN 'TAB
> LE' WHEN 'i' THEN 'INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'v' THEN
> 'VIEW' WHEN 'c' THEN 'TYPE' WHEN 'f' THEN 'FOREIGN TABLE' WHEN 'm'
> THEN 'MATERIALIZED VIEW' ELSE NULL END ELSE NULL
> END AS TABLE_TYPE, d.description AS REMARKS FROM
> pg_catalog.pg_namespace n, pg_catalog.pg_class c L
> 16384 | 15012 | idle | SELECT NULL AS TABLE_CAT, n.nspname AS
> TABLE_SCHEM, c.relname AS TABLE_NAME, CASE n.nspname ~ '^pg_' OR
> n.nspname = 'information_schema' WHEN true THEN CASE WHE
> N n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN
> CASE c.relkind WHEN 'r' THEN 'SYSTEM TABLE' WHEN 'v' THEN 'SYSTEM
> VIEW' WHEN 'i' THEN 'SYSTEM INDEX' ELSE NULL
> END WHEN n.nspname = 'pg_toast' THEN CASE c.relkind WHEN 'r' THEN
> 'SYSTEM TOAST TABLE' WHEN 'i' THEN 'SYSTEM TOAST INDEX' ELSE NULL
> END ELSE CASE c.relkind WHEN 'r' THEN 'TEMPOR
> ARY TABLE' WHEN 'i' THEN 'TEMPORARY INDEX' WHEN 'S' THEN
> 'TEMPORARY SEQUENCE' WHEN 'v' THEN 'TEMPORARY VIEW' ELSE NULL
> END END WHEN false THEN CASE c.relkind WHEN 'r' THEN 'TAB
> LE' WHEN 'i' THEN 'INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'v' THEN
> 'VIEW' WHEN 'c' THEN 'TYPE' WHEN 'f' THEN 'FOREIGN TABLE' WHEN 'm'
> THEN 'MATERIALIZED VIEW' ELSE NULL END ELSE NULL
> END AS TABLE_TYPE, d.description AS REMARKS FROM
> pg_catalog.pg_namespace n, pg_catalog.pg_class c L
> 16384 | 15013 | idle | SELECT NULL AS TABLE_CAT, n.nspname AS
> TABLE_SCHEM, c.relname AS TABLE_NAME, CASE n.nspname ~ '^pg_' OR
> n.nspname = 'information_schema' WHEN true THEN CASE WHE
> N n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN
> CASE c.relkind WHEN 'r' THEN 'SYSTEM TABLE' WHEN 'v' THEN 'SYSTEM
> VIEW' WHEN 'i' THEN 'SYSTEM INDEX' ELSE NULL
> END WHEN n.nspname = 'pg_toast' THEN CASE c.relkind WHEN 'r' THEN
> 'SYSTEM TOAST TABLE' WHEN 'i' THEN 'SYSTEM TOAST INDEX' ELSE NULL
> END ELSE CASE c.relkind WHEN 'r' THEN 'TEMPOR
> ARY TABLE' WHEN 'i' THEN 'TEMPORARY INDEX' WHEN 'S' THEN
> 'TEMPORARY SEQUENCE' WHEN 'v' THEN 'TEMPORARY VIEW' ELSE NULL
> END END WHEN false THEN CASE c.relkind WHEN 'r' THEN 'TAB
> LE' WHEN 'i' THEN 'INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'v' THEN
> 'VIEW' WHEN 'c' THEN 'TYPE' WHEN 'f' THEN 'FOREIGN TABLE' WHEN 'm'
> THEN 'MATERIALIZED VIEW' ELSE NULL END ELSE NULL
> END AS TABLE_TYPE, d.description AS REMARKS FROM
> pg_catalog.pg_namespace n, pg_catalog.pg_class c L
> 16384 | 15014 | idle | SELECT NULL AS TABLE_CAT, n.nspname AS
> TABLE_SCHEM, c.relname AS TABLE_NAME, CASE n.nspname ~ '^pg_' OR
> n.nspname = 'information_schema' WHEN true THEN CASE WHE
> N n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN
> CASE c.relkind WHEN 'r' THEN 'SYSTEM TABLE' WHEN 'v' THEN 'SYSTEM
> VIEW' WHEN 'i' THEN 'SYSTEM INDEX' ELSE NULL
> END WHEN n.nspname = 'pg_toast' THEN CASE c.relkind WHEN 'r' THEN
> 'SYSTEM TOAST TABLE' WHEN 'i' THEN 'SYSTEM TOAST INDEX' ELSE NULL
> END ELSE CASE c.relkind WHEN 'r' THEN 'TEMPOR
> ARY TABLE' WHEN 'i' THEN 'TEMPORARY INDEX' WHEN 'S' THEN
> 'TEMPORARY SEQUENCE' WHEN 'v' THEN 'TEMPORARY VIEW' ELSE NULL
> END END WHEN false THEN CASE c.relkind WHEN 'r' THEN 'TAB
> LE' WHEN 'i' THEN 'INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'v' THEN
> 'VIEW' WHEN 'c' THEN 'TYPE' WHEN 'f' THEN 'FOREIGN TABLE' WHEN 'm'
> THEN 'MATERIALIZED VIEW' ELSE NULL END ELSE NULL
> END AS TABLE_TYPE, d.description AS REMARKS FROM
> pg_catalog.pg_namespace n, pg_catalog.pg_class c L
Please look that all activity on database server are select on postgres
catalog and their state are idle, so it looks like there is no problem
related to dataserver.
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Application hangs
@ 2016-02-01 13:17 Thomas Kellerer <spam_eater@gmx.net>
parent: Ankur Kaushik <ankurkaushik@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Thomas Kellerer @ 2016-02-01 13:17 UTC (permalink / raw)
To: pgsql-admin
Ankur Kaushik schrieb am 22.01.2016 um 04:47:
> We are facing problem in our Live server , Applications hangs after some time
>
> Please Let me know Is this database related or Application ?
>
> Process list shows as below
>
> postgres=# select count(*) from pg_stat_activity ;
> count
> -------
> 37
> (1 row)
>
>
> "*SELECT NULL AS TABLE_CAT*" and "*SHOW TRANSACTION ISOLATION LEVEL*", Type query increases as
>
If the session's state is "idle", the query shown in pg_stat_activity is the last query the session has run, not the current one (because the session isn't doing anything - it is idle).
As all sessions are idle, there isn't really anything wrong here.
Regarding the query itself, I already answered that in the other thread you opened, but I'll repeat it here as well:
Those queries look suspiciously like the query that is used by DatabaseMetaData.getTables().
I think the c3p0 connection pool has a pretty stupid default configuration where the validation query (that is used to check if a connection is still "alive") uses exactly that call. And I also think this is configured to be called each time a connection is returned to the pool - which imposes a huge (and unnecessary load) on the database server.
If that is the case you should configure a different validation query that is less invasive. Something like: preferredTestQuery="select 42", once that is configure c3p0 will no longer call the (expensive) DatabaseMetaData.getTables() method. It's one of the many reasons I don't like the c3p0 pool. I think Tomcat7's pool is a much better choice (I'm _not_ talking about DBCP, but about the new "native" pool introduced with Tomcat7)
I also recommend to _not_ test the connections when the pools hands them out or if they are returned, because those validation query will then put too much load on the database server.
It's better to configure the pool to validate the connections when they are idle, **not** when they are taken out or returned to the pool.
Again, how you configure this, depends on the pool you are using.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2016-02-01 13:17 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-01-22 03:47 Application hangs Ankur Kaushik <ankurkaushik@gmail.com>
2016-02-01 13:00 ` jaime soler <jaime.soler@gmail.com>
2016-02-01 13:17 ` Thomas Kellerer <spam_eater@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox