Thread: Autovacuum on by default?
Is it time to turn on autovacuum by default in 8.2? I know we wanted to be on the side of caution with 8.1, but perhaps we should evaluate the experiences now. Comments? -- Peter Eisentraut http://developer.postgresql.org/~petere/
> Is it time to turn on autovacuum by default in 8.2? I know > we wanted to be on the side of caution with 8.1, but perhaps > we should evaluate the experiences now. Comments? FWIW, the win32 installer has enalbed autovacuum by default already in 8.1. So it's definitly received a fair amount of testing from those users (except those that turned it off, of course) //Magnus
Peter Eisentraut wrote: > Is it time to turn on autovacuum by default in 8.2? I know we wanted to > be on the side of caution with 8.1, but perhaps we should evaluate the > experiences now. Comments? Would be fine by me, but I'm curious to see what the community has to say. A few comments: Autovacuum can cause unpredictable performance issues, that is if it vacuums in the middle of a busy day and people don't want that, of course they turn it off easy enough, but they might be surprised. I haven't played with CVS HEAD much, but I think the logging issue has been addressed no? That is my single biggest gripe with the 8.1 autovacuum is that it's very hard to see if it's actually done anything without having to turn up the logging significantly. The remaining big ticket items for autovacuum are the maintenance window that Alvaro and I have just been discussing, and multiple concurrent vacuum, (possibly you could add the continued reduction of vacuum impact but that just a constant thing). Do we think it's worth waiting for either of these two features prior to turning on autovacuum by default? Matt
On Thu, 2006-08-17 at 18:32 +0200, Peter Eisentraut wrote: > Is it time to turn on autovacuum by default in 8.2? I know we wanted to > be on the side of caution with 8.1, but perhaps we should evaluate the > experiences now. Comments? I would say yes. I use it on 2 databases over the 200GB mark without any difficulties. One is OLTP and the other acts more like a data warehouse. The defaults could be a little more aggressive for both vacuum and analyze scale_factor settings; 10% and 5% respectively. --
Matthew T. O'Connor wrote: > Peter Eisentraut wrote: > > Is it time to turn on autovacuum by default in 8.2? I know we wanted to > > be on the side of caution with 8.1, but perhaps we should evaluate the > > experiences now. Comments? > > Would be fine by me, but I'm curious to see what the community has to > say. A few comments: > > Autovacuum can cause unpredictable performance issues, that is if it > vacuums in the middle of a busy day and people don't want that, of > course they turn it off easy enough, but they might be surprised. > > I haven't played with CVS HEAD much, but I think the logging issue has > been addressed no? That is my single biggest gripe with the 8.1 > autovacuum is that it's very hard to see if it's actually done anything > without having to turn up the logging significantly. This has not been addressed, except that pg_stat_activity now shows autovacuum. Someone was going to work on per-module log output, but it wasn't completed for 8.2. Does pg_stat_activity now show the table being vacuumed? > The remaining big ticket items for autovacuum are the maintenance window > that Alvaro and I have just been discussing, and multiple concurrent > vacuum, (possibly you could add the continued reduction of vacuum impact > but that just a constant thing). Do we think it's worth waiting for > either of these two features prior to turning on autovacuum by default? Probably not. It can always be turned off by people who don't want it on. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > Matthew T. O'Connor wrote: > >> Would be fine by me, but I'm curious to see what the community has to >> say. A few comments: >> >> Autovacuum can cause unpredictable performance issues, that is if it >> vacuums in the middle of a busy day and people don't want that, of >> course they turn it off easy enough, but they might be surprised. >> >> I haven't played with CVS HEAD much, but I think the logging issue has >> been addressed no? That is my single biggest gripe with the 8.1 >> autovacuum is that it's very hard to see if it's actually done anything >> without having to turn up the logging significantly. >> > > This has not been addressed, except that pg_stat_activity now shows > autovacuum. Someone was going to work on per-module log output, but it > wasn't completed for 8.2. Does pg_stat_activity now show the table > being vacuumed? Hmm... I though it had, not the full blown per-module log output stuff, but just a simple reorgainzing of the log levels for autovacuum messages. That is lowering the level for: LOG: autovacuum: processing database "foo" and increasing the log level when autovacuum actually fires off a VACUUM or ANALYZE command.
Rod Taylor wrote: > The defaults could be a little more aggressive for both vacuum and > analyze scale_factor settings; 10% and 5% respectively. I would agree with this, not sure of 10%/5% are right, but the general feedback I have heard is that while the defaults in 8.1 are much better than the ones in the contrib module, they are still not aggressive enough.
Matthew T. O'Connor wrote: > Bruce Momjian wrote: > > Matthew T. O'Connor wrote: > > > >> Would be fine by me, but I'm curious to see what the community has to > >> say. A few comments: > >> > >> Autovacuum can cause unpredictable performance issues, that is if it > >> vacuums in the middle of a busy day and people don't want that, of > >> course they turn it off easy enough, but they might be surprised. > >> > >> I haven't played with CVS HEAD much, but I think the logging issue has > >> been addressed no? That is my single biggest gripe with the 8.1 > >> autovacuum is that it's very hard to see if it's actually done anything > >> without having to turn up the logging significantly. > >> > > > > This has not been addressed, except that pg_stat_activity now shows > > autovacuum. Someone was going to work on per-module log output, but it > > wasn't completed for 8.2. Does pg_stat_activity now show the table > > being vacuumed? > > Hmm... I though it had, not the full blown per-module log output stuff, > but just a simple reorgainzing of the log levels for autovacuum > messages. That is lowering the level for: > LOG: autovacuum: processing database "foo" Yes, this was done. > and increasing the log level when autovacuum actually fires off a VACUUM > or ANALYZE command. This was not done because the logging control only for autovacuum was going to be added. Right now, if you want to see the vacuum activity, you end up seeing debug stuff too (very ugly). -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > Matthew T. O'Connor wrote: > >> and increasing the log level when autovacuum actually fires off a VACUUM >> or ANALYZE command. >> > > This was not done because the logging control only for autovacuum was > going to be added. Right now, if you want to see the vacuum activity, > you end up seeing debug stuff too (very ugly). Any chance we can make this change before release? I think it's very important to be able to look through the logs and *know* that you tables are getting vacuumed or not.
Peter, > Is it time to turn on autovacuum by default in 8.2? I know we wanted to > be on the side of caution with 8.1, but perhaps we should evaluate the > experiences now. Comments? I'm in favor of this, but do we want to turn on vacuum_delay by default as well? --Josh
Matthew T. O'Connor wrote: > Bruce Momjian wrote: > > Matthew T. O'Connor wrote: > > > >> and increasing the log level when autovacuum actually fires off a VACUUM > >> or ANALYZE command. > >> > > > > This was not done because the logging control only for autovacuum was > > going to be added. Right now, if you want to see the vacuum activity, > > you end up seeing debug stuff too (very ugly). > > Any chance we can make this change before release? I think it's very > important to be able to look through the logs and *know* that you tables > are getting vacuumed or not. Agreed. I just IM'ed Alvaro and he says pg_stat_activity should now show exactly what autovacuum is doing (and if it doesn't, let's fix it). I think that is the best solution to the monitoring problem, rather than throwing lines in the server logs. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > Matthew T. O'Connor wrote: > > Bruce Momjian wrote: > > > Matthew T. O'Connor wrote: > > > > > >> and increasing the log level when autovacuum actually fires off a VACUUM > > >> or ANALYZE command. > > >> > > > > > > This was not done because the logging control only for autovacuum was > > > going to be added. Right now, if you want to see the vacuum activity, > > > you end up seeing debug stuff too (very ugly). > > > > Any chance we can make this change before release? I think it's very > > important to be able to look through the logs and *know* that you tables > > are getting vacuumed or not. > > Agreed. I just IM'ed Alvaro and he says pg_stat_activity should now > show exactly what autovacuum is doing (and if it doesn't, let's fix it). > I think that is the best solution to the monitoring problem, rather than > throwing lines in the server logs. Well, the problem is that it shows what it's *currently* doing, but it doesn't let you know what has happened in the last day or whatever. It can't answer "has table foo been vacuumed recently?" or "what tables haven't been vacuumed at all during this week?" -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Bruce Momjian wrote: > Matthew T. O'Connor wrote: > >> Any chance we can make this change before release? I think it's very >> important to be able to look through the logs and *know* that you tables >> are getting vacuumed or not. >> > > Agreed. I just IM'ed Alvaro and he says pg_stat_activity should now > show exactly what autovacuum is doing (and if it doesn't, let's fix it). > I think that is the best solution to the monitoring problem, rather than > throwing lines in the server logs. I'm not sure I agree with this. I can use the pg_stat_activity table to see if autovacuum is doing something right now, but what I want to be able to do is look through my logs and see that table_foo hasn't been vacuumed since last week, or that table_bar has been vacuumed 7 times today. Can I do that just with the stat system alone?
Alvaro Herrera wrote: > Bruce Momjian wrote: >> Matthew T. O'Connor wrote: >>> Bruce Momjian wrote: >>>> Matthew T. O'Connor wrote: >>>> >>>>> and increasing the log level when autovacuum actually fires off a >>>>> VACUUM or ANALYZE command. >>>>> >>>> >>>> This was not done because the logging control only for autovacuum >>>> was going to be added. Right now, if you want to see the vacuum >>>> activity, you end up seeing debug stuff too (very ugly). >>> >>> Any chance we can make this change before release? I think it's >>> very important to be able to look through the logs and *know* that >>> you tables are getting vacuumed or not. >> >> Agreed. I just IM'ed Alvaro and he says pg_stat_activity should now >> show exactly what autovacuum is doing (and if it doesn't, let's fix >> it). I think that is the best solution to the monitoring problem, >> rather than throwing lines in the server logs. > > Well, the problem is that it shows what it's *currently* doing, but it > doesn't let you know what has happened in the last day or whatever. > It can't answer "has table foo been vacuumed recently?" or "what > tables haven't been vacuumed at all during this week?" I added last vacuum and last analyze (both auto and manual) dates in the 8.2 tree..... -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: ler@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893
Josh Berkus wrote: >> Is it time to turn on autovacuum by default in 8.2? I know we wanted >> to be on the side of caution with 8.1, but perhaps we should evaluate >> the experiences now. Comments? > > I'm in favor of this, but do we want to turn on vacuum_delay by default > as well? I thought about this, might be a good idea as it will mitigate the impact of vacuums, however it will also slow down vacuums, I'm a bit concerned that it won't be able to keep up on really large database, or that it'll fall really far behind after vacuuming a big table. Also, if we do enable it, what is a good default?
On Thu, Aug 17, 2006 at 12:41:57PM -0400, Matthew T. O'Connor wrote: > Peter Eisentraut wrote: > >Is it time to turn on autovacuum by default in 8.2? I know we wanted to > >be on the side of caution with 8.1, but perhaps we should evaluate the > >experiences now. Comments? > > Would be fine by me, but I'm curious to see what the community has to > say. A few comments: > > Autovacuum can cause unpredictable performance issues, that is if it > vacuums in the middle of a busy day and people don't want that, of > course they turn it off easy enough, but they might be surprised. Anyone in that situation needs to be more hands-on with the database anyway. I think the big target for autovac is beginners who otherwise would bloat themselves silly (of course, it's also very useful far beyond beginners, but by that time an admin should have some clue about what they're doing). And +1 on Rod's suggestion to make it more aggressive. I always drop the scale factor to at least 0.2 and 0.1 (though 0.1 and 0.05 don't seem unreasonable), and typically drop the thresholds to 200 and 100 (though again, lower is probably warrented). Actually, on a table small enough for the thresholds to kick in it's going to be extremely fast to vacuum anyway, and the table is probably either static or changing very rapidly. I'm wondering if maybe they should just default to 0? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Alvaro Herrera wrote: > Bruce Momjian wrote: > > Matthew T. O'Connor wrote: > > > Bruce Momjian wrote: > > > > Matthew T. O'Connor wrote: > > > > > > > >> and increasing the log level when autovacuum actually fires off a VACUUM > > > >> or ANALYZE command. > > > >> > > > > > > > > This was not done because the logging control only for autovacuum was > > > > going to be added. Right now, if you want to see the vacuum activity, > > > > you end up seeing debug stuff too (very ugly). > > > > > > Any chance we can make this change before release? I think it's very > > > important to be able to look through the logs and *know* that you tables > > > are getting vacuumed or not. > > > > Agreed. I just IM'ed Alvaro and he says pg_stat_activity should now > > show exactly what autovacuum is doing (and if it doesn't, let's fix it). > > I think that is the best solution to the monitoring problem, rather than > > throwing lines in the server logs. > > Well, the problem is that it shows what it's *currently* doing, but it > doesn't let you know what has happened in the last day or whatever. It > can't answer "has table foo been vacuumed recently?" or "what tables > haven't been vacuumed at all during this week?" True, but it seems strange to use the server logs for this type of information. If anything, perhaps we should allow an optional file name to put that information, or update pg_class with the most recent vacuum time, or even better append that information to a system table. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Larry Rosenman wrote: > Alvaro Herrera wrote: > > Bruce Momjian wrote: > >> Matthew T. O'Connor wrote: > >>> Bruce Momjian wrote: > >>>> Matthew T. O'Connor wrote: > >>>> > >>>>> and increasing the log level when autovacuum actually fires off a > >>>>> VACUUM or ANALYZE command. > >>>>> > >>>> > >>>> This was not done because the logging control only for autovacuum > >>>> was going to be added. Right now, if you want to see the vacuum > >>>> activity, you end up seeing debug stuff too (very ugly). > >>> > >>> Any chance we can make this change before release? I think it's > >>> very important to be able to look through the logs and *know* that > >>> you tables are getting vacuumed or not. > >> > >> Agreed. I just IM'ed Alvaro and he says pg_stat_activity should now > >> show exactly what autovacuum is doing (and if it doesn't, let's fix > >> it). I think that is the best solution to the monitoring problem, > >> rather than throwing lines in the server logs. > > > > Well, the problem is that it shows what it's *currently* doing, but it > > doesn't let you know what has happened in the last day or whatever. > > It can't answer "has table foo been vacuumed recently?" or "what > > tables haven't been vacuumed at all during this week?" > > I added last vacuum and last analyze (both auto and manual) dates in the 8.2 > tree..... Ah, true, I forgot about that. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Bruce Momjian wrote: > Larry Rosenman wrote: >> Alvaro Herrera wrote: >>> Bruce Momjian wrote: >>>> Matthew T. O'Connor wrote: >>>>> Bruce Momjian wrote: >>>>>> Matthew T. O'Connor wrote: >>>>>> >>>>>>> and increasing the log level when autovacuum actually fires off >>>>>>> a VACUUM or ANALYZE command. >>>>>>> >>>>>> >>>>>> This was not done because the logging control only for autovacuum >>>>>> was going to be added. Right now, if you want to see the vacuum >>>>>> activity, you end up seeing debug stuff too (very ugly). >>>>> >>>>> Any chance we can make this change before release? I think it's >>>>> very important to be able to look through the logs and *know* that >>>>> you tables are getting vacuumed or not. >>>> >>>> Agreed. I just IM'ed Alvaro and he says pg_stat_activity should >>>> now show exactly what autovacuum is doing (and if it doesn't, >>>> let's fix it). I think that is the best solution to the monitoring >>>> problem, rather than throwing lines in the server logs. >>> >>> Well, the problem is that it shows what it's *currently* doing, but >>> it doesn't let you know what has happened in the last day or >>> whatever. It can't answer "has table foo been vacuumed recently?" >>> or "what tables haven't been vacuumed at all during this week?" >> >> I added last vacuum and last analyze (both auto and manual) dates in >> the 8.2 tree..... > > Uh, where? I don't see it in pg_class, pg_database, or pg_autovacuum. > Is it somewhere else? pg_stat_*_tables -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: ler@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893
Larry Rosenman wrote: > Alvaro Herrera wrote: > >> Bruce Momjian wrote: >> >> Well, the problem is that it shows what it's *currently* doing, but it >> doesn't let you know what has happened in the last day or whatever. >> It can't answer "has table foo been vacuumed recently?" or "what >> tables haven't been vacuumed at all during this week?" >> > > I added last vacuum and last analyze (both auto and manual) dates in the 8.2 > tree..... Hmm... well that does address most of my issue. Doesn't tell me that table_foo is getting vacuumed constantly, but it does tell me that it was vacuumed recently. Might be good enough.
Larry Rosenman wrote: > Bruce Momjian wrote: > > Larry Rosenman wrote: > >> Alvaro Herrera wrote: > >>> Bruce Momjian wrote: > >>>> Matthew T. O'Connor wrote: > >>>>> Bruce Momjian wrote: > >>>>>> Matthew T. O'Connor wrote: > >>>>>> > >>>>>>> and increasing the log level when autovacuum actually fires off > >>>>>>> a VACUUM or ANALYZE command. > >>>>>>> > >>>>>> > >>>>>> This was not done because the logging control only for autovacuum > >>>>>> was going to be added. Right now, if you want to see the vacuum > >>>>>> activity, you end up seeing debug stuff too (very ugly). > >>>>> > >>>>> Any chance we can make this change before release? I think it's > >>>>> very important to be able to look through the logs and *know* that > >>>>> you tables are getting vacuumed or not. > >>>> > >>>> Agreed. I just IM'ed Alvaro and he says pg_stat_activity should > >>>> now show exactly what autovacuum is doing (and if it doesn't, > >>>> let's fix it). I think that is the best solution to the monitoring > >>>> problem, rather than throwing lines in the server logs. > >>> > >>> Well, the problem is that it shows what it's *currently* doing, but > >>> it doesn't let you know what has happened in the last day or > >>> whatever. It can't answer "has table foo been vacuumed recently?" > >>> or "what tables haven't been vacuumed at all during this week?" > >> > >> I added last vacuum and last analyze (both auto and manual) dates in > >> the 8.2 tree..... > > > > Uh, where? I don't see it in pg_class, pg_database, or pg_autovacuum. > > Is it somewhere else? > > pg_stat_*_tables Oh, great. I had forgotten about that. Thanks, so we are OK with monitoring autovacuum for 8.2. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Matthew T. O'Connor wrote: > Bruce Momjian wrote: > >Matthew T. O'Connor wrote: > > > >>Any chance we can make this change before release? I think it's very > >>important to be able to look through the logs and *know* that you tables > >>are getting vacuumed or not. > >> > > > >Agreed. I just IM'ed Alvaro and he says pg_stat_activity should now > >show exactly what autovacuum is doing (and if it doesn't, let's fix it). > >I think that is the best solution to the monitoring problem, rather than > >throwing lines in the server logs. > > I'm not sure I agree with this. I can use the pg_stat_activity table to > see if autovacuum is doing something right now, but what I want to be > able to do is look through my logs and see that table_foo hasn't been > vacuumed since last week, or that table_bar has been vacuumed 7 times > today. Can I do that just with the stat system alone? Actually Larry just reminded us that you can use pg_stat_all_tables to see that information. However I'm testing it and it doesn't seem to work for all tables ... strange. I'll have a look. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Jim C. Nasby wrote: > On Thu, Aug 17, 2006 at 12:41:57PM -0400, Matthew T. O'Connor wrote: > >> Would be fine by me, but I'm curious to see what the community has to >> say. A few comments: >> >> Autovacuum can cause unpredictable performance issues, that is if it >> vacuums in the middle of a busy day and people don't want that, of >> course they turn it off easy enough, but they might be surprised. >> > > Anyone in that situation needs to be more hands-on with the database > anyway. I think the big target for autovac is beginners who otherwise > would bloat themselves silly (of course, it's also very useful far > beyond beginners, but by that time an admin should have some clue about > what they're doing). > Fair enough, also if we turn on the delay setting by default, it will help address this. > And +1 on Rod's suggestion to make it more aggressive. I always drop the > scale factor to at least 0.2 and 0.1 (though 0.1 and 0.05 don't seem > unreasonable), and typically drop the thresholds to 200 and 100 (though > again, lower is probably warrented). > OK. > Actually, on a table small enough for the thresholds to kick in it's > going to be extremely fast to vacuum anyway, and the table is probably > either static or changing very rapidly. I'm wondering if maybe they > should just default to 0? > I assume you are suggesting that the base value be 0? Well for one thing if the table doesn't have any rows that will result in constant vacuuming of that table, so it needs to be greater than 0. For a small table, say 100 rows, there usually isn'tn much performance impact if the table if 50% dead space, so I think the base values you suggest are OK, but they shouldn't be 0.
Matthew T. O'Connor wrote: > Jim C. Nasby wrote: > >Actually, on a table small enough for the thresholds to kick in it's > >going to be extremely fast to vacuum anyway, and the table is probably > >either static or changing very rapidly. I'm wondering if maybe they > >should just default to 0? > > > I assume you are suggesting that the base value be 0? Well for one > thing if the table doesn't have any rows that will result in constant > vacuuming of that table, so it needs to be greater than 0. For a small > table, say 100 rows, there usually isn'tn much performance impact if the > table if 50% dead space, so I think the base values you suggest are OK, > but they shouldn't be 0. Actually Tom suggested some time ago that we should get rid of the base value completely, i.e. make it 0 forever. A row with 0 tables would not show any activity in pgstats, so it would not be vacuumed constantly. Only once after it's truncated. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote: > Matthew T. O'Connor wrote: > >> I assume you are suggesting that the base value be 0? Well for one >> thing if the table doesn't have any rows that will result in constant >> vacuuming of that table, so it needs to be greater than 0. For a small >> table, say 100 rows, there usually isn'tn much performance impact if the >> table if 50% dead space, so I think the base values you suggest are OK, >> but they shouldn't be 0. >> > > Actually Tom suggested some time ago that we should get rid of the base > value completely, i.e. make it 0 forever. > > A row with 0 tables would not show any activity in pgstats, so it would > not be vacuumed constantly. Only once after it's truncated. OK, forgot that. Well I put it in originally as a way to give more flexability to the calculation, if I want a tabled vacuumed every 100 updates, then I can set the scaling factor to 0 and the base value to 100, but maybe that's not really needed. It would simplify things if we got rid of it.
On Thu, Aug 17, 2006 at 01:29:57PM -0400, Matthew T. O'Connor wrote: > Josh Berkus wrote: > >>Is it time to turn on autovacuum by default in 8.2? I know we wanted > >>to be on the side of caution with 8.1, but perhaps we should evaluate > >>the experiences now. Comments? > > > >I'm in favor of this, but do we want to turn on vacuum_delay by default > >as well? > > I thought about this, might be a good idea as it will mitigate the > impact of vacuums, however it will also slow down vacuums, I'm a bit > concerned that it won't be able to keep up on really large database, or > that it'll fall really far behind after vacuuming a big table. Probably a better idea is to turn on autovacuum_delay instead of vacuum_delay. > Also, if we do enable it, what is a good default? I did some limited testing on one clients system and 10ms seemed a good value for their fairly decent drive array. How that would translate on a slower machine (which is what I'm more concerned with) I don't know. Maybe 20ms would be better? I also discovered on that system that upping vacuum_cost_limit and vacuum_cost_page_dirty to 300 and 30 was a better setting. I suspect that might partly be due to vacuum_cost_page_miss being pretty high. That number makes sense for a page that actually comes off the disk, but with so many folks still using 10% of memory for shared_buffers I think it should be lower (5?), since a lot of page misses will come out of the kernel cache anyway. Of course if we had some way to determine if a page came out of the OS cache... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Thu, Aug 17, 2006 at 01:47:37PM -0400, Matthew T. O'Connor wrote: > Alvaro Herrera wrote: > >Matthew T. O'Connor wrote: > > > >>I assume you are suggesting that the base value be 0? Well for one > >>thing if the table doesn't have any rows that will result in constant > >>vacuuming of that table, so it needs to be greater than 0. For a small > >>table, say 100 rows, there usually isn'tn much performance impact if the > >>table if 50% dead space, so I think the base values you suggest are OK, > >>but they shouldn't be 0. > >> > > > >Actually Tom suggested some time ago that we should get rid of the base > >value completely, i.e. make it 0 forever. > > > >A row with 0 tables would not show any activity in pgstats, so it would > >not be vacuumed constantly. Only once after it's truncated. > > OK, forgot that. Well I put it in originally as a way to give more > flexability to the calculation, if I want a tabled vacuumed every 100 > updates, then I can set the scaling factor to 0 and the base value to > 100, but maybe that's not really needed. It would simplify things if we > got rid of it. I think it makes more sense in the per-table settings (which I can't remember if we actually have yet). For example, on a frequently update table that you know should always be small, you might well want to set it to scaling factor 0 and base of 20 or whatever. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Bruce Momjian <bruce@momjian.us> writes:
> Agreed. I just IM'ed Alvaro and he says pg_stat_activity should now
> show exactly what autovacuum is doing (and if it doesn't, let's fix it).
> I think that is the best solution to the monitoring problem, rather than
> throwing lines in the server logs.
How do you figure that? The point of logging what's done is so that you
can find out what autovac has been doing, not what it's doing right now.
regards, tom lane
Alvaro Herrera wrote: > Matthew T. O'Connor wrote: > > Bruce Momjian wrote: > > >Matthew T. O'Connor wrote: > > > > > >>Any chance we can make this change before release? I think it's very > > >>important to be able to look through the logs and *know* that you tables > > >>are getting vacuumed or not. > > >> > > > > > >Agreed. I just IM'ed Alvaro and he says pg_stat_activity should now > > >show exactly what autovacuum is doing (and if it doesn't, let's fix it). > > >I think that is the best solution to the monitoring problem, rather than > > >throwing lines in the server logs. > > > > I'm not sure I agree with this. I can use the pg_stat_activity table to > > see if autovacuum is doing something right now, but what I want to be > > able to do is look through my logs and see that table_foo hasn't been > > vacuumed since last week, or that table_bar has been vacuumed 7 times > > today. Can I do that just with the stat system alone? > > Actually Larry just reminded us that you can use pg_stat_all_tables to > see that information. However I'm testing it and it doesn't seem to > work for all tables ... strange. I'll have a look. Nevermind -- it's just that if you vacuum a table which you haven't touched (insert, update, delete) since the last stats reset, then the vacuum info isn't recorded because we refuse to create the pgstat entry for the table. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Thu, Aug 17, 2006 at 03:17:07PM -0400, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Agreed. I just IM'ed Alvaro and he says pg_stat_activity should now > > show exactly what autovacuum is doing (and if it doesn't, let's fix it). > > I think that is the best solution to the monitoring problem, rather than > > throwing lines in the server logs. > > How do you figure that? The point of logging what's done is so that you > can find out what autovac has been doing, not what it's doing right now. Well, the big complaint has been "I have no way to know if autovac has actually been vacuuming table X", which the pg_stat changes address. But it would still be very handy to be able to do things like get stats from autovac (which presumably you can do if you crank the logging verbosity up high enough). Personally, I'd love the ability to have autovac (or heck, ever regular vac) log statistics on a per-relation basis to a table for later analysis. I realize there's a lot of transactionality issues there, but I'd happily pay the cost of having autovac keep a seperate logging connection open to be able to get this info. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Alvaro Herrera wrote: > Alvaro Herrera wrote: >> Matthew T. O'Connor wrote: >>> Bruce Momjian wrote: >>>> Matthew T. O'Connor wrote: >>>> >>>>> Any chance we can make this change before release? I think it's >>>>> very important to be able to look through the logs and *know* >>>>> that you tables are getting vacuumed or not. >>>>> >>>> >>>> Agreed. I just IM'ed Alvaro and he says pg_stat_activity should >>>> now show exactly what autovacuum is doing (and if it doesn't, >>>> let's fix it). I think that is the best solution to the monitoring >>>> problem, rather than throwing lines in the server logs. >>> >>> I'm not sure I agree with this. I can use the pg_stat_activity >>> table to see if autovacuum is doing something right now, but what I >>> want to be able to do is look through my logs and see that >>> table_foo hasn't been vacuumed since last week, or that table_bar >>> has been vacuumed 7 times today. Can I do that just with the stat >>> system alone? >> >> Actually Larry just reminded us that you can use pg_stat_all_tables >> to see that information. However I'm testing it and it doesn't seem >> to work for all tables ... strange. I'll have a look. > > Nevermind -- it's just that if you vacuum a table which you haven't > touched (insert, update, delete) since the last stats reset, then the > vacuum info isn't recorded because we refuse to create the pgstat > entry for the table. Do I need to write a Doc patch for that? It seemed consistent with other functions of the same class when I did the date patch. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: ler@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893
Larry Rosenman wrote: > Alvaro Herrera wrote: > > Alvaro Herrera wrote: > >> Matthew T. O'Connor wrote: > >>> Bruce Momjian wrote: > >>>> Matthew T. O'Connor wrote: > >>>> > >>>>> Any chance we can make this change before release? I think it's > >>>>> very important to be able to look through the logs and *know* > >>>>> that you tables are getting vacuumed or not. > >>>>> > >>>> > >>>> Agreed. I just IM'ed Alvaro and he says pg_stat_activity should > >>>> now show exactly what autovacuum is doing (and if it doesn't, > >>>> let's fix it). I think that is the best solution to the monitoring > >>>> problem, rather than throwing lines in the server logs. > >>> > >>> I'm not sure I agree with this. I can use the pg_stat_activity > >>> table to see if autovacuum is doing something right now, but what I > >>> want to be able to do is look through my logs and see that > >>> table_foo hasn't been vacuumed since last week, or that table_bar > >>> has been vacuumed 7 times today. Can I do that just with the stat > >>> system alone? > >> > >> Actually Larry just reminded us that you can use pg_stat_all_tables > >> to see that information. However I'm testing it and it doesn't seem > >> to work for all tables ... strange. I'll have a look. > > > > Nevermind -- it's just that if you vacuum a table which you haven't > > touched (insert, update, delete) since the last stats reset, then the > > vacuum info isn't recorded because we refuse to create the pgstat > > entry for the table. > > Do I need to write a Doc patch for that? It seemed consistent with other > functions of the > same class when I did the date patch. I'm not sure. It just makes sense overall ... the fact that the collector chooses to discard some info should be documented somewhere I think (maybe it already is, I don't know), but not specifically for the vacuum times. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Agreed. I just IM'ed Alvaro and he says pg_stat_activity should now > > show exactly what autovacuum is doing (and if it doesn't, let's fix it). > > I think that is the best solution to the monitoring problem, rather than > > throwing lines in the server logs. > > How do you figure that? The point of logging what's done is so that you > can find out what autovac has been doing, not what it's doing right now. I don't think the server logs is the place to record history autovacuum activity. I am not saying we might not need that functionality, but not in the server logs, and I think others seem to agree. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
> Tom Lane wrote:
>> How do you figure that? The point of logging what's done is so that you
>> can find out what autovac has been doing, not what it's doing right now.
> I don't think the server logs is the place to record history autovacuum
> activity. I am not saying we might not need that functionality, but not
> in the server logs, and I think others seem to agree.
Um, so what do you think the server log *is* for, if not a historical
activity record? This position would certainly surprise a lot of people
who use the log that way ...
regards, tom lane
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Tom Lane wrote: > >> How do you figure that? The point of logging what's done is so that you > >> can find out what autovac has been doing, not what it's doing right now. > > > I don't think the server logs is the place to record history autovacuum > > activity. I am not saying we might not need that functionality, but not > > in the server logs, and I think others seem to agree. > > Um, so what do you think the server log *is* for, if not a historical > activity record? This position would certainly surprise a lot of people > who use the log that way ... It is by default for unusual activity. It can be for normal activity using the proper GUC settings, but we don't have a way to control that just for autovacuum yet, and given what we have in 8.2, I don't see a need to add more until users say they need more. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > It is by default for unusual activity. It can be for normal activity > using the proper GUC settings, but we don't have a way to control > that just for autovacuum yet, and given what we have in 8.2, I don't > see a need to add more until users say they need more. Right, the server log should tend to being empty if there is nothing unusual happening. I recall that someone once complained about every checkpoint run making an entry; and every autovacuum run making one would be about the same. Certainly, there is room for more fine-tuning here, but the all-important question "Has autovacuum ever run (recently)" can now be easily answered in 8.2, so we're good. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Am Donnerstag, 17. August 2006 18:40 schrieb Josh Berkus: > I'm in favor of this, but do we want to turn on vacuum_delay by default > as well? People might complain that suddenly their vacuum runs take four times as long (or whatever). Of course, if we turn on autovacuum and advocate a more or less hands-off vacuum policy, they won't have to care either way. All of this would certainly be release-notes material. Does anyone, for that matter, want to propose possible default parameters for vacuum_delay? -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut wrote: > Am Donnerstag, 17. August 2006 18:40 schrieb Josh Berkus: >> I'm in favor of this, but do we want to turn on vacuum_delay by default >> as well? > > People might complain that suddenly their vacuum runs take four times as long > (or whatever). Of course, if we turn on autovacuum and advocate a more or > less hands-off vacuum policy, they won't have to care either way. All of > this would certainly be release-notes material. > > Does anyone, for that matter, want to propose possible default parameters for > vacuum_delay? I said vacuum_delay but I should have been more specific, there are autovacuum GUC variables which is what we should be talking about. This way manually run, or nighly run by cron vacuums are still as fast as they ever were.
Peter Eisentraut <peter_e@gmx.net> writes:
> Does anyone, for that matter, want to propose possible default parameters for
> vacuum_delay?
I haven't seen any sign that anyone's done any serious testing of delay
parameters, so I don't think we have the data needed to select some
defaults ...
regards, tom lane
Going back on-list... On Tue, Aug 22, 2006 at 08:47:04AM -0400, Alvaro Herrera wrote: > Jim Nasby wrote: > > On Aug 17, 2006, at 3:19 PM, Alvaro Herrera wrote: > > >Nevermind -- it's just that if you vacuum a table which you haven't > > >touched (insert, update, delete) since the last stats reset, then the > > >vacuum info isn't recorded because we refuse to create the pgstat > > >entry > > >for the table. > > > > Have you changed this? > > No ... > > > ISTM that it should go ahead and create the pgstat entry... > > What for? While on the surface it makes sense not to have a stat entry for a table "with no activity" (since no activity means no need to vacuum), there's 2 problems: This doesn't exactly meet the test of 'least surprise'. If the table's vacuumed for any reason (even manually), we should record the info. If there's a bunch of activity on a table but stats are reset before a vacuum is run on it and then a vacuum is run, the user will still be left thinking that the table needs to be vacuumed. -- Jim C. Nasby, Database Architect jim@nasby.net 512.569.9461 (cell) http://jim.nasby.net
"Jim C. Nasby" <jim@nasby.net> writes:
> If there's a bunch of activity on a table but stats are reset before a
> vacuum is run on it and then a vacuum is run, the user will still be
> left thinking that the table needs to be vacuumed.
Except that autovac *won't* vacuum it if the stats have been reset.
So I'm not seeing that there's really a problem in practice.
regards, tom lane
"Jim C. Nasby" <jnasby@pervasive.com> wrote: > And +1 on Rod's suggestion to make it more aggressive. I always drop the > scale factor to at least 0.2 and 0.1 (though 0.1 and 0.05 don't seem > unreasonable), and typically drop the thresholds to 200 and 100 (though > again, lower is probably warrented). The default fillfactors for index btree is 90%. So if we want to avoid spliting of the leaf pages, vacuum scale factors should be less than 0.1 in cases where tuples are only updated randomly. I think threshoulds should be less than PCTFREEs(=1-fillfactors) except ever-increasing tables. This is a too simplified policy, but we probably need documentation for the linkages between autovacuum and fillfactors. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center
On Tue, Aug 22, 2006 at 11:08:49AM -0400, Tom Lane wrote: > "Jim C. Nasby" <jim@nasby.net> writes: > > If there's a bunch of activity on a table but stats are reset before a > > vacuum is run on it and then a vacuum is run, the user will still be > > left thinking that the table needs to be vacuumed. > > Except that autovac *won't* vacuum it if the stats have been reset. > So I'm not seeing that there's really a problem in practice. IIRC the stats also include info about regular (manual) vacuums, so the above scenario still applies. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Wed, Aug 23, 2006 at 01:45:43PM +0900, ITAGAKI Takahiro wrote: > > "Jim C. Nasby" <jnasby@pervasive.com> wrote: > > > And +1 on Rod's suggestion to make it more aggressive. I always drop the > > scale factor to at least 0.2 and 0.1 (though 0.1 and 0.05 don't seem > > unreasonable), and typically drop the thresholds to 200 and 100 (though > > again, lower is probably warrented). > > The default fillfactors for index btree is 90%. So if we want to avoid > spliting of the leaf pages, vacuum scale factors should be less than 0.1 > in cases where tuples are only updated randomly. I think threshoulds should > be less than PCTFREEs(=1-fillfactors) except ever-increasing tables. Very good point, though at least for indexes the new code that tries to reclaim space on a page before splitting it will help. Doesn't help for the heap, though. So maybe the default should be 0.08? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote: > On Tue, Aug 22, 2006 at 11:08:49AM -0400, Tom Lane wrote: >> "Jim C. Nasby" <jim@nasby.net> writes: >>> If there's a bunch of activity on a table but stats are reset >>> before a vacuum is run on it and then a vacuum is run, the user >>> will still be left thinking that the table needs to be vacuumed. >> >> Except that autovac *won't* vacuum it if the stats have been reset. >> So I'm not seeing that there's really a problem in practice. > > IIRC the stats also include info about regular (manual) vacuums, so > the above scenario still applies. They do. The stats patch as applied captures both autovacuum and manual vacuum as well as analyze (both from the Autovacuum daemon and manual). -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: ler@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893
Larry Rosenman wrote: > Jim C. Nasby wrote: > > On Tue, Aug 22, 2006 at 11:08:49AM -0400, Tom Lane wrote: > >> "Jim C. Nasby" <jim@nasby.net> writes: > >>> If there's a bunch of activity on a table but stats are reset > >>> before a vacuum is run on it and then a vacuum is run, the user > >>> will still be left thinking that the table needs to be vacuumed. > >> > >> Except that autovac *won't* vacuum it if the stats have been reset. > >> So I'm not seeing that there's really a problem in practice. > > > > IIRC the stats also include info about regular (manual) vacuums, so > > the above scenario still applies. > > They do. The stats patch as applied captures both autovacuum and manual > vacuum > as well as analyze (both from the Autovacuum daemon and manual). But the original point still remains: if you manually VACUUM a table that does not have a pgstat entry, the pgstat system will drop the "vacuum timestamp" message on the floor without recreating the entry. I think there is a reasonable case for saying that a manual vacuum could hint pgstat to create the entry instead. On the other hand, if autovacuum never vacuums a table with no pgstat entry, then you could just create the pgstat entry in both cases and it would be the same anyway. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> I think there is a reasonable case for saying that a manual vacuum could
> hint pgstat to create the entry instead.
The problem with that is that a simple "VACUUM;" would force pgstat to
populate its entire hashtable. Which more or less defeats the idea of
not wasting table space on inactive tables --- and given the way the
reporting-file mechanism works, there's definitely an incentive to not
make the table bigger than it has to be.
It wouldn't be so bad if pgstat had a mechanism for aging out unused
table entries ...
regards, tom lane
On Thu, Aug 24, 2006 at 09:58:10AM -0400, Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: > > I think there is a reasonable case for saying that a manual vacuum could > > hint pgstat to create the entry instead. > > The problem with that is that a simple "VACUUM;" would force pgstat to > populate its entire hashtable. Which more or less defeats the idea of > not wasting table space on inactive tables --- and given the way the > reporting-file mechanism works, there's definitely an incentive to not > make the table bigger than it has to be. > > It wouldn't be so bad if pgstat had a mechanism for aging out unused > table entries ... Maybe a good compromise would be only populating info for tables that had dead tuples... that would eliminate any static tables, and most DBAs should know that those tables are static. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> On Thu, Aug 24, 2006 at 09:58:10AM -0400, Tom Lane wrote:
>> Alvaro Herrera <alvherre@commandprompt.com> writes:
>>> I think there is a reasonable case for saying that a manual vacuum could
>>> hint pgstat to create the entry instead.
>>
>> The problem with that is that a simple "VACUUM;" would force pgstat to
>> populate its entire hashtable.
> Maybe a good compromise would be only populating info for tables that
> had dead tuples... that would eliminate any static tables, and most DBAs
> should know that those tables are static.
Hm, that definitely seems like an idea. Does the current pgstat message
from vacuum tell how many rows it deleted?
regards, tom lane
Tom Lane wrote: > "Jim C. Nasby" <jnasby@pervasive.com> writes: > > On Thu, Aug 24, 2006 at 09:58:10AM -0400, Tom Lane wrote: > >> Alvaro Herrera <alvherre@commandprompt.com> writes: > >>> I think there is a reasonable case for saying that a manual vacuum could > >>> hint pgstat to create the entry instead. > >> > >> The problem with that is that a simple "VACUUM;" would force pgstat to > >> populate its entire hashtable. > > > Maybe a good compromise would be only populating info for tables that > > had dead tuples... that would eliminate any static tables, and most DBAs > > should know that those tables are static. > > Hm, that definitely seems like an idea. Does the current pgstat message > from vacuum tell how many rows it deleted? Hum, no. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Thu, Aug 24, 2006 at 01:48:50PM -0400, Alvaro Herrera wrote: > Tom Lane wrote: > > "Jim C. Nasby" <jnasby@pervasive.com> writes: > > > On Thu, Aug 24, 2006 at 09:58:10AM -0400, Tom Lane wrote: > > >> Alvaro Herrera <alvherre@commandprompt.com> writes: > > >>> I think there is a reasonable case for saying that a manual vacuum could > > >>> hint pgstat to create the entry instead. > > >> > > >> The problem with that is that a simple "VACUUM;" would force pgstat to > > >> populate its entire hashtable. > > > > > Maybe a good compromise would be only populating info for tables that > > > had dead tuples... that would eliminate any static tables, and most DBAs > > > should know that those tables are static. > > > > Hm, that definitely seems like an idea. Does the current pgstat message > > from vacuum tell how many rows it deleted? > > Hum, no. ISTM that wouldn't be bad info to track either... how many dead tuples the last [auto]vacuum encountered. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Summarizing this thread, I see support for the following: - autovacuum set to on by default in 8.2. - stats_row_level also defaults to on. (Perhaps stats_block_level should also default to on so it's not inconsistent, seeing that everything else in on, too.) - Delayed vacuum and delayed autovacuum will stay disabled. - Scale factor set to 0.08 (vacuum) and 0.04 (analyze) (?) (formerly 0.4 and 0.2) - Leave base thresholds alone (pending further analysis that might remove them altogether?) -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut wrote: > Summarizing this thread, I see support for the following: > > - autovacuum set to on by default in 8.2. Yes. > - stats_row_level also defaults to on. Yes. > (Perhaps stats_block_level should also default to on so it's not inconsistent, > seeing that everything else in on, too.) I haven't followed this closely, but are all the other stats commands other than block_level now on by default? In general I would vote to keep it off if not needed just for performance reasons, though I haven't measured the effect of block_level turned on. Anyone measured this? > - Delayed vacuum and delayed autovacuum will stay disabled. Unfortunately. > - Scale factor set to 0.08 (vacuum) and 0.04 (analyze) (?) (formerly 0.4 and > 0.2) That seems a big jump. BTW, I know .08 and .04 were suggested, but I didn't see confirmation that it was a good idea. I know my initial values were grossly over-conservative, but I am concerned about bogging down the server with lots of vacuums, especially since we don't have the delay settings on by default, nor do we have a maintenance windows yet. > - Leave base thresholds alone (pending further analysis that might remove them > altogether?) While there is talk of removing this all together, I think it was also agreed that as long as these values are there, they should be reduced. I think the defaults in 8.1 are 1000/500, I think 200/100 was suggested.
Am Freitag, 25. August 2006 17:32 schrieb Matthew T. O'Connor: > While there is talk of removing this all together, I think it was also > agreed that as long as these values are there, they should be reduced. > I think the defaults in 8.1 are 1000/500, I think 200/100 was suggested. I'm thinking leaving them higher would offset the jump in the scale factor a bit. But if the idea is to get rid of the base threshold, then that's not really a forward-looking strategy, I suppose. I don't mind if we go down with the scale factor a little less and move the threshold down more, but at least tying the scale factor to the fill factor doesn't make all those numbers too random. -- Peter Eisentraut http://developer.postgresql.org/~petere/
"Matthew T. O'Connor" <matthew@zeut.net> writes:
> Peter Eisentraut wrote:
>> - Leave base thresholds alone (pending further analysis that might remove them
>> altogether?)
> While there is talk of removing this all together, I think it was also
> agreed that as long as these values are there, they should be reduced.
> I think the defaults in 8.1 are 1000/500, I think 200/100 was suggested.
ISTM that if we don't want to remove the thresholds immediately,
we should make them default to zero for a release or two and see how
well it works.
At the moment I can't find the thread that discussed removing them,
but IIRC there were some good arguments why the thresholds should always
be zero.
regards, tom lane
Tom Lane wrote: > "Matthew T. O'Connor" <matthew@zeut.net> writes: > >> While there is talk of removing this all together, I think it was also >> agreed that as long as these values are there, they should be reduced. >> I think the defaults in 8.1 are 1000/500, I think 200/100 was suggested. >> > > ISTM that if we don't want to remove the thresholds immediately, > we should make them default to zero for a release or two and see how > well it works. > > At the moment I can't find the thread that discussed removing them, > but IIRC there were some good arguments why the thresholds should always > be zero. I don't have any significant objection to removing them, it just seemed to me that we are late in release cycle and that might be more than we want to do at the moment. If others think it's OK, then it's OK with me.
Tom Lane wrote: > "Matthew T. O'Connor" <matthew@zeut.net> writes: > > Peter Eisentraut wrote: > >> - Leave base thresholds alone (pending further analysis that might remove them > >> altogether?) > > > While there is talk of removing this all together, I think it was also > > agreed that as long as these values are there, they should be reduced. > > I think the defaults in 8.1 are 1000/500, I think 200/100 was suggested. > > ISTM that if we don't want to remove the thresholds immediately, > we should make them default to zero for a release or two and see how > well it works. > > At the moment I can't find the thread that discussed removing them, > but IIRC there were some good arguments why the thresholds should always > be zero. I can't find it either, but I think the bug reported here is related: http://archives.postgresql.org/pgsql-general/2006-06/thrd2.php#00951 On the other hand, I don't think we completely resolved this, so I proposed this be added to the "Open Items" list. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Fri, Aug 25, 2006 at 12:16:33PM -0400, Alvaro Herrera wrote: > Tom Lane wrote: > > "Matthew T. O'Connor" <matthew@zeut.net> writes: > > > Peter Eisentraut wrote: > > >> - Leave base thresholds alone (pending further analysis that might remove them > > >> altogether?) > > > > > While there is talk of removing this all together, I think it was also > > > agreed that as long as these values are there, they should be reduced. > > > I think the defaults in 8.1 are 1000/500, I think 200/100 was suggested. > > > > ISTM that if we don't want to remove the thresholds immediately, > > we should make them default to zero for a release or two and see how > > well it works. > > > > At the moment I can't find the thread that discussed removing them, > > but IIRC there were some good arguments why the thresholds should always > > be zero. > > I can't find it either, but I think the bug reported here is related: > > http://archives.postgresql.org/pgsql-general/2006-06/thrd2.php#00951 > > On the other hand, I don't think we completely resolved this, so I > proposed this be added to the "Open Items" list. Yeah, I think there's reasons we can't go to zero. 200/100 or even 20/10 would probably be a good compromise. I agree that droping to 0.08 might be a bit much, but it would be good if we started recommending that value to folks to see how well it works. I thought we had agreed it would be a good idea to turn autovac_delay on? I know there was question as to what a good value would be, but 5-10ms seems pretty reasonable. I think it'd also be good to up the cost threshold and the dirty_page cost, though I don't have much data to back that up (I did testing at one customer on a drive array and found 300 and 30 were good values). If we've got command stats turned on by default now, I'll have a hard time buying performance as any reason to turn the others off. I think we should turn them all on and let those who are trying to eek the last few percent of performance out of a system turn them off. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote: > I thought we had agreed it would be a good idea to turn autovac_delay > on? We had not, because there was no experience available about where to put the default numbers. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On 8/25/06, Peter Eisentraut <peter_e@gmx.net> wrote: > Summarizing this thread, I see support for the following: > - autovacuum set to on by default in 8.2. > - stats_row_level also defaults to on. > - Delayed vacuum and delayed autovacuum will stay disabled. > - Scale factor set to 0.08 (vacuum) and 0.04 (analyze) (?) (formerly 0.4 and > 0.2) IMHO, we shoud also change superuser_reserved_connections from 2 to 3 because one of the connections will be used by autovacuum. -- Guillaume
Centuries ago, Nostradamus foresaw when peter_e@gmx.net (Peter Eisentraut) would write: > Jim C. Nasby wrote: >> I thought we had agreed it would be a good idea to turn autovac_delay >> on? > > We had not, because there was no experience available about where to put > the default numbers. I would also not because for Very Large Tables, the delay may make vacuums run really inordinately long. And that becomes an "evil big long-running transaction" to worsen things. -- (format nil "~S@~S" "cbbrowne" "gmail.com") http://cbbrowne.com/info/internet.html Signs of a Klingon Programmer - 2. "Specifications are for the weak and timid!"
Guillaume Smet wrote: > IMHO, we shoud also change superuser_reserved_connections from 2 to 3 > because one of the connections will be used by autovacuum. Yes, good point. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Matthew T. O'Connor wrote: > That seems a big jump. BTW, I know .08 and .04 were suggested, but I > didn't see confirmation that it was a good idea. I know my initial > values were grossly over-conservative, but I am concerned about > bogging down the server with lots of vacuums, especially since we > don't have the delay settings on by default, nor do we have a > maintenance windows yet. OK, it seems that while everyone wants autovacuum be more aggressive by default, no one has any good data to support one setting or another. I so I suggest that we just cut scale factor and base threshold in half right now (so it'd be 0.2, 0.1, 500, 250) and see about a better-researched setting for the next release. -- Peter Eisentraut http://developer.postgresql.org/~petere/
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> If we've got command stats turned on by default now, I'll have a hard
> time buying performance as any reason to turn the others off.
That's a mistaken argument, because the reason stats_command_string
is now on is that it was reimplemented in a way that has basically
nothing to do with the stats subsystem ...
regards, tom lane
Peter, > OK, it seems that while everyone wants autovacuum be more aggressive by > default, no one has any good data to support one setting or another. I > so I suggest that we just cut scale factor and base threshold in half > right now (so it'd be 0.2, 0.1, 500, 250) and see about a > better-researched setting for the next release. I'd recommend actually 0.4 and 0.2 and 200 and 100. I think that 20% and 10% are too aggresive. 0.4 and 0.2 are what I've been using in production on many machines. On the other hand, I think that the thresholds are much too high -- that means that many small tables may never get vacuumed at all, even after 100% row replacement. I'll admit, however, that I don't have test data to support this. Unfortunately we never got to good Autovac tests on the STP before it went down. -- Josh Berkus PostgreSQL @ Sun San Francisco
Folks, > all, even after 100% row replacement. Er, "even after 1000% row replacement." -- Josh Berkus PostgreSQL @ Sun San Francisco
On Tue, Aug 29, 2006 at 09:23:53PM -0700, Josh Berkus wrote: > Peter, > > > OK, it seems that while everyone wants autovacuum be more aggressive by > > default, no one has any good data to support one setting or another. I > > so I suggest that we just cut scale factor and base threshold in half > > right now (so it'd be 0.2, 0.1, 500, 250) and see about a > > better-researched setting for the next release. > > I'd recommend actually 0.4 and 0.2 and 200 and 100. I think that 20% and 10% > are too aggresive. 0.4 and 0.2 are what I've been using in production on > many machines. On the other hand, I think that the thresholds are much too > high -- that means that many small tables may never get vacuumed at all, even > after 100% row replacement. Do you think .2 and .1 (or even .08 and .04, as suggested by the default page fill percentage) are too aggressive *on small systems*? IMO, these defaults are meant more for less experienced folks, which are much more likely to be running a smaller database than a large one. FWIW, I've been using .2 and .1 (as well as cutting the thresholds down; typically to between 200 and 400 and 100 and 200) without issue, though I did tweak the delay costs at one customer. > I'll admit, however, that I don't have test data to support this. > Unfortunately we never got to good Autovac tests on the STP before it went > down. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Peter Eisentraut wrote: > Guillaume Smet wrote: > > IMHO, we shoud also change superuser_reserved_connections from 2 to 3 > > because one of the connections will be used by autovacuum. > > Yes, good point. Done, because most people will turn autovacuum on, even if it isn't on by default. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > > Done, because most people will turn autovacuum on, even if it isn't on > by default. > I wonder how many distros will turn on autovacuum as well, making it the de-facto standard anyway. Regards,
Andreas Pflug wrote: > Bruce Momjian wrote: > > > > Done, because most people will turn autovacuum on, even if it isn't on > > by default. > > > I wonder how many distros will turn on autovacuum as well, making it the > de-facto standard anyway. Win32 already does. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +