From: Craig Ringer <craig@postnewspapers.com.au>
To: Royce Ausburn <royce@inomial.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: CPU bound
Date: Mon, 13 Dec 2010 19:10:37 +0800
Message-ID: <4D05FF2D.4030505@postnewspapers.com.au> (raw)
In-Reply-To: <F13DEE85-D114-4761-AD4D-924F1A741ACC@inomial.com>
References: <F13DEE85-D114-4761-AD4D-924F1A741ACC@inomial.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig@postnewspapers.com.au, royce@inomial.com
Subject: Re: CPU bound
In-Reply-To: <4D05FF2D.4030505@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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