Re: cpu bound postgresql setup.

От: Benjamin Krajmalnik
Тема: Re: cpu bound postgresql setup.
Дата: ,
Msg-id: F4E6A2751A2823418A21D4A160B68988614647@fletch.stackdump.local
(см: обсуждение, исходный текст)
Ответ на: Re: cpu bound postgresql setup.  (Rajesh Kumar Mallah)
Ответы: Re: cpu bound postgresql setup.  (Bruce Momjian)
Список: pgsql-performance

Скрыть дерево обсуждения

Re: cpu bound postgresql setup. Firstly many thanks for responding. I am concerned because the load averages have increased and users complaining of slowness. I do not change settings frequenly. I was curious if there is any half dead component in th  (Rajesh Kumar Mallah, )
 Re: cpu bound postgresql setup.  ("Kevin Grittner", )
 Re: cpu bound postgresql setup.  (Rajesh Kumar Mallah, )
  Re: cpu bound postgresql setup.  ("Kevin Grittner", )
   Re: cpu bound postgresql setup.  (Rajesh Kumar Mallah, )
    Re: cpu bound postgresql setup.  (Rajesh Kumar Mallah, )
     Re: cpu bound postgresql setup.  ("Benjamin Krajmalnik", )
      Re: cpu bound postgresql setup.  (Bruce Momjian, )
       Re: cpu bound postgresql setup.  ("Benjamin Krajmalnik", )
        Re: cpu bound postgresql setup.  (Bruce Momjian, )
    Re: cpu bound postgresql setup.  (Alvaro Herrera, )
    Re: cpu bound postgresql setup.  ("Kevin Grittner", )

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: 
[mailto:pgsql-performance-
> ] On Behalf Of Rajesh Kumar Mallah
> Sent: Thursday, June 24, 2010 11:27 AM
> To: Kevin Grittner
> Cc: 
> Subject: Re: [PERFORM] 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
> <> wrote:
> > On Thu, Jun 24, 2010 at 8:57 PM, Kevin Grittner
> > <> 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 <> 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-
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance


В списке pgsql-performance по дате сообщения:

От: Alvaro Herrera
Дата:
Сообщение: Re: System tables screwed up? (WAS requested shared memory size overflows size_t)
От: Craig James
Дата:
Сообщение: Re: Occasional giant spikes in CPU load