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 <mllag...@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 [t...@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 <mllag...@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 >