Re: SHMMAX / SHMALL Was (Re: postgresql-8.0.1 performance tuning)

От: Martin Fandel
Тема: Re: SHMMAX / SHMALL Was (Re: postgresql-8.0.1 performance tuning)
Дата: ,
Msg-id: 1117788544.4380.79.camel@fandelm.ecommit.de
(см: обсуждение, исходный текст)
Ответ на: Re: SHMMAX / SHMALL Was (Re: postgresql-8.0.1 performance  (Mark Kirkwood)
Ответы: Re: SHMMAX / SHMALL Was (Re: postgresql-8.0.1 performance  (Mark Kirkwood)
Список: pgsql-performance

Скрыть дерево обсуждения

postgresql-8.0.1 performance tuning  ("Martin Fandel", )
 Re: postgresql-8.0.1 performance tuning  (John A Meinel, )
  Re: postgresql-8.0.1 performance tuning  ("Martin Fandel", )
 Re: postgresql-8.0.1 performance tuning  (Cosimo Streppone, )
  Re: postgresql-8.0.1 performance tuning  (Mark Kirkwood, )
   Re: postgresql-8.0.1 performance tuning  (Cosimo Streppone, )
    Re: postgresql-8.0.1 performance tuning  (Mark Kirkwood, )
   SHMMAX / SHMALL Was (Re: postgresql-8.0.1 performance tuning)  (Paul McGarry, )
    Re: SHMMAX / SHMALL Was (Re: postgresql-8.0.1 performance  (Mark Kirkwood, )
     Re: SHMMAX / SHMALL Was (Re: postgresql-8.0.1 performance tuning)  ("Martin Fandel", )
      Re: SHMMAX / SHMALL Was (Re: postgresql-8.0.1 performance  (Mark Kirkwood, )
       Re: SHMMAX / SHMALL Was (Re: postgresql-8.0.1 performance tuning)  ("Martin Fandel", )
  Re: postgresql-8.0.1 performance tuning  ("Steinar H. Gunderson", )
   Re: postgresql-8.0.1 performance tuning  (Cosimo Streppone, )
   Re: postgresql-8.0.1 performance tuning  ("Martin Fandel", )
   Re: postgresql-8.0.1 performance tuning  ("Martin Fandel", )
    Re: postgresql-8.0.1 performance tuning  ("Martin Fandel", )
     Re: postgresql-8.0.1 performance tuning  ("Martin Fandel", )

Aah ok :)

I've set my values now as follow (2GB RAM):

SHMMAX=`cat /proc/meminfo | grep MemTotal | cut -d: -f 2 | awk '{print
$1*1024/3}'`
echo kernel.shmmax=${SHMMAX} >> /etc/sysctl.conf
SHMALL=`expr ${SHMALL} / 4096 \* \( 4096 / 16 \)`
echo kernel.shmall=${SHMALL} >> /etc/sysctl.conf

sysctl.conf:
kernel.shmmax=708329472
kernel.shmall=44270592

postgresql.conf:
max_connections=500
shared_buffers=40000 # ~312MB, min. 1000, max ~ 83000

best regards,
Martin


Am Freitag, den 03.06.2005, 18:11 +1200 schrieb Mark Kirkwood:
> Paul McGarry wrote:
>
> > Based on the powerpostgresql.com Performance Checklist [1]  and
> > Annotated Postgresql.conf [2] I understand that:
> > -I should have less than 1/3 of my total memory as shared_buffers
> > -For my server 15000 is a fairly reasonable starting point for
> > shared_buffers which is ~120MB
> > -I have 100 max_connections.
> >
> > So I was going to set SHMMAX to 134217728 (ie 128 Meg)
> >
> > What should SHMALL be?
> >
> > The current system values are
> > postgres@localhost:~/data$ cat /proc/sys/kernel/shmmax
> > 33554432
> > postgres@localhost:~/data$ cat /proc/sys/kernel/shmall
> > 2097152
> >
> > ie SHMALL seems to be 1/16 of SHMMAX
> >
>
> No - shmall is in 4k pages _ so this amounts to 8G! This is fine -
> unless you wish to decrease it in order to prevent too many shared
> memory applications running.
>
> BTW - the docs have been amended for 8.1 to suggest shmmax=134217728 and
> shmall=2097152 (was going to point you at them - but I cannot find them
> on the Postgresql site anymore...).
>
> There seems to be some longstanding confusion in the Linux community
> about the units for shmall (some incorrect documentation from Oracle on
> the issue does not help I am sure....) - to the point where I downloaded
> kernel source to check (reproducing here):
>
>
> linux-2.6.11.1/include/linux/shm.h:13->
>
> #define SHMMAX 0x2000000         /* max shared seg size (bytes) */
> #define SHMMIN 1             /* min shared seg size (bytes) */
> #define SHMMNI 4096          /* max num of segs system wide */
> #define SHMALL (SHMMAX/PAGE_SIZE*(SHMMNI/16)) /* max shm system wide
> (pages) */
> #define SHMSEG SHMMNI
>
>
> Hope that helps
>
> Best wishes
>
> Mark



В списке pgsql-performance по дате сообщения:

От:
Дата:
Сообщение: Re: Query plan for very large number of joins
От: Bruno Wolff III
Дата:
Сообщение: Re: BUG #1697: Select getting slower on continously updating data