Обсуждение: System Query hit to Database

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.

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

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

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
>
>
>