X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 26C3A9FB2FC for ; Tue, 5 Sep 2006 00:17:19 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 67183-10 for ; Tue, 5 Sep 2006 00:17:09 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 49BF19FB2F8 for ; Tue, 5 Sep 2006 00:17:09 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k853H6U7018083; Mon, 4 Sep 2006 23:17:06 -0400 (EDT) To: Gregory Stark cc: Alvaro Herrera , Matteo Sgalaberni , pgsql-performance@postgresql.org Subject: Re: database bloat, non removovable rows, slow query etc... [RESOLVED] In-reply-to: <878xkz17fs.fsf@stark.xeocode.com> References: <20060901123915.GT2266@sgala.com> <4027.1157121810@sss.pgh.pa.us> <20060901172829.GX2266@sgala.com> <12756.1157132120@sss.pgh.pa.us> <20060902083725.GB2266@sgala.com> <2191.1157206883@sss.pgh.pa.us> <87ejur1drl.fsf@stark.xeocode.com> <20060904224716.GB14107@alvh.no-ip.org> <878xkz17fs.fsf@stark.xeocode.com> Comments: In-reply-to Gregory Stark message dated "04 Sep 2006 20:30:15 -0400" Date: Mon, 04 Sep 2006 23:17:06 -0400 Message-ID: <18082.1157426226@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.117 tagged_above=0 required=5 tests=AWL, SPF_HELO_PASS, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/27 X-Sequence-Number: 20713 Gregory Stark writes: > I must be misunderstanding Tom's comment then. > What I'm referring to is lazy_vacuum_rel() calls vacuum_set_xid_limits with > the relisshared flag of the relation. vacuum_set_xid_limits passes that to > GetOldestXmin as the allDbs parameter. GetOldestXmin ignores transactions not > connected to the same database unless allDbs is true. The problem is the indirect effect of other backends' xmin values, which are computed across all live backends. In the current structure, it's hard to see how to fix this except by making each backend compute and advertise both a global and database-local xmin. This seems a bit ugly. Also, someone asked recently whether we could avoid counting prepared xacts when figuring vacuum cutoffs, which seems a fair question --- but again, how to do that without doubling the number of advertised xmin values yet again? I'm starting to feel that we've reached the limits of this system of accounting for live XIDs, but I have no idea what the next step might look like... regards, tom lane