From: Craig Ringer <craig@postnewspapers.com.au>
To: Tomas Vondra <tv@fuzzy.cz>
Cc: pgsql-performance@postgresql.org
Subject: Re: 100% CPU Utilization when we run queries.
Date: Thu, 07 Jul 2011 07:36:38 +0800
Message-ID: <4E14F186.5020105@postnewspapers.com.au> (raw)
In-Reply-To: <4E14B1AD.40306@fuzzy.cz>
References: <1307506768241-4465765.post@n5.nabble.com>
<4DEF037E.9020105@postnewspapers.com.au>
<1307691557007-4475458.post@n5.nabble.com>
<4DF21007.2010807@postnewspapers.com.au>
<1308198900426-4493567.post@n5.nabble.com>
<1309959046671-4556775.post@n5.nabble.com>
<4E14B1AD.40306@fuzzy.cz>
On 7/07/2011 3:04 AM, Tomas Vondra wrote:
> That is done by sorting the data, and sorting is very CPU intensive task
> usually. So the fact that the CPU is 100% utilized is kind of expected
> in this case. So that's a feature, not a bug.
>
> In general each process is hitting some bottleneck. It might be an I/O,
> it might be a CPU, it might be something less visible (memory bandwidth
> or something like that).
>
This is worth stressing. PostgreSQL will always use as much CPU time and
disk I/O as it can to get a job done as quickly as possible. Because
most queries need more CPU and less disk, or more disk and less CPU,
you'll usually find that PostgreSQL maxes out one or the other but not
both. People monitor CPU use more than disk use, so they tend to notice
when CPU use is maxed out, but Pg maxes out your disk a lot too.
This is normal, and a good thing. If Pg didn't max out your CPU or disk,
queries would be slower. If you want to make things other than
PostgreSQL happen faster at the expense of slowing down queries, you can
use your operating system's process priority mechanisms to give
PostgreSQL a lower priority for access to CPU and/or disk. That will
still allow PostgreSQL to use all your CPU and disk when nothing else
wants it, but will let other programs use it in preference to PostgreSQL
if they need it.
The same thing applies to memory use. People notice that their operating
system reports very little "free" memory and get worried about it. The
truth is that your OS should never have much free memory, because that
memory is not being used for anything useful. It usually keeps disk
cache in memory when it's not needed for anything else, and trying to
make more "free" memory clears out the disk cache, making the computer
slower. CPU use is a bit like that - it's not doing any good idle, so if
nothing else needs it more you might as well use it.
If you're on linux, you can use the "nice", "renice" and "ionice"
programs to control CPU and disk access priority.
--
Craig Ringer
POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088 Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/
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, tv@fuzzy.cz
Subject: Re: 100% CPU Utilization when we run queries.
In-Reply-To: <4E14F186.5020105@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