Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER

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