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/