On Thu, Feb 23, 2012 at 10:01 AM, Steve Crawford < scrawf...@pinpointresearch.com> wrote:
> ** > The documentation has information like "This parameter can only be set in > the postgresql.conf file or on the server command line." that will tell > you in advance which settings will fail when you attempt to set them > through SQL statements. > > Ah. I missed that. Sorry for asking stupid questions. > But autovacuum is pretty smart about not vacuuming tables until reasonably > necessary. And beware that autovacuum is also controlling when to analyze a > table. Mass inserts are probably changing the characteristics of your table > such that it needs to be analyzed to allow the planner to properly optimize > your queries. > > Okay, that makes more sense to me; because the stats would be changing quickly and so while vacuuming may not be necessary, analyzing would be. At the same time, I can't afford to analyze if it's causing my inserts to take over 50 ms. Something else I should add: if my selects are slow, that's annoying; but if my inserts are slow, that could be disastrous. Does analyze increase the efficiency of inserts or just selects? (I assumed the latter.) Obviously, I will need to analyze sometimes, but perhaps not nearly as often as postgres would predict under the circumstances. > Have you identified that vacuum is actually causing a problem? If not, I'd > leave it alone. The system tables have a lot of information on table > vacuuming and analyzing: > > Not indubitably, but circumstantially, I did notice that significantly fewer of my commits were taking over 50 ms after I set autovacuum_enabled=off on many of my tables. Unfortunately, it was not an isolated experiment, so I can't really be sure. At the same time, I'm hesitant to turn it back on until I'm sure it either didn't make a difference or I've got a better plan for how to deal with this. > select > relname, > last_vacuum, > last_autovacuum, > last_analyze, > last_autoanalyze, > vacuum_count, > autovacuum_count, > analyze_count, > autoanalyze_count > from > pg_stat_user_tables; > > Apparently the last four columns don't exist in my database. As for the first four, that is somewhat illuminating. It looks like the last_autovacuum that occurred on any of my tables was late Monday evening (almost two days before I set autovacuum_enabled=off). The last_autoanalyze on one of the tables where I set autovacuum_enabled=off was yesterday at 10:30, several hours before I disabled auto-vacuum. (I've had others since then on tables where I didn't disable auto-vacuum.) It looks like disabling auto-vacuum also disabled auto-analyze (did it?) but it also looks like that might not have been the continuous problem I thought it was. So if it's not auto-vacuuming that's making my inserts so slow, what is it? I'm batching my inserts (that didn't seem to help at all actually, but maybe cause I had already turned off synchronous_commit anyway). I've gotten rid of a bunch of indices (especially those with low cardinality–that I did around the same time as disabling auto-vacuum, so that could account for the coincidental speed up). I'm not sure what else I could be doing wrong. It's definitely better than it was a few days ago, but I still see "LOG: duration: 77.315 ms statement: COMMIT" every minute or two. Thank you, -Alessandro