From gfaludi@fits.hu Mon Jul  28 06:50:52 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id AB7D764FCFF
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 28 Jul 2008 03:50:52 -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 29274-09 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 28 Jul 2008 03:50:43 -0300 (ADT)
X-Greylist: delayed 00:06:40.213992 by SQLgrey-1.7.6
Received: from smtp-a.tvnetwork.hu (smtp-a.tvnetwork.hu [80.95.81.66])
	by postgresql.org (Postfix) with SMTP id 7E6A564FCC0
	for <pgsql-performance@postgresql.org>;
	Mon, 28 Jul 2008 03:50:44 -0300 (ADT)
Received: (qmail 18324 invoked by uid 1001); 28 Jul 2008 08:48:10 +0200
Received: from 84.236.80.89 by smtp-a.tvnetwork.hu (envelope-from
	<gfaludi@fits.hu>, uid 64011) with qmail-scanner-1.25st
	(clamdscan: 0.88.1/1396. spamassassin: 3.0.3. perlscan: 1.25st.  
	Clear:RC:1(84.236.80.89):SA:0(-2.5/5.0):.
	Processed in 9.939147 secs); 28 Jul 2008 06:48:10 -0000
X-Envelope-From: gfaludi@fits.hu
Received: from unknown (HELO fitsdevpc) (84.236.80.89)
	by smtp-a.tvnetwork.hu with SMTP; 28 Jul 2008 08:48:00 +0200
From: =?iso-8859-2?Q?Faludi_G=E1bor?= <gfaludi@fits.hu>
To: <pgsql-performance@postgresql.org>
Subject: how does pg handle concurrent queries and same queries
Date: Mon, 28 Jul 2008 08:43:51 +0200
Message-ID: <000301c8f07d$4bee0f10$e3ca2d30$@hu>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0004_01C8F08E.0F76DF10"
X-Mailer: Microsoft Office Outlook 12.0
thread-index: AcjwfUt5W36Wx6wHT6q2NfwtfeOtww==
Content-Language: hu
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200807/232
X-Sequence-Number: 30832

Ez egy többrészes, MIME formátumú üzenet.

------=_NextPart_000_0004_01C8F08E.0F76DF10
Content-Type: text/plain;
	charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable

Hi All,

=20

I have taken over the maintenance of a server farm , recently. 2 =
webserver
on  db server. They are quite powerful 2 processor xeon w/ 6Gig of ram .

=20

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 =
almost
not able to respond or in cases it did not.

=20

=20

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 =
and
takes the one second and blocks the page to load completly . The load =
was
roughly ~300 connections in one minute .

=20

So here are my questions :=20

=20

.         Why does the second and the later queries take the whole on =
second
if the dataset is the same . Shouldn't PG realise that the query is the =
same
so i give the user the same resultset ?

.         How do I know if one query blocks the other ?

.         Is there a way to log the long running queries in 7.4 ? If not =
is
it available in any newer version ?

=20

thanks for your help !=20

=20

=DCdv=F6zlettel/kind regards,

Faludi, G=E1bor

FITS Magyarorsz=E1g Kft.

 <http://www.fits.hu/> http://www.FITS.hu

Tel.:+36 30 4945862

Email:  <mailto:falu@fits.hu> falu@fits.hu

Ingyenes vide=F3 tanfolyamok(Excel,Access,Word) :
<http://www.fits.hu/trainings> http://www.fits.hu/trainings

=20


------=_NextPart_000_0004_01C8F08E.0F76DF10
Content-Type: text/html;
	charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-2">
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:Wingdings;
	panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
	{font-family:Tahoma;
	panose-1:2 11 6 4 3 5 4 4 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
	{mso-style-priority:34;
	margin-top:0cm;
	margin-right:0cm;
	margin-bottom:0cm;
	margin-left:36.0pt;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
span.E-mailStlus17
	{mso-style-type:personal-compose;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;}
@page Section1
	{size:612.0pt 792.0pt;
	margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.Section1
	{page:Section1;}
 /* List Definitions */
 @list l0
	{mso-list-id:1668744603;
	mso-list-type:hybrid;
	mso-list-template-ids:-1886238210 68026369 68026371 68026373 68026369 =
68026371 68026373 68026369 68026371 68026373;}
@list l0:level1
	{mso-level-number-format:bullet;
	mso-level-text:\F0B7;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-18.0pt;
	font-family:Symbol;}
ol
	{margin-bottom:0cm;}
ul
	{margin-bottom:0cm;}
-->
</style>
<!--[if gte mso 9]><xml>
 <o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
 <o:shapelayout v:ext=3D"edit">
  <o:idmap v:ext=3D"edit" data=3D"1" />
 </o:shapelayout></xml><![endif]-->
</head>

<body lang=3DHU link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal>Hi All,<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>I have taken over the maintenance of a server farm =
,
recently. 2 webserver on=A0 db server. They are quite powerful 2 =
processor xeon
w/ 6Gig of ram .<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>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 almost not able to respond or in cases it did not.<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>After some analysis i suspect that there is a query =
that
takes up to 1 second and that is the cause. Upon=A0 each page loading =
this query
fires and takes the one second and blocks the page to load completly . =
The load
was roughly ~300 connections in one minute .<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>So here are my questions : <o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoListParagraph style=3D'text-indent:-18.0pt;mso-list:l0 =
level1 lfo1'><![if !supportLists]><span
lang=3DEN-US style=3D'font-family:Symbol'><span =
style=3D'mso-list:Ignore'>&middot;<span
style=3D'font:7.0pt "Times New =
Roman"'>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
</span></span></span><![endif]>Why does the second and the later queries =
take
the whole on second if the dataset is the same . Shouldn&#8217;t PG =
realise
that the query is the same so i give the user the same resultset ?<span
lang=3DEN-US><o:p></o:p></span></p>

<p class=3DMsoListParagraph style=3D'text-indent:-18.0pt;mso-list:l0 =
level1 lfo1'><![if !supportLists]><span
lang=3DEN-US style=3D'font-family:Symbol'><span =
style=3D'mso-list:Ignore'>&middot;<span
style=3D'font:7.0pt "Times New =
Roman"'>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
</span></span></span><![endif]>How do I know if one query blocks the =
other ?<span
lang=3DEN-US><o:p></o:p></span></p>

<p class=3DMsoListParagraph style=3D'text-indent:-18.0pt;mso-list:l0 =
level1 lfo1'><![if !supportLists]><span
lang=3DEN-US style=3D'font-family:Symbol'><span =
style=3D'mso-list:Ignore'>&middot;<span
style=3D'font:7.0pt "Times New =
Roman"'>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
</span></span></span><![endif]>Is there a way to log the long running =
queries
in 7.4 ? If not is it available in any newer version ?<span =
lang=3DEN-US><o:p></o:p></span></p>

<p class=3DMsoNormal><span lang=3DEN-US><o:p>&nbsp;</o:p></span></p>

<p class=3DMsoNormal><span lang=3DEN-US>thanks for your help ! =
<o:p></o:p></span></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal><span =
style=3D'font-family:"Tahoma","sans-serif";color:blue'>=DCdv=F6zlettel/ki=
nd
regards,<o:p></o:p></span></p>

<p class=3DMsoNormal><span =
style=3D'font-family:"Tahoma","sans-serif"'>Faludi,
G=E1bor<o:p></o:p></span></p>

<p class=3DMsoNormal><b><span =
style=3D'font-family:"Tahoma","sans-serif"'>FITS
Magyarorsz=E1g Kft.</span></b><span =
style=3D'font-family:"Tahoma","sans-serif"'><o:p></o:p></span></p>

<p class=3DMsoNormal><span =
style=3D'font-family:"Tahoma","sans-serif"'><a
href=3D"http://www.fits.hu/"><span =
style=3D'color:windowtext'>http://www.FITS.hu</span></a><o:p></o:p></span=
></p>

<p class=3DMsoNormal><span =
style=3D'font-family:"Tahoma","sans-serif"'>Tel.:+36 <b>30
4945862</b><o:p></o:p></span></p>

<p class=3DMsoNormal><span =
style=3D'font-family:"Tahoma","sans-serif"'>Email: <a
href=3D"mailto:falu@fits.hu"><span =
style=3D'color:windowtext'>falu@fits.hu</span></a><o:p></o:p></span></p>

<p class=3DMsoNormal =
style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span
style=3D'font-size:10.0pt;font-family:"Tahoma","sans-serif";color:blue'>I=
ngyenes
vide=F3 tanfolyamok(Excel,Access,Word) : </span><span =
style=3D'font-size:12.0pt;
font-family:"Tahoma","sans-serif";color:blue'><a
href=3D"http://www.fits.hu/trainings"><span =
style=3D'font-size:10.0pt;color:blue'>http://www.fits.hu/trainings</span>=
</a><o:p></o:p></span></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

</div>

</body>

</html>

------=_NextPart_000_0004_01C8F08E.0F76DF10--




From kometen@gmail.com Mon Jul  28 06:56:03 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 0CDE0650337
	for <pgsql-performance-postgresql.org@postgresql.org>;
	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
	<pgsql-performance-postgresql.org@postgresql.org>;
	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 <pgsql-performance@postgresql.org>;
	Mon, 28 Jul 2008 03:55:53 -0300 (ADT)
Received: by fg-out-1718.google.com with SMTP id 16so2662442fgg.23
	for <pgsql-performance@postgresql.org>;
	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: <b41c75520807272355m28a5f111re80e17f70ef7d87c@mail.gmail.com>
Date: Mon, 28 Jul 2008 08:55:53 +0200
From: "Claus Guttesen" <kometen@gmail.com>
To: "=?ISO-8859-1?Q?Faludi_G=E1bor?=" <gfaludi@fits.hu>
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



From gfaludi@fits.hu Mon Jul  28 07:53:42 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id D490564FCC0
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 28 Jul 2008 04:53:42 -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 88740-03 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 28 Jul 2008 04:53:36 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtp-b.tvnetwork.hu (smtp-b.TvNetWork.Hu [80.95.81.67])
	by postgresql.org (Postfix) with SMTP id 313A964FCE3
	for <pgsql-performance@postgresql.org>;
	Mon, 28 Jul 2008 04:53:37 -0300 (ADT)
Received: (qmail 32124 invoked by uid 1001); 28 Jul 2008 09:57:30 +0200
Received: from 84.236.80.89 by smtp-b.tvnetwork.hu (envelope-from
	<gfaludi@fits.hu>, uid 64011) with qmail-scanner-1.25st
	(clamdscan: 0.88.1/1396. spamassassin: 3.0.3. perlscan: 1.25st.  
	Clear:RC:1(84.236.80.89):SA:0(-2.5/5.0):.
	Processed in 10.439343 secs); 28 Jul 2008 07:57:30 -0000
X-Envelope-From: gfaludi@fits.hu
Received: from unknown (HELO fitsdevpc) (84.236.80.89)
	by smtp-b.tvnetwork.hu with SMTP; 28 Jul 2008 09:57:20 +0200
From: =?iso-8859-2?Q?Faludi_G=E1bor?= <gfaludi@fits.hu>
To: "'Claus Guttesen'" <kometen@gmail.com>
Cc: <pgsql-performance@postgresql.org>
References: <000301c8f07d$4bee0f10$e3ca2d30$@hu>
	<b41c75520807272355m28a5f111re80e17f70ef7d87c@mail.gmail.com>
In-Reply-To: <b41c75520807272355m28a5f111re80e17f70ef7d87c@mail.gmail.com>
Subject: Re: how does pg handle concurrent queries and same queries
Date: Mon, 28 Jul 2008 09:53:24 +0200
Message-ID: <001101c8f087$037f1350$0a7d39f0$@hu>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
X-Mailer: Microsoft Office Outlook 12.0
thread-index: AcjwfxyE5c+tSHzVSoWqhLxRjwvCYQAB0g2Q
Content-Language: 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/234
X-Sequence-Number: 30834

Hi,

here is what the original query was which was obviously nonsense :
EXPLAIN ANALYZE SELECT DISTINCT letoltes.cid, s.elofordulas FROM =
letoltes
INNER JOIN (select letoltes.cid, count(letoltes.cid) AS elofordulas FROM
letoltes GROUP BY cid) s ON s.cid=3Dletoltes.cid ORDER BY s.elofordulas =
DESC
LIMIT 5;
=20
QUERY PLAN
-------------------------------------------------------------------------=
---
-------------------------------------------------------------------------=
---
------------
 Limit  (cost=3D73945.35..73945.65 rows=3D5 width=3D12) (actual
time=3D4191.396..4351.966 rows=3D5 loops=3D1)
   ->  Unique  (cost=3D73945.35..77427.99 rows=3D58800 width=3D12) =
(actual
time=3D4191.390..4351.956 rows=3D5 loops=3D1)
         ->  Sort  (cost=3D73945.35..75106.23 rows=3D464351 width=3D12) =
(actual
time=3D4191.386..4283.545 rows=3D175944 loops=3D1)
               Sort Key: s.elofordulas, letoltes.cid
               ->  Merge Join  (cost=3D9257.99..30238.65 rows=3D464351 =
width=3D12)
(actual time=3D652.535..2920.304 rows=3D464351 loops=3D1)
                     Merge Cond: ("outer".cid =3D "inner".cid)
                     ->  Index Scan using idx_letoltes_cid on letoltes
(cost=3D0.00..12854.51 rows=3D464351 width=3D4) (actual =
time=3D0.084..1270.588
rows=3D464351 loops=3D1)
                     ->  Sort  (cost=3D9257.99..9258.73 rows=3D294 =
width=3D12)
(actual time=3D652.434..810.941 rows=3D464176 loops=3D1)
                           Sort Key: s.cid
                           ->  Subquery Scan s  (cost=3D9242.26..9245.94
rows=3D294 width=3D12) (actual time=3D651.343..652.028 rows=3D373 =
loops=3D1)
                                 ->  HashAggregate  =
(cost=3D9242.26..9243.00
rows=3D294 width=3D4) (actual time=3D651.339..651.661 rows=3D373 =
loops=3D1)
                                       ->  Seq Scan on letoltes
(cost=3D0.00..6920.51 rows=3D464351 width=3D4) (actual =
time=3D0.014..307.469
rows=3D464351 loops=3D1)
 Total runtime: 4708.434 ms
(13 sor)

However after fixing the query this is 1/4 th of the time  but still =
blocks
the site :


EXPLAIN ANALYZE SELECT DISTINCT letoltes.cid, count(letoltes.cid)  AS
elofordulas FROM letoltes GROUP BY cid ORDER BY elofordulas DESC LIMIT =
5;
                                                               QUERY =
PLAN
-------------------------------------------------------------------------=
---
-------------------------------------------------------------
 Limit  (cost=3D9255.05..9255.09 rows=3D5 width=3D4) (actual =
time=3D604.734..604.743
rows=3D5 loops=3D1)
   ->  Unique  (cost=3D9255.05..9257.26 rows=3D294 width=3D4) (actual
time=3D604.732..604.737 rows=3D5 loops=3D1)
         ->  Sort  (cost=3D9255.05..9255.79 rows=3D294 width=3D4) =
(actual
time=3D604.730..604.732 rows=3D5 loops=3D1)
               Sort Key: count(cid), cid
               ->  HashAggregate  (cost=3D9242.26..9243.00 rows=3D294 =
width=3D4)
(actual time=3D604.109..604.417 rows=3D373 loops=3D1)
                     ->  Seq Scan on letoltes  (cost=3D0.00..6920.51
rows=3D464351 width=3D4) (actual time=3D0.022..281.413 rows=3D464351 =
loops=3D1)
 Total runtime: 604.811 ms


here is the table :=20
\d letoltes
                     T=C3=A1bla "public.letoltes"
 Oszlop | T=C3=ADpus  |                  M=C3=B3dos=C3=ADt=C3=B3
--------+---------+------------------------------------------------
 id     | integer | not null default nextval('letoltes_seq'::text)
 cid    | integer |
Indexes:
    "idx_letoltes_cid" btree (cid)
    "idx_letoltes_id" btree (id)

select count(1) from letoltes;
 count
--------
 464351


VACUM ANALYZE runs overnight every day.

thanks,
Gabor

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Claus =
Guttesen
Sent: Monday, July 28, 2008 8:56 AM
To: Faludi G=E1bor
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] how does pg handle concurrent queries and same
queries

> I have taken over the maintenance of a server farm , recently. 2 =
webserver
> 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
almost
> 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 =
and
> 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 :
>
> .         Why does the second and the later queries take the whole on
second
> if the dataset is the same . Shouldn't PG realise that the query is =
the
same
> so i give the user the same resultset ?
>
> .         How do I know if one query blocks the other ?
>
> .         Is there a way to log the long running queries in 7.4 ? If =
not
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

--=20
Sent via pgsql-performance mailing list =
(pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

No virus found in this incoming message.
Checked by AVG - http://www.avg.com=20
Version: 8.0.138 / Virus Database: 270.5.6/1576 - Release Date: =
2008.07.27.
16:16




From craig@postnewspapers.com.au Mon Jul  28 08:46:21 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id A27F064FD11
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 28 Jul 2008 05:46:21 -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 39010-02 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 28 Jul 2008 05:46:14 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from www.postnewspapers.com.au (www.postnewspapers.com.au
	[202.61.230.242])
	by postgresql.org (Postfix) with ESMTP id 1FEE264FCE3
	for <pgsql-performance@postgresql.org>;
	Mon, 28 Jul 2008 05:46:17 -0300 (ADT)
Received: from mail.postnewspapers.com.au
	(202-89-185-120.static.dsl.amnet.net.au [202.89.185.120])
	by www.postnewspapers.com.au (Postfix) with ESMTP id BD87C5C0E4;
	Mon, 28 Jul 2008 16:46:12 +0800 (WST)
Received: from [10.0.0.53] (wallace.localnet [10.0.0.53])
	by mail.postnewspapers.com.au (Postfix) with ESMTP id A5CD11E0434;
	Mon, 28 Jul 2008 16:46:12 +0800 (WST)
Message-ID: <488D8754.2010208@postnewspapers.com.au>
Date: Mon, 28 Jul 2008 16:46:12 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Icedove 1.5.0.14eol (X11/20080509)
MIME-Version: 1.0
To: =?ISO-8859-2?Q?Faludi_G=E1bor?= <gfaludi@fits.hu>
Cc: pgsql-performance@postgresql.org
Subject: Re: how does pg handle concurrent queries and same queries
References: <000301c8f07d$4bee0f10$e3ca2d30$@hu>
In-Reply-To: <000301c8f07d$4bee0f10$e3ca2d30$@hu>
X-Enigmail-Version: 0.94.2.0
Content-Type: text/plain; charset=ISO-8859-2
Content-Transfer-Encoding: 8bit
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/235
X-Sequence-Number: 30835

Faludi Gábor wrote:

> .         Why does the second and the later queries take the whole on second
> if the dataset is the same . Shouldn't PG realise that the query is the same
> so i give the user the same resultset ?

That would require a result cache. I don't know if Pg even has a query
result cache - I don't think so, but I'm not sure. Even if it does, it'd
still only be useful if the queries were issued under *exactly* the same
conditions - in other words, no writes had been made to the database
since the cached query was issued, and the first query had committed
before the second began (or was read-only). Additionally, no volatile
functions could be called in the query, because their values/effects
might be different when the query is executed a second time. That
includes triggers, etc.

Since 7.4 doesn't do lazy xid allocation it can't really tell that
nothing has been changed since the previous query was cached. So, if I'm
not missing something here, a query result cache would be useless anyway.

> .         How do I know if one query blocks the other ?

Examination of pg_catalog.pg_locks is certainly a start. It's trickier
with lots of short-running queries, though.

> .         Is there a way to log the long running queries in 7.4 ? If not is
> it available in any newer version ?

It's certainly available in 8.3, as log_min_duration_statement in
postgresql.conf . You can find out if it's in 7.4, and if not what
version it was introduced in, by looking through the documentation for
versions 7.4 and up.

--
Craig Ringer



From matthew@flymine.org Mon Jul  28 11:27:44 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 1D0F8650293
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 28 Jul 2008 08:27:44 -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 88575-06 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 28 Jul 2008 08:27:36 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from ppsw-1.csi.cam.ac.uk (ppsw-1.csi.cam.ac.uk [131.111.8.131])
	by postgresql.org (Postfix) with ESMTP id 47E21650292
	for <pgsql-performance@postgresql.org>;
	Mon, 28 Jul 2008 08:27:40 -0300 (ADT)
X-Cam-SpamDetails: Not scanned
X-Cam-AntiVirus: No virus found
X-Cam-ScannerInfo: http://www.cam.ac.uk/cs/email/scanner/
Received: from flymine.sysbiol.cam.ac.uk ([131.111.60.120]:50075
	helo=flymine.flymine.org)
	by ppsw-1.csi.cam.ac.uk (ppsw.cam.ac.uk [131.111.8.131]:25)
	with esmtp id 1KNQsx-0000eU-4O (Exim 4.67) for
	pgsql-performance@postgresql.org
	(return-path <matthew@flymine.org>); Mon, 28 Jul 2008 12:27:27 +0100
Received: from host103
	([192.168.128.103] helo=aragorn.flymine.org ident=Debian-exim)
	by flymine.flymine.org with esmtp (Exim 4.12) id 1KNQsx-0008DA-00
	for pgsql-performance@postgresql.org; Mon, 28 Jul 2008 12:27:27 +0100
Received: from mnw21 (helo=localhost)
	by aragorn.flymine.org with local-esmtp (Exim 4.69)
	(envelope-from <matthew@flymine.org>) id 1KNQsx-0004nj-56
	for pgsql-performance@postgresql.org; Mon, 28 Jul 2008 12:27:27 +0100
Date: Mon, 28 Jul 2008 12:27:27 +0100 (BST)
From: Matthew Wakeling <matthew@flymine.org>
X-X-Sender: mnw21@aragorn.flymine.org
To: pgsql-performance@postgresql.org
Subject: Re: how does pg handle concurrent queries and same queries
In-Reply-To: <001101c8f087$037f1350$0a7d39f0$@hu>
Message-ID: <Pine.LNX.4.64.0807281219300.5954@aragorn.flymine.org>
References: <000301c8f07d$4bee0f10$e3ca2d30$@hu>
	<b41c75520807272355m28a5f111re80e17f70ef7d87c@mail.gmail.com>
	<001101c8f087$037f1350$0a7d39f0$@hu>
MIME-Version: 1.0
X-Virus-Scanned: Maia Mailguard 1.0.1
Content-Type: TEXT/PLAIN; charset=iso-8859-1; format=flowed
Content-Transfer-Encoding: QUOTED-PRINTABLE
X-Archive-Number: 200807/236
X-Sequence-Number: 30836

On Mon, 28 Jul 2008, Faludi G=E1bor wrote:
> EXPLAIN ANALYZE SELECT DISTINCT letoltes.cid, count(letoltes.cid)  AS
> elofordulas FROM letoltes GROUP BY cid ORDER BY elofordulas DESC LIMIT 5;
>                                                               QUERY PLAN
> -------------------------------------------------------------------------=
----------------------------------------------------------------
> Limit  (cost=3D9255.05..9255.09 rows=3D5 width=3D4) (actual  time=3D604.7=
34..604.743 > rows=3D5 loops=3D1)
>   ->  Unique  (cost=3D9255.05..9257.26 rows=3D294 width=3D4) (actual time=
=3D604.732..604.737 rows=3D5 loops=3D1)
>         ->  Sort  (cost=3D9255.05..9255.79 rows=3D294 width=3D4) (actual =
time=3D604.730..604.732 rows=3D5 loops=3D1)
>               Sort Key: count(cid), cid
>               ->  HashAggregate  (cost=3D9242.26..9243.00 rows=3D294 widt=
h=3D4) (actual time=3D604.109..604.417 rows=3D373 loops=3D1)
>                     ->  Seq Scan on letoltes  (cost=3D0.00..6920.51 rows=
=3D464351 width=3D4) (actual time=3D0.022..281.413 rows=3D464351 loops=3D1)
> Total runtime: 604.811 ms

So this query is doing a sequential scan of the letoltes table for each=20
query. You may get some improvement by creating an index on cid and=20
clustering on that index, but probably not much.

Moving to Postgres 8.3 will probably help a lot, as it will allow multiple=
=20
queries to use the same sequential scan in parallel. That's assuming the=20
entire table isn't in cache.

Another solution would be to create an additional table that contains the=
=20
results of this query, and keep it up to date using triggers on the=20
original table. Then query that table instead.

However, probably the best solution is to examine the problem and work out=
=20
if you can alter the application to make it avoid doing such an expensive=
=20
query so often. Perhaps it could cache the results.

Matthew

--=20
Psychotics are consistently inconsistent. The essence of sanity is to
be inconsistently inconsistent.=



From alvherre@commandprompt.com Mon Jul  28 13:47:36 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id DA34B65035D
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 28 Jul 2008 10:47:36 -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 28214-01-3
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 28 Jul 2008 10:47:16 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from lists.commandprompt.com (host-159.commandprompt.net
	[207.173.203.159])
	by postgresql.org (Postfix) with ESMTP id 8C403650408
	for <pgsql-performance@postgresql.org>;
	Mon, 28 Jul 2008 10:47:14 -0300 (ADT)
Received: from perhan.alvh.no-ip.org (190-95-31-93.bk17-dsl.surnet.cl
	[190.95.31.93]) (authenticated bits=0)
	by lists.commandprompt.com (8.13.8/8.13.8) with ESMTP id m6SDnamk032315
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO);
	Mon, 28 Jul 2008 06:49:40 -0700
Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000)
	id A119E47C86; Mon, 28 Jul 2008 09:47:02 -0400 (CLT)
Date: Mon, 28 Jul 2008 09:47:02 -0400
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Craig Ringer <craig@postnewspapers.com.au>
Cc: Faludi =?iso-8859-1?Q?G=E1bor?= <gfaludi@fits.hu>,
	pgsql-performance@postgresql.org
Subject: Re: how does pg handle concurrent queries and same
	queries
Message-ID: <20080728134702.GA14617@alvh.no-ip.org>
References: <000301c8f07d$4bee0f10$e3ca2d30$@hu>
	<488D8754.2010208@postnewspapers.com.au>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <488D8754.2010208@postnewspapers.com.au>
User-Agent: Mutt/1.5.18 (2008-05-17)
X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.0
	(lists.commandprompt.com [207.173.203.159]);
	Mon, 28 Jul 2008 06:49:41 -0700 (PDT)
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/237
X-Sequence-Number: 30837

Craig Ringer wrote:
> Faludi Gábor wrote:
> 
> > .         Why does the second and the later queries take the whole on second
> > if the dataset is the same . Shouldn't PG realise that the query is the same
> > so i give the user the same resultset ?
> 
> That would require a result cache. I don't know if Pg even has a query
> result cache - I don't think so, but I'm not sure.

It doesn't.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support



From brakhane@googlemail.com Wed Jul  30 18:59:19 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id B72F46501AD
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 30 Jul 2008 15:59:19 -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 13412-09 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 30 Jul 2008 15:59:14 -0300 (ADT)
X-Greylist: delayed 00:18:24.958233 by SQLgrey-1.7.6
Received: from yw-out-2324.google.com (yw-out-2324.google.com [74.125.46.29])
	by postgresql.org (Postfix) with ESMTP id 72A9C650175
	for <pgsql-performance@postgresql.org>;
	Wed, 30 Jul 2008 15:59:16 -0300 (ADT)
Received: by yw-out-2324.google.com with SMTP id 3so86887ywj.73
	for <pgsql-performance@postgresql.org>;
	Wed, 30 Jul 2008 11:59:15 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=googlemail.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=N/n/uxtNg2USckt+h9kqTeo3OOxIpBvSM3UPiTOYjU4=;
	b=Tj05PGBNi685dSMoMM+rbFgF0jQhl1XIjZ44Vqzni/uthWP6tpxovT7Cdn6VQzT2Ms
	lGlJi5gx7xTQCfq+oR7qF0HCu0r6IEhqsA7epNyue275XHjU7MC23ZRvqa7VeJjFh9kA
	yIFoxQoP8azYNlzZaCxyHKcxJ4B/8C+bNGZHE=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.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=UwpMek4n9k7hU47aV6CCPNQwW7fuEl7GBS0kA9UGXKq66OI1TPFybI9pU8BEtGVMk6
	sihJHFQT5sAI2SuPt/veJU0IYVxiBzGA8/n1aD/VNmzSeyDxQ1pW4v8yKjl+iYORYmqQ
	wJLWRJv0yvGzx1giGFhyF1ffcbfFThBxd/f1Q=
Received: by 10.114.148.2 with SMTP id v2mr8987364wad.173.1217442856826;
	Wed, 30 Jul 2008 11:34:16 -0700 (PDT)
Received: by 10.114.15.5 with HTTP; Wed, 30 Jul 2008 11:34:16 -0700 (PDT)
Message-ID: <226a19190807301134p188cf6d7gaf829b10430629aa@mail.gmail.com>
Date: Wed, 30 Jul 2008 20:34:16 +0200
From: "Dennis Brakhane" <brakhane@googlemail.com>
To: "=?UTF-8?Q?Faludi_G=C3=A1bor?=" <gfaludi@fits.hu>
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=UTF-8
Content-Transfer-Encoding: 7bit
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/257
X-Sequence-Number: 30857

Slightly off-topic, but judging from the fact that you were able to
"fix" the query, it seems you have some way to modify the application
code itself. In that case, I'd try to implement caching (at least for
this statement) on the application side, for example with memcached.