pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SHMMAX problem - how much is needed?
6+ messages / 4 participants
[nested] [flat]

* SHMMAX problem - how much is needed?
@ 2007-11-13 20:00  Laszlo Nagy <gandalf@shopzeus.com>
  0 siblings, 2 replies; 6+ messages in thread

From: Laszlo Nagy @ 2007-11-13 20:00 UTC (permalink / raw)
  To: pgsql-admin


  Hi All,

I tried to follow the instructions that are described here:

http://www.postgresql.org/docs/8.1/interactive/kernel-resources.html#SHARED-MEMORY-PARAMETERS
http://www.postgresql.org/docs/8.1/interactive/kernel-resources.html#SYSVIPC

My values config are:

page size = 4096
BLCKSZ = 8
max_connections=50
max_prepared_transactions=5
shared_buffers=2048
wal_buffers=8
max_fsm_relations=3000
max_fsm_pages=450000

I'm using FreeBSD 6.2 and I have a test machine with 1GB memory. I would 
like to use 25% (256MB) as shared memory.

Here is what I have calculated (Python syntax, but probably everybody 
will understand):

shmmin = \
    parvalues['max_connections']*(400 + 
220*parvalues['max_locks_per_transaction']) +\
    parvalues['max_prepared_transactions']*(600 + 
220*parvalues['max_locks_per_transaction']) +\
    parvalues['shared_buffers']*parvalues['BLCKSZ']*8300 +\
    parvalues['wal_buffers']*parvalues['BLCKSZ']*8200 +\
    parvalues['max_fsm_relations']*70 +\
    parvalues['max_fsm_pages']*6+\
    500*1024 # From the docs: As a rough approximation, you can estimate 
the required segment size as 500 kB plus the variable amounts shown in 
the table.

The result for this was:

Minimum shared mem:  140731400 bytes = 135.0 MB
Maximum shared mem:  268435456 bytes = 256.0 MB

So far I put these values in /boot/loader.conf  (they were also 
calculated, I followed the recommendations in the docs):

kern.ipc.shmall="65536"
kern.ipc.shmmin="140731400"
kern.ipc.shmmax="268435456"
kern.ipc.shmseg="10"
kern.ipc.shmmni="30"
kern.ipc.semmni="66"
kern.ipc.semmns="372"
kern.ipc.semmsl="240"
kern.ipc.semmap="372"
kern.ipc.shm_use_phys="1"

After rebooted I got this message:

Nov 13 20:46:19 neptunus postgres[669]: [1-1] FATAL:  could not create 
shared memory segment: Invalid argument
Nov 13 20:46:19 neptunus postgres[669]: [1-2] DETAIL:  Failed system 
call was shmget(key=5432001, size=21725184, 03600).
Nov 13 20:46:19 neptunus postgres[669]: [1-3] HINT:  This error usually 
means that PostgreSQL's request for a shared memory segment exceeded 
your kernel's SHMMAX parameter.
Nov 13 20:46:19 neptunus postgres[669]: [1-4]  You can either reduce the 
request size or reconfigure the kernel with larger SHMMAX.  To reduce 
the request size (currently
Nov 13 20:46:19 neptunus postgres[669]: [1-5]  21725184 bytes), reduce 
PostgreSQL's shared_buffers parameter (currently 2048) and/or its 
max_connections parameter (currently
Nov 13 20:46:19 neptunus postgres[669]: [1-6]  50).
Nov 13 20:46:19 neptunus postgres[669]: [1-7]   If the request size is 
already small, it's possible that it is less than your kernel's SHMMIN 
parameter, in which case raising
Nov 13 20:46:19 neptunus postgres[669]: [1-8]  the request size or 
reconfiguring SHMMIN is called for.


What is wrong with my calculation?

The reason for this question: I'm writing a program that reads some 
input parameters (PostgreSQL and kernel configuration parameters), 
queries the computer for kernel page size, total available memory etc. 
and finally it calculates sysctl values.

This is not a toy: I have a server with memory problems and I would also 
like to find the optimal settings. (e.g. keep shared memory in physical 
memory, optimize for performance, but do not reserve more memory than 
needed). I would also like to contribute this program so other beginners 
will be able to set these parameters easily. My idea was that this 
program would warn me whenever there is a problem (e.g. SHMMIN > SHMMAX) 
but apparently I could not understand the basics and I failed to write 
the calculations.

Thanks,

   Laszlo




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: SHMMAX problem - how much is needed?
@ 2007-11-13 20:27  Steve Holdoway <steve.holdoway@firetrust.com>
  parent: Laszlo Nagy <gandalf@shopzeus.com>
  1 sibling, 1 reply; 6+ messages in thread

From: Steve Holdoway @ 2007-11-13 20:27 UTC (permalink / raw)
  To: pgsql-admin

On Tue, 13 Nov 2007 21:00:02 +0100
Laszlo Nagy <gandalf@shopzeus.com> wrote:

[snip]

What are postgres's ulimits???

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: SHMMAX problem - how much is needed?
@ 2007-11-13 20:57  Laszlo Nagy <gandalf@shopzeus.com>
  parent: Steve Holdoway <steve.holdoway@firetrust.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Laszlo Nagy @ 2007-11-13 20:57 UTC (permalink / raw)
  To: Steve Holdoway <steve.holdoway@firetrust.com>; pgsql-admin

Steve Holdoway wrote:
> On Tue, 13 Nov 2007 21:00:02 +0100
> Laszlo Nagy <gandalf@shopzeus.com> wrote:
>
> [snip]
>
> What are postgres's ulimits???
>   
Output of "cat /etc/login.conf" (part of it):

default:\
        :passwd_format=md5:\
        :copyright=/etc/COPYRIGHT:\
        :welcome=/etc/motd:\
        :setenv=MAIL=/var/mail/$,BLOCKSIZE=K,FTP_PASSIVE_MODE=YES:\
        :path=/sbin /bin /usr/sbin /usr/bin /usr/games /usr/local/sbin 
/usr/local/bin /usr/X11R6/bin ~/bin:\
        :nologin=/var/run/nologin:\
        :cputime=unlimited:\
        :datasize=unlimited:\
        :stacksize=unlimited:\
        :memorylocked=unlimited:\
        :memoryuse=unlimited:\
        :filesize=unlimited:\
        :coredumpsize=unlimited:\
        :openfiles=unlimited:\
        :maxproc=unlimited:\
        :sbsize=unlimited:\
        :vmemoryuse=unlimited:\
        :priority=0:\
        :ignoretime@:\
        :umask=022:


The login class of the user "pgsql" is not given, so it must be the default.

Regards,

   Laszlo




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: SHMMAX problem - how much is needed?
@ 2007-11-13 21:08  Peter Koczan <pjkoczan@gmail.com>
  parent: Laszlo Nagy <gandalf@shopzeus.com>
  1 sibling, 1 reply; 6+ messages in thread

From: Peter Koczan @ 2007-11-13 21:08 UTC (permalink / raw)
  To: Laszlo Nagy <gandalf@shopzeus.com>; +Cc: pgsql-admin

> Minimum shared mem:  140731400 bytes = 135.0 MB
> Maximum shared mem:  268435456 bytes = 256.0 MB
>
> kern.ipc.shmmin="140731400"
> kern.ipc.shmmax="268435456"
>
> Nov 13 20:46:19 neptunus postgres[669]: [1-2] DETAIL:  Failed system
> call was shmget(key=5432001, size=21725184, 03600).

The shared memory request, according to the error message, was for ~20
MB, far less than the 135 MB minimum limit you put in the kernel.
You'll need to lower the limit or raise your request size, probably
with regards to shared_buffers, as I think you're only allocating 2 MB
to shared_buffers. You'll want to raise that on a production machine.



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: SHMMAX problem - how much is needed?
@ 2007-11-13 21:20  Laszlo Nagy <gandalf@shopzeus.com>
  parent: Peter Koczan <pjkoczan@gmail.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Laszlo Nagy @ 2007-11-13 21:20 UTC (permalink / raw)
  To: Peter Koczan <pjkoczan@gmail.com>; +Cc: pgsql-admin

Peter Koczan wrote:
>> Minimum shared mem:  140731400 bytes = 135.0 MB
>> Maximum shared mem:  268435456 bytes = 256.0 MB
>>
>> kern.ipc.shmmin="140731400"
>> kern.ipc.shmmax="268435456"
>>
>> Nov 13 20:46:19 neptunus postgres[669]: [1-2] DETAIL:  Failed system
>> call was shmget(key=5432001, size=21725184, 03600).
>>     
>
> The shared memory request, according to the error message, was for ~20
> MB, far less than the 135 MB minimum limit you put in the kernel.
>   
It means that I really did not know what it means. What are the 
disadvantages of reducing shmmin to one?

> You'll need to lower the limit or raise your request size, probably
> with regards to shared_buffers, as I think you're only allocating 2 MB
> to shared_buffers. You'll want to raise that on a production machine.
>   

I used shared_buffers=2048 which is 2048*8192 bytes = 16MB. The 
documentation tells that each buffer is 8K (more precisely, BLCKSZ) and 
it also tells that some thousand is good for a production environment.

shared_buffers (integer)

Sets the number of shared memory buffers used by the database server. 
The default is typically 1000, but may be less if your kernel settings 
will not support it (as determined during initdb). Each buffer is 8192 
bytes, unless a different value of BLCKSZ was chosen when building the 
server. This setting must be at least 16, as well as at least twice the 
value of max_connections 
<http://www.postgresql.org/docs/8.1/interactive/runtime-config-connection.html#GUC-MAX-CONNECTIONS;; 
however, settings significantly higher than the minimum are usually 
needed for good performance. Values of a few thousand are recommended 
for production installations. This option can only be set at server start.




Well, this is a test server and I'm using 4096 on a production server 
but now I'm confused. What should be a good value for this?

Thanks,

   Laszlo




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: SHMMAX problem - how much is needed?
@ 2007-11-14 01:22  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Laszlo Nagy <gandalf@shopzeus.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2007-11-14 01:22 UTC (permalink / raw)
  To: Laszlo Nagy <gandalf@shopzeus.com>; +Cc: Peter Koczan <pjkoczan@gmail.com>; pgsql-admin

Laszlo Nagy <gandalf@shopzeus.com> writes:
> It means that I really did not know what it means. What are the 
> disadvantages of reducing shmmin to one?

None that I've ever heard --- that is the customary setting.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2007-11-14 01:22 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-13 20:00 SHMMAX problem - how much is needed? Laszlo Nagy <gandalf@shopzeus.com>
2007-11-13 20:27 ` Steve Holdoway <steve.holdoway@firetrust.com>
2007-11-13 20:57   ` Laszlo Nagy <gandalf@shopzeus.com>
2007-11-13 21:08 ` Peter Koczan <pjkoczan@gmail.com>
2007-11-13 21:20   ` Laszlo Nagy <gandalf@shopzeus.com>
2007-11-14 01:22     ` Tom Lane <tgl@sss.pgh.pa.us>

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