Обсуждение: Is Vacuum Working ?

Поиск
Список
Период
Сортировка

Is Vacuum Working ?

От
"Thorne, Francis"
Дата:

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
__________________________________________________

Re: Is Vacuum Working ?

От
Tom Lane
Дата:
"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