Thread: postgresql.conf question... CPU spikes
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% .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.
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 gettingCPU 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
On Thu, Apr 10, 2014 at 12:43 AM, Bala Venkat <akpgeek@gmail.com> wrote:
In the config fileHi 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% .shared_buffers=2GB.work_mem = 128MBeffective_cache_size=48GBmaintaince_work_mem= 500MBmax_connections = 300When 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
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_topOn Fri, Apr 11, 2014 at 12:35 AM, Venkata Balaji Nagothi <vbnpgc@gmail.com> wrote:
On Thu, Apr 10, 2014 at 12:43 AM, Bala Venkat <akpgeek@gmail.com> wrote:In the config fileHi 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% .shared_buffers=2GB.work_mem = 128MBeffective_cache_size=48GBmaintaince_work_mem= 500MBmax_connections = 300When 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 NFujitsu Australia