Обсуждение: Performance with the new security release?
Hi, We are seeing some overall performance degradation in our application since we installed the security release. Other commitswere 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. Afterreading this "It was created as a side effect of a refactoring effort to make establishing new connections to a PostgreSQLserver faster, and the associated code more maintainable.", I am thinking it is quite possible. Please let me know. Thanks, Anne
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
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
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:josh@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
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 > >
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'tget 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: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). Alsowhich 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 somecases (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 > >
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'tget 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 >> >> > >
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 >> >> > >
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@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 >>> >>> >> > >
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: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 >>> >>> >> > >
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 >>>> >>>> >> > >
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 thetable 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-acquirethe lock and continue. This fix also greatly reduces the average time before autovacuum releases the lock aftera 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:steve@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: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 >>>> >>>> >> > >
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 releasethe table lock) when there are conflicting lock requests. Under load, it is easily possible that truncation wouldnever occur, resulting in table bloat. Fix by performing a partial truncation, releasing the lock, then attempting tore-acquire the lock and continue. This fix also greatly reduces the average time before autovacuum releases the lock aftera 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:steve@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: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 manualvacuums during 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 issuesin 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 >>>>> >>>>> >> > >
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 releasethe table lock) when there are conflicting lock requests. Under load, it is easily possible that truncation wouldnever occur, resulting in table bloat. Fix by performing a partial truncation, releasing the lock, then attempting tore-acquire the lock and continue. This fix also greatly reduces the average time before autovacuum releases the lock aftera 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 Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services