Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id D490564FCC0 for ; 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 ; 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 ; 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 , 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?= To: "'Claus Guttesen'" Cc: References: <000301c8f07d$4bee0f10$e3ca2d30$@hu> In-Reply-To: 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