Re: 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:steve@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
statisticsthen that should help.
 
> A manual vacuum will , unfortunately, behave like the auto-vacuum. The only way to get vacuum past this (until this
issueis 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
vacuumsduring those times.
 
>
>
>
>
>> -----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_t
>> nRhy+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
>>>>
>>>>
>>
>
>