Rajesh, I had a similar situation a few weeks ago whereby performance all of a sudden decreased. The one tunable which resolved the problem in my case was increasing the number of checkpoint segments. After increasing them, everything went back to its normal state. > -----Original Message----- > From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance- > owner@xxxxxxxxxxxxxx] On Behalf Of Rajesh Kumar Mallah > Sent: Thursday, June 24, 2010 11:27 AM > To: Kevin Grittner > Cc: pgsql-performance@xxxxxxxxxxxxxx > Subject: Re: cpu bound postgresql setup. > > >i do not remember well but there is a system view that (i think) > >guides at what stage the marginal returns of increasing it > >starts disappearing , i had set it a few years back. > > Sorry the above comment was regarding setting shared_buffers > not effective_cache_size. > > > > On Thu, Jun 24, 2010 at 10:55 PM, Rajesh Kumar Mallah > <mallah.rajesh@xxxxxxxxx> wrote: > > On Thu, Jun 24, 2010 at 8:57 PM, Kevin Grittner > > <Kevin.Grittner@xxxxxxxxxxxx> wrote: > >> I'm not clear whether you still have a problem, or whether the > >> changes you mention solved your issues. I'll comment on potential > >> issues that leap out at me. > > > > It shall require more observation to know if the "problem" is solved. > > my "problem" was high load average in the server . We find that > > when ldavg is between 10-20 responses of applications were acceptable > > ldavg > 40 makes things slower. > > > > What prompted me to post to list is that the server transitioned from > > being IO bound to CPU bound and 90% of syscalls being > > lseek(XXX, 0, SEEK_END) = YYYYYYY > > > >> > >> Rajesh Kumar Mallah <mallah.rajesh@xxxxxxxxx> wrote: > >> > >>> 3. we use xfs and our controller has BBU , we changed barriers=1 > >>> to barriers=0 as i learnt that having barriers=1 on xfs and fsync > >>> as the sync method, the advantage of BBU is lost unless barriers > >>> is = 0 (correct me if my understanding is wrong) > >> > >> We use noatime,nobarrier in /etc/fstab. I'm not sure where you're > >> setting that, but if you have a controller with BBU, you want to set > >> it to whichever disables write barriers. > > > > as per suggestion in discussions on some other thread I set it > > in /etc/fstab. > > > >> > >>> max_connections = 300 > >> > >> As I've previously mentioned, I would use a connection pool, in > >> which case this wouldn't need to be that high. > > > > We do use connection pooling provided to mod_perl server > > via Apache::DBI::Cache. If i reduce this i *get* "too many > > connections from non-superuser ... " error. Will pgpool - I/II > > still applicable in this scenario ? > > > > > >> > >>> work_mem = 4GB > >> > >> That's pretty high. That much memory can be used by each active > >> connection, potentially for each of several parts of the active > >> query on each connection. You should probably set this much lower > >> in postgresql.conf and boost it if necessary for individual queries. > > > > hmmm.. it was 8GB for many months ! > > > > i shall reduce it further, but will it not result in usage of too > many > > temp files > > and saturate i/o? > > > > > > > >> > >>> effective_cache_size = 18GB > >> > >> With 32GB RAM on the machine, I would probably set this higher -- > >> somewhere in the 24GB to 30GB range, unless you have specific > >> reasons to believe otherwise. It's not that critical, though. > > > > i do not remember well but there is a system view that (i think) > > guides at what stage the marginal returns of increasing it > > starts disappearing , i had set it a few years back. > > > > > >> > >>> add_missing_from = on > >> > >> Why? There has been discussion of eliminating this option -- do you > >> have queries which rely on the non-standard syntax this enables? > > > > unfortunately yes. > > > >> > >>> Also i would like to apologize that some of the discussions on > >>> this problem inadvertently became private between me & kevin. > >> > >> Oops. I failed to notice that. Thanks for bringing it back to the > >> list. (It's definitely in your best interest to keep it in front of > >> all the other folks here, some of whom regularly catch things I miss > >> or get wrong.) > >> > >> If you still do have slow queries, please follow up with details. > > > > > > I have now set log_min_duration_statement = 5000 > > and there are few queries that come to logs. > > > > please comment on the connection pooling aspect. > > > > Warm Regards > > Rajesh Kumar Mallah. > > > >> > >> -Kevin > >> > > > > -- > Sent via pgsql-performance mailing list (pgsql- > performance@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
