pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion about postmaster's CPU usage
6+ messages / 6 participants
[nested] [flat]
* Question about postmaster's CPU usage
@ 2013-03-28 19:07 kelphet xiong <kelphet@gmail.com>
2013-03-28 21:03 ` Re: Question about postmaster's CPU usage Kevin Grittner <kgrittn@ymail.com>
2013-03-30 23:45 ` Re: Question about postmaster's CPU usage Ants Aasma <ants.aasma@eesti.ee>
0 siblings, 2 replies; 6+ messages in thread
From: kelphet xiong @ 2013-03-28 19:07 UTC (permalink / raw)
To: pgsql-performance
Hi all,
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!
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
15401 postgres 20 0 4371m 517m 515m R 99.8 3.2 0:30.14 postmaster
Query: select * from inventory;
explain analyze select * from inventory;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
------------
Seq Scan on inventory (cost=0.00..180937.00 rows=11745000 width=16)
(actual time=0.005..1030.403 rows=117450
00 loops=1)
Total runtime: 1750.889 ms
(2 rows)
Table "public.inventory"
Column | Type | Modifiers
----------------------+---------+-----------
inv_date_sk | integer | not null
inv_item_sk | integer | not null
inv_warehouse_sk | integer | not null
inv_quantity_on_hand | integer |
Indexes:
"inventory_pkey" PRIMARY KEY, btree (inv_date_sk, inv_item_sk,
inv_warehouse_sk)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Question about postmaster's CPU usage
2013-03-28 19:07 Question about postmaster's CPU usage kelphet xiong <kelphet@gmail.com>
@ 2013-03-28 21:03 ` Kevin Grittner <kgrittn@ymail.com>
2013-03-28 21:20 ` Re: Question about postmaster's CPU usage ktm@rice.edu <ktm@rice.edu>
2013-03-31 04:00 ` Re: Question about postmaster's CPU usage Kelphet Xiong <kelphet@gmail.com>
1 sibling, 2 replies; 6+ messages in thread
From: Kevin Grittner @ 2013-03-28 21:03 UTC (permalink / raw)
To: kelphet xiong <kelphet@gmail.com>; pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Question about postmaster's CPU usage
2013-03-28 19:07 Question about postmaster's CPU usage kelphet xiong <kelphet@gmail.com>
2013-03-28 21:03 ` Re: Question about postmaster's CPU usage Kevin Grittner <kgrittn@ymail.com>
@ 2013-03-28 21:20 ` ktm@rice.edu <ktm@rice.edu>
1 sibling, 0 replies; 6+ messages in thread
From: ktm@rice.edu @ 2013-03-28 21:20 UTC (permalink / raw)
To: Kevin Grittner <kgrittn@ymail.com>; +Cc: kelphet xiong <kelphet@gmail.com>; pgsql-performance
On Thu, Mar 28, 2013 at 02:03:42PM -0700, Kevin Grittner 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
>
Alternatively, purchase a faster CPU if CPU is the bottleneck as it
is in this case or partition the work into parallel queuries that can
each use a processor.
Regards,
Ken
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Question about postmaster's CPU usage
2013-03-28 19:07 Question about postmaster's CPU usage kelphet xiong <kelphet@gmail.com>
2013-03-28 21:03 ` Re: Question about postmaster's CPU usage Kevin Grittner <kgrittn@ymail.com>
@ 2013-03-31 04:00 ` Kelphet Xiong <kelphet@gmail.com>
2013-04-01 13:42 ` Re: Question about postmaster's CPU usage Merlin Moncure <mmoncure@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Kelphet Xiong @ 2013-03-31 04:00 UTC (permalink / raw)
To: Kevin Grittner <kgrittn@ymail.com>; ktm@rice.edu; ants.aasma@eesti.ee; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Question about postmaster's CPU usage
2013-03-28 19:07 Question about postmaster's CPU usage kelphet xiong <kelphet@gmail.com>
2013-03-28 21:03 ` Re: Question about postmaster's CPU usage Kevin Grittner <kgrittn@ymail.com>
2013-03-31 04:00 ` Re: Question about postmaster's CPU usage Kelphet Xiong <kelphet@gmail.com>
@ 2013-04-01 13:42 ` Merlin Moncure <mmoncure@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Merlin Moncure @ 2013-04-01 13:42 UTC (permalink / raw)
To: Kelphet Xiong <kelphet@gmail.com>; +Cc: Kevin Grittner <kgrittn@ymail.com>; pgsql-performance
On Sat, Mar 30, 2013 at 11:00 PM, Kelphet Xiong <kelphet@gmail.com> wrote:
> 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.
postgres can use an arbitrary amount of threads to read data, but only
one per database connection.
> 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;
>
> ^
Ability to manipulate timing was added in 9.2.
merlin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Question about postmaster's CPU usage
2013-03-28 19:07 Question about postmaster's CPU usage kelphet xiong <kelphet@gmail.com>
@ 2013-03-30 23:45 ` Ants Aasma <ants.aasma@eesti.ee>
1 sibling, 0 replies; 6+ messages in thread
From: Ants Aasma @ 2013-03-30 23:45 UTC (permalink / raw)
To: kelphet xiong <kelphet@gmail.com>; +Cc: pgsql-performance
On Mar 28, 2013 9:07 PM, "kelphet xiong" <kelphet@gmail.com> wrote:
> explain analyze select * from inventory;
> QUERY PLAN
>
>
--------------------------------------------------------------------------------------------------------------
> ------------
> Seq Scan on inventory (cost=0.00..180937.00 rows=11745000 width=16)
(actual time=0.005..1030.403 rows=117450
> 00 loops=1)
> Total runtime: 1750.889 ms
> (2 rows)
A large fraction of that time, if not most is due to timing overhead. You
can try the same query without timing by using explain (analyze on, timing
off) select * from inventory;
Regards,
Ants Aasma
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2013-04-01 13:42 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-03-28 19:07 Question about postmaster's CPU usage kelphet xiong <kelphet@gmail.com>
2013-03-28 21:03 ` Kevin Grittner <kgrittn@ymail.com>
2013-03-28 21:20 ` ktm@rice.edu <ktm@rice.edu>
2013-03-31 04:00 ` Kelphet Xiong <kelphet@gmail.com>
2013-04-01 13:42 ` Merlin Moncure <mmoncure@gmail.com>
2013-03-30 23:45 ` Ants Aasma <ants.aasma@eesti.ee>
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