Обсуждение: autovacuum and transaction id wraparound
Hi, I have a question regarding the following snippet in the official Postgres documentation: "If for some reason autovacuum fails to clear old XIDs from a table, the system will begin to emit warning messages like this when the database's oldest XIDs reach ten million transactions from the wraparound point..." Do you know any real reason why the autovacuum may fail to clear old XIDs? Is this highly probable ? Thanks, Pawel -- View this message in context: http://postgresql.1045698.n5.nabble.com/autovacuum-and-transaction-id-wraparound-tp5545412p5545412.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Wednesday 07 March 2012 21:13:26 pawel_kukawski wrote: Hi, > Do you know any real reason why the autovacuum may fail to clear old XIDs? If it's unable to keep up. Or may be, if there're very long running idle in transactions. > Is this highly probable ? postmaster will shutdown to prevent wraparound, if there are fewer than1 million transactions left until wraparound. Rgds, Jens
Hi Jens, Thanks for answer. One more question: Can the manual VACUUM operate on database where there are long lasting transactions? In other words, do I need to restart the server or kill long lasting transactions in order to allow manual VACUUM to clear old XIDs? Regards, Pawel -- View this message in context: http://postgresql.1045698.n5.nabble.com/autovacuum-and-transaction-id-wraparound-tp5545412p5549786.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Fri, Mar 9, 2012 at 12:15 AM, pawel_kukawski <kukismen@gmail.com> wrote: > Hi Jens, > > Thanks for answer. One more question: > > Can the manual VACUUM operate on database where there are long lasting > transactions? > > In other words, do I need to restart the server or kill long lasting > transactions in order to allow manual VACUUM to clear old XIDs? Long running transactions block both manual and automatic vacuums. The only difference between manual and automatic vacuuming is the costing parameters, which don't have to be different. I.e. if you set the autovacuum_* costs to the same as the vacuum_* costs no diff in operation. The issue you might run into is the one Tom mentioned, that if you've got a long running autovacuum that started with the older less aggressive settings you might need to kill it (you can use pg_cancel_backend() or sigint from the command like) to get it to start back up with the new costs.