Re: Little use of CPU ( < 5%)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of luchot
Sent: Friday, April 21, 2006 4:33 AM
To: pgsql-performance@xxxxxxxxxxxxxx
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,


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux