Обсуждение: Is Vacuum Working ?
I am hoping someone might be able to help ascertain if the nightly vacuum that we have set up is actually working correctly.
Every evening we run a Vacuum Analyse on the whole database after a large data import. I am concerned that the vacuum might not be working correctly but to be honest am completely overwhelmed by some of the 'vacuum concepts'. The database log is not showing any error logs but I have gathered the following information if someone could tell me if this is normal.
Information in Log file after database restart
01/08/2009 - Next Transaction ID = 1400435363
01/09/2009 - Next Transaction ID = 1462025864
Transaction_ID Wrapwround limit is 214784146 limited by database postgres
Is this normal, I would have thought the transactional ID would have stayed roughly the same due to vacuum ? If this keeps increasing will it mean I am getting to close my 2 billion transaction limit ?
Running SELECT datname, age(datfrozenxid) FROM pg_database; I get
postgres : 1463012368
maindb : 1078111767
template1 : 1463012368
template0 : 1463012368
Does this look O.K ?
Any help would be really appreciated on this matter
Thanks
Francis
___________________________________________________
This email is intended for the named recipient. The information contained
in it is confidential. You should not copy it for any purposes, nor
disclose its contents to any other party. If you received this email
in error, please notify the sender immediately via email, and delete
it from your computer.
Any views or opinions presented are solely those of the author and do not
necessarily represent those of the company.
Cromwell Tools Limited, PO Box 14, 65 Chartwell Drive
Wigston, Leicester LE18 1AT. Tel 0116 2888000
Registered in England and Wales, Reg No 00986161
VAT GB 115 5713 87 900
__________________________________________________
"Thorne, Francis" <thornef@cromwell.co.uk> writes: > Information in Log file after database restart > 01/08/2009 - Next Transaction ID = 1400435363 > 01/09/2009 - Next Transaction ID = 1462025864 > Transaction_ID Wrapwround limit is 214784146 limited by database > postgres > Is this normal, I would have thought the transactional ID would have > stayed roughly the same due to vacuum ? If this keeps increasing will it > mean I am getting to close my 2 billion transaction limit ? Yeah, this looks perfectly normal. VACUUM doesn't make the next-XID counter go backwards. What it will do from time to time is push out the wraparound limit (by "freezing" very old rows' XID numbers). As long as there's a few million transactions' worth of daylight between the wrap limit and current XIDs, there's nothing to worry about. regards, tom lane