pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedtransaction wraparound
2+ messages / 2 participants
[nested] [flat]
* transaction wraparound
@ 2011-02-24 18:28 Dolafi, Tom <dolafit@janelia.hhmi.org>
2011-02-24 20:02 ` Re: transaction wraparound Scott Mead <scottm@openscg.com>
0 siblings, 1 reply; 2+ messages in thread
From: Dolafi, Tom @ 2011-02-24 18:28 UTC (permalink / raw)
To: pgsql-admin
Hi,
I am getting database shutdown issues as transaction wraparound limit hits 999999.
Here is the story...
We recently inherited a system in which a postgresql 8.1.18 server hosts 700+ databases and growing. Essentially it is horizontal partitioning per user, but on a single physical sever. Each database is on avg 250M. Vacuum Full Analyze takes 30 sec per database. The web interface performs 100,000’s transactions per request (99% read). We have up to 200 concurrent connections. We seem to reach the transaction id limits faster than we can vacuum.
Any suggestions on how to deal with this issue?? (without re-architecting if possible)
Autovacuum parameters...
vacuum_cost_delay = 10 # 0-1000 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
vacuum_cost_limit = 200 # 0-10000 credits
autovacuum = on # enable autovacuum subprocess?
autovacuum_naptime = 8642 # time between autovacuum runs, in secs
#autovacuum_vacuum_threshold = 1000 # min # of tuple updates before
# vacuum
#autovacuum_analyze_threshold = 500 # min # of tuple updates before
autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before
# vacuum
autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before
autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
# autovac, -1 means use
# vacuum_cost_delay
autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovac, -1 means use
# vacuum_cost_limit
Thank you,
Tom
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: transaction wraparound
2011-02-24 18:28 transaction wraparound Dolafi, Tom <dolafit@janelia.hhmi.org>
@ 2011-02-24 20:02 ` Scott Mead <scottm@openscg.com>
0 siblings, 0 replies; 2+ messages in thread
From: Scott Mead @ 2011-02-24 20:02 UTC (permalink / raw)
To: Dolafi, Tom <dolafit@janelia.hhmi.org>; +Cc: pgsql-admin
On Thu, Feb 24, 2011 at 1:28 PM, Dolafi, Tom <dolafit@janelia.hhmi.org>wrote:
> Hi,
>
> I am getting database shutdown issues as transaction wraparound limit hits
> 999999.
>
> Here is the story...
> We recently inherited a system in which a postgresql 8.1.18 server hosts
> 700+ databases and growing. Essentially it is horizontal partitioning per
> user, but on a single physical sever. Each database is on avg 250M. Vacuum
> Full Analyze takes 30 sec per database. The web interface performs
> 100,000’s transactions per request (99% read). We have up to 200 concurrent
> connections. We seem to reach the transaction id limits faster than we can
> vacuum.
>
If you're actually 99% read, then upgrading to postgres 8.3 or higher
(I *highly
recommend* you go to 9 though) will help. With the introduction of
virtualxid in 8.3, you won't hit wrap around just because of your read
transactions.
> Any suggestions on how to deal with this issue?? (without re-architecting
> if possible)
>
> Autovacuum parameters...
> vacuum_cost_delay = 10 # 0-1000 milliseconds
> #vacuum_cost_page_hit = 1 # 0-10000 credits
> #vacuum_cost_page_miss = 10 # 0-10000 credits
> #vacuum_cost_page_dirty = 20 # 0-10000 credits
> vacuum_cost_limit = 200 # 0-10000 credits
> autovacuum = on # enable autovacuum subprocess?
> autovacuum_naptime = 8642 # time between autovacuum runs, in
> secs
> #autovacuum_vacuum_threshold = 1000 # min # of tuple updates before
> # vacuum
> #autovacuum_analyze_threshold = 500 # min # of tuple updates before
> autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before
> # vacuum
> autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before
> autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
> # autovac, -1 means use
> # vacuum_cost_delay
> autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
> # autovac, -1 means use
> # vacuum_cost_limit
>
> Thank you,
> Tom
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2011-02-24 20:02 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-02-24 18:28 transaction wraparound Dolafi, Tom <dolafit@janelia.hhmi.org>
2011-02-24 20:02 ` Scott Mead <scottm@openscg.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox