Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id C9768133798E for ; Thu, 24 Feb 2011 14:38:37 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 25647-08 for ; Thu, 24 Feb 2011 18:38:30 +0000 (UTC) X-Greylist: delayed 00:09:46.493452 by SQLgrey-1.7.6 Received: from mail0.janelia.org (mail0.janelia.org [128.177.18.34]) by mail.postgresql.org (Postfix) with ESMTP id 66FBB1337BD3 for ; Thu, 24 Feb 2011 14:38:29 -0400 (AST) X-Reputation: None X-IronPort-AV: E=Sophos;i="4.62,219,1297054800"; d="scan'208,217";a="1474365" Received: from unknown (HELO CAS01.janelia.priv) ([10.42.5.20]) by mail0.janelia.org with ESMTP; 24 Feb 2011 13:28:41 -0500 Received: from MB02.janelia.priv ([fe80::b5ae:ee36:7e0d:f4cd]) by CAS01.janelia.priv ([::1]) with mapi id 14.01.0270.002; Thu, 24 Feb 2011 13:28:41 -0500 From: "Dolafi, Tom" To: "pgsql-admin@postgresql.org" Subject: transaction wraparound Thread-Topic: transaction wraparound Thread-Index: AcvUUKdUa7kQlmRlmkWmVLICPJvT2A== Date: Thu, 24 Feb 2011 18:28:39 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: user-agent: Microsoft-Entourage/13.6.0.100712 x-originating-ip: [10.40.3.10] Content-Type: multipart/alternative; boundary="_000_C98C0D86340Edolafitjaneliahhmiorg_" MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201102/213 X-Sequence-Number: 36852 --_000_C98C0D86340Edolafitjaneliahhmiorg_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable 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 70= 0+ databases and growing. Essentially it is horizontal partitioning per u= ser, 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,00= 0=92s transactions per request (99% read). We have up to 200 concurrent co= nnections. We seem to reach the transaction id limits faster than we can v= acuum. Any suggestions on how to deal with this issue?? (without re-architecting = if possible) Autovacuum parameters... vacuum_cost_delay =3D 10 # 0-1000 milliseconds #vacuum_cost_page_hit =3D 1 # 0-10000 credits #vacuum_cost_page_miss =3D 10 # 0-10000 credits #vacuum_cost_page_dirty =3D 20 # 0-10000 credits vacuum_cost_limit =3D 200 # 0-10000 credits autovacuum =3D on # enable autovacuum subprocess? autovacuum_naptime =3D 8642 # time between autovacuum runs, i= n secs #autovacuum_vacuum_threshold =3D 1000 # min # of tuple updates before # vacuum #autovacuum_analyze_threshold =3D 500 # min # of tuple updates before autovacuum_vacuum_scale_factor =3D 0.4 # fraction of rel size before # vacuum autovacuum_analyze_scale_factor =3D 0.2 # fraction of rel size before autovacuum_vacuum_cost_delay =3D -1 # default vacuum cost delay for # autovac, -1 means use # vacuum_cost_delay autovacuum_vacuum_cost_limit =3D -1 # default vacuum cost limit for # autovac, -1 means use # vacuum_cost_limit Thank you, Tom --_000_C98C0D86340Edolafitjaneliahhmiorg_ Content-Type: text/html; charset="Windows-1252" Content-ID: <92E46E2328B2114798402989DBF3749E@janelia.hhmi.org> Content-Transfer-Encoding: quoted-printable transaction wraparound 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 70= 0+ databases and growing.  Essentially it is horizontal partitioni= ng  per user, but on a single physical sever.  Each database is o= n avg 250M.  Vacuum Full Analyze takes 30 sec per database.  The web interface performs 100,000=92s transactions per request (99%= read).  We have up to 200 concurrent connections.  We seem to re= ach the transaction id limits faster than we can vacuum.

Any suggestions on how to deal with this issue??  (without re-architec= ting if possible)

Autovacuum parameters...
vacuum_cost_delay =3D 10         &n= bsp;        # 0-1000 milliseconds #vacuum_cost_page_hit =3D 1         = ;      # 0-10000 credits
#vacuum_cost_page_miss =3D 10        &nb= sp;    # 0-10000 credits
#vacuum_cost_page_dirty =3D 20        &n= bsp;   # 0-10000 credits
vacuum_cost_limit =3D 200         #= 0-10000 credits
autovacuum =3D on          &nb= sp;      # enable autovacuum subprocess?
autovacuum_naptime =3D 8642         = ;      # time between autovacuum runs, in sec= s
#autovacuum_vacuum_threshold =3D 1000     # min # of tu= ple updates before
            &nb= sp;            =             &nb= sp;  # vacuum
#autovacuum_analyze_threshold =3D 500     # min # of tu= ple updates before
autovacuum_vacuum_scale_factor =3D 0.4    # fraction of rel = size before
            &nb= sp;            =             &nb= sp;  # vacuum
autovacuum_analyze_scale_factor =3D 0.2   # fraction of rel size = before
autovacuum_vacuum_cost_delay =3D -1       # d= efault vacuum cost delay for
            &nb= sp;            =             &nb= sp;  # autovac, -1 means use
            &nb= sp;            =             &nb= sp;  # vacuum_cost_delay
autovacuum_vacuum_cost_limit =3D -1       # d= efault vacuum cost limit for
            &nb= sp;            =             &nb= sp;  # autovac, -1 means use
            &nb= sp;            =             &nb= sp;  # vacuum_cost_limit

Thank you,
Tom --_000_C98C0D86340Edolafitjaneliahhmiorg_--