Re: PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax - Mailing list pgsql-admin

From K P Manoj
Subject Re: PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax
Date
Msg-id CAMVgnQ5W9kieTZ4hPphYUGNv9-JW35QCaSWvqvOx5NsgwsTMjA@mail.gmail.com
Whole thread Raw
In response to Re: PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax  (Mel Llaguno <mllaguno@coverity.com>)
List pgsql-admin
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@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@coverity.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@sss.pgh.pa.us]
Sent: Wednesday, February 06, 2013 10:49 PM
To: Mel Llaguno
Cc: Pavan Deolasee; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax

Mel Llaguno <mllaguno@coverity.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

pgsql-admin by date:

Previous
From: Mel Llaguno
Date:
Subject: Re: PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax
Next
From: Vasiliy I Ozerov
Date:
Subject: Unexpected chunk size when doing dump