X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 0649ED1B4E1 for ; Fri, 14 Nov 2003 19:49:31 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 53927-01 for ; Fri, 14 Nov 2003 15:49:01 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 47F45D1B4E4 for ; Fri, 14 Nov 2003 15:49:00 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id hAEJn0Nu046147 for ; Fri, 14 Nov 2003 19:49:00 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id hAEJKfCQ041683 for pgsql-performance@postgresql.org; Fri, 14 Nov 2003 19:20:41 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: Help with count(*) Date: Fri, 14 Nov 2003 14:16:56 -0500 Organization: cbbrowne Computing Inc Lines: 35 Message-ID: References: <200311141251.38786.mallah@trade-india.com> <1068831807.2540.44.camel@fuji.krosing.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1002 (Gnus v5.10.2) XEmacs/21.4 (Reasonable Discussion, linux) Cancel-Lock: sha1:vI2O3yzZC8Gyhd5OMVqxPwSIIdc= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/118 X-Sequence-Number: 4678 After a long battle with technology, hannu@tm.ee (Hannu Krosing), an earthling, wrote: > Christopher Browne kirjutas R, 14.11.2003 kell 16:13: >> Martha Stewart called it a Good Thing when mallah@trade-india.com (Rajesh Kumar Mallah) wrote: >> > INFO: "profiles": found 0 removable, 369195 nonremovable row versions in 43423 pages >> > DETAIL: 246130 dead row versions cannot be removed yet. >> > Nonremovable row versions range from 136 to 2036 bytes long. >> >> It seems as though you have a transaction open that is holding onto a >> whole lot of old rows. >> >> I have seen this happen somewhat-invisibly when a JDBC connection >> manager opens transactions for each connection, and then no processing >> happens to use those connections for a long time. The open >> transactions prevent vacuums from doing any good... > > Can't the backend be made to delay the "real" start of transaction until > the first query gets executed ? One would hope so. Some time when I have the Round Tuits, I ought to take a browse of the connection pool code to notice if there's anything to notice. The thing that I keep imagining would be a slick idea would be to have a thread periodically go through once for however many connections the pool permits and fire a short transaction through every otherwise-unoccupied connection in the pool, in effect, doing a sort of "vacuum" of the connections. I don't get very favorable reactions when I suggest that, though... -- (reverse (concatenate 'string "ac.notelrac.teneerf" "@" "454aa")) http://cbbrowne.com/info/sgml.html Rules of the Evil Overlord #80. "If my weakest troops fail to eliminate a hero, I will send out my best troops instead of wasting time with progressively stronger ones as he gets closer and closer to my fortress."