Re: cpu bound postgresql setup.

От: Kevin Grittner
Тема: Re: cpu bound postgresql setup.
Дата: ,
Msg-id: 4C23332E0200002500032A1C@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: Re: cpu bound postgresql setup.  (Rajesh Kumar Mallah)
Ответы: Re: cpu bound postgresql setup.  (Rajesh Kumar Mallah)
Список: 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", )

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.

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.

> 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.

> 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.

> 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.

> 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?

> 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.

-Kevin


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

От: "A.M."
Дата:
Сообщение: Re: PostgreSQL as a local in-memory cache
От: Pavel Stehule
Дата:
Сообщение: Re: PostgreSQL as a local in-memory cache