Message-ID: <4D05FF2D.4030505@postnewspapers.com.au>
Date: Mon, 13 Dec 2010 19:10:37 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.9) Gecko/20100921 Fedora/3.1.4-1.fc14 Thunderbird/3.1.4
MIME-Version: 1.0
To: Royce Ausburn <royce@inomial.com>
CC: pgsql-performance@postgresql.org
Subject: Re: CPU bound
References: <F13DEE85-D114-4761-AD4D-924F1A741ACC@inomial.com>
In-Reply-To: <F13DEE85-D114-4761-AD4D-924F1A741ACC@inomial.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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