Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 0CDE0650337 for ; Mon, 28 Jul 2008 03:56:03 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 37622-01 for ; Mon, 28 Jul 2008 03:55:52 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.159]) by postgresql.org (Postfix) with ESMTP id 2B56E650136 for ; Mon, 28 Jul 2008 03:55:53 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id 16so2662442fgg.23 for ; Sun, 27 Jul 2008 23:55:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=saK5sgXcZ43G3vcyQggzNaE9EY/0Gb11L71y9LieScI=; b=F6O3e2b1/M9KdLZhfgzMv3zoGIzA7KTRVdqoqzq4Uw91JVu1UMSOLTfu+YjPKA0PE5 5rV8MoaWD034u74uKfIcRS4QABs6TmvYYNlQPBxQGExqpTWZilfci3ql91xzZTLI0iPZ xAE2hNZLRE8NLW7smopeCWT+CI26OHY2DjHgw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=A211W9bbkYTerGbpkEbZVth6ecZyJjLzG0Jqyp16OYhqfhi9MqcdIrld18s4VXn5ku neFz+uwlWuc+WRJrwcmWiOE8cMYOF6dMlhJ+liPw5N9MJC3pZ9+9YIAT4plPgvuLKdJx FVj0qLEnZnj1uFMgnQ3+yOeTQ25gWD5Bp7i00= Received: by 10.86.76.20 with SMTP id y20mr2284753fga.53.1217228153494; Sun, 27 Jul 2008 23:55:53 -0700 (PDT) Received: by 10.86.79.5 with HTTP; Sun, 27 Jul 2008 23:55:53 -0700 (PDT) Message-ID: Date: Mon, 28 Jul 2008 08:55:53 +0200 From: "Claus Guttesen" To: "=?ISO-8859-1?Q?Faludi_G=E1bor?=" Subject: Re: how does pg handle concurrent queries and same queries Cc: pgsql-performance@postgresql.org In-Reply-To: <000301c8f07d$4bee0f10$e3ca2d30$@hu> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <000301c8f07d$4bee0f10$e3ca2d30$@hu> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200807/233 X-Sequence-Number: 30833 > I have taken over the maintenance of a server farm , recently. 2 webserve= r > on db server. They are quite powerful 2 processor xeon w/ 6Gig of ram . > > Couple of days ago we had a serious performance hit and the db server (pg= . > v7.4) was overloaded w/ something in a way that operating system was almo= st > not able to respond or in cases it did not. > > After some analysis i suspect that there is a query that takes up to 1 > second and that is the cause. Upon each page loading this query fires an= d > takes the one second and blocks the page to load completly . The load was > roughly ~300 connections in one minute . > > So here are my questions : > > =B7 Why does the second and the later queries take the whole on s= econd > if the dataset is the same . Shouldn't PG realise that the query is the s= ame > so i give the user the same resultset ? > > =B7 How do I know if one query blocks the other ? > > =B7 Is there a way to log the long running queries in 7.4 ? If no= t is > it available in any newer version ? Can you post the queries? Can you provide an 'analyze explain'? Do you perform a 'vacuum analyze' on a regular basis? --=20 regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare