Обсуждение: xid wraparound
When the XID wraps, at the moment it does so, unless you set vacuum_freeze_min_age to 0 and a vacuum has just been performed, is there not a chance that there will be some data loss? If it changes value from (2^32 -1) to (0), it’s value is going to be less than SOME rows – the ones which have not been “frozen” and whose XID was presumably greater than (relfrozenid) at the time the wrap occurred - and will they not disappear until they too are frozen?
Mark Rostron <mrostron@ql2.com> writes:
> When the XID wraps, at the moment it does so, unless you set
> vacuum_freeze_min_age to 0 and a vacuum has just been performed, is
> there not a chance that there will be some data loss?
No. XID comparisons are modulo 2^31.
regards, tom lane
> >> When the XID wraps, at the moment it does so, unless you set >> vacuum_freeze_min_age to 0 and a vacuum has just been performed, is >> there not a chance that there will be some data loss? > >No. XID comparisons are modulo 2^31. > Thanks - I'm still trying to wrap my mind around this (sorry). So, for the sake of this description: - the XID space (size 2^32) is split into two sub-spaces, each of size 2^31 - each XID is quantified in terms of "space" (0,1) plus "offset" (modulo 2^31) : x(s,o) - each db row is stamped with an "age" XID : r(s,o) - each db query start is an XID : q(s,o) Therefore, a query can only see rows where: (q.o > r.o) && (q.s == r.s) || (q.o <= r.o) && (q.s != r.s) Something like that?
Mark Rostron <mrostron@ql2.com> writes:
>> No. XID comparisons are modulo 2^31.
> Thanks - I'm still trying to wrap my mind around this (sorry).
> So, for the sake of this description:
> - the XID space (size 2^32) is split into two sub-spaces, each of size 2^31
No, it is not. The XID space is continuous and circular. For any given
XID, there are 2^31-1 possible XIDs that are "before" it and 2^31-1 that
are "after" it (plus the special FrozenXID value, which is always before
everything else). There's no absolute comparisons possible, only
relative ones. Everything works without wraparound hiccups, because the
XID space has no endpoints. The price is that consistency is lost if
there are ever XIDs in the system that are more than 2^31 transactions
apart. We avoid that by replacing old XIDs with FrozenXID before they
get to be more than 2^31 transactions old.
> Therefore, a query can only see rows where:
> (q.o > r.o) && (q.s == r.s) || (q.o <= r.o) && (q.s != r.s)
Actually, to compare two XIDs we just do a signed subtraction (ignoring
overflow) and see if the result is positive or negative.
regards, tom lane
> No, it is not. The XID space is continuous and circular. For any given XID, there are 2^31-1 possible XIDs that are "before"it and 2^31-1 that are "after" it (plus the special > FrozenXID value, which is always before everything else). There's no absolute comparisons possible, only relative ones. Everything works without wraparound hiccups, because > the XID space has no endpoints. The price is that consistency is lost if there are ever XIDs in the system that are morethan 2^31 transactions apart. We avoid that by replacing old > XIDs with FrozenXID before they get to be more than 2^31 transactions old. ..... > Actually, to compare two XIDs we just do a signed subtraction (ignoring > overflow) and see if the result is positive or negative. ...... Ok Thanks - If we could please continue this. Server version is 8.3.7 What I am trying to do is find out if/how we can speed up the autovacuum workers that are running on some really large tables and have been doing so for days. One of our large tables is currently being autovacuum'd and the autovac workers (to prevent wraparound) has been running for 7 days now. Linux (ps -alf) indicates it has only run up about 11m cpu time over 7 days. And I never see them active. So I don't know what the auto-vac workers are actually doing (except making my users nervous). Initially, we thought that a high value of pg_class.relfrozenid indicated that we were in danger of wraparound, but now, I realize that the age(relfrozenid) indicates that this is unlikely at this time (query below). # 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? 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? Is there anything else I could be doing on-the-fly ? Mr
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@2ndQuadrant.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