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(at)ssinger(dot)info]
> Sent: Monday, April 22, 2013 4:35 PM
> To: Anne Rosset
> Cc: pgsql-hackers(at)postgresql(dot)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:steve(at)ssinger(dot)info]
>> Sent: Monday, April 22, 2013 1:26 PM
>> To: Anne Rosset
>> Cc: pgsql-hackers(at)postgresql(dot)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(at)mail(dot)gmail(dot)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(at)ssinger(dot)info]
>>> Sent: Monday, April 22, 2013 12:59 PM
>>> To: Anne Rosset
>>> Cc: pgsql-hackers(at)postgresql(dot)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
>>>>
>>>>
>>
>
>