X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E95935E470E for ; Wed, 25 Aug 2004 23:59:28 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 57301-02 for ; Thu, 26 Aug 2004 02:59:35 +0000 (GMT) Received: from mammoth.utopiasystems.net (meridian1.utopiasystems.net [66.150.45.40]) by svr1.postgresql.org (Postfix) with ESMTP id C18775E470C for ; Wed, 25 Aug 2004 23:59:27 -0300 (ADT) Received: from [192.168.1.100] ([67.173.111.107]) by mammoth.utopiasystems.net with Microsoft SMTPSVC(6.0.3790.0); Wed, 25 Aug 2004 22:57:16 -0400 User-Agent: Microsoft-Entourage/10.1.4.030702.0 Date: Wed, 25 Aug 2004 21:59:25 -0500 Subject: Re: Odd query behavior [urgent, but long] From: Steve Lane To: Steve Lane , Tom Lane Cc: , Jamie Thomas Message-ID: In-Reply-To: Mime-version: 1.0 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit X-OriginalArrivalTime: 26 Aug 2004 02:57:17.0000 (UTC) FILETIME=[6567E480:01C48B18] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_RFCI X-Spam-Level: X-Archive-Number: 200408/257 X-Sequence-Number: 14652 OK, well I may have some more useful information. The queries that blow up seem, by and large, to be updates to the session table. Again, some of these updates are big: I measured one at 50K today. I suspected that insufficient vacuuming might be involved. I did a full vacuum and got this for the session table: NOTICE: --Relation nsse_session-- NOTICE: Pages 3544: Changed 11, reaped 696, Empty 0, New 0; Tup 26121: Vac 37327, Keep/VTL 0/0, Crash 0, UnUsed 107, MinLen 132, MaxLen 2032; Re-using: Free/Avail. Space 5345328/5336916; EndEmpty/Avail. Pages 0/684. CPU 0.08s/0.03u sec. NOTICE: Index nsse_session_pkey: Pages 1435; Tuples 26121: Deleted 36871. CPU 0.07s/0.13u sec. NOTICE: Rel nsse_session: Pages: 3544 --> 2873; Tuple(s) moved: 13. CPU 0.02s/0.06u sec. NOTICE: Index nsse_session_pkey: Pages 1435; Tuples 26121: Deleted 13. CPU 0.00s/0.01u sec. NOTICE: --Relation pg_toast_3521195-- NOTICE: Pages 83872: Changed 6, reaped 81999, Empty 0, New 0; Tup 8100: Vac 327763, Keep/VTL 0/0, Crash 0, UnUsed 88, MinLen 45, MaxLen 2034; Re-using: Free/Avail. Space 668306776/668306512; EndEmpty/Avail. Pages 0/81954. CPU 3.22s/0.30u sec. NOTICE: Index pg_toast_3521195_idx: Pages 1668; Tuples 8100: Deleted 327763. CPU 0.33s/1.07u sec. NOTICE: Rel pg_toast_3521195: Pages: 83872 --> 1933; Tuple(s) moved: 15. CPU 10.16s/4.87u sec. NOTICE: Index pg_toast_3521195_idx: Pages 1668; Tuples 8100: Deleted 15. CPU 0.05s/0.00u sec. Now granted, this was after I had written scripts to perform that 50K update about 10-15K times -- I did this just to check the degradation in insert performance on the unvacuumed table, and as expected I saw a slow, steady degradation in insert time, but nothing catastrophic. Clearly the toast table for nsse_session is big, and the table is very frequently accessed. This seems like a recipe for page faults of some kind, but it seems this could lead to two opposite conclusions: 1) lower shared buffers in case shared buffers are starving the OS disk caching buffers 2) raise shared buffers so as to get the whole session/toast table in memory ?? -- sgl > From: Steve Lane > Date: Wed, 25 Aug 2004 14:26:52 -0500 > To: Tom Lane > Cc: , Jamie Thomas > Subject: Re: [ADMIN] Odd query behavior [urgent, but long] > > > >> From: Tom Lane >> Date: Wed, 25 Aug 2004 15:16:32 -0400 >> To: Steve Lane >> Cc: pgsql-admin@postgresql.org, Jamie Thomas >> Subject: Re: [ADMIN] Odd query behavior [urgent, but long] >> >> Steve Lane writes: >>> I have enabled pretty extensive query logging on the 7.1.3 instance. What I >>> see is that certain queries take hideously long. >> >> Tell us about foreign keys associated with the table being updated? >> An UPDATE would fire triggers for both referencing and referenced keys ... >> >> I'm suspecting an unindexed or datatype-incompatible foreign key column. > > Hi Tom: > > Thanks. There are, unfortunately, no foreign keys or triggers anywhere in > the db that I know of. And this query is not always slow, just occasionally. > > -- sgl > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >