Thread: Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER

Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER

From
Philipp Marek
Date:
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



Re: Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER

From
Scott Marlowe
Date:
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.

Re: Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER

From
Alvaro Herrera
Date:
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.

Re: Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER

From
Philipp Marek
Date:
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



Re: Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER

From
Alvaro Herrera
Date:
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.

Re: Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER

From
Philipp Marek
Date:
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


Re: Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER

From
Alvaro Herrera
Date:
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

Re: Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER

From
Philipp Marek
Date:
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


Re: Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER

From
Philipp Marek
Date:
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