Re: [HACKERS] Performance with the new security release?

2013-04-23 Thread Steve Singer

On 13-04-22 11:46 PM, Anne Rosset wrote:

Thanks Steve.
I have read that a fix has been put in release 9.2.3 for this issue. Is that 
right?
Thanks,
Anne


No this issue is present in 9.0.13, 9.1.9 and 9.2.4 (as well as 9.2.3).

There is talk about fixing this for the next set of minor releases but I 
haven't yet seen a patch.



-Original Message-
From: Steve Singer [mailto:st...@ssinger.info]
Sent: Monday, April 22, 2013 4:35 PM
To: Anne Rosset
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Performance with the new security release?

On 13-04-22 04:41 PM, Anne Rosset wrote:

Hi Steve,
Yes I see these messages in our log. Is there a solution to this?
Thanks,
Anne

A manual analyze of the effected tables should work and give you updated 
statistics.  If your problem is just statistics then that should help.
A manual vacuum will , unfortunately, behave like the auto-vacuum. The only way 
to get vacuum past this (until this issue is fixed) is for
vacuum to be able to get that exclusive lock.   If there are times of
the day your database is less busy you might have some luck turning off 
auto-vacuum on these tables  and doing manual vacuums during those times.





-Original Message-
From: Steve Singer [mailto:st...@ssinger.info]
Sent: Monday, April 22, 2013 1:26 PM
To: Anne Rosset
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Performance with the new security release?

On 13-04-22 04:15 PM, Anne Rosset wrote:

Hi Steve,
Thanks for your reply.
We are now running  9.0.13. Before it was 9.0.7.
How can I find out if we are running into this issue: ie if
statistics are no longer being updated because analyze can't get the exclusive lock 
for truncation?

This issue is discussed in the thread
http://www.postgresql.org/message-id/CAMkU=1xYXOJp=jLAASPdSAqab-HwhA_t
nRhy+JUe=4=b=v3...@mail.gmail.com

If your seeing messages in your logs of the form:

automatic vacuum of table XXX.YYY cannot (re)acquire exclusive lock for truncate 
scan

then you might be hitting this issue.



I will dig into our logs to see for the query times.
Thanks,
Anne

-Original Message-
From: Steve Singer [mailto:st...@ssinger.info]
Sent: Monday, April 22, 2013 12:59 PM
To: Anne Rosset
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Performance with the new security release?

On 13-04-22 01:38 PM, Anne Rosset wrote:

Hi,
We are seeing some overall performance degradation in our application
 since we installed the security release. Other commits were also
done at the same time in the application so we don't know yet if the
 degradation has any relationship with the security release.

 While we are digging into this, I would like to know if it is possible
 that the release has some impact on performance. After reading this
 It was created as a side effect of a refactoring effort to make
 establishing new connections to a PostgreSQL server faster, and the
 associated code more maintainable., I am thinking it is quite possible.

 Please let me know. Thanks,

Exactly which version of PostgreSQL are you running? (we released security 
update releases for multiple PG versions).  Also which version were you running 
before?

There were some changes to analyze/vacuum in the previous set of minor releases 
that could cause performance issues in some cases (ie if statistics are no 
longer being updated because analyze can't get the
exclusive lock for truncation).   There might be other unintended
performance related changes.

Are all queries taking longer or only some?  Can you find any sort of pattern 
that might help narrow the issue?

Steve


 Anne











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


Re: [HACKERS] Performance with the new security release?

2013-04-23 Thread Anne Rosset
Thanks Steve.
I found this: http://www.postgresql.org/docs/current/static/release-9-2-3.html

Fix performance problems with autovacuum truncation in busy workloads (Jan 
Wieck)
Truncation of empty pages at the end of a table requires exclusive lock, but 
autovacuum was coded to fail (and release the table lock) when there are 
conflicting lock requests. Under load, it is easily possible that truncation 
would never occur, resulting in table bloat. Fix by performing a partial 
truncation, releasing the lock, then attempting to re-acquire the lock and 
continue. This fix also greatly reduces the average time before autovacuum 
releases the lock after a conflicting request arrives.

So that is not the fix? 

(Sorry to ask a second time but I really need to make sure).
Thanks,
Anne



-Original Message-


From: Steve Singer [mailto:st...@ssinger.info] 
Sent: Tuesday, April 23, 2013 6:33 AM
To: Anne Rosset
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Performance with the new security release?

On 13-04-22 11:46 PM, Anne Rosset wrote:
 Thanks Steve.
 I have read that a fix has been put in release 9.2.3 for this issue. Is that 
 right?
 Thanks,
 Anne

No this issue is present in 9.0.13, 9.1.9 and 9.2.4 (as well as 9.2.3).

There is talk about fixing this for the next set of minor releases but I 
haven't yet seen a patch.

 -Original Message-
 From: Steve Singer [mailto:st...@ssinger.info]
 Sent: Monday, April 22, 2013 4:35 PM
 To: Anne Rosset
 Cc: pgsql-hackers@postgresql.org
 Subject: Re: [HACKERS] Performance with the new security release?

 On 13-04-22 04:41 PM, Anne Rosset wrote:
 Hi Steve,
 Yes I see these messages in our log. Is there a solution to this?
 Thanks,
 Anne
 A manual analyze of the effected tables should work and give you updated 
 statistics.  If your problem is just statistics then that should help.
 A manual vacuum will , unfortunately, behave like the auto-vacuum. The only 
 way to get vacuum past this (until this issue is fixed) is for
 vacuum to be able to get that exclusive lock.   If there are times of
 the day your database is less busy you might have some luck turning off 
 auto-vacuum on these tables  and doing manual vacuums during those times.




 -Original Message-
 From: Steve Singer [mailto:st...@ssinger.info]
 Sent: Monday, April 22, 2013 1:26 PM
 To: Anne Rosset
 Cc: pgsql-hackers@postgresql.org
 Subject: Re: [HACKERS] Performance with the new security release?

 On 13-04-22 04:15 PM, Anne Rosset wrote:
 Hi Steve,
 Thanks for your reply.
 We are now running  9.0.13. Before it was 9.0.7.
 How can I find out if we are running into this issue: ie if 
 statistics are no longer being updated because analyze can't get the 
 exclusive lock for truncation?
 This issue is discussed in the thread 
 http://www.postgresql.org/message-id/CAMkU=1xYXOJp=jLAASPdSAqab-HwhA_
 t
 nRhy+JUe=4=b=v3...@mail.gmail.com

 If your seeing messages in your logs of the form:

 automatic vacuum of table XXX.YYY cannot (re)acquire exclusive lock for 
 truncate scan

 then you might be hitting this issue.


 I will dig into our logs to see for the query times.
 Thanks,
 Anne

 -Original Message-
 From: Steve Singer [mailto:st...@ssinger.info]
 Sent: Monday, April 22, 2013 12:59 PM
 To: Anne Rosset
 Cc: pgsql-hackers@postgresql.org
 Subject: Re: [HACKERS] Performance with the new security release?

 On 13-04-22 01:38 PM, Anne Rosset wrote:
 Hi,
 We are seeing some overall performance degradation in our application
  since we installed the security release. Other commits were 
 also done at the same time in the application so we don't know yet if the
  degradation has any relationship with the security release.

  While we are digging into this, I would like to know if it is possible
  that the release has some impact on performance. After reading this
  It was created as a side effect of a refactoring effort to make
  establishing new connections to a PostgreSQL server faster, and the
  associated code more maintainable., I am thinking it is quite 
 possible.

  Please let me know. Thanks,
 Exactly which version of PostgreSQL are you running? (we released security 
 update releases for multiple PG versions).  Also which version were you 
 running before?

 There were some changes to analyze/vacuum in the previous set of minor 
 releases that could cause performance issues in some cases (ie if 
 statistics are no longer being updated because analyze can't get the
 exclusive lock for truncation).   There might be other unintended
 performance related changes.

 Are all queries taking longer or only some?  Can you find any sort of 
 pattern that might help narrow the issue?

 Steve

  Anne








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


Re: [HACKERS] Performance with the new security release?

2013-04-23 Thread Steve Singer

On 13-04-23 10:04 AM, Anne Rosset wrote:

Thanks Steve.
I found this: http://www.postgresql.org/docs/current/static/release-9-2-3.html

Fix performance problems with autovacuum truncation in busy workloads (Jan 
Wieck)
Truncation of empty pages at the end of a table requires exclusive lock, but 
autovacuum was coded to fail (and release the table lock) when there are conflicting 
lock requests. Under load, it is easily possible that truncation would never occur, 
resulting in table bloat. Fix by performing a partial truncation, releasing the 
lock, then attempting to re-acquire the lock and continue. This fix also greatly 
reduces the average time before autovacuum releases the lock after a conflicting 
request arrives.

So that is not the fix?
No, that is the change that caused this problem.   That fix addresses a 
slightly different set of symptoms where the truncate as part of an 
auto-vacuum doesn't happen because the lock gets pre-empted.  An 
unintended/undesirable consequence of that fix was that it means if 
vacuum can't do the truncate stage because it can't obtain the lock in 
the first place then statistics don't get updated.





(Sorry to ask a second time but I really need to make sure).
Thanks,
Anne



-Original Message-


From: Steve Singer [mailto:st...@ssinger.info]
Sent: Tuesday, April 23, 2013 6:33 AM
To: Anne Rosset
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Performance with the new security release?

On 13-04-22 11:46 PM, Anne Rosset wrote:

Thanks Steve.
I have read that a fix has been put in release 9.2.3 for this issue. Is that 
right?
Thanks,
Anne

No this issue is present in 9.0.13, 9.1.9 and 9.2.4 (as well as 9.2.3).

There is talk about fixing this for the next set of minor releases but I 
haven't yet seen a patch.


-Original Message-
From: Steve Singer [mailto:st...@ssinger.info]
Sent: Monday, April 22, 2013 4:35 PM
To: Anne Rosset
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Performance with the new security release?

On 13-04-22 04:41 PM, Anne Rosset wrote:

Hi Steve,
Yes I see these messages in our log. Is there a solution to this?
Thanks,
Anne

A manual analyze of the effected tables should work and give you updated 
statistics.  If your problem is just statistics then that should help.
A manual vacuum will , unfortunately, behave like the auto-vacuum. The only way 
to get vacuum past this (until this issue is fixed) is for
vacuum to be able to get that exclusive lock.   If there are times of
the day your database is less busy you might have some luck turning off 
auto-vacuum on these tables  and doing manual vacuums during those times.





-Original Message-
From: Steve Singer [mailto:st...@ssinger.info]
Sent: Monday, April 22, 2013 1:26 PM
To: Anne Rosset
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Performance with the new security release?

On 13-04-22 04:15 PM, Anne Rosset wrote:

Hi Steve,
Thanks for your reply.
We are now running  9.0.13. Before it was 9.0.7.
How can I find out if we are running into this issue: ie if
statistics are no longer being updated because analyze can't get the exclusive lock 
for truncation?

This issue is discussed in the thread
http://www.postgresql.org/message-id/CAMkU=1xYXOJp=jLAASPdSAqab-HwhA_
t
nRhy+JUe=4=b=v3...@mail.gmail.com

If your seeing messages in your logs of the form:

automatic vacuum of table XXX.YYY cannot (re)acquire exclusive lock for truncate 
scan

then you might be hitting this issue.



I will dig into our logs to see for the query times.
Thanks,
Anne

-Original Message-
From: Steve Singer [mailto:st...@ssinger.info]
Sent: Monday, April 22, 2013 12:59 PM
To: Anne Rosset
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Performance with the new security release?

On 13-04-22 01:38 PM, Anne Rosset wrote:

Hi,
We are seeing some overall performance degradation in our application
  since we installed the security release. Other commits were
also done at the same time in the application so we don't know yet if the
  degradation has any relationship with the security release.

  While we are digging into this, I would like to know if it is possible
  that the release has some impact on performance. After reading this
  It was created as a side effect of a refactoring effort to make
  establishing new connections to a PostgreSQL server faster, and the
  associated code more maintainable., I am thinking it is quite possible.

  Please let me know. Thanks,

Exactly which version of PostgreSQL are you running? (we released security 
update releases for multiple PG versions).  Also which version were you running 
before?

There were some changes to analyze/vacuum in the previous set of minor releases 
that could cause performance issues in some cases (ie if statistics are no 
longer being updated because analyze can't get the
exclusive lock for truncation).   There might be other unintended
performance related changes

Re: [HACKERS] Performance with the new security release?

2013-04-23 Thread Alvaro Herrera
Anne Rosset wrote:
 Thanks Steve.
 I found this: http://www.postgresql.org/docs/current/static/release-9-2-3.html
 
 Fix performance problems with autovacuum truncation in busy workloads (Jan 
 Wieck)
 Truncation of empty pages at the end of a table requires exclusive lock, but 
 autovacuum was coded to fail (and release the table lock) when there are 
 conflicting lock requests. Under load, it is easily possible that truncation 
 would never occur, resulting in table bloat. Fix by performing a partial 
 truncation, releasing the lock, then attempting to re-acquire the lock and 
 continue. This fix also greatly reduces the average time before autovacuum 
 releases the lock after a conflicting request arrives.
 
 So that is not the fix? 
 
 (Sorry to ask a second time but I really need to make sure).

That's the commit that created the bug, AFAIU.  It's a fix for a serious
problem, but we overlooked that it introduced some other problems which
is what you're now seeing.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training  Services


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


[HACKERS] Performance with the new security release?

2013-04-22 Thread Anne Rosset
Hi,
We are seeing some overall performance degradation in our application 
 since we installed the security release. Other commits were also done 
at the same time in the application so we don't know yet if the 
 degradation has any relationship with the security release.

 While we are digging into this, I would like to know if it is possible 
 that the release has some impact on performance. After reading this 
 It was created as a side effect of a refactoring effort to make 
 establishing new connections to a PostgreSQL server faster, and the 
 associated code more maintainable., I am thinking it is quite possible.

 Please let me know. Thanks,

 Anne


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


Re: [HACKERS] Performance with the new security release?

2013-04-22 Thread Josh Berkus
On 04/22/2013 10:38 AM, Anne Rosset wrote:
  While we are digging into this, I would like to know if it is possible 
  that the release has some impact on performance. After reading this 
  It was created as a side effect of a refactoring effort to make 
  establishing new connections to a PostgreSQL server faster, and the 
  associated code more maintainable., I am thinking it is quite possible.

Does your application do a lot of rapidfire reconnection to PostgreSQL?
 i.e. hundreds of new connections per minute?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


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


Re: [HACKERS] Performance with the new security release?

2013-04-22 Thread Josh Berkus
On 04/22/2013 10:38 AM, Anne Rosset wrote:
  While we are digging into this, I would like to know if it is possible 
  that the release has some impact on performance. After reading this 
  It was created as a side effect of a refactoring effort to make 
  establishing new connections to a PostgreSQL server faster, and the 
  associated code more maintainable., I am thinking it is quite possible.

Does your application do a lot of rapidfire reconnection to the
database?  As in hundreds of new connections per minute?

Mind you, if it does, I strongly recommend pgbouncer ...

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


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


Re: [HACKERS] Performance with the new security release?

2013-04-22 Thread Anne Rosset
Hi Josh,
Thanks for your reply. 
I don't think this is the case since we are using jboss/jdbc driver with a 
connection pool. 

Thanks,
Anne





-Original Message-
From: Josh Berkus [mailto:j...@agliodbs.com] 
Sent: Monday, April 22, 2013 10:58 AM
To: Anne Rosset
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Performance with the new security release?

On 04/22/2013 10:38 AM, Anne Rosset wrote:
  While we are digging into this, I would like to know if it is 
 possible  that the release has some impact on performance. After 
 reading this  It was created as a side effect of a refactoring effort 
 to make  establishing new connections to a PostgreSQL server faster, 
 and the  associated code more maintainable., I am thinking it is quite 
 possible.

Does your application do a lot of rapidfire reconnection to the database?  As 
in hundreds of new connections per minute?

Mind you, if it does, I strongly recommend pgbouncer ...

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

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


Re: [HACKERS] Performance with the new security release?

2013-04-22 Thread Steve Singer

On 13-04-22 01:38 PM, Anne Rosset wrote:

Hi,
We are seeing some overall performance degradation in our application
  since we installed the security release. Other commits were also done
at the same time in the application so we don't know yet if the
  degradation has any relationship with the security release.

  While we are digging into this, I would like to know if it is possible
  that the release has some impact on performance. After reading this
  It was created as a side effect of a refactoring effort to make
  establishing new connections to a PostgreSQL server faster, and the
  associated code more maintainable., I am thinking it is quite possible.

  Please let me know. Thanks,


Exactly which version of PostgreSQL are you running? (we released 
security update releases for multiple PG versions).  Also which version 
were you running before?


There were some changes to analyze/vacuum in the previous set of minor 
releases that could cause performance issues in some cases (ie if 
statistics are no longer being updated because analyze can't get the 
exclusive lock for truncation).   There might be other unintended 
performance related changes.


Are all queries taking longer or only some?  Can you find any sort of 
pattern that might help narrow the issue?


Steve


  Anne






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


Re: [HACKERS] Performance with the new security release?

2013-04-22 Thread Anne Rosset
Hi Steve,
Thanks for your reply.
We are now running  9.0.13. Before it was 9.0.7.
How can I find out if we are running into this issue: ie if statistics are no 
longer being updated because analyze can't get the 
exclusive lock for truncation?

I will dig into our logs to see for the query times.
Thanks,
Anne

-Original Message-
From: Steve Singer [mailto:st...@ssinger.info] 
Sent: Monday, April 22, 2013 12:59 PM
To: Anne Rosset
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Performance with the new security release?

On 13-04-22 01:38 PM, Anne Rosset wrote:
 Hi,
 We are seeing some overall performance degradation in our application
   since we installed the security release. Other commits were also 
 done at the same time in the application so we don't know yet if the
   degradation has any relationship with the security release.

   While we are digging into this, I would like to know if it is possible
   that the release has some impact on performance. After reading this
   It was created as a side effect of a refactoring effort to make
   establishing new connections to a PostgreSQL server faster, and the
   associated code more maintainable., I am thinking it is quite possible.

   Please let me know. Thanks,

Exactly which version of PostgreSQL are you running? (we released security 
update releases for multiple PG versions).  Also which version were you running 
before?

There were some changes to analyze/vacuum in the previous set of minor releases 
that could cause performance issues in some cases (ie if statistics are no 
longer being updated because analyze can't get the 
exclusive lock for truncation).   There might be other unintended 
performance related changes.

Are all queries taking longer or only some?  Can you find any sort of pattern 
that might help narrow the issue?

Steve

   Anne





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


Re: [HACKERS] Performance with the new security release?

2013-04-22 Thread Steve Singer

On 13-04-22 04:15 PM, Anne Rosset wrote:

Hi Steve,
Thanks for your reply.
We are now running  9.0.13. Before it was 9.0.7.
How can I find out if we are running into this issue: ie if statistics are no 
longer being updated because analyze can't get the
exclusive lock for truncation?


This issue is discussed in the thread 
http://www.postgresql.org/message-id/CAMkU=1xYXOJp=jLAASPdSAqab-HwhA_tnRhy+JUe=4=b=v3...@mail.gmail.com


If your seeing messages in your logs of the form:

automatic vacuum of table XXX.YYY cannot (re)acquire exclusive lock for 
truncate scan


then you might be hitting this issue.



I will dig into our logs to see for the query times.
Thanks,
Anne

-Original Message-
From: Steve Singer [mailto:st...@ssinger.info]
Sent: Monday, April 22, 2013 12:59 PM
To: Anne Rosset
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Performance with the new security release?

On 13-04-22 01:38 PM, Anne Rosset wrote:

Hi,
We are seeing some overall performance degradation in our application
   since we installed the security release. Other commits were also
done at the same time in the application so we don't know yet if the
   degradation has any relationship with the security release.

   While we are digging into this, I would like to know if it is possible
   that the release has some impact on performance. After reading this
   It was created as a side effect of a refactoring effort to make
   establishing new connections to a PostgreSQL server faster, and the
   associated code more maintainable., I am thinking it is quite possible.

   Please let me know. Thanks,

Exactly which version of PostgreSQL are you running? (we released security 
update releases for multiple PG versions).  Also which version were you running 
before?

There were some changes to analyze/vacuum in the previous set of minor releases 
that could cause performance issues in some cases (ie if statistics are no 
longer being updated because analyze can't get the
exclusive lock for truncation).   There might be other unintended
performance related changes.

Are all queries taking longer or only some?  Can you find any sort of pattern 
that might help narrow the issue?

Steve


   Anne









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


Re: [HACKERS] Performance with the new security release?

2013-04-22 Thread Anne Rosset
Hi Steve,
Yes I see these messages in our log. Is there a solution to this? 
Thanks,
Anne
-Original Message-
From: Steve Singer [mailto:st...@ssinger.info] 
Sent: Monday, April 22, 2013 1:26 PM
To: Anne Rosset
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Performance with the new security release?

On 13-04-22 04:15 PM, Anne Rosset wrote:
 Hi Steve,
 Thanks for your reply.
 We are now running  9.0.13. Before it was 9.0.7.
 How can I find out if we are running into this issue: ie if 
 statistics are no longer being updated because analyze can't get the 
 exclusive lock for truncation?

This issue is discussed in the thread
http://www.postgresql.org/message-id/CAMkU=1xYXOJp=jLAASPdSAqab-HwhA_tnRhy+JUe=4=b=v3...@mail.gmail.com

If your seeing messages in your logs of the form:

automatic vacuum of table XXX.YYY cannot (re)acquire exclusive lock for 
truncate scan

then you might be hitting this issue.


 I will dig into our logs to see for the query times.
 Thanks,
 Anne

 -Original Message-
 From: Steve Singer [mailto:st...@ssinger.info]
 Sent: Monday, April 22, 2013 12:59 PM
 To: Anne Rosset
 Cc: pgsql-hackers@postgresql.org
 Subject: Re: [HACKERS] Performance with the new security release?

 On 13-04-22 01:38 PM, Anne Rosset wrote:
 Hi,
 We are seeing some overall performance degradation in our application
since we installed the security release. Other commits were also 
 done at the same time in the application so we don't know yet if the
degradation has any relationship with the security release.

While we are digging into this, I would like to know if it is possible
that the release has some impact on performance. After reading this
It was created as a side effect of a refactoring effort to make
establishing new connections to a PostgreSQL server faster, and the
associated code more maintainable., I am thinking it is quite possible.

Please let me know. Thanks,
 Exactly which version of PostgreSQL are you running? (we released security 
 update releases for multiple PG versions).  Also which version were you 
 running before?

 There were some changes to analyze/vacuum in the previous set of minor 
 releases that could cause performance issues in some cases (ie if statistics 
 are no longer being updated because analyze can't get the
 exclusive lock for truncation).   There might be other unintended
 performance related changes.

 Are all queries taking longer or only some?  Can you find any sort of pattern 
 that might help narrow the issue?

 Steve

Anne







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


Re: [HACKERS] Performance with the new security release?

2013-04-22 Thread Steve Singer

On 13-04-22 04:41 PM, Anne Rosset wrote:

Hi Steve,
Yes I see these messages in our log. Is there a solution to this?
Thanks,
Anne


A manual analyze of the effected tables should work and give you updated 
statistics.  If your problem is just statistics then that should help.
A manual vacuum will , unfortunately, behave like the auto-vacuum. The 
only way to get vacuum past this (until this issue is fixed) is for 
vacuum to be able to get that exclusive lock.   If there are times of 
the day your database is less busy you might have some luck turning off 
auto-vacuum on these tables  and doing manual vacuums during those times.






-Original Message-
From: Steve Singer [mailto:st...@ssinger.info]
Sent: Monday, April 22, 2013 1:26 PM
To: Anne Rosset
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Performance with the new security release?

On 13-04-22 04:15 PM, Anne Rosset wrote:

Hi Steve,
Thanks for your reply.
We are now running  9.0.13. Before it was 9.0.7.
How can I find out if we are running into this issue: ie if
statistics are no longer being updated because analyze can't get the exclusive lock 
for truncation?

This issue is discussed in the thread
http://www.postgresql.org/message-id/CAMkU=1xYXOJp=jLAASPdSAqab-HwhA_tnRhy+JUe=4=b=v3...@mail.gmail.com

If your seeing messages in your logs of the form:

automatic vacuum of table XXX.YYY cannot (re)acquire exclusive lock for truncate 
scan

then you might be hitting this issue.



I will dig into our logs to see for the query times.
Thanks,
Anne

-Original Message-
From: Steve Singer [mailto:st...@ssinger.info]
Sent: Monday, April 22, 2013 12:59 PM
To: Anne Rosset
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Performance with the new security release?

On 13-04-22 01:38 PM, Anne Rosset wrote:

Hi,
We are seeing some overall performance degradation in our application
since we installed the security release. Other commits were also
done at the same time in the application so we don't know yet if the
degradation has any relationship with the security release.

While we are digging into this, I would like to know if it is possible
that the release has some impact on performance. After reading this
It was created as a side effect of a refactoring effort to make
establishing new connections to a PostgreSQL server faster, and the
associated code more maintainable., I am thinking it is quite possible.

Please let me know. Thanks,

Exactly which version of PostgreSQL are you running? (we released security 
update releases for multiple PG versions).  Also which version were you running 
before?

There were some changes to analyze/vacuum in the previous set of minor releases 
that could cause performance issues in some cases (ie if statistics are no 
longer being updated because analyze can't get the
exclusive lock for truncation).   There might be other unintended
performance related changes.

Are all queries taking longer or only some?  Can you find any sort of pattern 
that might help narrow the issue?

Steve


Anne











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


Re: [HACKERS] Performance with the new security release?

2013-04-22 Thread Anne Rosset
Thanks Steve.
I have read that a fix has been put in release 9.2.3 for this issue. Is that 
right?
Thanks,
Anne

-Original Message-
From: Steve Singer [mailto:st...@ssinger.info] 
Sent: Monday, April 22, 2013 4:35 PM
To: Anne Rosset
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Performance with the new security release?

On 13-04-22 04:41 PM, Anne Rosset wrote:
 Hi Steve,
 Yes I see these messages in our log. Is there a solution to this?
 Thanks,
 Anne

A manual analyze of the effected tables should work and give you updated 
statistics.  If your problem is just statistics then that should help.
A manual vacuum will , unfortunately, behave like the auto-vacuum. The only way 
to get vacuum past this (until this issue is fixed) is for 
vacuum to be able to get that exclusive lock.   If there are times of 
the day your database is less busy you might have some luck turning off 
auto-vacuum on these tables  and doing manual vacuums during those times.




 -Original Message-
 From: Steve Singer [mailto:st...@ssinger.info]
 Sent: Monday, April 22, 2013 1:26 PM
 To: Anne Rosset
 Cc: pgsql-hackers@postgresql.org
 Subject: Re: [HACKERS] Performance with the new security release?

 On 13-04-22 04:15 PM, Anne Rosset wrote:
 Hi Steve,
 Thanks for your reply.
 We are now running  9.0.13. Before it was 9.0.7.
 How can I find out if we are running into this issue: ie if 
 statistics are no longer being updated because analyze can't get the 
 exclusive lock for truncation?
 This issue is discussed in the thread
 http://www.postgresql.org/message-id/CAMkU=1xYXOJp=jLAASPdSAqab-HwhA_t
 nRhy+JUe=4=b=v3...@mail.gmail.com

 If your seeing messages in your logs of the form:

 automatic vacuum of table XXX.YYY cannot (re)acquire exclusive lock for 
 truncate scan

 then you might be hitting this issue.


 I will dig into our logs to see for the query times.
 Thanks,
 Anne

 -Original Message-
 From: Steve Singer [mailto:st...@ssinger.info]
 Sent: Monday, April 22, 2013 12:59 PM
 To: Anne Rosset
 Cc: pgsql-hackers@postgresql.org
 Subject: Re: [HACKERS] Performance with the new security release?

 On 13-04-22 01:38 PM, Anne Rosset wrote:
 Hi,
 We are seeing some overall performance degradation in our application
 since we installed the security release. Other commits were also 
 done at the same time in the application so we don't know yet if the
 degradation has any relationship with the security release.

 While we are digging into this, I would like to know if it is possible
 that the release has some impact on performance. After reading this
 It was created as a side effect of a refactoring effort to make
 establishing new connections to a PostgreSQL server faster, and the
 associated code more maintainable., I am thinking it is quite possible.

 Please let me know. Thanks,
 Exactly which version of PostgreSQL are you running? (we released security 
 update releases for multiple PG versions).  Also which version were you 
 running before?

 There were some changes to analyze/vacuum in the previous set of minor 
 releases that could cause performance issues in some cases (ie if statistics 
 are no longer being updated because analyze can't get the
 exclusive lock for truncation).   There might be other unintended
 performance related changes.

 Are all queries taking longer or only some?  Can you find any sort of 
 pattern that might help narrow the issue?

 Steve

 Anne








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