Re: postgresql.conf question... CPU spikes

On 04/09/2014 09:43 AM, Bala Venkat 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?
>
> Thanks for your help.

My guess would be you are running queries that dont use indexes, so its table scanning, and the tables all fit in memory.

You should run "explain analyze" on some of your queries and make sure you have good indexes.

You could also log slow queries, which might give some hints.

> Do you think , I have to reduce the effective_cache and work_mem for this?

I would doubt it.

-Andy