Re: Performance with the new security release? - Mailing list pgsql-hackers

Hi Steve,
Yes I see these messages in our log. Is there a solution to this?
Thanks,
Anne
-----Original Message-----
From: Steve Singer [mailto:steve@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=v3KoQ@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:steve@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
insome 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
>>
>>
>
>