Message-ID: <4E14F186.5020105@postnewspapers.com.au>
Date: Thu, 07 Jul 2011 07:36:38 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:5.0) Gecko/20110624 Thunderbird/5.0
MIME-Version: 1.0
To: Tomas Vondra <tv@fuzzy.cz>
CC: pgsql-performance@postgresql.org
Subject: Re: 100% CPU Utilization when we run queries.
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>
In-Reply-To: <4E14B1AD.40306@fuzzy.cz>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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/