Re: postgresql.conf question... CPU spikes

[Venkata Balaji Nagothi <vbnpgc@xxxxxxxxx>]

On Thu, Apr 10, 2014 at 12:43 AM, Bala Venkat <akpgeek@xxxxxxxxx> 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