Re: PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax

Hi ,

You are mentioned SHMMAX larger value is no harm for the database , can i
keep this value as 100% of RAM ?

Right now we have two cluster in this server , one is having 8 GB and other
2 GB shared buffer .
But i am facing some issue , OS cache is filled frequently once i run some
query on database its uses 100 % of the processor also I am unable to
login the database.

Also query is taking more time as normal, seems to be I/O as normal.

DETAILS
========

kernel.shmmax = 68719476736

kernel.shmall = 4294967296

[postgres(at)xxxx ~]$ free -m
total used free shared buffers cached
Mem: 64433 48750 15682 0 240 38327
-/+ buffers/cache: 10182 54250
Swap: 6027 0 6027

On Thu, Feb 7, 2013 at 11:28 AM, Mel Llaguno <mllaguno(at)coverity(dot)com> wrote:

> Tom,
>
> Thanks for the response. I've been doing a lot of performance tuning for
> our customers and I've found that wiki link a life saver ;-)
>
> I'm trying to come up with a precise way to calculate the shmget() value
> which postgresql uses in the pgctl.log message when the kernel.shmmax is
> set too low. There are situations when knowing this exact value is useful
> as our customers are sometimes not as familiar with postgresql as we'd
> like. Being able to calculate this value from enabled settings in
> postgresql.conf would help us provide accurate guidance. As per Pavan's
> suggestion, I'm having a look at the src/backend/storage/ipc/ipci.c.
>
> Thanks,
>
> Mel
> ________________________________________
> From: Tom Lane [tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Wednesday, February 06, 2013 10:49 PM
> To: Mel Llaguno
> Cc: Pavan Deolasee; pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] PostgreSQL's share_buffer calculation using shmget()
> versus kernel.shmmax
>
> Mel Llaguno <mllaguno(at)coverity(dot)com> writes:
> > Thanks for your reply. I agree with your statement that you should set
> the configuration parameters first, but I would like to be able to
> calculate the SHMMAX value based on those parameters. This is particularly
> useful when suggesting postgresql.conf optimizations to our customers whose
> machine have a lot of RAM (64+GB). Having to guess this value is far from
> ideal; what I'd like is the formula used by postgresql that generates the
> shmget() value displayed in the pgctl.log.
>
> There's some rather old information in Table 17-2 here:
> http://www.postgresql.org/docs/9.2/static/kernel-resources.html#SYSVIPC
>
> As Pavan says, the shared_buffers term is usually the only one worth
> worrying about. The traditional advice is to not set that to more than
> about a quarter of your physical RAM, which would mean that this script
> you're using to set SHMMAX is leaving lots of headroom, which is
> perfectly OK. (AFAIK there is no penalty to setting SHMMAX larger than
> you need.)
>
> There's more info worth looking at here:
> https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
>
> regards, tom lane
>
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>