X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id 5D46A11F6744 for ; Fri, 21 Apr 2006 10:54:23 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -2.499 X-Spam-Level: X-Spam-Status: No, score=-2.499 required=5 tests=[AWL=0.099, BAYES_00=-2.599, HTML_MESSAGE=0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id JZx9vwpQ8FbR for ; Fri, 21 Apr 2006 10:54:21 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from globalrelay.com (mail1.globalrelay.com [216.18.71.77]) by postgresql.org (Postfix) with ESMTP id 505F111F678B for ; Fri, 21 Apr 2006 10:54:17 -0300 (ADT) X-Virus-Scanned: Scanned by GRC-AntiVirus Gateway X-GR-Acctd: YES Received: from [67.90.96.2] (HELO DaveEMachine) by globalrelay.com (CommuniGate Pro SMTP 4.2.3) with ESMTP id 88367025; Fri, 21 Apr 2006 06:54:17 -0700 From: "Dave Dutcher" To: , Subject: Re: Little use of CPU ( < 5%) Date: Fri, 21 Apr 2006 08:54:16 -0500 Message-ID: <007f01c6654b$15077280$8300a8c0@tridecap.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0080_01C66521.2C316A80" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 In-reply-to: <4673479.1145611995600.JavaMail.www@wwinf4104> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 Importance: Normal X-Archive-Number: 200604/502 X-Sequence-Number: 18586 This is a multi-part message in MIME format. ------=_NextPart_000_0080_01C66521.2C316A80 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Maybe you could post the query and an EXPLAIN ANALYZE of the query. That would give more information for trying to decide what is wrong. So your question is basically why you get a slower read rate on this query than on other queries? If I had to guess, maybe it could be that you are scanning an index with a low correlation (The order of the records in the index is very different then the order of the records on the disk.) causing your drives to do a lot of seeking. A possible fix for this might be to cluster the table on the index, but I would check out the explain analyze first to see which step is really the slow one. -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of luchot Sent: Friday, April 21, 2006 4:33 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] Little use of CPU ( < 5%) Hello , I have a problem of performance with a query. I use PostgreSQL 8.1.3. The distribution of Linux is Red Hat Enterprise Linux ES release 4 (Nahant Update 2) and the server is a bi-processor Xeon 2.4GHz with 1 Go of Ram and the size of the database files is about 60 Go. The problem is that this query uses only a few percentage of the cpu as seen with the top command : PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 3342 postgres 18 0 140m 134m 132m D 5.9 13.3 17:04.06 postmaster The vm stat command : procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 0 1 184 16804 38104 933516 0 0 3092 55 667 145 12 4 71 14 0 1 184 16528 38140 933480 0 0 2236 0 1206 388 2 1 50 47 0 1 184 15008 38188 935252 0 0 2688 92 1209 396 2 0 49 48 The config of PostgresQL is : shared_buffers = 16384 (128Mo) work_mem = 65536 (64 Mo) maintenance_work_mem = 98304 (96 Mo) effective_cache_size = 84000 I think that the problem is there are too much %wait that are waiting cause of the really bad rate of lecture (bi) which is only 3 Mo/s . It is this value I do not understand because whit other queries this rate is about 120 Mo/s. I use SCSI DISK and a RAID 0 hardware system . This is the query plan of the query : QUERY PLAN ------------------------------------------------------------------------ ------------------------------------ Aggregate (cost=24582205.20..24582205.22 rows=1 width=13) -> Nested Loop (cost=2.11..24582054.88 rows=60129 width=13) Join Filter: ("inner".l_quantity < (subplan)) -> Seq Scan on part (cost=0.00..238744.00 rows=6013 width=4) Filter: ((p_brand = 'Brand#51'::bpchar) AND (p_container = 'MED JAR'::bpchar)) -> Bitmap Heap Scan on lineitem (cost=2.11..126.18 rows=31 width=27) Recheck Cond: ("outer".p_partkey = lineitem.l_partkey) -> Bitmap Index Scan on id_partkey_lineitem (cost=0.00..2.11 rows=31 width=0) Index Cond: ("outer".p_partkey = lineitem.l_partkey) SubPlan -> Aggregate (cost=126.50..126.51 rows=1 width=10) -> Index Scan using id_partkey_lineitem on lineitem (cost=0.00..126.42 rows=31 width=10) Index Cond: (l_partkey = $0) (13 rows) The number of tuples in Lineitem is 180 000 000. So my question is what I have to do to increase the rate of the read which improve the execution of the query? I add that the server is only dedicated for PostgreSQL. Regards, ------=_NextPart_000_0080_01C66521.2C316A80 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Maybe you could post the query and = an EXPLAIN ANALYZE of the query. That would give more information for = trying to decide what is wrong.

 

So your question is basically why = you get a slower read rate on this query than on other queries?  If I had to guess, maybe it = could be that you are scanning an index with a low correlation (The order of the = records in the index is very different then the order of the records on the disk.) = causing your drives to do a lot of seeking.  A possible fix for this might be to cluster the table on the = index, but I would check out the explain analyze first = to see which step is really the slow one.

 

 

-----Original Message-----
From: = pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of luchot
Sent: Friday, April 21, = 2006 4:33 AM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Little = use of CPU ( < 5%)

 

Hello ,

 

I have a problem of performance with a query. I use PostgreSQL = 8.1.3.

 

The distribution of Linux is Red Hat = Enterprise Linux ES release 4 (Nahant Update 2) and the server is a bi-processor = Xeon 2.4GHz with 1 Go of = Ram and the size of the database files is about 60 Go.

 

The problem is that this query uses only a few percentage of the = cpu as seen with the top command :

 

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND       &nbs= p; 

3342 postgres  18   0  140m 134m 132m D  5.9 13.3  17:04.06 = postmaster

 

The vm stat command = : 

procs -----------memory---------- = ---swap-- -----io---- --system-- ----cpu----

 r  b   swpd   free   buff  cache       &nbs= p;  si   so    bi    bo   in      cs     us  sy  id wa

 0  1    184  16804  38104 933516    0    0  3092    55  667   145  12  4   71 = 14

 0  1    = 184  16528  38140 933480    0    0  2236     0 1206   388   2   1  50 = 47

 0  1    = 184  15008  38188 935252    0    0  2688    92 1209    396  2  0  49 = 48

 

 

The config of = PostgresQL is :  =

 

 

shared_buffers =3D = 16384        = ;    = (128Mo)

work_mem =3D = 65536       &nbs= p;  (64 Mo)

maintenance_work_mem = =3D 98304  (96 = Mo)

effective_cache_size =3D 84000

 

I think that the = problem is there are too much  = %wait  that are waiting cause of the = really bad rate of lecture (bi) which is only 3 Mo/s .

It is this value I do = not understand because whit other queries this rate is about 120 Mo/s. I use = SCSI DISK and a RAID 0 hardware system .

 

This is the query = plan of the query :

 

       &nbs= p;            = ;            =             &= nbsp;    QUERY PLAN       &nbs= p;            = ;            =             &= nbsp;   

------------------------------------------------= ------------------------------------------------------------

 Aggregate  = (cost=3D24582205.20..24582205.22 rows=3D1 width=3D13)

   ->  Nested = Loop  (cost=3D2.11..24582054.88 = rows=3D60129 width=3D13)

       &nbs= p; Join Filter: = ("inner".l_quantity < (subplan))

       &nbs= p; ->  Seq Scan on part  (cost=3D0.00..238744.00 = rows=3D6013 width=3D4)

       &nbs= p;       Filter: ((p_brand =3D 'Brand#51'::bpchar) AND (p_container =3D = 'MED JAR'::bpchar))

       &nbs= p; ->  Bitmap Heap = Scan on lineitem  = (cost=3D2.11..126.18 rows=3D31 width=3D27)

       &nbs= p;       Recheck Cond: ("outer".p_partkey =3D = lineitem.l_partkey)

       &nbs= p;       ->  Bitmap Index = Scan on id_partkey_lineitem  (cost=3D0.00..2.11 rows=3D31 = width=3D0)

       &nbs= p;            = ; Index Cond: ("outer".p_partkey =3D = lineitem.l_partkey)

       &nbs= p; SubPlan

       &nbs= p;   ->  = Aggregate  (cost=3D126.50..126.51 rows=3D1 = width=3D10)

       &nbs= p;         ->  Index Scan = using id_partkey_lineitem on lineitem  (cost=3D0.00..126.42 rows=3D31 = width=3D10)

       &nbs= p;            = ;   Index Cond: (l_partkey =3D $0)

(13 rows)

 

 

The number of tuples = in Lineitem is 180 000 000.

 

So my question is = what I have to do to increase the rate of the read which improve the execution of = the query?

I add that the server = is only dedicated for PostgreSQL.

 

Regards, =

------=_NextPart_000_0080_01C66521.2C316A80--