Re: [HACKERS] Still recommending daily vacuum...

2007-07-07 Thread Jim Nasby
On Jul 3, 2007, at 3:36 PM, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: On Mon, Jul 02, 2007 at 11:19:12PM -0400, Tom Lane wrote: Is there a reason to say anything beyond use autovac? There is; I know that things like web session tables aren't handled very well by autovacuum if

Re: [HACKERS] Still recommending daily vacuum...

2007-07-06 Thread Alvaro Herrera
Matthew T. O'Connor wrote: Alvaro Herrera wrote: Jim C. Nasby wrote: FWIW, I normally go with the 8.2 defaults, though I could see dropping vacuum_scale_factor down to 0.1 or 0.15. I also think the thresholds could be decreased further, maybe divide by 10. How about pushing thresholds all

Re: [HACKERS] Still recommending daily vacuum...

2007-07-06 Thread Alvaro Herrera
Kevin Grittner wrote: On Tue, Jul 3, 2007 at 5:34 PM, in message [EMAIL PROTECTED], Alvaro Herrera [EMAIL PROTECTED] wrote: Kevin Grittner wrote: Autovacuum is enabled with very aggressive settings, to cover small tables, including one with about 75 rows that can be updated 100 or

Re: [HACKERS] Still recommending daily vacuum...

2007-07-06 Thread Matthew T. O'Connor
Alvaro Herrera wrote: Matthew T. O'Connor wrote: Well, if a table has 10 rows, and we keep the current threshold of 1000 rows, then this table must have 1002 dead tuples (99% dead tuples, 1002 dead + 10 live) before being vacuumed. This seems wasteful because there are 500 dead tuples on it and

Re: [HACKERS] Still recommending daily vacuum...

2007-07-06 Thread Kevin Grittner
On Fri, Jul 6, 2007 at 2:19 PM, in message [EMAIL PROTECTED], Alvaro Herrera [EMAIL PROTECTED] wrote: Kevin Grittner wrote: 2. The point of autovacuum is to get rid of maintenance burden, not add to it. If you know which tables are small and frequently updated, then configure those to

Re: [HACKERS] Still recommending daily vacuum...

2007-07-06 Thread Alvaro Herrera
Kevin Grittner wrote: This all started with the question about whether the documentation should say anything about vacuum schedules other than enable autovacuum. My point was that I have a use case where I think that a scheduled vacuum will be better than leaving everything to autovacuum. I

Re: [HACKERS] Still recommending daily vacuum...

2007-07-05 Thread Michael Paesold
Alvaro Herrera wrote: So what you are proposing above amounts to setting scale factor = 0.05. The threshold is unimportant -- in the case of a big table it matters not if it's 0 or 1000, it will be almost irrelevant in calculations. In the case of small tables, then the table will be vacuumed

Re: [HACKERS] Still recommending daily vacuum...

2007-07-05 Thread Florian G. Pflug
Michael Paesold wrote: Alvaro Herrera wrote: So what you are proposing above amounts to setting scale factor = 0.05. The threshold is unimportant -- in the case of a big table it matters not if it's 0 or 1000, it will be almost irrelevant in calculations. In the case of small tables, then the

Re: [HACKERS] Still recommending daily vacuum...

2007-07-05 Thread Kevin Grittner
On Tue, Jul 3, 2007 at 5:34 PM, in message [EMAIL PROTECTED], Alvaro Herrera [EMAIL PROTECTED] wrote: Kevin Grittner wrote: Autovacuum is enabled with very aggressive settings, to cover small tables, including one with about 75 rows that can be updated 100 or more times per second. Even

Re: [HACKERS] Still recommending daily vacuum...

2007-07-04 Thread Gregory Stark
Alvaro Herrera [EMAIL PROTECTED] writes: FWIW, I normally go with the 8.2 defaults, though I could see dropping vacuum_scale_factor down to 0.1 or 0.15. I also think the thresholds could be decreased further, maybe divide by 10. How about pushing thresholds all the way down to 0? My

Re: [HACKERS] Still recommending daily vacuum...

2007-07-04 Thread Alvaro Herrera
Gregory Stark wrote: Alvaro Herrera [EMAIL PROTECTED] writes: FWIW, I normally go with the 8.2 defaults, though I could see dropping vacuum_scale_factor down to 0.1 or 0.15. I also think the thresholds could be decreased further, maybe divide by 10. How about pushing thresholds all

Re: [HACKERS] Still recommending daily vacuum...

2007-07-03 Thread Michael Paesold
Joshua D. Drake wrote: Alvaro Herrera wrote: Joshua D. Drake wrote: Did we change the default autovac parameters for 8.3 (beyond turning it on?) because on any reasonably used database, they are way to conservative. We're still on time to change them ... Any concrete proposals? I could

Re: [HACKERS] Still recommending daily vacuum...

2007-07-03 Thread Jim C. Nasby
On Tue, Jul 03, 2007 at 11:31:08AM +0200, Michael Paesold wrote: Joshua D. Drake wrote: Alvaro Herrera wrote: Joshua D. Drake wrote: Did we change the default autovac parameters for 8.3 (beyond turning it on?) because on any reasonably used database, they are way to conservative. We're

Re: [HACKERS] Still recommending daily vacuum...

2007-07-03 Thread Jim C. Nasby
On Mon, Jul 02, 2007 at 11:19:12PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: http://developer.postgresql.org/pgdocs/postgres/routine-vacuuming.html : Well, with autovac defaulting to ON in 8.3, that's certainly obsolete text now. Is there a reason to say anything

Re: [HACKERS] Still recommending daily vacuum...

2007-07-03 Thread Tom Lane
Jim C. Nasby [EMAIL PROTECTED] writes: On Mon, Jul 02, 2007 at 11:19:12PM -0400, Tom Lane wrote: Is there a reason to say anything beyond use autovac? There is; I know that things like web session tables aren't handled very well by autovacuum if there are any moderately large tables (anything

Re: [HACKERS] Still recommending daily vacuum...

2007-07-03 Thread Alvaro Herrera
Jim C. Nasby wrote: On Tue, Jul 03, 2007 at 11:31:08AM +0200, Michael Paesold wrote: Joshua D. Drake wrote: Alvaro Herrera wrote: Joshua D. Drake wrote: Did we change the default autovac parameters for 8.3 (beyond turning it on?) because on any reasonably used database, they are way to

Re: [HACKERS] Still recommending daily vacuum...

2007-07-03 Thread Kevin Grittner
On Tue, Jul 3, 2007 at 3:36 PM, in message [EMAIL PROTECTED], Tom Lane [EMAIL PROTECTED] wrote: Jim C. Nasby [EMAIL PROTECTED] writes: On Mon, Jul 02, 2007 at 11:19:12PM -0400, Tom Lane wrote: Is there a reason to say anything beyond use autovac? There is; I know that things like web

Re: [HACKERS] Still recommending daily vacuum...

2007-07-03 Thread Kevin Grittner
On Tue, Jul 3, 2007 at 5:17 PM, in message [EMAIL PROTECTED], Kevin Grittner [EMAIL PROTECTED] wrote: We have a 406GB table where 304GB is in one table. The next two tables It's probably obvious, but I meant a 406GB database. Sorry. ---(end of

Re: [HACKERS] Still recommending daily vacuum...

2007-07-03 Thread Alvaro Herrera
Kevin Grittner wrote: We have a 406GB table where 304GB is in one table. The next two tables are 57GB and 40GB. Inserts to these three tables are constant during the business day, along with inserts, updates, and very few deletes to the other tables. Database modifications are few and

Re: [HACKERS] Still recommending daily vacuum...

2007-07-03 Thread Matthew T. O'Connor
Alvaro Herrera wrote: Jim C. Nasby wrote: FWIW, I normally go with the 8.2 defaults, though I could see dropping vacuum_scale_factor down to 0.1 or 0.15. I also think the thresholds could be decreased further, maybe divide by 10. How about pushing thresholds all the way down to 0? As long

Re: [HACKERS] Still recommending daily vacuum...

2007-07-02 Thread Robert Treat
On Monday 02 July 2007 17:52, Jim C. Nasby wrote: From http://developer.postgresql.org/pgdocs/postgres/routine-vacuuming.html : Recommended practice for most sites is to schedule a database-wide VACUUM once a day at a low-usage time of day, supplemented by more frequent vacuuming of

Re: [HACKERS] Still recommending daily vacuum...

2007-07-02 Thread Tom Lane
Jim C. Nasby [EMAIL PROTECTED] writes: http://developer.postgresql.org/pgdocs/postgres/routine-vacuuming.html : Well, with autovac defaulting to ON in 8.3, that's certainly obsolete text now. Is there a reason to say anything beyond use autovac? regards, tom lane

Re: [HACKERS] Still recommending daily vacuum...

2007-07-02 Thread Joshua D. Drake
Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: http://developer.postgresql.org/pgdocs/postgres/routine-vacuuming.html : Well, with autovac defaulting to ON in 8.3, that's certainly obsolete text now. Is there a reason to say anything beyond use autovac? Did we change the default

Re: [HACKERS] Still recommending daily vacuum...

2007-07-02 Thread Alvaro Herrera
Joshua D. Drake wrote: Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: http://developer.postgresql.org/pgdocs/postgres/routine-vacuuming.html : Well, with autovac defaulting to ON in 8.3, that's certainly obsolete text now. Is there a reason to say anything beyond use autovac?

Re: [HACKERS] Still recommending daily vacuum...

2007-07-02 Thread Joshua D. Drake
Alvaro Herrera wrote: Joshua D. Drake wrote: Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: http://developer.postgresql.org/pgdocs/postgres/routine-vacuuming.html : Well, with autovac defaulting to ON in 8.3, that's certainly obsolete text now. Is there a reason to say anything