On Thu, Feb 24, 2011 at 1:28 PM, Dolafi, Tom <[email protected]>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 >
