Re: Little use of CPU ( < 5%)

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(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of luchot
Sent: Friday, April 21, 2006 4:33 AM
To: pgsql-performance(at)postgresql(dot)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,