Обсуждение: Tunning FreeeBSD and PostgreSQL

On Monday 14 Jul 2003 3:31 pm, Stephen Howie wrote:
[snip]
> My problem is that I have not totally put my head around the concepts of
> the shmmax, shmmaxpgs, etc....  As it pertains to my current setup and the
> shared mem values in postgresql.conf.  I'm looking for a good rule of thumb
> when approaching this.  Any help or direction would be greatly appreciated.

There are two articles recently posted here:

http://www.varlena.com/GeneralBits/

They should provide a good start.
--
  Richard Huxton

Richard-

That was very helpfull Thanks!
I still would like some guidance on tunning FreeBSD (shmmax and shmmaxpgs).
Do I need to even touch these settings?

Stephen Howie

>There are two articles recently posted here:
>
>http://www.varlena.com/GeneralBits/
>
>They should provide a good start.
>--
> Richard Huxton
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster

> I still would like some guidance on tunning FreeBSD (shmmax and
> shmmaxpgs).
> Do I need to even touch these settings?

Stephen- I have no idea what these are set to by default in FreeBSD, but
here's the page that covers changing it in the postgresql docs:

http://www.postgresql.org/docs/7.3/static/kernel-resources.html

-Nick

>>>>> "SH" == Stephen Howie <showie@centwire.com> writes:

SH> Richard-
SH> That was very helpfull Thanks!
SH> I still would like some guidance on tunning FreeBSD (shmmax and shmmaxpgs).
SH> Do I need to even touch these settings?

Here's what I use on FreeBSD 4.7/4.8.  The kernel settings don't hurt
anything being too large for the SHM values, since they are limits,
not anything pre-allocated (from my understanding).  These settings
allow for up to 100,000 shared buffers (I currently only use 30,000
buffers)


options         SYSVMSG                 #SYSV-style message queues

# only purpose of this box is to run PostgreSQL, which needs tons of shared
# memory, and some semaphores.
# Postgres allocates buffers in 8k chunks, so tell Postgres to use about
# 150 fewer than SHMMAXPGS/2 buffers to leave some room for other Postgres
# shared memory needs.
options         SYSVSHM                 #SYSV-style shared memory
# Maximum number of shared memory pages system wide.
options         SHMALL=262144
# Maximum size, in pages (4k), of a single System V shared memory region.
options         SHMMAXPGS=262144

# only need semaphores for PostgreSQL
options         SYSVSEM                 #SYSV-style semaphores
# Maximum number of System V semaphores that can be used on the system at
# one time.
options         SEMMNI=32
# Total number of semaphores system wide
options         SEMMNS=512
# Maximum number of entries in a semaphore map.
options         SEMMAP=256


Also, in /etc/sysctl.conf I put

# need lots of files for database
kern.maxfiles=8000
# tuning for PostgreSQL
kern.ipc.shm_use_phys=1



--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

Vivek,
Thanks,  for your reply.  May I ask what you system setup is like (i.e.
memory and such)?

----- Original Message -----
From: "Vivek Khera" <khera@kcilink.com>
Newsgroups: ml.postgres.performance
To: <pgsql-performance@postgresql.org>
Sent: Tuesday, July 15, 2003 11:44 AM
Subject: Re: [PERFORM] Tunning FreeeBSD and PostgreSQL


> >>>>> "SH" == Stephen Howie <showie@centwire.com> writes:
>
> SH> Richard-
> SH> That was very helpfull Thanks!
> SH> I still would like some guidance on tunning FreeBSD (shmmax and
shmmaxpgs).
> SH> Do I need to even touch these settings?
>
> Here's what I use on FreeBSD 4.7/4.8.  The kernel settings don't hurt
> anything being too large for the SHM values, since they are limits,
> not anything pre-allocated (from my understanding).  These settings
> allow for up to 100,000 shared buffers (I currently only use 30,000
> buffers)
>
>
> options         SYSVMSG                 #SYSV-style message queues
>
> # only purpose of this box is to run PostgreSQL, which needs tons of
shared
> # memory, and some semaphores.
> # Postgres allocates buffers in 8k chunks, so tell Postgres to use about
> # 150 fewer than SHMMAXPGS/2 buffers to leave some room for other Postgres
> # shared memory needs.
> options         SYSVSHM                 #SYSV-style shared memory
> # Maximum number of shared memory pages system wide.
> options         SHMALL=262144
> # Maximum size, in pages (4k), of a single System V shared memory region.
> options         SHMMAXPGS=262144
>
> # only need semaphores for PostgreSQL
> options         SYSVSEM                 #SYSV-style semaphores
> # Maximum number of System V semaphores that can be used on the system at
> # one time.
> options         SEMMNI=32
> # Total number of semaphores system wide
> options         SEMMNS=512
> # Maximum number of entries in a semaphore map.
> options         SEMMAP=256
>
>
> Also, in /etc/sysctl.conf I put
>
> # need lots of files for database
> kern.maxfiles=8000
> # tuning for PostgreSQL
> kern.ipc.shm_use_phys=1
>
>
>
> --
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> Vivek Khera, Ph.D.                Khera Communications, Inc.
> Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
> AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>

>>>>> "SH" == Stephen Howie <showie@centwire.com> writes:

SH> Vivek,
SH> Thanks,  for your reply.  May I ask what you system setup is like (i.e.
SH> memory and such)?

Current box is dual P3 1GHz and 2GB RAM.  RAID0+1 on 4 disks.  I'm
about to order a bigger box, since I'm saturating the disk bandwidth
as far as I can measure it.  I'm thinking along the lines of 8 disks
on RAID0+1 and 4GB RAM.  The CPUs twiddle their thumbs a lot, so no
point in really beefing that up.

Vivek Khera wrote:
> >>>>> "SH" == Stephen Howie <showie@centwire.com> writes:
>
> SH> Richard-
> SH> That was very helpfull Thanks!
> SH> I still would like some guidance on tunning FreeBSD (shmmax and shmmaxpgs).
> SH> Do I need to even touch these settings?
>
> Here's what I use on FreeBSD 4.7/4.8.  The kernel settings don't hurt
> anything being too large for the SHM values, since they are limits,
> not anything pre-allocated (from my understanding).  These settings
> allow for up to 100,000 shared buffers (I currently only use 30,000
> buffers)

I think the only downside to making them too big is that you allocate
page tables and prevent that address range from being used by other
processes.  Of course, if you have much less than 4 gigs of RAM in the
machine, it probably isn't an issue.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

>>>>> "BM" == Bruce Momjian <pgman@candle.pha.pa.us> writes:

>> not anything pre-allocated (from my understanding).  These settings
>> allow for up to 100,000 shared buffers (I currently only use 30,000
>> buffers)

BM> I think the only downside to making them too big is that you allocate
BM> page tables and prevent that address range from being used by other

Does this apply in general or just on FreeBSD?

BM> processes.  Of course, if you have much less than 4 gigs of RAM in the
BM> machine, it probably isn't an issue.

Probably, but wasting page table entries is never a good idea...

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

Vivek Khera wrote:
> >>>>> "BM" == Bruce Momjian <pgman@candle.pha.pa.us> writes:
>
> >> not anything pre-allocated (from my understanding).  These settings
> >> allow for up to 100,000 shared buffers (I currently only use 30,000
> >> buffers)
>
> BM> I think the only downside to making them too big is that you allocate
> BM> page tables and prevent that address range from being used by other
>
> Does this apply in general or just on FreeBSD?

Let me tell you how it traditionally worked  --- each process has the
kernel address space accessible at a fixed address --- it has to so the
process can make kernel calls and run those kernel calls in its own
address space, though with a kernel stack and data space.

What they did with shared memory was to put shared memory in the same
address space with the kernel, because everyone had that address range
mapped into their address space already.  If each process had its own
private copy of the kernel page tables, there is bloat in having the
kernel address space be larger than required.  However, if the kernel
page tables are shared by all processes, then there isn't much bloat,
just less addressable user memory, and if you don't have anything near 4
gigs of RAM, it isn't a problem.

I know Linux has pagable shared memory, and you can resize the maximum
in a running kernel, so it seems they must have abandonded the linkage
between shared page tables and the kernel.  This looks interesting:

    http://www.linux-tutorial.info/cgi-bin/display.pl?312&0&0&0&3

and the Contents on the left show additional info like the i386 virtual
directory/page tables:

    http://www.linux-tutorial.info/cgi-bin/display.pl?261&0&0&0&3

So it seems Linux has moved in the direction of making shared memory act
just like ordinary allocated memory, except it is shared, meaning I
think each process has its own pages tables for the shared memory.  Once
you do that, you get the ability to size it however you want, but you
lose shared page tables, and it can now be swapped out, which can be bad
for performance.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

>>>>> "BM" == Bruce Momjian <pgman@candle.pha.pa.us> writes:

BM> I know Linux has pagable shared memory, and you can resize the maximum
BM> in a running kernel, so it seems they must have abandonded the linkage
BM> between shared page tables and the kernel.  This looks interesting:

Thanks for the info.  You can resize it in FreeBSD as well, using the
sysctl command to set the various kern.ipc.shm* values.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/