From: luchot <luchot@voila.fr>
To: pgsql-performance@postgresql.org
Subject: Little use of CPU ( < 5%)
Date: Fri, 21 Apr 2006 11:33:15 +0200 (CEST)
Message-ID: <4673479.1145611995600.JavaMail.www@wwinf4104> (raw)
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,
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: luchot@voila.fr
Subject: Re: Little use of CPU ( < 5%)
In-Reply-To: <4673479.1145611995600.JavaMail.www@wwinf4104>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox