X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 8FDD3D1B4F0 for ; Thu, 28 Aug 2003 06:47:49 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 69871-05 for ; Thu, 28 Aug 2003 03:47:16 -0300 (ADT) Received: from smtp.pspl.co.in (unknown [202.54.11.65]) by svr1.postgresql.org (Postfix) with ESMTP id 9856BD1B8E7 for ; Thu, 28 Aug 2003 03:47:10 -0300 (ADT) Received: (from root@localhost) by smtp.pspl.co.in (8.12.9/8.12.9) id h7S6pcu3025539 for ; Thu, 28 Aug 2003 12:21:38 +0530 Received: from daithan (daithan.intranet.pspl.co.in [192.168.7.161]) (authenticated bits=0) by persistent.co.in (8.12.9/8.12.9) with ESMTP id h7S6pchi025526 for ; Thu, 28 Aug 2003 12:21:38 +0530 From: "Shridhar Daithankar" To: pgsql-performance@postgresql.org Date: Thu, 28 Aug 2003 12:19:07 +0530 MIME-Version: 1.0 Subject: Re: Queries sometimes take 1000 times the normal time Reply-To: shridhar_daithankar@persistent.co.in Message-ID: <3F4DF33B.11939.3364D27@localhost> In-reply-to: X-mailer: Pegasus Mail for Windows (v4.12a) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/391 X-Sequence-Number: 3207 On 28 Aug 2003 at 1:07, Anders K. Pedersen wrote: > Hello, > > We're running a set of Half-Life based game servers that lookup user > privileges from a central PostgreSQL 7.3.4 database server (I recently > ported the MySQL code in Adminmod to PostgreSQL to be able to do this). > > The data needed by the game servers are combined from several different > tables, so we have some views set up to provide the data in the format > needed. > > Currently there's only a few users in the database for testing purposes, > and most of the time the user lookup's take 2-3 ms (I have syslog'ing of > queries and duration turned on), but several times per hour the duration > for one of the queries is 2-3 seconds (1000 times larger), while the > surrounding lookups take the usual 2-3 ms. Check vmstat during the same period if it is syncing at that point as Tom suggested. Are you using pooled connections? If yes you could shorten life of a connection and force making a new connection every 10-15 minutes say. That would avoid IO avelanche at the end of the hour types. HTH. Bye Shridhar -- ignorance, n.: When you don't know anything, and someone else finds out.