Re: postgresql.conf question... CPU spikes

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