Thread: System Query hit to Database

System Query hit to Database

From
dhaval jaiswal
Date:

Kindly redirect me to the correct forum, If below question is not relevant to you.  



I am getting lot of following queries on my Database.  I need  your help to stop hitting this kind of queries to database.

While going through search found the following article saying it's caused by jdbc driver.

http://stackoverflow.com/questions/5453992/a-very-strange-sql-of-postgresql

 

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  WHEN 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 'TEMPORARY 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 'TABLE'  WHEN 'i' THEN 'INDEX'  WHEN 'S' THEN 'SEQUENCE'  WHEN 'v' THEN 'VIEW'  WHEN 'c' THEN 'TYPE'  ELSE NULL  END  ELSE NULL  END  AS TABLE_TYPE, d.description AS REMARKS  FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c  LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)  LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class')  LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog')  WHERE c.relnamespace = n.oid  AND c.relname LIKE 'PROBABLYNOT'  AND (false  OR ( c.relkind = 'r' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' ) )  ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME



Environment details:

Version: PostgreSQL 9.4.0

Driver version: postgresql-9.4-1201.jdbc41.jar

JVM: 1.7



Re: System Query hit to Database

From
Thomas Kellerer
Date:
dhaval jaiswal schrieb am 11.02.2016 um 06:02:
> Kindly redirect me to the correct forum, If below question is not relevant to you.
>
>
> I am getting lot of following queries on my Database.  I need  your help to stop hitting this kind of queries to
database.
>
> While going through search found the following article saying it's caused by jdbc driver.
>
> http://stackoverflow.com/questions/5453992/a-very-strange-sql-of-postgresql
>
> 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  WHEN 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 'TEMPORARY 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
> 'TABLE'  WHEN 'i' THEN 'INDEX'  WHEN 'S' THEN 'SEQUENCE' WHEN 'v'
> THEN 'VIEW'  WHEN 'c' THEN 'TYPE'  ELSE NULL  END  ELSE NULL  END  AS
> TABLE_TYPE, d.description AS REMARKS  FROM pg_catalog.pg_namespace n,
> pg_catalog.pg_class c  LEFT JOIN pg_catalog.pg_description d ON
> (c.oid = d.objoid AND d.objsubid = 0)  LEFT JOIN pg_catalog.pg_class
> dc ON (d.classoid=dc.oid AND dc.relname='pg_class')  LEFT JOIN
> pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND
> dn.nspname='pg_catalog')  WHERE c.relnamespace = n.oid  AND c.relname
> LIKE 'PROBABLYNOT'  AND (false  OR ( c.relkind = 'r' AND n.nspname !~
> '^pg_' AND n.nspname <> 'information_schema' ) )  ORDER BY
> TABLE_TYPE,TABLE_SCHEM,TABLE_NAME
>

this is the query sent by getTables() (as mention on Stackoverflow).

There are some connection pools out there that have a stupid default configuration and use getTables() as the query to
validateif a connection is still alive. If you additionally have enabled validating connections when they are taken out
orreturned to the pool, this will be called for each "getConnection()" inside your code. 

Check the configuration of your connection pool.



Re: System Query hit to Database

From
John R Pierce
Date:
On 2/10/2016 9:02 PM, dhaval jaiswal wrote:
> I am getting lot of following queries on my Database. I need  your
> help to stop hitting this kind of queries to database.

I ran that query on a pg server I manage and it took 2.3 milliseconds.
on another instance on the same server, it took 2.00 ms.  you'd have to
do that 400-500 times a second continuously to use up one cpu core.
ooh, on a couple older servers it took 4.4ms and 5.0ms, so 200/s.

I'm just saying...   If 'lot of queries' means a dozen per second
continuously, thats not much performance wise.




--
john r pierce, recycling bits in santa cruz



Re: System Query hit to Database

From
Dave Cramer
Date:
Depends on the size of the catalogs. There are some db's with 1000's of tables and columns, which can end up being a very slow query


On 11 February 2016 at 02:26, John R Pierce <pierce@hogranch.com> wrote:
On 2/10/2016 9:02 PM, dhaval jaiswal wrote:
I am getting lot of following queries on my Database. I need  your help to stop hitting this kind of queries to database.

I ran that query on a pg server I manage and it took 2.3 milliseconds.  on another instance on the same server, it took 2.00 ms.  you'd have to do that 400-500 times a second continuously to use up one cpu core.     ooh, on a couple older servers it took 4.4ms and 5.0ms, so 200/s.

I'm just saying...   If 'lot of queries' means a dozen per second continuously, thats not much performance wise.




--
john r pierce, recycling bits in santa cruz




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

Re: System Query hit to Database

From
dhaval jaiswal
Date:

Can anyone help me on below query.





From: dhaval jaiswal <dhavallj@hotmail.com>
Sent: Thursday, February 11, 2016 10:32 AM
To: pgsql-jdbc@postgresql.org
Subject: System Query hit to Database
 

Kindly redirect me to the correct forum, If below question is not relevant to you.  



I am getting lot of following queries on my Database.  I need  your help to stop hitting this kind of queries to database.

While going through search found the following article saying it's caused by jdbc driver.

http://stackoverflow.com/questions/5453992/a-very-strange-sql-of-postgresql

 

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  WHEN 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 'TEMPORARY 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 'TABLE'  WHEN 'i' THEN 'INDEX'  WHEN 'S' THEN 'SEQUENCE'  WHEN 'v' THEN 'VIEW'  WHEN 'c' THEN 'TYPE'  ELSE NULL  END  ELSE NULL  END  AS TABLE_TYPE, d.description AS REMARKS  FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c  LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)  LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class')  LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog')  WHERE c.relnamespace = n.oid  AND c.relname LIKE 'PROBABLYNOT'  AND (false  OR ( c.relkind = 'r' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' ) )  ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME



Environment details:

Version: PostgreSQL 9.4.0

Driver version: postgresql-9.4-1201.jdbc41.jar

JVM: 1.7



Re: System Query hit to Database

From
Vladimir Sitnikov
Date:
Here's what you can do:
1) Collect lots of thread dumps. For instance, for 15-60 minutes once
each second (watch -n 1 kill -3 <pid_of_java_process> if using linux).
2) grep getTables in the thread dumps

It will highlight the code that calls getTables.

Vladimir


Re: System Query hit to Database

From
Thomas Kellerer
Date:
I have already answered that 2 days ago:

This is the query sent by getTables() (as mention on Stackoverflow).

There are some connection pools out there that have a stupid default configuration and use getTables() as the query to
validateif a connection is still alive. If you additionally have enabled validating connections when they are taken out
orreturned to the pool, this will be called for each "getConnection()" inside your code. 

Check the configuration of your connection pool.


dhaval jaiswal schrieb am 13.02.2016 um 11:53:
> Can anyone help me on below query.
>
>
>
>
>
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> *From:* dhaval jaiswal <dhavallj@hotmail.com>
> *Sent:* Thursday, February 11, 2016 10:32 AM
> *To:* pgsql-jdbc@postgresql.org
> *Subject:* System Query hit to Database
>
> Kindly redirect me to the correct forum, If below question is not relevant to you.
>
>
>
> I am getting lot of following queries on my Database.  I need  your help to stop hitting this kind of queries to
database.
>
> While going through search found the following article saying it's caused by jdbc driver.
>
> http://stackoverflow.com/questions/5453992/a-very-strange-sql-of-postgresql
>
> Environment details:
>
> Version: PostgreSQL 9.4.0
>
> Driver version: postgresql-9.4-1201.jdbc41.jar
>
> JVM: 1.7
>
>
>