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
