Thread: Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER
Hello everybody, we're using postgresql 8.3 for some logging framework. There are several tables for each day (which are inherited from a common base), which - are filled during the day, - after midnight the indizes are changed to FILLFACTOR=100, and - the tables get CLUSTERed by the most important index. - Some time much later the tables that aren't needed anymore are DROPped. So far, so fine. A few days before we found the machine much slower, because of the autovacuum processes that were started automatically ["autovacuum: VACUUM ... (to prevent wraparound)"]. After several days we killed that, and, as a quick workaround, changed "autovacuum_freeze_max_age" to 1G and restarted the server, which worked as before (and didn't ran the autovacuum processes). As a next idea we changed the cluster/reindex script to set "vacuum_freeze_min_age=0" before the CLUSTER call, hoping that this would solve our transaction ID wraparound problem. We don't know yet whether that's enough (is it?), but we're seeing another problem - the btree indizes aren't cleaned up. By this I mean that for two compareable tables (with about the same amount of data, one done before the "vacuum_freeze_min_age=0" and one with that), have about the same size for the GIST/GIN-, but about 30-50% difference for the btree indizes (after the ALTER INDEX/CLUSTER). So, as summary: "vacuum_freeze_min_age=0" seems to interfere with btree indizes with FILLFACTOR=100 in some way, so that CLUSTER doesn't return space to the filesystem. Now I'd like to ask whether that's a known problem, and maybe even solved for 8.4 (which we'd like to use because of the "FOR UPDATE" across inherited tables). Regards, Phil
On Mon, May 11, 2009 at 12:20 AM, Philipp Marek <philipp.marek@emerion.com> wrote: > Hello everybody, > > we're using postgresql 8.3 for some logging framework. > > There are several tables for each day (which are inherited from a common > base), which > - are filled during the day, > - after midnight the indizes are changed to FILLFACTOR=100, and > - the tables get CLUSTERed by the most important index. > - Some time much later the tables that aren't needed anymore are DROPped. > > So far, so fine. > > > A few days before we found the machine much slower, because of the autovacuum > processes that were started automatically ["autovacuum: VACUUM ... (to prevent > wraparound)"]. Try increasing autovacuum_vacuum_cost_delay to 20 or 30 milliseconds and see if that helps during autovacuum. > After several days we killed that, and, as a quick workaround, changed > "autovacuum_freeze_max_age" to 1G and restarted the server, which worked as > before (and didn't ran the autovacuum processes). It will still have to eventually run, just less often. > As a next idea we changed the cluster/reindex script to set > "vacuum_freeze_min_age=0" before the CLUSTER call, hoping that this would > solve our transaction ID wraparound problem. No, only vacuuming will solve it. It has to happen eventually. If you put it off too far, and the database can't get the vacuum to reset the txids to the magical frozentxid, then the db will shut down and demand that you vacuum it in single user mode. Which will definitely make it run slower than if autovacuum is doing the job. > We don't know yet whether that's enough (is it?), but we're seeing another > problem - the btree indizes aren't cleaned up. > By this I mean that for two compareable tables (with about the same amount of > data, one done before the "vacuum_freeze_min_age=0" and one with that), have > about the same size for the GIST/GIN-, but about 30-50% difference for the > btree indizes (after the ALTER INDEX/CLUSTER). Not sure about all this part. Could it just be index bloat due to updates and / or delete insert cycles? > So, as summary: "vacuum_freeze_min_age=0" seems to interfere with btree > indizes with FILLFACTOR=100 in some way, so that CLUSTER doesn't return space > to the filesystem. I'm not sure that's the issue here. Cluster doesn't return index space. reindex returns index space. vacuum makes dead index space available for reuse.
Philipp Marek wrote: > A few days before we found the machine much slower, because of the autovacuum > processes that were started automatically ["autovacuum: VACUUM ... (to prevent > wraparound)"]. > > After several days we killed that, and, as a quick workaround, changed > "autovacuum_freeze_max_age" to 1G and restarted the server, which worked as > before (and didn't ran the autovacuum processes). Several days? How large is your vacuum_cost_delay and autovacuum_vacuum_cost_delay parameters? > As a next idea we changed the cluster/reindex script to set > "vacuum_freeze_min_age=0" before the CLUSTER call, hoping that this would > solve our transaction ID wraparound problem. REINDEX? What are you doing REINDEX for? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Hello Alvaro, On Montag, 11. Mai 2009, Alvaro Herrera wrote: > Philipp Marek wrote: > > A few days before we found the machine much slower, because of the > > autovacuum processes that were started automatically ["autovacuum: VACUUM > > ... (to prevent wraparound)"]. > > > > After several days we killed that, and, as a quick workaround, changed > > "autovacuum_freeze_max_age" to 1G and restarted the server, which worked > > as before (and didn't ran the autovacuum processes). > > Several days? How large is your vacuum_cost_delay and > autovacuum_vacuum_cost_delay parameters? They're set to 0 and 20ms resp. > > As a next idea we changed the cluster/reindex script to set > > "vacuum_freeze_min_age=0" before the CLUSTER call, hoping that this would > > solve our transaction ID wraparound problem. > > REINDEX? What are you doing REINDEX for? Some tables get CLUSTERed; I put an option in the script to just do a REINDEX, if wanted. That's just the name of the script, it normally doesn't run REINDEX. Regards, Phil
Philipp Marek wrote: > Hello everybody, > > we're using postgresql 8.3 for some logging framework. > > There are several tables for each day (which are inherited from a common > base), which > - are filled during the day, > - after midnight the indizes are changed to FILLFACTOR=100, and > - the tables get CLUSTERed by the most important index. > - Some time much later the tables that aren't needed anymore are DROPped. > > So far, so fine. Do say, do you have any long-running transactions, or "idle" transactions? Maybe someone opened a terminal somewhere and left it open for days? Have a look at pg_stat_activity. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Mittwoch, 13. Mai 2009, Alvaro Herrera wrote: > > we're using postgresql 8.3 for some logging framework. > > > > There are several tables for each day (which are inherited from a common > > base), which > > - are filled during the day, > > - after midnight the indizes are changed to FILLFACTOR=100, and > > - the tables get CLUSTERed by the most important index. > > - Some time much later the tables that aren't needed anymore are DROPped. > > > > So far, so fine. > > Do say, do you have any long-running transactions, or "idle" > transactions? Maybe someone opened a terminal somewhere and left it > open for days? Have a look at pg_stat_activity. Yes, I have two terminal windows for different users/schemas in the same DB open - but they're set to auto-commit, and have no tables open or locked. Please, let me repeat myself: > So, as summary: "vacuum_freeze_min_age=0" seems to interfere with btree > indizes with FILLFACTOR=100 in some way, so that CLUSTER doesn't return > space to the filesystem. Might the open connections make a difference? Regards, Phil
Philipp Marek wrote: > On Mittwoch, 13. Mai 2009, Alvaro Herrera wrote: > > > we're using postgresql 8.3 for some logging framework. > > > > > > There are several tables for each day (which are inherited from a common > > > base), which > > > - are filled during the day, > > > - after midnight the indizes are changed to FILLFACTOR=100, and > > > - the tables get CLUSTERed by the most important index. > > > - Some time much later the tables that aren't needed anymore are DROPped. > > > > > > So far, so fine. > > > > Do say, do you have any long-running transactions, or "idle" > > transactions? Maybe someone opened a terminal somewhere and left it > > open for days? Have a look at pg_stat_activity. > Yes, I have two terminal windows for different users/schemas in the same DB > open - but they're set to auto-commit, and have no tables open or locked. Please close them and try again. > Please, let me repeat myself: > > So, as summary: "vacuum_freeze_min_age=0" seems to interfere with btree > > indizes with FILLFACTOR=100 in some way, so that CLUSTER doesn't return > > space to the filesystem. > > Might the open connections make a difference? I see no reason at all for CLUSTER not to "return space to the filesystem", unless it is copying all the tuples over including dead ones (which can only be explained if you have open transactions). I also see no reason for vacuum_freeze_min_age=0 to interfere with btree cleaning. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Donnerstag, 14. Mai 2009, Alvaro Herrera wrote:
> Philipp Marek wrote:
> > On Mittwoch, 13. Mai 2009, Alvaro Herrera wrote:
> > > Do say, do you have any long-running transactions, or "idle"
> > > transactions? Maybe someone opened a terminal somewhere and left it
> > > open for days? Have a look at pg_stat_activity.
> >
> > Yes, I have two terminal windows for different users/schemas in the same
> > DB open - but they're set to auto-commit, and have no tables open or
> > locked.
>
> Please close them and try again.
I'll try this tonight.
> > Please, let me repeat myself:
> > > So, as summary: "vacuum_freeze_min_age=0" seems to interfere with btree
> > > indizes with FILLFACTOR=100 in some way, so that CLUSTER doesn't return
> > > space to the filesystem.
> >
> > Might the open connections make a difference?
>
> I see no reason at all for CLUSTER not to "return space to the
> filesystem", unless it is copying all the tuples over including dead
> ones (which can only be explained if you have open transactions).
>
> I also see no reason for vacuum_freeze_min_age=0 to interfere with btree
> cleaning.
Well, I now looked into pg_stat_user_tables and found that since we're trying
to use vacuum_freeze_min_age CLUSTER doesn't seem to work anymore:
select relname, n_live_tup, n_dead_tup
from pg_stat_user_tables
where relname like 'log_lines__2009%' order by relname;
relname | n_live_tup | n_dead_tup
---------------------+------------+------------
log_lines__20090418 | 12469112 | 24
log_lines__20090419 | 12782920 | 12
log_lines__20090420 | 13548366 | 27
log_lines__20090421 | 14212689 | 12
log_lines__20090422 | 13266117 | 30
log_lines__20090423 | 16463312 | 549
log_lines__20090424 | 15435935 | 449
log_lines__20090425 | 11521196 | 457
log_lines__20090426 | 11015089 | 184
log_lines__20090427 | 11886995 | 106
log_lines__20090428 | 13261038 | 255
log_lines__20090429 | 12731062 | 351
log_lines__20090430 | 12897104 | 355
log_lines__20090501 | 12560355 | 378740
log_lines__20090502 | 12334676 | 13
log_lines__20090503 | 11931585 | 352089
log_lines__20090504 | 13013210 | 67727
log_lines__20090505 | 13617898 | 487454
log_lines__20090506 | 14875983 | 194299
log_lines__20090507 | 13435968 | 222537
log_lines__20090508 | 13970324 | 459262
log_lines__20090509 | 12321769 | 448003
log_lines__20090510 | 12358591 | 390394
log_lines__20090511 | 12109246 | 457838
log_lines__20090512 | 11979171 | 438211
log_lines__20090513 | 12747908 | 423004
log_lines__20090514 | 7685059 | 281043
Let's see what tomorrow brings ;-)
Regards,
Phil
On Donnerstag, 14. Mai 2009, Philipp Marek wrote: > On Donnerstag, 14. Mai 2009, Alvaro Herrera wrote: > > Philipp Marek wrote: > > > On Mittwoch, 13. Mai 2009, Alvaro Herrera wrote: > > > > Do say, do you have any long-running transactions, or "idle" > > > > transactions? Maybe someone opened a terminal somewhere and left it > > > > open for days? Have a look at pg_stat_activity. > > > > > > Yes, I have two terminal windows for different users/schemas in the > > > same DB open - but they're set to auto-commit, and have no tables open > > > or locked. > > > > Please close them and try again. > > I'll try this tonight. Well, that doesn't seem to help either. * I had vacuum_freeze_min_age=0 set for the session doing CLUSTER. * No session were open during the CLUSTER. Result: * Indizes are not compacted. Compared to an earlier night with a similar number of records two btree- indizes went from 300M to ~400M (roughly). Regards, Phil