> Gaetano Mendola <mendola@bigfoot.com> writes:
>
>> We do ~4000 txn/minute so in 6 month you are screewd up...
>
> Sure, but if you ran without vacuuming for 6 months, wouldn't you notice
> the
> huge slowdowns from all those dead tuples before that?
>
>
I would think that only applies to databases where UPDATE and DELETE are
done often. What about databases that are 99.999% inserts? A DBA lightly
going over the docs may not even know that vacuum needs to be run.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Greg Stark wrote:
> Gaetano Mendola <mendola@bigfoot.com> writes:
>
>
>>We do ~4000 txn/minute so in 6 month you are screewd up...
>
>
> Sure, but if you ran without vacuuming for 6 months, wouldn't you notice the
> huge slowdowns from all those dead tuples before that?
>

In my applications yes, for sure I see the huge slowdown after 2 days
without it, but giveng the fact that someone crossed the limit I
immagine that is possible without performance loose


Regards
Gaetano Mendola

Tom Lane wrote:
> Gaetano Mendola <mendola@bigfoot.com> writes:
> > BTW, why not do an automatic vacuum instead of shutdown ? At least the
> > DB do not stop working untill someone study what the problem is and
> > how solve it.
>
> No, the entire point of this discussion is to whup the DBA upside the
> head with a big enough cluestick to get him to install autovacuum.
>
> Once autovacuum is default, it won't matter anymore.

I have a concern about this that I hope is just based on some
misunderstanding on my part.

My concern is: suppose that a database is modified extremely
infrequently? So infrequently, in fact, that over a billion read
transactions occur before the next write transaction. Once that write
transaction occurs, you're hosed, right? Autovacuum won't catch this
because it takes action based on the write activity that occurs in the
tables.

So: will autovacuum be coded to explicitly look for transaction
wraparound, or to automatically vacuum every N number of transactions
(e.g., 500 million)?



--
Kevin Brown kevin@sysexperts.com

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Fri, 18 Feb 2005 04:38 pm, Kevin Brown wrote:
> Tom Lane wrote:
> > Gaetano Mendola <mendola@bigfoot.com> writes:
> > > BTW, why not do an automatic vacuum instead of shutdown ? At least the
> > > DB do not stop working untill someone study what the problem is and
> > > how solve it.
> >
> > No, the entire point of this discussion is to whup the DBA upside the
> > head with a big enough cluestick to get him to install autovacuum.
> >
> > Once autovacuum is default, it won't matter anymore.
>
> I have a concern about this that I hope is just based on some
> misunderstanding on my part.
>
> My concern is: suppose that a database is modified extremely
> infrequently? So infrequently, in fact, that over a billion read
> transactions occur before the next write transaction. Once that write
> transaction occurs, you're hosed, right? Autovacuum won't catch this
> because it takes action based on the write activity that occurs in the
> tables.
>
> So: will autovacuum be coded to explicitly look for transaction
> wraparound, or to automatically vacuum every N number of transactions
> (e.g., 500 million)?
>
autovacuum already checks for both Transaction wraparound, and table updates.
It vacuums individual tables as they need it, from a free space/recovery point of view.

It also does checks to ensure that no database is nearing transaction wraparound, if it
is, it initiates a database wide vacuum to resolve that issue.

Regards

Russell Smith
>
>

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Thursday 17 February 2005 07:47, pgsql@mohawksoft.com wrote:
> > Gaetano Mendola <mendola@bigfoot.com> writes:
> >> We do ~4000 txn/minute so in 6 month you are screewd up...
> >
> > Sure, but if you ran without vacuuming for 6 months, wouldn't you notice
> > the
> > huge slowdowns from all those dead tuples before that?
>
> I would think that only applies to databases where UPDATE and DELETE are
> done often. What about databases that are 99.999% inserts? A DBA lightly
> going over the docs may not even know that vacuum needs to be run.
>

Yup... I don't vacuum a least a 100 of the tables in my schema cause they are
continuous insert with big deletions once every 6 months or so. Generally
speaking it isn't worth the performance hit to vacuum these big tables
regularly, so I only do it when I have to.... like every six months when wrap
around gets close.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Russell Smith wrote:

>On Fri, 18 Feb 2005 04:38 pm, Kevin Brown wrote:
>
>
>>Tom Lane wrote:
>>
>>
>>> No, the entire point of this discussion is to whup the DBA upside the
>>>
>>>head with a big enough cluestick to get him to install autovacuum.
>>>
>>>Once autovacuum is default, it won't matter anymore.
>>>
>>>
>>I have a concern about this that I hope is just based on some
>>misunderstanding on my part.
>>
>>My concern is: suppose that a database is modified extremely
>>infrequently? So infrequently, in fact, that over a billion read
>>transactions occur before the next write transaction. Once that write
>>transaction occurs, you're hosed, right? Autovacuum won't catch this
>>because it takes action based on the write activity that occurs in the
>>tables.
>>
>>So: will autovacuum be coded to explicitly look for transaction
>>wraparound, or to automatically vacuum every N number of transactions
>>(e.g., 500 million)?
>>
>>
>autovacuum already checks for both Transaction wraparound, and table updates.
>It vacuums individual tables as they need it, from a free space/recovery point of view.
>
>It also does checks to ensure that no database is nearing transaction wraparound, if it
>is, it initiates a database wide vacuum to resolve that issue.
>
Right, the check that autovacuum does for wraparound is totally separate
from the monitoring of inserts updates and deletes.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Matthew T. O'Connor wrote:
> Tom Lane wrote:
>
> >pgsql@mohawksoft.com writes:
> >
> >
> >>Maybe I'm missing something, but shouldn't the prospect of data loss (even
> >>in the presense of admin ignorance) be something that should be
> >>unacceptable? Certainly within the realm "normal PostgreSQL" operation.
> >>
> >>
> >
> >Once autovacuum gets to the point where it's used by default, this
> >particular failure mode should be a thing of the past, but in the
> >meantime I'm not going to panic about it.
> >
> Which I hope will be soon.

I am ready to help you implement integrated autovacuum in 8.1.

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Does auto_vacuum vacuum the system tables?

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Feb 16, 2005, at 5:42 PM, Matthew T. O'Connor wrote:

> Tom Lane wrote:
>
>> pgsql@mohawksoft.com writes:
>>
>>> Maybe I'm missing something, but shouldn't the prospect of data loss
>>> (even
>>> in the presense of admin ignorance) be something that should be
>>> unacceptable? Certainly within the realm "normal PostgreSQL"
>>> operation.
>>>
>>
>> Once autovacuum gets to the point where it's used by default, this
>> particular failure mode should be a thing of the past, but in the
>> meantime I'm not going to panic about it.
>>
> Which I hope will be soon.


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Thomas F.O'Connell wrote:

> Does auto_vacuum vacuum the system tables?


Yes

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match