Search Postgresql Archives

Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux