Re: cpu bound postgresql setup. - Mailing list pgsql-performance

>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@gmail.com> wrote:
> On Thu, Jun 24, 2010 at 8:57 PM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov> 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@gmail.com> 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
>>
>