Skip site navigation (1) Skip section navigation (2)

Little use of CPU ( < 5%)

From: luchot <luchot(at)voila(dot)fr>
To: pgsql-performance(at)postgresql(dot)org
Subject: Little use of CPU ( < 5%)
Date: 2006-04-21 09:33:15
Message-ID: 4673479.1145611995600.JavaMail.www@wwinf4104 (view raw or flat)
Thread:
Lists: pgsql-performance
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, 

Responses

pgsql-performance by date

Next:From: FriendsDate: 2006-04-21 10:28:48
Subject: security for row level but not based on Database user's login
Previous:From: Jan DittmerDate: 2006-04-21 08:37:10
Subject: Better way to write aggregates?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group