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 0FD665E46EC for ; Thu, 26 Aug 2004 09:17:19 -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 27685-03 for ; Thu, 26 Aug 2004 12:17:11 +0000 (GMT) Received: from mammoth.utopiasystems.net (meridian1.utopiasystems.net [66.150.45.40]) by svr1.postgresql.org (Postfix) with ESMTP id C65A45E37D0 for ; Thu, 26 Aug 2004 09:17:08 -0300 (ADT) Received: from [192.168.1.100] ([67.173.111.107]) by mammoth.utopiasystems.net with Microsoft SMTPSVC(6.0.3790.0); Thu, 26 Aug 2004 08:14:55 -0400 User-Agent: Microsoft-Entourage/10.1.4.030702.0 Date: Thu, 26 Aug 2004 07:17:02 -0500 Subject: Odd double queries continues From: Steve Lane To: Steve 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 12:14:55.0453 (UTC) FILETIME=[4C32A8D0:01C48B66] 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/259 X-Sequence-Number: 14654 My odd "double" queries continue. On the theory that I had some kind of page-faulting issue tied into large, frequent updates of a table, I vacuumed the whole database and began to watch it closely this morning. Already, after very little activity, I get this in the log: 2004-08-26 07:01:26 [22056] DEBUG: query: INSERT INTO nsse_session (id_user, id_session, expiration, value) VALUES () 2004-08-26 07:01:26 [22056] ERROR: Cannot insert a duplicate key into unique index nsse_session_pkey 2004-08-26 07:01:26 [22056] DEBUG: query: UPDATE nsse_session SET WHERE id_session = '32845b87350846bf51176079c19f2fb4' EXECUTOR STATISTICS ! system usage stats: ! 0.000183 elapsed 0.000000 user 0.000000 system sec ! [0.000000 user 0.030000 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 2/4 [414/257] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 0/0 [0/0] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 0 read, 0 written, buffer hit rate = 100.00% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written EXECUTOR STATISTICS ! system usage stats: ! 10.626398 elapsed 10.490000 user 0.010000 system sec ! [10.510000 user 0.010000 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 261/10 [691/281] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 0/0 [0/0] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 0 read, 0 written, buffer hit rate = 100.00% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written Same pattern as before -- the same query seems to get run twice, the second time ridiculously long, with a high number of page faults. (Again, this is a straightforward query involving a single index scan, and the planner says as much). I realize no one may have any insight in the whole problem, but can anyone at least tell me the significance of each of these numbers from the stats: 261/10 [691/281] page faults/reclaims, 0 [0] swaps That might give me an idea of where to begin. -- sgl