Re: [GENERAL] Perfomance issue. statement in the log file..

2016-02-24 Thread Bala Venkat 
Thank you. That will help us. We are changing the settings. We are running
9.0.4.  Appreciate your time and help

On Tue, Feb 23, 2016 at 3:20 PM, Roxanne Reid-Bennett 
wrote:

> On 2/23/2016 11:57 AM, Bala Venkat wrote:
>
>> Dear All -
>>
>>We are seeing lot of these statements in the log file. We have 2
>> functions .  Submit and update .  Between submit and update which will int
>> the process, the messages are appearning. The process is very slow. We
>> don't know if this the main reason for the performance issue
>>
>> Can you please help ? if any one has come across  this issue and
>> resolved. Appreciate your time.
>>
>> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
>> execute : SELECT 1  FROM pg_catalog.pg_attrdef  WHERE adrelid = $1
>> AND adn
>> um = $2   AND pg_catalog.pg_get_expr(adbin, adrelid)   LIKE
>> '%nextval(%'
>> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testDETAIL:
>> parameters: $1 = '3003958', $2 = '1'
>> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
>> duration: 0.059 ms
>> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
>> duration: 0.117 ms
>> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
>> duration: 1.087 ms
>> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
>> execute : SELECT attnotnull FROM pg_catalog.pg_attribute WHERE
>> attrelid =
>> $1 AND attnum = $2
>> 2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testDETAIL:
>> parameters: $1 = '3003958', $2 = '1'
>>
>
> ...
> Having done a cursory search for "SELECT attnotnull FROM
> pg_catalog.pg_attribute WHERE attrelid ="
> It appears your potential culprit is a JDBC or ODBC (or ORM managed)
> connection querying meta-data over and over.
> To identify connection information, back in 2010 [so... your mileage may
> vary] Tom suggested the use of:
>
> See log_connections and log_disconnections.  You probably want to
> add PID to log_line_prefix, too, so that you can associate different
> log entries for the same session.
>
>
> Version of Postgres and the execution environment of the 2 functions.
> [e.g. pgsql, PL/?, code etc] might provide more input to help identify a
> more specific answer.
>
> Roxanne
>
> --
> [At other schools] I think the most common fault in general is to teach
> students how to pass exams instead of teaching them the science.
> Donald Knuth
>
>
>
> --
> 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] Perfomance issue. statement in the log file..

2016-02-23 Thread Adrian Klaver 

On 02/23/2016 11:57 AM, Bala Venkat wrote:

Dear All -

We are seeing lot of these statements in the log file. We have 2
functions .  Submit and update .  Between submit and update which will
int the process, the messages are appearning. The process is very slow.
We don't know if this the main reason for the performance issue

Can you please help ? if any one has come across  this issue and
resolved. Appreciate your time.


Help will require more information. For future reference you may want to 
take a look at:


https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

In mean time and as a starting point:

What version of OS and Postgres?

What client is generating the queries?

Define slow and how are you determining it?

Can you show the contents of the two functions?



2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT 1  FROM pg_catalog.pg_attrdef  WHERE adrelid =
$1 AND adn
um = $2   AND pg_catalog.pg_get_expr(adbin, adrelid)   LIKE '%nextval(%'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName ,
db=testDETAIL:  parameters: $1 = '3003958', $2 = '1'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.059 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.117 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 1.087 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT attnotnull FROM pg_catalog.pg_attribute WHERE
attrelid =
$1 AND attnum = $2
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName ,
db=testDETAIL:  parameters: $1 = '3003958', $2 = '1'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.037 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.081 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.120 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT 1  FROM pg_catalog.pg_attrdef  WHERE adrelid =
$1 AND adn
um = $2   AND pg_catalog.pg_get_expr(adbin, adrelid)   LIKE '%nextval(%'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName ,
db=testDETAIL:  parameters: $1 = '3003958', $2 = '2'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.028 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.058 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.163 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT attnotnull FROM pg_catalog.pg_attribute WHERE
attrelid =
$1 AND attnum = $2
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName ,
db=testDETAIL:  parameters: $1 = '3003958', $2 = '2'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.032 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.074 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.118 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT 1  FROM pg_catalog.pg_attrdef  WHERE adrelid =
$1 AND adn
um = $2   AND pg_catalog.pg_get_expr(adbin, adrelid)   LIKE '%nextval(%'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName ,
db=testDETAIL:  parameters: $1 = '3003958', $2 = '3'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.027 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.058 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.161 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT attnotnull FROM pg_catalog.pg_attribute WHERE
attrelid =
$1 AND attnum = $2
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName ,
db=testDETAIL:  parameters: $1 = '3003958', $2 = '3'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.032 ms
2016-02-23 18:11:24 GMT 172.26.22.145(52330)  user=postgres ,
db=testLOG:  duration: 1210.331 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 1.230 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.083 ms



--
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] Perfomance issue. statement in the log file..

2016-02-23 Thread Roxanne Reid-Bennett 

On 2/23/2016 11:57 AM, Bala Venkat wrote:

Dear All -

   We are seeing lot of these statements in the log file. We have 
2 functions .  Submit and update .  Between submit and update which 
will int the process, the messages are appearning. The process is very 
slow. We don't know if this the main reason for the performance issue


Can you please help ? if any one has come across  this issue and 
resolved. Appreciate your time.


2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:  
execute : SELECT 1  FROM pg_catalog.pg_attrdef  WHERE adrelid 
= $1 AND adn
um = $2   AND pg_catalog.pg_get_expr(adbin, adrelid)   LIKE 
'%nextval(%'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , 
db=testDETAIL:  parameters: $1 = '3003958', $2 = '1'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:  
duration: 0.059 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:  
duration: 0.117 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:  
duration: 1.087 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:  
execute : SELECT attnotnull FROM pg_catalog.pg_attribute 
WHERE attrelid =

$1 AND attnum = $2
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , 
db=testDETAIL:  parameters: $1 = '3003958', $2 = '1'


...
Having done a cursory search for "SELECT attnotnull FROM 
pg_catalog.pg_attribute WHERE attrelid ="
It appears your potential culprit is a JDBC or ODBC (or ORM managed) 
connection querying meta-data over and over.
To identify connection information, back in 2010 [so... your mileage may 
vary] Tom suggested the use of:


See log_connections and log_disconnections.  You probably want to
add PID to log_line_prefix, too, so that you can associate different
log entries for the same session.


Version of Postgres and the execution environment of the 2 functions. 
[e.g. pgsql, PL/?, code etc] might provide more input to help identify a 
more specific answer.


Roxanne

--
[At other schools] I think the most common fault in general is to teach 
students how to pass exams instead of teaching them the science.
Donald Knuth



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

[GENERAL] Perfomance issue. statement in the log file..

2016-02-23 Thread Bala Venkat 
Dear All -

   We are seeing lot of these statements in the log file. We have 2
functions .  Submit and update .  Between submit and update which will int
the process, the messages are appearning. The process is very slow. We
don't know if this the main reason for the performance issue

Can you please help ? if any one has come across  this issue and resolved.
Appreciate your time.

2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT 1  FROM pg_catalog.pg_attrdef  WHERE adrelid = $1
AND adn
um = $2   AND pg_catalog.pg_get_expr(adbin, adrelid)   LIKE '%nextval(%'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testDETAIL:
parameters: $1 = '3003958', $2 = '1'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.059 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.117 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 1.087 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT attnotnull FROM pg_catalog.pg_attribute WHERE
attrelid =
$1 AND attnum = $2
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testDETAIL:
parameters: $1 = '3003958', $2 = '1'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.037 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.081 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.120 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT 1  FROM pg_catalog.pg_attrdef  WHERE adrelid = $1
AND adn
um = $2   AND pg_catalog.pg_get_expr(adbin, adrelid)   LIKE '%nextval(%'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testDETAIL:
parameters: $1 = '3003958', $2 = '2'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.028 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.058 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.163 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT attnotnull FROM pg_catalog.pg_attribute WHERE
attrelid =
$1 AND attnum = $2
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testDETAIL:
parameters: $1 = '3003958', $2 = '2'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.032 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.074 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.118 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT 1  FROM pg_catalog.pg_attrdef  WHERE adrelid = $1
AND adn
um = $2   AND pg_catalog.pg_get_expr(adbin, adrelid)   LIKE '%nextval(%'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testDETAIL:
parameters: $1 = '3003958', $2 = '3'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.027 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.058 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.161 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT attnotnull FROM pg_catalog.pg_attribute WHERE
attrelid =
$1 AND attnum = $2
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testDETAIL:
parameters: $1 = '3003958', $2 = '3'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.032 ms
2016-02-23 18:11:24 GMT 172.26.22.145(52330)  user=postgres , db=testLOG:
duration: 1210.331 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 1.230 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.083 ms