Thread: 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,
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@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of luchot
Sent: Friday, April 21, 2006 4:33 AM
To: pgsql-performance@postgresql.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,
Hi, I hope you can help me...there's something wrong going on my db server (OS. GNU/linux White box)... here's the problem... The amount of Inactive memory Grows unlimited .... this happens only when Postgresql (8.1.1) is running... after a few days it "eats" all the RAM memory ... so I've have to restart the server to free RAM.... Here's the parametres actually active in then postgresql.conf file max_connections = 100 authentication_timeout = 60 password_encryption = on shared_buffers = 1000 work_mem = 131076 maintenance_work_mem = 262152 redirect_stderr = on log_directory = 'pg_log' log_truncate_on_rotation = on log_rotation_age = 1440 log_rotation_size = 0 lc_messages = 'es_ES.UTF-8' lc_monetary = 'es_ES.UTF-8' lc_numeric = 'es_ES.UTF-8' lc_time = 'es_ES.UTF-8' Below you can find the content of meminfo file with 18 users connected (average during working days) and the server has benn running during 2 days 2:19 #cat /proc/meminfo MemTotal: 2074844 kB MemFree: 1371660 kB Buffers: 61748 kB Cached: 555492 kB SwapCached: 0 kB Active: 348604 kB Inactive: 305876 kB HighTotal: 1179440 kB HighFree: 579904 kB LowTotal: 895404 kB LowFree: 791756 kB SwapTotal: 2048248 kB SwapFree: 2048248 kB Dirty: 260 kB Writeback: 0 kB Mapped: 54824 kB Slab: 35756 kB Committed_AS: 117624 kB PageTables: 3404 kB VmallocTotal: 106488 kB VmallocUsed: 3356 kB VmallocChunk: 102920 kB HugePages_Total: 0 HugePages_Free: 0 Hugepagesize: 2048 kB thanks in advance for your help, Alvaro Arcila pd: sorry for my english It's not very good, I hope I'm clear....
ALVARO ARCILA wrote: > Hi, > > I hope you can help me...there's something wrong going on my db server (OS. > GNU/linux White box)... here's the problem... > > The amount of Inactive memory Grows unlimited .... this happens only when > Postgresql (8.1.1) is running... after a few days it "eats" all the RAM > memory ... so I've have to restart the server to free RAM.... This is normal Unix behavior. Leave it running for a few more days and you'll that nothing wrong happens. Why do you think you need "free" memory? Only if the swap memory start getting used a lot you need to worry about memory consumption. You should upgrade to 8.1.3 BTW. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Hi, >> The amount of Inactive memory Grows unlimited .... this happens only when >> Postgresql (8.1.1) is running... after a few days it "eats" all the RAM >> memory ... so I've have to restart the server to free RAM.... > > This is normal Unix behavior. Leave it running for a few more days and > you'll that nothing wrong happens. Why do you think you need "free" > memory? > > Only if the swap memory start getting used a lot you need to worry about > memory consumption. > > You should upgrade to 8.1.3 BTW. Also, shared_buffers seem too low and work_mem too much high for your setup: > shared_buffers = 1000 > work_mem = 131076 You should really raise shared_buffers and decrease work_mem: 1000 shared_buffers is probably too conservative. On the other hand 128MB per sort could be eating your RAM quickly with 18 users connected. Best regards -- Matteo Beccati http://phpadsnew.com http://phppgads.com