Thread: Tunning FreeeBSD and PostgreSQL
Tried to search the list but the search wasn't working.
I have a server running strictly PostgreSQL that I'm trying to tune for performance. The specs are
2 X 2.4 Athlon MP processors
2G Reg DDR
FreeBSD 4.8 SMP kernel complied
PostgreSQL 7.3.3
4 X 80G IDE Raid 5
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.
Thanks
Stephen Howie
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/