Re: [HACKERS] Autovacuum on by default?
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 [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Autovacuum on by default?
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, ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Autovacuum on by default?
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 [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Autovacuum on by default?
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Autovacuum on by default?
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Autovacuum on by default?
Folks, all, even after 100% row replacement. Er, even after 1000% row replacement. -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [Open Item] Re: [HACKERS] Autovacuum on by default?
Jim C. Nasby [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [Open Item] Re: [HACKERS] Autovacuum on by default?
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/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Autovacuum on by default?
On 8/25/06, Peter Eisentraut [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Autovacuum on by default?
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/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Autovacuum on by default?
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/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Autovacuum on by default?
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/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Autovacuum on by default?
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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Autovacuum on by default?
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/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Autovacuum on by default?
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Autovacuum on by default?
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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[Open Item] Re: [HACKERS] Autovacuum on by default?
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [Open Item] Re: [HACKERS] Autovacuum on by default?
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Autovacuum on by default?
Jim C. Nasby wrote: On Tue, Aug 22, 2006 at 11:08:49AM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Autovacuum on by default?
Larry Rosenman wrote: Jim C. Nasby wrote: On Tue, Aug 22, 2006 at 11:08:49AM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] 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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Autovacuum on by default?
Alvaro Herrera [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Autovacuum on by default?
On Thu, Aug 24, 2006 at 09:58:10AM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Autovacuum on by default?
Jim C. Nasby [EMAIL PROTECTED] writes: On Thu, Aug 24, 2006 at 09:58:10AM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Autovacuum on by default?
Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: On Thu, Aug 24, 2006 at 09:58:10AM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] 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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Autovacuum on by default?
On Thu, Aug 24, 2006 at 01:48:50PM -0400, Alvaro Herrera wrote: Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: On Thu, Aug 24, 2006 at 09:58:10AM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Autovacuum on by default?
On Tue, Aug 22, 2006 at 11:08:49AM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Autovacuum on by default?
On Wed, Aug 23, 2006 at 01:45:43PM +0900, ITAGAKI Takahiro wrote: Jim C. Nasby [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Autovacuum on by default?
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 [EMAIL PROTECTED] 512.569.9461 (cell) http://jim.nasby.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Autovacuum on by default?
Jim C. Nasby [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Autovacuum on by default?
Jim C. Nasby [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Autovacuum on by default?
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/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Autovacuum on by default?
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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Autovacuum on by default?
Peter Eisentraut [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 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? 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Autovacuum on by default?
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Autovacuum on by default?
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. -- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Autovacuum on by default?
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 [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Autovacuum on by default?
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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Autovacuum on by default?
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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Autovacuum on by default?
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 [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Autovacuum on by default?
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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Autovacuum on by default?
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Autovacuum on by default?
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 [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Autovacuum on by default?
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Autovacuum on by default?
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? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Autovacuum on by default?
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Autovacuum on by default?
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? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Autovacuum on by 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Autovacuum on by default?
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 [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Autovacuum on by default?
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Autovacuum on by default?
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Autovacuum on by default?
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 [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Autovacuum on by default?
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Autovacuum on by default?
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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Autovacuum on by default?
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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Autovacuum on by default?
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Autovacuum on by default?
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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Autovacuum on by default?
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Autovacuum on by default?
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Autovacuum on by default?
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Autovacuum on by default?
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Autovacuum on by default?
On Thu, Aug 17, 2006 at 03:17:07PM -0400, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Autovacuum on by default?
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Autovacuum on by default?
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Autovacuum on by default?
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Autovacuum on by default?
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Autovacuum on by default?
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Autovacuum on by default?
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/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
