From: | Rajesh Kumar Mallah <mallah(dot)rajesh(at)gmail(dot)com> |
---|---|
To: | Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: cpu bound postgresql setup. |
Date: | 2010-06-24 17:25:32 |
Message-ID: | AANLkTik0XxiLb-1doYhioxPKdrWjOrOcbIjxpH0us9YJ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Thu, Jun 24, 2010 at 8:57 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)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(dot)rajesh(at)gmail(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Rajesh Kumar Mallah | 2010-06-24 17:27:23 | Re: cpu bound postgresql setup. |
Previous Message | Jesper Krogh | 2010-06-24 17:02:45 | Re: Write performance |