Re: postgresql.conf question... CPU spikes

As Andy mentioned. After tuning a query, every thing settled in . Now the
cpu utilization has come down a lot..

Thanks a lot for the help. I will certainly use the tool, pg_top

kind regards

On Fri, Apr 11, 2014 at 12:35 AM, Venkata Balaji Nagothi
<vbnpgc(at)gmail(dot)com>wrote:

>
> On Thu, Apr 10, 2014 at 12:43 AM, Bala Venkat <akpgeek(at)gmail(dot)com> wrote:
>
>> Hi all -
>>
>> We are running postgres 9.0 ( 32 bit ) + postgis 1.5.2 on Solaris
>> Sparc M5000 with 64GB . Recently we are getting CPU utilitzation to 99% .
>>
>> In the config file
>>
>>
>> shared_buffers=2GB.
>> work_mem = 128MB
>> effective_cache_size=48GB
>> maintaince_work_mem= 500MB
>> max_connections = 300
>>
>> When the CPU spikes happens, when I look at the pg_stat_activity log, the
>> queries where current_query not like '%<IDLE>%' are between 100-110.
>>
>> Do you think , I have to reduce the effective_cache and work_mem for this?
>>
>
> What does the load average say ? What about memory usage and disk IO ?
>
> Best way to look at CPU spikes issue is through "top" or equivalent
> utility which helps us know the PIDs for top resource consuming processes
> and the processes / sessions info using the same PIDs can be pulled in from
> pg_stat_activity.
>
> Another best way - which i felt is the best tool is - pg_top. pg_top is an
> excellent tool which help us identify the top resource consuming queries
> responsible for high CPU consumption or high DISK IO.
>
> Once you identify resource consuming processes or queries, things can be
> taken from there.
>
> Regards,
>
> Venkata Balaji N
> Fujitsu Australia
>