pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCPU bound
12+ messages / 8 participants
[nested] [flat]
* CPU bound
@ 2010-12-13 02:43 Royce Ausburn <royce@inomial.com>
0 siblings, 3 replies; 12+ messages in thread
From: Royce Ausburn @ 2010-12-13 02:43 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I notice that when restoring a DB on a laptop with an SDD, typically postgres is maxing out a CPU - even during a COPY. I wonder, what is postgres usually doing with the CPU? I would have thought the disk would usually be the bottleneck in the DB, but occasionally it's not. We're embarking on a new DB server project and it'd be helpful to understand where the CPU is likely to be the bottleneck.
Cheers,
--Royce
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: CPU bound
@ 2010-12-13 11:10 Craig Ringer <craig@postnewspapers.com.au>
parent: Royce Ausburn <royce@inomial.com>
2 siblings, 0 replies; 12+ messages in thread
From: Craig Ringer @ 2010-12-13 11:10 UTC (permalink / raw)
To: Royce Ausburn <royce@inomial.com>; +Cc: pgsql-performance
On 12/13/2010 10:43 AM, Royce Ausburn wrote:
> Hi all,
>
> I notice that when restoring a DB on a laptop with an SDD, typically postgres is maxing out a CPU - even during a COPY. I wonder, what is postgres usually doing with the CPU? I would have thought the disk would usually be the bottleneck in the DB, but occasionally it's not. We're embarking on a new DB server project and it'd be helpful to understand where the CPU is likely to be the bottleneck.
A few thoughts:
- Pg isn't capable of using more than one core for a single task, so if
you have one really big job, you'll more easily start struggling on CPU.
Restores appear to be a pain point here, though recent work has been
done to address that.
- Even with pg_dump/pg_restore's parallel restore, you can't be using
more than one core to do work for a single COPY or other individual
operation. You can only parallelize down to the table level at the moment.
- Pg's design has always focused on rotating media. It can make sense to
trade increased CPU costs for reduced I/O when disk storage is slower
relative to CPU/RAM. There aren't, AFAIK, many controls beyond the
random/seq io knobs to get Pg to try to save CPU at the cost of more I/O
when opportunities to do so appear.
- Pg's CPU load depends a lot on the data types and table structures in
use. What're your tables like? Do they have indexes added at the end, or
are they created with indexes then populated with rows? The former is
MUCH faster. Are they full of NUMERIC fields? Those seem to be
incredibly slow compared to int/float/etc, which is hardly surprising
given their storage and how they work.
--
Craig Ringer
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: CPU bound
@ 2010-12-13 18:59 Josh Berkus <josh@agliodbs.com>
parent: Royce Ausburn <royce@inomial.com>
2 siblings, 1 reply; 12+ messages in thread
From: Josh Berkus @ 2010-12-13 18:59 UTC (permalink / raw)
To: Royce Ausburn <royce@inomial.com>; +Cc: pgsql-performance
On 12/12/10 6:43 PM, Royce Ausburn wrote:
> Hi all,
>
> I notice that when restoring a DB on a laptop with an SDD, typically postgres is maxing out a CPU - even during a COPY. I wonder, what is postgres usually doing with the CPU? I would have thought the disk would usually be the bottleneck in the DB, but occasionally it's not. We're embarking on a new DB server project and it'd be helpful to understand where the CPU is likely to be the bottleneck.
That's pretty normal; as soon as you get decent disk, especially
something like an SSD with a RAM cache, you become CPU-bound. COPY does
a LOT of parsing and data manipulation. Index building, of course, is
almost pure CPU if you have a decent amount of RAM available.
If you're restoring from a pg_dump file, and have several cores
available, I suggest using parallel pg_restore.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: CPU bound
@ 2010-12-13 21:03 Royce Ausburn <royce@inomial.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 12+ messages in thread
From: Royce Ausburn @ 2010-12-13 21:03 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; Craig Ringer <craig@postnewspapers.com.au>; +Cc: pgsql-performance
Thanks guys - interesting.
On 14/12/2010, at 5:59 AM, Josh Berkus wrote:
> On 12/12/10 6:43 PM, Royce Ausburn wrote:
>> Hi all,
>>
>> I notice that when restoring a DB on a laptop with an SDD, typically postgres is maxing out a CPU - even during a COPY. I wonder, what is postgres usually doing with the CPU? I would have thought the disk would usually be the bottleneck in the DB, but occasionally it's not. We're embarking on a new DB server project and it'd be helpful to understand where the CPU is likely to be the bottleneck.
>
> That's pretty normal; as soon as you get decent disk, especially
> something like an SSD with a RAM cache, you become CPU-bound. COPY does
> a LOT of parsing and data manipulation. Index building, of course, is
> almost pure CPU if you have a decent amount of RAM available.
>
> If you're restoring from a pg_dump file, and have several cores
> available, I suggest using parallel pg_restore.
>
>
> --
> -- Josh Berkus
> PostgreSQL Experts Inc.
> http://www.pgexperts.com
>
> --
> 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] 12+ messages in thread
* Re: CPU bound
@ 2010-12-20 00:57 James Cloos <cloos@jhcloos.com>
parent: Royce Ausburn <royce@inomial.com>
2 siblings, 1 reply; 12+ messages in thread
From: James Cloos @ 2010-12-20 00:57 UTC (permalink / raw)
To: Royce Ausburn <royce@inomial.com>; +Cc: pgsql-performance
>>>>> "RA" == Royce Ausburn <royce@inomial.com> writes:
RA> I notice that when restoring a DB on a laptop with an SDD,
RA> typically postgres is maxing out a CPU - even during a COPY.
The time the CPUs spend waiting on system RAM shows up as CPU
time, not as Wait time. It could be just that the SSD is fast
enough that the RAM is now the bottleneck, although parsing
and text<=>binary conversions (especially for integers, reals
and anything stored as an integer) also can be CPU-intensive.
-JimC
--
James Cloos <cloos@jhcloos.com> OpenPGP: 1024D/ED7DAEA6
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: CPU bound
@ 2010-12-20 06:47 Mladen Gogala <mladen.gogala@vmsinfo.com>
parent: James Cloos <cloos@jhcloos.com>
0 siblings, 2 replies; 12+ messages in thread
From: Mladen Gogala @ 2010-12-20 06:47 UTC (permalink / raw)
To: James Cloos <cloos@jhcloos.com>; +Cc: Royce Ausburn <royce@inomial.com>; pgsql-performance
On 12/19/2010 7:57 PM, James Cloos wrote:
>>>>>> "RA" == Royce Ausburn<royce@inomial.com> writes:
> RA> I notice that when restoring a DB on a laptop with an SDD,
> RA> typically postgres is maxing out a CPU - even during a COPY.
>
> The time the CPUs spend waiting on system RAM shows up as CPU
> time, not as Wait time. It could be just that the SSD is fast
> enough that the RAM is now the bottleneck, although parsing
> and text<=>binary conversions (especially for integers, reals
> and anything stored as an integer) also can be CPU-intensive.
>
> -JimC
Good time accounting is the most compelling reason for having a wait
event interface, like Oracle. Without the wait event interface, one
cannot really tell where the time is spent, at least not without
profiling the database code, which is not an option for a production
database.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: CPU bound
@ 2010-12-20 15:33 James Cloos <cloos@jhcloos.com>
parent: Mladen Gogala <mladen.gogala@vmsinfo.com>
1 sibling, 2 replies; 12+ messages in thread
From: James Cloos @ 2010-12-20 15:33 UTC (permalink / raw)
To: Mladen Gogala <mladen.gogala@vmsinfo.com>; +Cc: Royce Ausburn <royce@inomial.com>; pgsql-performance
>>>>> "MG" == Mladen Gogala <mladen.gogala@vmsinfo.com> writes:
MG> Good time accounting is the most compelling reason for having a wait
MG> event interface, like Oracle. Without the wait event interface, one
MG> cannot really tell where the time is spent, at least not without
MG> profiling the database code, which is not an option for a production
MG> database.
And how exactly, given that the kernel does not know whether the CPU is
active or waiting on ram, could an application do so?
-JimC
--
James Cloos <cloos@jhcloos.com> OpenPGP: 1024D/ED7DAEA6
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: CPU bound
@ 2010-12-20 15:48 Kenneth Marshall <ktm@rice.edu>
parent: James Cloos <cloos@jhcloos.com>
1 sibling, 1 reply; 12+ messages in thread
From: Kenneth Marshall @ 2010-12-20 15:48 UTC (permalink / raw)
To: James Cloos <cloos@jhcloos.com>; +Cc: Mladen Gogala <mladen.gogala@vmsinfo.com>; Royce Ausburn <royce@inomial.com>; pgsql-performance
On Mon, Dec 20, 2010 at 10:33:26AM -0500, James Cloos wrote:
> >>>>> "MG" == Mladen Gogala <mladen.gogala@vmsinfo.com> writes:
>
> MG> Good time accounting is the most compelling reason for having a wait
> MG> event interface, like Oracle. Without the wait event interface, one
> MG> cannot really tell where the time is spent, at least not without
> MG> profiling the database code, which is not an option for a production
> MG> database.
>
> And how exactly, given that the kernel does not know whether the CPU is
> active or waiting on ram, could an application do so?
>
Exactly. I have only seen this data from hardware emulators. It would
be nice to have... :)
Ken
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: CPU bound
@ 2010-12-20 17:59 Jeremy Harris <jgh@wizmail.org>
parent: Kenneth Marshall <ktm@rice.edu>
0 siblings, 0 replies; 12+ messages in thread
From: Jeremy Harris @ 2010-12-20 17:59 UTC (permalink / raw)
To: pgsql-performance
On 2010-12-20 15:48, Kenneth Marshall wrote:
>> And how exactly, given that the kernel does not know whether the CPU is
>> active or waiting on ram, could an application do so?
>>
>
> Exactly. I have only seen this data from hardware emulators. It would
> be nice to have... :)
There's no reason that the cpu hardware couldn't gather such, and
IMHO it's be dead useful, at least at the outermost cache level
(preferably separately at each level). But people have trouble
understanding vmstat already....
Note that dtrace *can* get to the cpu performance counters,
just that the kernel doesn't routinely account for all that info
per-process as routine. I'd expect IBM to have equivalent
facilities.
--
Jeremy
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: CPU bound
@ 2010-12-21 11:34 Mladen Gogala <mladen.gogala@vmsinfo.com>
parent: James Cloos <cloos@jhcloos.com>
1 sibling, 0 replies; 12+ messages in thread
From: Mladen Gogala @ 2010-12-21 11:34 UTC (permalink / raw)
To: James Cloos <cloos@jhcloos.com>; +Cc: Royce Ausburn <royce@inomial.com>; pgsql-performance
On 12/20/2010 10:33 AM, James Cloos wrote:
>
> And how exactly, given that the kernel does not know whether the CPU is
> active or waiting on ram, could an application do so?
>
> -JimC
That particular aspect will remain hidden, it's a domain of the hardware
architecture. Nevertheless, there are things like waiting on I/O or
waiting on lock, that would be extremely useful.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: CPU bound
@ 2011-01-02 21:57 Jim Nasby <Jim@Nasby.net>
parent: Mladen Gogala <mladen.gogala@vmsinfo.com>
1 sibling, 1 reply; 12+ messages in thread
From: Jim Nasby @ 2011-01-02 21:57 UTC (permalink / raw)
To: Mladen Gogala <mladen.gogala@vmsinfo.com>; +Cc: James Cloos <cloos@jhcloos.com>; Royce Ausburn <royce@inomial.com>; pgsql-performance
On Dec 20, 2010, at 12:47 AM, Mladen Gogala wrote:
> Good time accounting is the most compelling reason for having a wait event interface, like Oracle. Without the wait event interface, one cannot really tell where the time is spent, at least not without profiling the database code, which is not an option for a production database.
Out of curiosity, have you tried using the information that Postgres exposes to dtrace? I suspect it comes close to what you can get directly out of Oracle...
--
Jim C. Nasby, Database Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: CPU bound
@ 2011-01-03 16:17 Mladen Gogala <mladen.gogala@vmsinfo.com>
parent: Jim Nasby <Jim@Nasby.net>
0 siblings, 0 replies; 12+ messages in thread
From: Mladen Gogala @ 2011-01-03 16:17 UTC (permalink / raw)
To: Jim Nasby <Jim@Nasby.net>; +Cc: James Cloos <cloos@jhcloos.com>; Royce Ausburn <royce@inomial.com>; pgsql-performance
Jim Nasby wrote:
> On Dec 20, 2010, at 12:47 AM, Mladen Gogala wrote:
>
>> Good time accounting is the most compelling reason for having a wait event interface, like Oracle. Without the wait event interface, one cannot really tell where the time is spent, at least not without profiling the database code, which is not an option for a production database.
>>
>
> Out of curiosity, have you tried using the information that Postgres exposes to dtrace? I suspect it comes close to what you can get directly out of Oracle...
> --
> Jim C. Nasby, Database Architect jim@nasby.net
> 512.569.9461 (cell) http://jim.nasby.net
>
>
>
>
No, I haven't but I looked it in the documentation. I surmise, however,
that I will have to build my software with "--enable-dtrace", which is
not enabled by default. This certainly looks promising.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2011-01-03 16:17 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-13 02:43 CPU bound Royce Ausburn <royce@inomial.com>
2010-12-13 11:10 ` Craig Ringer <craig@postnewspapers.com.au>
2010-12-13 18:59 ` Josh Berkus <josh@agliodbs.com>
2010-12-13 21:03 ` Royce Ausburn <royce@inomial.com>
2010-12-20 00:57 ` James Cloos <cloos@jhcloos.com>
2010-12-20 06:47 ` Mladen Gogala <mladen.gogala@vmsinfo.com>
2010-12-20 15:33 ` James Cloos <cloos@jhcloos.com>
2010-12-20 15:48 ` Kenneth Marshall <ktm@rice.edu>
2010-12-20 17:59 ` Jeremy Harris <jgh@wizmail.org>
2010-12-21 11:34 ` Mladen Gogala <mladen.gogala@vmsinfo.com>
2011-01-02 21:57 ` Jim Nasby <Jim@Nasby.net>
2011-01-03 16:17 ` Mladen Gogala <mladen.gogala@vmsinfo.com>
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