Postgres Pro
Downloads
Home   >   mailing lists

Re: Question about postmaster's CPU usage - Mailing list pgsql-performance

From Kelphet Xiong
Subject Re: Question about postmaster's CPU usage
Date
Msg-id CANnt-4Y7bW9nRLFirKoHrkEKJ8S6kpJAKGQ=jCRFtOTgzFjPHQ@mail.gmail.com
Whole thread Raw
In response to Re: Question about postmaster's CPU usage  (Kevin Grittner <kgrittn@ymail.com>)
Responses Re: Question about postmaster's CPU usage
List pgsql-performance
Tree view 
Thanks a lot for replies from Kevin, Ken, and Ants Aasma. I really aappreciate your suggestions and comments.
 
My server configuration is two physical quad-core CPUs with hyper-threading enabled. 
Each CPU is Intel(R) Xeon(R) CPU E5620@2.40GHz. Physical memory is 16GB. 
I set shared_buffers as 4GB, effective_cache_size as 10GB and inventory table is around 500MB.
 
From the information provided by top command, although the row for postmaster shows that postmaster is using 100%CPU, 
the total CPU user time for the whole server never goes beyond 6.6%us. 

I guess it is because postgres only uses a single thread to read 
the data or “pushing the data around in RAM” according to Kevin’s statement. 
Then my question is actually why postgres can not use the remaining 93.4%CPU.


 
Btw, I also tried the command suggested by Ants Aasma, but got an error:
explain (analyze on, timing off) select * from inventory;
ERROR:  syntax error at or near "analyze"

LINE 1: explain (analyze on, timing off) select * from inventory;
                 ^
Thanks!

Best regards
Kelphet Xiong

On Thu, Mar 28, 2013 at 2:03 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
kelphet xiong <kelphet@gmail.com> wrote:
> When I use postgres and issue a simple sequential scan for a
> table inventory using query "select * from inventory;", I can see
> from "top" that postmaster is using 100% CPU, which limits the
> query execution time. My question is that, why CPU is the
> bottleneck here and what is postmaster doing? Is there any way to
> improve the performance? Thanks!
> explain analyze select * from inventory;
>
> Seq Scan on inventory  (cost=0.00..180937.00 rows=11745000 width=16) (actual time=0.005..1030.403 rows=11745000 loops=1)
>  Total runtime: 1750.889 ms
So it is reading and returning 11.7 million rows in about 1 second,
or about 88 nanoseconds (billionths of a second) per row.  You
can't be waiting for a hard drive for many of those reads, or it
would take a lot longer, so the bottleneck is the CPU pushing the
data around in RAM.  I'm not sure why 100% CPU usage would surprise
you.  Are you wondering why the CPU works on the query straight
through until it is done, rather than taking a break periodically
and letting the unfinished work sit there?
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

pgsql-performance by date:

Previous
From: Ants Aasma
Date:
Subject: Re: Question about postmaster's CPU usage
Next
From: Jeff Janes
Date:
Subject: Re: 9.2.3 upgrade reduced pgbench performance by 60%