pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax
9+ messages / 5 participants
[nested] [flat]
* PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax
@ 2013-02-07 00:58 Mel Llaguno <mllaguno@coverity.com>
0 siblings, 2 replies; 9+ messages in thread
From: Mel Llaguno @ 2013-02-07 00:58 UTC (permalink / raw)
To: pgsql-admin
All,
I'm wondering about how postgresql calculates the value for shared buffers as I see some discrepancies with what the following script provides versus what is recommended in the pgctl.log when the database fails to start.
#!/bin/bash
# simple shmsetup script
page_size=`getconf PAGE_SIZE`
phys_pages=`getconf _PHYS_PAGES`
shmall=`expr $phys_pages / 2`
shmmax=`expr $shmall \* $page_size`
echo kernel.shmmax = $shmmax
echo kernel.shmall = $shmall
Any pointers which would explain these differences would be greatly appreciate. Ultimately, I'd like to calculate the expected kernel.shmmax which matches postgresql's shmget() call.
TIA,
Mel
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax
@ 2013-02-07 05:12 Pavan Deolasee <pavan.deolasee@gmail.com>
parent: Mel Llaguno <mllaguno@coverity.com>
1 sibling, 1 reply; 9+ messages in thread
From: Pavan Deolasee @ 2013-02-07 05:12 UTC (permalink / raw)
To: Mel Llaguno <mllaguno@coverity.com>; +Cc: pgsql-admin
On Thu, Feb 7, 2013 at 6:28 AM, Mel Llaguno <mllaguno@coverity.com> wrote:
> All,
>
> I'm wondering about how postgresql calculates the value for shared buffers
> as I see some discrepancies with what the following script provides versus
> what is recommended in the pgctl.log when the database fails to start.
>
> #!/bin/bash
> # simple shmsetup script
> page_size=`getconf PAGE_SIZE`
> phys_pages=`getconf _PHYS_PAGES`
> shmall=`expr $phys_pages / 2`
> shmmax=`expr $shmall \* $page_size`
> echo kernel.shmmax = $shmmax
> echo kernel.shmall = $shmall
>
> Any pointers which would explain these differences would be greatly
> appreciate. Ultimately, I'd like to calculate the expected kernel.shmmax
> which matches postgresql's shmget() call.
>
I don't know where you got hold of this script, but it seems this is
setting SHMMAX equal to half the size of RAM. So if your system has
4GB RAM, a process can request maximum of 2GB of shared memory. The
amount of shared memory PostgreSQL needs is governed by several
configuration parameters, but the most important and the one which
will eat up most of that shared memory is "shared_buffers". So if you
want to work within the bounds of SHMMAX set by this script, you would
need to set shared_buffers a notch lower than that. But often its
easier and better to decide your Postgres configuration parameters and
then set SHMMAX to satisfy that.
Thanks,
Pavan
--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax
@ 2013-02-07 05:31 Mel Llaguno <mllaguno@coverity.com>
parent: Pavan Deolasee <pavan.deolasee@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Mel Llaguno @ 2013-02-07 05:31 UTC (permalink / raw)
To: Pavan Deolasee <pavan.deolasee@gmail.com>; +Cc: pgsql-admin
Pavan,
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.
Thanks,
Mel
________________________________________
From: Pavan Deolasee [pavan.deolasee@gmail.com]
Sent: Wednesday, February 06, 2013 10:12 PM
To: Mel Llaguno
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax
On Thu, Feb 7, 2013 at 6:28 AM, Mel Llaguno <mllaguno@coverity.com> wrote:
> All,
>
> I'm wondering about how postgresql calculates the value for shared buffers
> as I see some discrepancies with what the following script provides versus
> what is recommended in the pgctl.log when the database fails to start.
>
> #!/bin/bash
> # simple shmsetup script
> page_size=`getconf PAGE_SIZE`
> phys_pages=`getconf _PHYS_PAGES`
> shmall=`expr $phys_pages / 2`
> shmmax=`expr $shmall \* $page_size`
> echo kernel.shmmax = $shmmax
> echo kernel.shmall = $shmall
>
> Any pointers which would explain these differences would be greatly
> appreciate. Ultimately, I'd like to calculate the expected kernel.shmmax
> which matches postgresql's shmget() call.
>
I don't know where you got hold of this script, but it seems this is
setting SHMMAX equal to half the size of RAM. So if your system has
4GB RAM, a process can request maximum of 2GB of shared memory. The
amount of shared memory PostgreSQL needs is governed by several
configuration parameters, but the most important and the one which
will eat up most of that shared memory is "shared_buffers". So if you
want to work within the bounds of SHMMAX set by this script, you would
need to set shared_buffers a notch lower than that. But often its
easier and better to decide your Postgres configuration parameters and
then set SHMMAX to satisfy that.
Thanks,
Pavan
--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax
@ 2013-02-07 05:41 Pavan Deolasee <pavan.deolasee@gmail.com>
parent: Mel Llaguno <mllaguno@coverity.com>
1 sibling, 1 reply; 9+ messages in thread
From: Pavan Deolasee @ 2013-02-07 05:41 UTC (permalink / raw)
To: Mel Llaguno <mllaguno@coverity.com>; +Cc: pgsql-admin
On Thu, Feb 7, 2013 at 11:01 AM, Mel Llaguno <mllaguno@coverity.com> wrote:
> 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 is no easy way or at least none that I'm aware of, to get the
exact value of shared memory needed for Postgres. If you have access
to the source code, you can look at CreateSharedMemoryAndSemaphores()
function in src/backend/storage/ipc/ipci.c to see what all goes in
determining the size for shmget()
Thanks,
Pavan
--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax
@ 2013-02-07 05:43 Mel Llaguno <mllaguno@coverity.com>
parent: Pavan Deolasee <pavan.deolasee@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Mel Llaguno @ 2013-02-07 05:43 UTC (permalink / raw)
To: Pavan Deolasee <pavan.deolasee@gmail.com>; +Cc: pgsql-admin
Pavan,
Thanks. I'll have a look at the source code.
M.
________________________________________
From: Pavan Deolasee [pavan.deolasee@gmail.com]
Sent: Wednesday, February 06, 2013 10:41 PM
To: Mel Llaguno
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax
On Thu, Feb 7, 2013 at 11:01 AM, Mel Llaguno <mllaguno@coverity.com> wrote:
> 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 is no easy way or at least none that I'm aware of, to get the
exact value of shared memory needed for Postgres. If you have access
to the source code, you can look at CreateSharedMemoryAndSemaphores()
function in src/backend/storage/ipc/ipci.c to see what all goes in
determining the size for shmget()
Thanks,
Pavan
--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax
@ 2013-02-07 05:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mel Llaguno <mllaguno@coverity.com>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2013-02-07 05:49 UTC (permalink / raw)
To: Mel Llaguno <mllaguno@coverity.com>; +Cc: Pavan Deolasee <pavan.deolasee@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax
@ 2013-02-07 05:58 Mel Llaguno <mllaguno@coverity.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Mel Llaguno @ 2013-02-07 05:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pavan Deolasee <pavan.deolasee@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax
@ 2013-02-07 12:09 K P Manoj <kpmanojpg@gmail.com>
parent: Mel Llaguno <mllaguno@coverity.com>
0 siblings, 0 replies; 9+ messages in thread
From: K P Manoj @ 2013-02-07 12:09 UTC (permalink / raw)
To: Mel Llaguno <mllaguno@coverity.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pavan Deolasee <pavan.deolasee@gmail.com>; 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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax
@ 2013-02-07 13:31 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Mel Llaguno <mllaguno@coverity.com>
1 sibling, 0 replies; 9+ messages in thread
From: Scott Ribe @ 2013-02-07 13:31 UTC (permalink / raw)
To: Mel Llaguno <mllaguno@coverity.com>; +Cc: pgsql-admin
On Feb 6, 2013, at 5:58 PM, Mel Llaguno wrote:
> Any pointers which would explain these differences would be greatly appreciate.
Postgres is likely not the only thing on your system that allocates shared memory.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2013-02-07 13:31 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-02-07 00:58 PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax Mel Llaguno <mllaguno@coverity.com>
2013-02-07 05:12 ` Pavan Deolasee <pavan.deolasee@gmail.com>
2013-02-07 05:31 ` Mel Llaguno <mllaguno@coverity.com>
2013-02-07 05:41 ` Pavan Deolasee <pavan.deolasee@gmail.com>
2013-02-07 05:43 ` Mel Llaguno <mllaguno@coverity.com>
2013-02-07 05:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-02-07 05:58 ` Mel Llaguno <mllaguno@coverity.com>
2013-02-07 12:09 ` K P Manoj <kpmanojpg@gmail.com>
2013-02-07 13:31 ` Scott Ribe <scott_ribe@elevated-dev.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox