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 F12E511F8B7D for ; Fri, 21 Apr 2006 07:01:35 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -2.598 X-Spam-Level: X-Spam-Status: No, score=-2.598 required=5 tests=[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 Dsh8QG13E4ga for ; Fri, 21 Apr 2006 07:01:21 -0300 (ADT) X-Greylist: delayed 00:27:53.936718 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 7ACD211F8BD1 for ; Fri, 21 Apr 2006 07:01:12 -0300 (ADT) Received: from smtp1.voila.fr (smtp2.voila.fr [193.252.22.175]) by svr4.postgresql.org (Postfix) with ESMTP id AA7095AFA24 for ; Fri, 21 Apr 2006 09:33:19 +0000 (GMT) Received: from me-wanadoo.net (localhost [127.0.0.1]) by mwinf4112.voila.fr (SMTP Server) with ESMTP id AC6CE1C0043F for ; Fri, 21 Apr 2006 11:33:15 +0200 (CEST) Received: from wwinf4104 (wwinf4104 [172.22.152.31]) by mwinf4112.voila.fr (SMTP Server) with ESMTP id 9560F1C0040C for ; Fri, 21 Apr 2006 11:33:15 +0200 (CEST) X-ME-UUID: 20060421093315611.9560F1C0040C@mwinf4112.voila.fr Message-ID: <4673479.1145611995600.JavaMail.www@wwinf4104> From: luchot Reply-To: luchot@voila.fr To: pgsql-performance@postgresql.org Subject: Little use of CPU ( < 5%) Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_14449_10775149.1145611995598" X-Originating-IP: [129.185.75.10] X-Wum-Nature: EMAIL-NATURE X-WUM-FROM: |~| X-WUM-TO: |~| X-WUM-REPLYTO: |~| Date: Fri, 21 Apr 2006 11:33:15 +0200 (CEST) X-Archive-Number: 200604/495 X-Sequence-Number: 18579 ------=_Part_14449_10775149.1145611995598 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit 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, ------=_Part_14449_10775149.1145611995598 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

Hello ,

 

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

 

The distribution of Linux is Red Hat En= terprise Linux ES release 4 (Nahant Update 2) and the server is a bi-proces= sor Xeon 2.4GH= z with 1 Go of Ram and the size of the database files is about 60 Go.<= /o:p>

 

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

 

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

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

 

The vm stat command : 

procs ---------= --memory---------- ---swap-- -----io---- --system-- ----cpu----<= /FONT>

 r  <= /SPAN>b   swpd   free   buff  cache     &n= bsp;    si &nb= sp; so    bi=     bo   in      cs     us&nbs= p; sy  id wa

 0  1<= SPAN style=3D"mso-spacerun: yes">    184  16804  38104 933516  &n= bsp; 0    0&= nbsp; 3092    55  667   145&nbs= p; 12  4   71 14

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

 0&= nbsp; 1    1= 84  15008  38188 935252&= nbsp;   0  &nb= sp; 0  2688    92 1209    396  2  0  49 48

 

 

The config of PostgresQL is : 

 

 

shared_buffers =3D 16384            (128Mo)

work_mem =3D 65536          (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 :

 

Join Filter: ("inner".l_quantity < (= subplan))

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