Re: CPU bound

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
bottleneckin the DB, but occasionally it's not.  We're embarking on a new DB server project and it'd be helpful to
understandwhere 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