Re: xid wraparound

Mark Rostron wrote:
> # select relname, age(relfrozenxid), relfrozenxid FROM pg_class WHERE relkind = 'r' and relname = 'error';
> relname | age | relfrozenxid
> ---------+-----------+--------------
> error | 286569433 | 3781443077
>
> autovacuum_freeze_max_age is the default of 200m,
> so I assume age(relfrozenid) triggered the autovac processing we are currently seeing?
>

Looks that way. The anti-wraparound work starts long before there is
any danger of wraparound; only 10% of the way as you've seen here. The
idea is that this gives you plenty of time to work through VACUUM even
on a very large table, which is the situation you're in now.

> Also, maintenance_work_mem is 256MB.
> And the table size is 132GB.
> Should we increase maintenance_work_memory?
> if we increase maintenance_work_memory and reload,
> will the autovacuum workers pick up the change on the fly?
> I assume that killing the auto-vacuum workers is not a good idea?
> If we do, would auto-vac restart, with the increased memory allocation?
>

Here's how you check that sort of thing:

postgres=# select name,context from pg_settings where
name='maintenance_work_mem';
name | context
----------------------+---------
maintenance_work_mem | user

This shows that maintenance_work_mem will pick up a change each time a
new user session starts, so no need for a full server restart. So long
as you do a regular kill, and not "kill -9", it shouldn't be dangerous
to kill the AV workers. You can expect them to turn around and start
right back up again though, doing the same job; make any server
parameter changes active before killing them. Also, some additional
logging you probably want to turn on here:

log_autovacuum_min_duration log_checkpoints

And take a look at all the data for this table in pg_stat_user_tables ,
which will show you a variety of vacuum and autovacuum influencing data.

If you have the RAM, increasing this parameter should help vacuum out.
But your current setting is big enough that it shouldn't be limiting
things too badly, and from the slow rate of progress you're seeing, it
sounds more like you're hitting some sort of disk bottleneck instead.
Either that, or your autovacuum cost parameters are really restricting
the activity of the workers to a minimum.

If you are just suffering from general system performance limits here,
you might follow some of the usual advice at
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server to speed
things up. You can do a change to checkpoint_segments on the fly too.
shared_buffers you'll have to do a full server restart for. Those are
the main three (along with maintenance_work_mem) that impact how fast
VACUUM work progresses.

--
Greg Smith, 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance" Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book